Jump to content

Cannot update multiple db rows


dodgeitorelse3
Go to solution Solved by dodgeitorelse3,

Recommended Posts

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.

 

test.jpg

 

Any ideas why it only updates the one row? sorry had the code to update rows commented out, removed comment marks.

Edited by dodgeitorelse3
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

@ 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 :happy-04:

Edited by dodgeitorelse3
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.