MYSQL Adding old column as rows in new table

Question

I have new table in database to track tags, like so:

    TAG NAME | IMAGE ID 

    funny       570
    funny       571
    dog         649

and I have old table to track tags, like so:

TAG ID | TAG NAME | IMAGES IDS

11       funny       570,571,572,573,574,575,576,577,578,579,580,581,58...
12       dog         649,650,651,652,653,654,655,656,657,658,659,660,66...
13       cat         844,845,846,847,848,849,850,851,852,853,854,855,85...

I need to add all the old image IDs / associated tags to new table

Is there any limit on the number of IDs that can be associated with a tag? Perhaps, it seems lot easier to do it using PHP instead of writing SQL query to do the same. — Srinivas, Jan 25 '13 at 03:53
php? you mean get the Image IDs and do a loop through the array, inserting each one? — bushdiver, Jan 25 '13 at 03:55
Yes. Check [this](http://stackoverflow.com/questions/1096679/can-mysql-split-a-column), mysql doesn't provide for out of the box split function which is required to split tags. — Srinivas, Jan 25 '13 at 03:56
Check this [blog](http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/), achieves the said effect in mysql using stored procedure. — Srinivas, Jan 25 '13 at 03:58

score 1 · Accepted Answer · answered Jan 25 '13 at 03:57

If i am not wrong you want to add old table record to new table.

So all you need to do is query old table and explode imgid and loop through exploded string and add it in new table.

Sample Code.

<?php

    $query = mysqli_query("SELECT * FROM oldTable")or die(mysqli_error());
    while($row = mysql_fetch_assoc($query))
    {
        $ids = explode(",",$row['imageID']);

        foreach($ids as $newImgId)
        {
            $Insert_query = mysqli_query("INSERT INTO NEWTABLE ('tagname','img_id') VALUES ('".$row['tag']."','".$newImgId."')")or die(mysqli_error());
        }
    }

score 1 · Answer 2 · answered Jan 25 '13 at 03:57

You have to fetch each row and loop through and insert like below.

Fetch the 1st row from the database.
Use explode to explode image ids into an array.
Loop through the exploded array and insert them with respective tag name.

foreach()

{

//this loop with take care of the records

//loop till the records ends

//exploded here

foreach()

{

//loop through and insert the exploded array to database

}

}

MYSQL Adding old column as rows in new table

2 Answers2