dodgeitorelse3 Posted March 10, 2013 Share Posted March 10, 2013 (edited) I have written some code to update last 10 newest maps in my database that are decided by the date in a column named released. I have written the code to show me step by step what is happening. However the only row that gets updated is the 10th one. I first show what rows are labeled New in db, then the code clears all those rows. It then shows me a list of the 10 rows to be updated and then shows the rows that have been updated. this is the code for this.... <html> <head> <title>Update 10 newest maps in database</title> </head> <body> <?php // show maps marked as new $conn = mysql_connect('localhost','xxx','xxx'); if(!$conn) die("Failed to connect to database!"); $status = mysql_select_db(americas_army, $conn); if(!$status) die("Failed to select database!"); // show maps marked as new $sql_current = "select content, released, Newest from maps WHERE Newest = 'New' ORDER BY released desc"; $result_current = mysql_query($sql_current); $num_rows = mysql_num_rows($result_current); $i=1; echo '<font size=3>These are the current maps marked as new in database</font>'; echo '<table>'; echo '<thead>'; echo '<tr>'; echo '<th bgcolor="transparent" align="left"><font color="Black">Status</font></th>'; echo '<th width="160" bgcolor="transparent" align="center"><font color="black">Released</font></th>'; echo '<th width="180" bgcolor="transparent" align="center"><font color="black">Content</font></th>'; echo '</tr>'; echo '</thead>'; while($rows_current=mysql_fetch_assoc($result_current)){ echo "<tr><td align='left'><font color='black'><blink>".$rows_current[Newest]."</blink></font></td><td align='left'><font color='black'><b>".$rows_current[released]."</b></font></td><td align='center'><font color='black'>".$rows_current[content]."</font></td></tr>"; } echo '</table>'; //clear New from Newest column //clear New from Newest column $sql_clear = "UPDATE maps ". "SET Newest = '' "; mysql_select_db('americas_army'); $retval = mysql_query( $sql_clear, $conn ); if(! $retval ) { die('<br />Could not enter New data in Newest column: ' . mysql_error()); } echo "<br />Cleared New data in Newest column successfully\n"; // show any fields with New left in Newest column $sql_cleared_results = "select content, released, Newest from maps WHERE Newest = 'New' ORDER BY released desc"; $result_cleared_results = mysql_query($sql_cleared_results); $num_rows = mysql_num_rows($result_cleared_results); $i=1; echo '<br /><br /><font size=3>These are the current maps marked as new that were not cleared to prepare for adding New to last 10 maps (should show none)</font>'; echo '<table>'; echo '<thead>'; echo '<tr>'; echo '<th bgcolor="transparent" align="left"><font color="Black">Status</font></th>'; echo '<th width="160" bgcolor="transparent" align="center"><font color="black">Released</font></th>'; echo '<th width="180" bgcolor="transparent" align="center"><font color="black">Content</font></th>'; echo '</tr>'; echo '</thead>'; while($rows_cleared_results=mysql_fetch_assoc($result_cleared_results)){ echo "<tr><td align='left'><font color='black'><blink>".$rows_cleared_results[Newest]."</blink></font></td><td align='left'><font color='black'><b>".$rows_cleared_results[released]."</b></font></td><td align='center'><font color='black'>".$rows_cleared_results[content]."</font></td></tr>"; } echo '</table>'; // show newest 10 maps to be marked as new $sql_to_be_marked_new = "select content, released, Newest from maps ORDER BY released desc limit 10"; $result_to_be_marked_new = mysql_query($sql_to_be_marked_new); $num_rows = mysql_num_rows($result_to_be_marked_new); $i=1; echo '<br /><br /><font size=3>These are the 10 maps to be marked as new in database</font>'; echo '<table>'; echo '<thead>'; echo '<tr>'; echo '<th bgcolor="transparent" align="left"><font color="Black">Status</font></th>'; echo '<th width="160" bgcolor="transparent" align="center"><font color="black">Released</font></th>'; echo '<th width="180" bgcolor="transparent" align="center"><font color="black">Content</font></th>'; echo '</tr>'; echo '</thead>'; while($rows_to_be_marked_new=mysql_fetch_assoc($result_to_be_marked_new)){ echo "<tr><td align='left'><font color='black'><blink>".$rows_to_be_marked_new[Newest]."</blink></font></td><td align='left'><font color='black'><b>".$rows_to_be_marked_new[released]."</b></font></td><td align='center'><font color='black'>".$rows_to_be_marked_new[content]."</font></td></tr>"; $sql_add_new = "UPDATE maps ". "SET Newest = 'New' ". "WHERE released = '".$rows_to_be_marked_new[released]."'" ; } echo '</table>'; $last_ten_results_to_be_marked_as_new[] = "$rows_to_be_marked_new[Newest]"; //Update New from 10 most recent form released column while($rows_to_be_marked_new=mysql_fetch_assoc($result_to_be_marked_new)){ $sql_add_new = "UPDATE maps ". "SET Newest = 'New' ". "WHERE released = '".$rows_to_be_marked_new[released]."'" ;} mysql_select_db('americas_army'); $retval = mysql_query( $sql_add_new, $conn ); if(! $retval ) { die('<br /><br />Could not add New data in Newest column: ' . mysql_error()); } echo "<br /><br />Added New data in Newest column successfully\n"; // get 10 newest maps by released column $sql_new_results = "select content, released, Newest from maps ORDER BY released desc limit 10"; $result_new_results = mysql_query($sql_new_results); $num_rows = mysql_num_rows($result_new_results); $i=1; echo '<br /><br /><font size=3>These are the newest 10 maps marked as new </font>'; echo '<table>'; echo '<thead>'; echo '<tr>'; echo '<th bgcolor="transparent" align="left"><font color="Black">Status</font></th>'; echo '<th width="160" bgcolor="transparent" align="center"><font color="black">Released</font></th>'; echo '<th width="180" bgcolor="transparent" align="center"><font color="black">Content</font></th>'; echo '</tr>'; echo '</thead>'; while($rows_new_results=mysql_fetch_assoc($result_new_results)){ echo "<tr><td align='left'><font color='black'><blink>".$rows_new_results[Newest]."</blink></font></td><td align='left'><font color='black'><b>".$rows_new_results[released]."</b></font></td><td align='center'><font color='black'>".$rows_new_results[content]."</font></td></tr>"; } echo '</table>'; mysql_close(); ?> This is the screen shot of what I see on page when code is executed. Any ideas why it only updates the one row? sorry had the code to update rows commented out, removed comment marks. Edited March 10, 2013 by dodgeitorelse3 Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/ Share on other sites More sharing options...
dodgeitorelse3 Posted March 10, 2013 Author Share Posted March 10, 2013 would I need to use a foreach instead of doing the update in the while statement? Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1417825 Share on other sites More sharing options...
jcbones Posted March 10, 2013 Share Posted March 10, 2013 Ideally what you need to do is create arrays that hold the id's of the rows to be updated, then do 1 update query. while($row_to_be_marked_new = mysqli_fetch_assoc($result_set)) { $mark_these_as_new[] = $row_to_be_marked_new['id']; } $sql = "UPDATE `maps` SET `Newest` = 'New' WHERE `id` IN (" . implode(',',$mark_these_as_new) . ')'; mysqli_query($con,$sql); Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1417838 Share on other sites More sharing options...
dodgeitorelse3 Posted March 16, 2013 Author Share Posted March 16, 2013 I have tried your suggestion and cannot get it to update more than the same 1 row I was getting when I originally posted. I also tried removing the i from your code at both mysqli sections to no avail. Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1418976 Share on other sites More sharing options...
Jessica Posted March 16, 2013 Share Posted March 16, 2013 Uhm, you don't need to select the rows then do an update. You can just update them. Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1418979 Share on other sites More sharing options...
Jessica Posted March 16, 2013 Share Posted March 16, 2013 Really you probably don't need ANY of this at all. Why do you have a "new" column? Use the date... Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1418981 Share on other sites More sharing options...
dodgeitorelse3 Posted March 16, 2013 Author Share Posted March 16, 2013 (edited) @ Jessica, I will try to figure out how to just update without selecting the rows. I have a new column in the database that holds the word "New" which shows on our downloads pagew to let the users know which maps arte the 10 newest. Please see http://chevys-place.nl/downloads.php What we have is a standalone cms and the owner of the site is even more of a noob than I so I am making him a control panel to edit all his files saving him from going to each file and manually editing it. Basically I am trying to automate as much as I can for him lol. Imagine a noob making it easier for a noob Edited March 16, 2013 by dodgeitorelse3 Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1418984 Share on other sites More sharing options...
Solution dodgeitorelse3 Posted March 16, 2013 Author Solution Share Posted March 16, 2013 Thank you Jessica Quote Link to comment https://forums.phpfreaks.com/topic/275462-cannot-update-multiple-db-rows/#findComment-1419000 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.