Page 1 of 2
UPDATE?
Posted: Tue Sep 24, 2002 4:19 am
by Coco
ok i understand that update returns the number of rows updated...
but
if i attempt to update a row that _doesnt_ exist, it still returns 1!
why
Posted: Tue Sep 24, 2002 10:28 am
by BDKR
Hey,
Yes that's true, but that answer (the number of rows affected) is 1 row by itself. Based on the fact that you are posting, I'm assuming that you want to get the number of rows returned. If that's correct, what function/functions are you trying to use to accomplish this?
Cheers,
BDKR
Posted: Tue Sep 24, 2002 10:37 am
by Coco
i was trying to do error checking...
am trying to avoid having to script a check as i want to try keep off the sql server as much as poss
Code: Select all
if($planet = $HTTP_POST_VARSї'planet']){
$title = $HTTP_POST_VARSї'title'];
$result = mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'");
if($result == 1)
echo 'Planet name successfully changed';
else
echo 'error changing planet name';
still dont understand how you can update a non-existant row
Posted: Tue Sep 24, 2002 11:20 am
by BDKR
Hey C,
Well, according to the manual, mysql_query() returns true or false. Therefore, you could do something like this.
Code: Select all
if($planet = $HTTP_POST_VARSї'planet'])
{
$title = $HTTP_POST_VARSї'title'];
$result = mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'");
if($result == true)
{ echo 'Planet name successfully changed'; }
elseif($result == false)
{ echo 'error changing planet name'; }
}
Or, to shorten it up by a line,...
Code: Select all
if($planet = $HTTP_POST_VARSї'planet'])
{
$title = $HTTP_POST_VARSї'title'];
if(mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'")==true)
{ echo 'Planet name successfully changed'; }
else
{ echo 'error changing planet name'; }
}
Just double check with the manual to see what functions return.
Cheers,
BDKR
Posted: Tue Sep 24, 2002 11:23 am
by BDKR
And what's up with the line number action? Jason, did you change something.
Cheers,
BDKR
Posted: Tue Sep 24, 2002 11:32 am
by Coco
not sure it that'll work but hey...
ill give it a whirl soon as my host decides that today is a day that it wants to work

Posted: Tue Sep 24, 2002 12:11 pm
by BDKR
Hey Coco,
Well, according to the manual, mysql_query() returns true or false. Therefore, you could do something like this.
Sorry about the line above. It's true, but on certain types of queries. The way that sentence is written above, it seems as though it's correct for all queries.
From the manual...
Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements
mysql_query() returns a resource identifier or FALSE if the query was
not executed correctly. For other type of SQL statements,
mysql_query() returns TRUE on success
and FALSE on error. A non-FALSE return value means that the query was legal and could be executed by
the server. It does not indicate anything about the number of
rows affected or returned. It is perfectly possible for a query
to succeed but affect no rows or return no rows.
Cheers,
BDKR
Posted: Tue Sep 24, 2002 6:17 pm
by Coco
well it didnt work
so it takes me back to my origional question
why does update return a value of 1 when it attempts to change a non-existant row?
sure i can code around it but that means an extra call to the sql server and i really wanna try keep that to a minimum
Posted: Tue Sep 24, 2002 6:32 pm
by mydimension
Posted: Tue Sep 24, 2002 6:41 pm
by Coco
still returns a value of 1

Posted: Tue Sep 24, 2002 6:55 pm
by mydimension
to me this ought to work and makes perfect sense. if it dosen't work there maybe more information that we don't know yet
Code: Select all
if($planet = $HTTP_POST_VARSї'planet']){
$title = $HTTP_POST_VARSї'title'];
$result = mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'");
$num = mysql_affected_rows($result);
if($num > 0){
echo 'Planet name successfully changed'; }
elseif($num =< 0){
echo 'error changing planet name';
}
}
Posted: Tue Sep 24, 2002 7:33 pm
by hob_goblin
Code: Select all
$result = mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'") or die("foo!");
OR run another query to check if it exists correctly and use 'mysql_num_rows()'
Posted: Wed Sep 25, 2002 9:40 am
by BDKR
well it didnt work
so it takes me back to my origional question
why does update return a value of 1 when it attempts to change a non-existant row?
sure i can code around it but that means an extra call to the sql server and i really wanna try keep that to a minimum
HOW (

) are you getting this var or value of 1?
Something to think about. If you type in something like "SELECT COUNT(*) FROM testTable", the answer you're going to get is only
1 / ONE row long, inspite of how many rows are in the table.
Similarly, if your query is an update query, mysql is going to return
something to tell you that it worked or flailed. If there was a failure due to something like a table or field that doesn't exist, that false return is going to be
1 / ONE row long. In other words, that
1 row is to tell you that the query bombed!
The manual is your friend.
Cheers,
BDKR
Posted: Wed Sep 25, 2002 10:00 am
by Coco
well i changed my script as was suggested and it didnt work...
i understand that mysql_query returns true or false on an update, BUT it only returns false IF the query is INVALID...
so affected rows must be used instead:
Code: Select all
if($planet = $HTTP_POST_VARSї'planet']){
$title = $HTTP_POST_VARSї'title'];
mysql_query("UPDATE Planet SET title = '$title' WHERE PID = '$planet'");
if(mysql_affected_rows() == 1)
echo 'Planet name successfully changed';
else
echo 'error changing planet name';
}//end if
no matter what i put as values for title and planet it always affects 1 row
always
*sighs* guess i may aswell code in a select query to check if the name was changed
Posted: Thu Sep 26, 2002 12:08 pm
by Coco
strike me down blind but im sure that there is a goblin running my database...
all of a sudden, for no reason at all, the above script has started working