linking two tables from a single form.

Following article elaborates more on your issue http://fideloper.com/laravel-multiple-database-connections

Thanks highnoon. Even though schools is a separate table do I treat as a database? So in the config file would it look like this?

'connections' => [

        'sqlite' => [
            'driver' => 'sqlite',
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
        ],

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'schools',
            'username' => 'root',
            'password' => 'secret',
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

Then in my model it would be

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Member extends Model
{
    protected $fillable = ['name', 'email'];

    protected $connection = 'mysql2';

    public function schools()
    {

      return $this->belongsToMany(School::class);
    }
    public function setFirstNameAttribute($value)
    {
    // do stuff
    $this->attributes['member_id'] = $value;
    }

}

this is the error I am getting

SQLSTATE[HY000] [1049] Unknown database 'schools' (SQL: insert into `members` (`name`, `email`, `updated_at`, `created_at`) values (Anthony Davison, [email protected], 2017-05-26 12:46:28, 2017-05-26 12:46:28))

@amidabrian

Considering you updated the question you might want to carefully examine the error stating member_id doesnt have a default value. Foreign- or primary Keys generally dont have a default value. It looks like member_id doesnt have a value while the database schema indicaties it should have a default value?

@highnoon I have set the a default for the member_id.

public function up()
    {
        Schema::create('schools', function (Blueprint $table) {
            $table->increments('id');

            $table->integer('member_id')->default(1);

            $table->string('school');

            $table->timestamps();
        });
    }

It does not give me an error but I am not sure it is linking the two tables together. As the member_id does not increment by one each time I enter a member. Both entries show a member_id of one. Thanks for all your help @highnoon.

public function schools()
    {

      return $this->belongsToMany(School::class);
    }

should be something like return $this->belongsToMany('App/Model/YourClass');

or whatever namespace your class has

This article explains more about eloquent relationships: https://laravel.com/docs/5.4/eloquent-relationships

@highnoon my member model does say this.

Hey @amidabrian we need to see your controller. Can you please share the controller code?

@st8113 Here is my controller:

<?php

namespace App\Http\Controllers;

use App\Member;

use App\School;

use Illuminate\Support\Facades\Input;

use Illuminate\Http\Request;

class MembersController extends Controller
{
    public function index()
    {

      $members = Member::all();

      return view('index', compact('members'));
      //return view('create');
    }

    public function show(Member $member)
    {
        //return $member;
      // $member = Member::find($id);

      return view('show', compact('member'));

    }
    public function create()
    {
        //return $member;
      // $member = Member::find($id);
      

       return view('create');

    }
    public function store()
    {
      // create a new member using the request Data
        //dd($request->all());
      //   $member = new Member;
      // //
      //   $member->name = request('name');
      // //
      //   $member->email = request('email');
      // //
      //   $member->school= request('school');
      $this->validate(request(),[
        'name' => 'required',

        'email' => 'required',

        'school'=> 'required'
      ]);

        Member::create([
          'name' => request('name'),
          'email' => request('email')
        ]);
        School::create([
          'school' => request('school')
        ]);
      // // save it to the Database
      // $member->save();
      //
      // // and then redirect to the home page.
       return redirect('/members');

    }

}

What do you want to accomplish exactly?

if each members can be part of several schools you will need a pivot table with a belongsToMany relation on both models.

but if each members can only be part of one school, your foreign key should be on the user table not the school one. Then your relationship will be Members -> belongsTo school and school -> hasMany members and you controller should look something like this :

        Member::create([
          'name' => request('name'),
          'email' => request('email'),
      'school_id' => request('school')
        ]);

ps : with the way your form look i assume you don't actually want to create a new school just bind a new member to a existing one.

edit : also in your form, the value field of your options should be the school's id not the name

@marthz basically this is what I want to achieve- I want to build a web app that allows someone to add a new member with the fields “Name”, “Email Address” and "School" (selected from a list). The web app should display all members for a selected school. Each member can be associated with 1 or more school. What might be the best way to approach this?

Okay so if each member can be associated with multiple schools you will need a pivot table between the two (if you have no idea what this is, look up "pivot table" or "many to many relationship" on google)

Then your models will both have a belongsToMany relationship pointing to the other.

Also for your front end you should look at something like Select2 to easily select multiple schools when creating a member. (you can achieve it just by adding the "multiple" tag to your select, it's fine when you are creating your app but pretty crappy UX wise)

@marthz Thanks for your help. I will look into this.

@marthz I do have one more question. I was looking at Jeffreys video on how to this. I kinda of follow the backend; but I don't understand where he got the Tags. It's pretty much what I want to do but how do you add the on the front end. I have a form for the name and email address.

@amidabrian I'm not sure what you're asking exactly here, but i would suggest that you watch the video again and follow along with Jeff.

Pause the video and understand what you're typing at each steps, don't just copy the code because that's a core concept you'll have to understand.

@marthz thanks for all your help. My question is related to the many to many relationships in Jeffreys video. I see how he associated the tags with the posts in Sequel Pro but how is this done on the front end? Do you create a new blade file with a form and <form method="POST" action="/members/schools/{school}" > ?

@marthz I think I might have figured this out.

Good job man!

I suggest you toy around a bit with the attach/detatch and sync that laravel provides so you'll be able to manipulate your data easily.

@marthz Ah, that was not what I had in mind. I was thinking of creating a schools.blade.php file and including that in the form.

<form method="POST" action="/members/schools/{$school->id}">
<select class="form-group" id="school" name="school">
    <option>Rodean</option>
    <option>Eton</option>
    <option>Waldergrave</option>
    <option>Stowe</option>
    <option>Licensed Victuallers</option>
</select>
</form>

I am not sure on the attach/detatch and sync.

@marthz That did not work.

@marthz are you able to give me a clue as to how this might work?

Yeah a couple problems there :

<form method="POST" action="/members/schools/{$school->id}">

is this a creatind a editing form? cause creating form cant really pass an in if it doesn't exists yet and editing should get a patch or put method field.

<select class="form-group" id="school" name="school">

your select need to have the multiple attribute and the name should be name="schools[]"

Also your options should come from the database and have their id as their value attribute.

as for attach/detatch/sync they are the methods to manipulate manyToMany relationships

attach will add one or several relations

detatch will remove one or several relations

sync will take an array of id's and will sync (duh) the relation so that it contains only the id's you passed to it.

@marthz I have gone over Jeffreys video about a hundred times and can't figure out how this can be implemented. Any help would be much appreciated.

Can you post your controller? and any error you get?

Here is the controller I have for Schools. The issue I have how do get the front-end to replicate what he did in sequel pro?

<?php

namespace App\Http\Controllers;

use App\School;

use Illuminate\Http\Request;


class SchoolsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(School $school)
    {
        $members = $school->members;

        return view('index', compact('members'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}

Well your controller is quite empty, can't really help you much with that, but when you say " how do get the front-end to replicate what he did in sequel pro" what are you talking about exactly? displaying all the members attached to the school?

@marthz Basically what I want is the user to go to the form and input his name, email address and school (if that is possible). Or do they put their name and email address in the form and then submit. Then afterward they can add the school in another form? My current form is like this:

@extends('layout')

@section('content')

  <h1>Create a Member</h1>
  <form method="POST" action="/members">
    {{ csrf_field() }}

    <div class="form-group">
    <label for="name">Name</label>
    <input type="text" class="form-control" id="name" name="name">
    </div>

    <div class="form-group">
          <label for="email">email</label>
          <textarea id="email" name="email" class="form-control"></textarea>
    </div>

    

     @include('layouts.errors')



  <button type="submit" class="btn btn-primary">Submit</button>
</form>

@endsection

No you can do it in one go, you just have to supply the user with a list a schools from your database that you grab in your create method.

then your select will look something like this :

<select id="schools" name="schools[]" multiple>
    @foreach($schools as $school)
    <option value="{{ $school->id }}">{{ $school->name }}</option>
    @endforeach 
</select>

ps : they way you're describring it you're look to attach ONLY ONE school to a member, in this case you don't need a many To Many relation, so do you want your members to have multiple schools attached to each of them or not?

@marthz Yes, I do want them to have multiple schools. Forgive me for asking a stupid question. By the way, you have been a great help. How do include a list of schools in a database in the project? So the above code I can add to the form? Plus, will this not affect my routes?

Route::get('/', 'MembersController@index');

Route::get('/members/create', 'MembersController@create');

Route::post('/members', 'MembersController@store');

Route::get('/members/{member}', 'MembersController@show');

Route::get('/members/schools/{school}', 'SchoolsController@index');

Route::post('/members/{member}/comments', 'CommentsController@store');

Don't worry about stupid questions, you're learning^^

So, no it won't modify your routes as for including a list a school in your database, well first you can add some manually in mysql to learn how to manipulate the relationships, then later create an admin page that will be able to add new schools in your app.

You could allow the users to add new schools themselves, it might be what your want for your project, but it's generally not advisable.