Updating row php query

Hi I am trying to update a row in my sql table

first i select 2 fields from table in my database adpackend, countad

$countmoney=mysql_query("SELECT adpackend,countad FROM orders WHERE  category= 'adpack'AND orderPaid=1 and username = ".quote_smart($_SESSION['login'])."");
$money=mysql_fetch_array($countmoney);
extract($money);

then if countad is greater than adpackend i want to update my database like this

mysql_query("UPDATE orders SET matured ='yes'   WHERE countad >= ".$money['adpackend']." AND username = ".quote_smart($_SESSION['login'])."");

it updates ok, but its taking the value from the database as the last entry, so if i have three values in my database like this

adpackend 10.00 countad 30.00
adpackend 11.00 countad 10.00
adpackend 22.00 countad 23.00 <----------------- so this being the last row in the database, if countad is greater than adpackend it updates all the other fields,
So I am trying to update the field if countad field is greater than adpackend update that row only

I’m not 100% sure as you haven’t shown all the code between the SELECT query and the UPDATE query, but it looks as if you loop through the results from the SELECT, then after that loop has closed you then run the UPDATE query. This will only operate against the last value of the $money array, which is whatever your last retrieved row set it to, so it will use one consistent value of $money['adpackend'] and update multiple rows according to your WHERE clause.

Assuming I’m guessing correctly on that, there are a few ways to do it. First, you could also retrieve the unique row id along with the two columns you already retrieve, move the UPDATE into the loop and use the row id to determine which row to update.

Second, if we assume that you want to set the matured value to any row that you retrieve in the SELECT query where countad is bigger than adpackend, instead of retrieving the values and running a separate update, I think you could have MySQL do the work for you, though I’m only reasonably sure of the exact syntax:

UPDATE orders SET matured='yes' WHERE countad >= adpackend and CATEGORY = 'adpack' AND orderpaid=1 and username='whatever'

You also need to stop using the old-style mysql calls like ‘mysql_query()’ and ‘mysql_fetch_array()’ = they’ve gone from being deprecated for many years, to being no longer part of the current version of the PHP language. Look at mysqli or (my preference) PDO for database access functions, there’s loads of articles around on how to make the move.

Hi thanks for the reply, yes you are spot on what I am tying to achieve, i have tried it every way but still doesn’t work this is what I am using at the moment i have left the echo there for now to see if it is showing the correct values it is, so why doesn’t my update query work, if i type the id in the update query it works but as soon as i put this in the query it doesn’t id=“.$row[‘id’].” but it echos out the correct values.

$query = "SELECT id,adpackend,countad FROM orders WHERE  matured='no' AND category= 'adpack'AND orderPaid=1 and username = ".quote_smart($_SESSION['login'])."";

$result = mysql_query($query);

if($result === FALSE) {
    die(mysql_error("error message for the user")); 
}

while($row = mysql_fetch_array($result))
{


if (number_format($row['countad'],2) >= number_format($row['$adpackend'],2)){
echo $row['adpackend'];	
echo $row['id'];
echo $row['countad'];	
mysql_query("UPDATE orders SET matured ='yes'   WHERE countad >= ".number_format($row['adpackend'],2)." AND id=".$row['id']."AND username = ".quote_smart($_SESSION['login'])."");
}

That’ll be giving you a syntax error in the UPDATE query because you haven’t got a space between the end of $row['id'] and the start of the AND username clause.

)." AND id=".$row['id']."AND username = ".quote_smart
                         ^ here 

But if the row id is unique, you don’t need to AND the username check.

Thank you very much that did the trick, i will also have a look at updating my querys

For some reason you are checking the result of SELECT query but not doing so for UPDATE. Thus all the hassle.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.