Fearpig Posted May 5, 2013 Share Posted May 5, 2013 Hello,I need help updating multiple rows at once Table Columns:Model_ID, Image I want to update all rows where the image field is empty to have a value of: 'data/Products/' . $model . '.jpg' I can update one row at a time but I want to upload things in a batch! I've written a select page <html> <body> <?php //Conntect to Database include 'Database_Connection.php'; if ($db_found) { //print "<i>Database Found (" . $db_handle.")</i></br></br>"; $SQL = "SELECT * FROM `oc_product` WHERE image = ''"; $result = mysql_query($SQL); $x = 1; echo "<table class='Data'><thead><tr><th>Model</th><th>Image</th><th>Image Should Be...</th></tr></thead><tbody>"; while ($db_field = mysql_fetch_assoc($result)){ $model = $db_field['model']; $image = $db_field['image']; $image_should_be = 'data/Products/' . $model . '.jpg'; if ($x < 0){ $Table_Row = 'Row_Style1'; } else { $Table_Row = 'Row_Style2'; } print " <tr class='$Table_Row'> <td>$model</td> <td>$image</td> <td>$image_should_be</td> </tr>"; $x = $x * (-1); } echo "</table>"; } else { echo "Whoops!"; } echo "<a href=Update_Images.php>Update</a>"; ?> </body> </html> My database connection is fine as the user has the correct permissions but when I try to update the images I'm getting something wrong and the records are not updating or giving me an error message. My idea was to loop through the records using a select query and then update each record. <html> <body> <?php //Conntect to Database include 'Database_Connection.php'; if ($db_found) { //print "<i>Database Found (" . $db_handle.")</i></br></br>"; $SQL = "SELECT * FROM `oc_product` WHERE image = ''"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)){ $model = $db_field['model']; echo "$model<br>"; $image_should_be = 'data/Products/' . $model . '.jpg'; $SQL_UPDATE = "UPDATE `oc_product` SET image = '$image_should_be' WHERE model = $model"; $result_update = mysql_query($SQL_UPDATE); } } else { echo "Whoops!"; } echo "<a href=test.php>Test</a>"; ?> </body> </html> I added in the... echo "$model<br>"; ...just to see if the page was doing anything and I now get a list of model numbers but no updates in the table. Quote Link to comment https://forums.phpfreaks.com/topic/277680-update-multiple-rows/ Share on other sites More sharing options...
Barand Posted May 5, 2013 Share Posted May 5, 2013 $image_should_be = 'data/Products/' . $model . '.jpg'; mysql_query("UPDATE `oc_product` SET image = '$image_should_be' WHERE image = '' "); Quote Link to comment https://forums.phpfreaks.com/topic/277680-update-multiple-rows/#findComment-1428484 Share on other sites More sharing options...
Fearpig Posted May 5, 2013 Author Share Posted May 5, 2013 Thank you Barand! I've been looking at that for hours scratching my head, tested and all working. Quote Link to comment https://forums.phpfreaks.com/topic/277680-update-multiple-rows/#findComment-1428490 Share on other sites More sharing options...
Solution Barand Posted May 5, 2013 Solution Share Posted May 5, 2013 I meant to post this - you could do the whole table with a single query with mysql_query("UPDATE `oc_product` SET image = CONCAT('data/Products/' , model, '.jpg') WHERE image = '' "); Quote Link to comment https://forums.phpfreaks.com/topic/277680-update-multiple-rows/#findComment-1428492 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.