crazysam Posted October 21, 2008 Share Posted October 21, 2008 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. Quote Link to comment Share on other sites More sharing options...
grim1208 Posted October 21, 2008 Share Posted October 21, 2008 Could you give an example of what you mean by putting it back into the database? $str_one."|".$str_two ... Quote Link to comment Share on other sites More sharing options...
johntp Posted October 21, 2008 Share Posted October 21, 2008 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 Quote Link to comment Share on other sites More sharing options...
crazysam Posted October 21, 2008 Author Share Posted October 21, 2008 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) Quote Link to comment Share on other sites More sharing options...
crazysam Posted October 21, 2008 Author Share Posted October 21, 2008 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. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted October 21, 2008 Share Posted October 21, 2008 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 } ?> Quote Link to comment Share on other sites More sharing options...
crazysam Posted October 22, 2008 Author Share Posted October 22, 2008 $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. Quote Link to comment Share on other sites More sharing options...
haku Posted October 22, 2008 Share Posted October 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
crazysam Posted October 22, 2008 Author Share Posted October 22, 2008 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.