Explode and Save? Array Issue

[crazysam]

Thank you for reading my post! I hope that you have a kind heart and decide to assist me. This is a PHP question about a MySQL problem, so I wasn't sure where to go to post this.

 

I'm kind of new to coding, but I'm working on an extension to update an old script. I have a lot of data that is saved in a format that I really don't like.

 

Okay, the old database structure had two columns, one with userid, and the other with an imploded array separated by '|'. On the left, a person's userid number, and on the right, all their favorite stories on the site.

 

id

favs

45

105|81

 

What I want to do is break up these arrays so the table looks like this:

 

id

favs

45

105

45

81

 

My problem is that I'm not sure how to convert the existing data

 

while($row = mysql_fetch_array($result)){
echo $row['uid']."<br />";
print_r (explode("|",$row['favs]));
echo "<br />";
}

 

That will print what I want to do, but I don't know how to get that into the database.

[grim1208]

Could you give an example of what you mean by putting it back into the database?

 

$str_one."|".$str_two ...

[johntp]

i beleive you want to do this.

 

$test1= explode("|",$row['favs]);
$test= $test1[1];

echo "$test";

 

change the 1 to 2 if it's not displaying the right side of the | that you want.and then to put it in the database use $test

[crazysam]

I mean saving an id with separate rows for each fav id.

 

I can print it out with the id, and then the values broken apart, but I don't know how to structure an SQL query so that item in the array is a separate item in the database.

 

45

Array ( [0] => 105 [1] => 81 )

 

I guess for this, I would want a query that ultimately resulted in something like this:

INSERT INTO FAVTABLE (uid, favs) VALUES (45,105), (45,81)

 

 

[crazysam]

Thanks so much for the replies!

 

$test1= explode("|",$row['favs]);
$test= $test1[1];

echo "$test";

 

I'm getting blank output for this for some reason.

[DarkWater]

That's because he missed a ' in the array key.  You'd probably end up using something like this:

 

<?php
foreach (explode('|', $rows['favs']) as $fav) {
   //do insert query with $fav
}
?>

[crazysam]

$conn = mysql_connect('localhost', "root", "");
if (!conn){
echo "Unable to connect to MySQL server <br>";
echo "Error" . mysql_errno() . "-" . mysql_error();
exit;
}

if (!mysql_select_db("test")){
echo "Unable to select database <br>";
exit;
}
$query = "SELECT uid from favtable"; 

$result = mysql_query($query) or die(mysql_error());
/*foreach (explode('|', $rows['favs']) as $fav) {
   echo $row['uid']."<br />";
echo;

}*/
$uids = mysql_fetch_array($result);
foreach ($uids as $uid){
$query = "SELECT favs from favtable where uid = $uid"; 
$result = mysql_query($query) or die(mysql_error());
$test1= explode("|",$row['favs']);
$test= $test1[1];

echo "$test";

}

 

Let's just say I wanted something really basic, like:

(uid, fav)

 

So, for this row:

45 105|81

 

I would have this output:

(45,105)

(145,81)

 

Thank you all so much for the help. I'm just having a hard time getting around this.

[haku]

while($row = mysql_fetch_array($result))
{
   $uid = $row['uid']
   $favs = explode('|', $row['favs']);
   $count = count($favs);
   for($i = 0;$i < $count; $i++)
   {
      $query = "INSERT INTO table (uid, fav) VALUES ('{$uid}', '{$favs[$i]}');
      mysql_query($query);
   }
}

 

Or something like that. I didn't test it, but it should get you on the right path. BUT, you should enter all the data into a separate table from the one you are pulling it out of, or else you are going to end up with both the old data and the new data in the table.

[crazysam]

First of all, thank you all so much! This has definitely helped me solve my problem!

 

echo "CREATE TABLE IF NOT EXISTS `favstable` (<br />
  `uid` int(11) NOT NULL,<br />
  `fav` text NOT NULL<br />
) ENGINE=MyISAM DEFAULT CHARSET=latin1;<br />
INSERT INTO favestable (uid, fav) VALUES";

while($row = mysql_fetch_array($result))
{
   $uid = $row['uid'];
   $favs = explode('|', $row['favs']);
   $count = count($favs);
   for($i = 0;$i < $count; $i++)
   { 
  echo "({$uid}, '{$favs[$i]}')".","."<br />";

   }
   

 

This creates a perfect export file I can simply import, which is good because some of the data moves from localhost to live server.

 

Thank you so, so, so much!