Mugluck Mugluck - 16 days ago 8
MySQL Question

How do I move this exploded data into another table? Laravel 5.3

I'm doing a database refactor at the moment to move data into pivot tables and create proper relationships. It's an old database, so there's some interesting choices of storage... Something like this:

enter image description here

Yeah it's good fun. Anyway, I'm still a bit shakey on how laravel does it's objects and arrays, which still seem like madness to me. So I have this query here, which is one half:

public function move() {

$prefs = DB::table('users')->select('id', 'Preferences')->where('Preferences', '!=', '')->get();

foreach ($prefs as $pref) {
$tags = $pref->Preferences;
$tag = explode(',', $tags);
print_r('$tag');
}


[Solved minor issue]Right now print_r just prints $tag$tag$tag$tag. I know it's got something to do with me messing up my understanding of how laravel works with arrays. So how to do I do this properly?

My goal is to go through each user, grab their tags, explode those tags, then print them out to the pivot table (tag_user) beside the user_id.

Note: The tags are contained in the preferences collumn, which is shown in the above image.

Edit:

The table for storing the tags will look something like this:

id | tag_id | user_id

Since there's a lot more refactoring to do, I'll have to figure out how to handle the links next.

Answer

You need to make model for user_tag table in console php artisan make:model Tag. In model:

    class Tag extends Model
{
protected $table = 'tag_user'; //this is name of table in your DB
protected $fillable = ['user_id','tag_id']; //this for mass assingment
...
...
...
}

And then, for inserting new row:

public function move() {

        $prefs = DB::table('users')->select('id', 'Preferences')->where('Preferences', '!=', '')->get();

            foreach ($prefs as $pref) {
                 $tags = $pref->Preferences;
                 $tag = explode(',', $tags);
                 foreach ($tag as $t) {
                     $taguser = new Tag(array(
                                       'user_id' => $pref->id,
                                       'tag_id' => $t
                                      ));
                     $taguser->save(); //save in table
                 }
            }
}