Multiple Update query php/mysql not working

July 23rd, 2005, 7:17 pm

The first query updates just fine, but my second query update doesn't do anything. I changed the error handling to join the two strings just for
this purpose and I'm getting the full sentence as if no errors, but somethings wrong as the table is not updating in the pending column.
The column pending is identical in each table and I would like to use one query as it is the only column needing updating from yes to no, but I couldn't get that working either. And this only works half A$$ed. The items table will usually have multiple rows to update as the people table is only one row...I don't know if that would have anything to do with it as this seems correct. Thanks for any help.

$q = "UPDATE people SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
$r = mysql_query($q);
$sql = "UPDATE items SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
$x = mysql_query($sql);
if (!x){
$updateStatus = "Error archiving record.";
} else {
$updateStatus = "Record Archived";
}
if (!r){
$us = "Please contact us.";
} else {
$us = "successfully.";
}
echo "$orderstatus $us";

July 24th, 2005, 1:00 am

There doesn't appear to be any syntax error with your SQL. So my question would be: is "today" in both tables, in the exact same format?

-Tony

July 24th, 2005, 8:17 pm

Thanks for the reply. Sorry I didn't receive an email notification of it and am just now able to check on my post. The today columns are identical in each table. Both just store the month, day, and year in the format YYYY-MM-DD. Anymore help would be greatly appreciated. If anyone can think of any possibilities I can explore, I'd be grateful to be able to keep exploring this problem.

July 24th, 2005, 9:24 pm

You'll need to post the tables. Not the data, but the "CREATE TABLE" stuff. I think you can do "SHOW CREATE" to have MySQL spit out what the tables look like. You might need to do it for each table individually. Anyway, then we can match up the query to the table and see if the field types are off or something else.

-Tony

July 25th, 2005, 7:00 am

CREATE TABLE `people` (
`primaryKey` int(11) NOT NULL auto_increment,
`cust_id` int(11) NOT NULL default '0',
`today` date NOT NULL default '0000-00-00',
`pending` char(3) default NULL,
`record_id` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`primaryKey`)
) TYPE=MyISAM


CREATE TABLE `items` (
`prime` int(11) NOT NULL auto_increment,
`item_num` varchar(50) NOT NULL default '',
`cust_id` varchar(10) NOT NULL default '0',
`product1` varchar(125) NOT NULL default '',
`qty1` varchar(10) NOT NULL default '',
`today` date NOT NULL default '0000-00-00',
`time` timestamp(14) NOT NULL,
`ponum` varchar(20) default NULL,
`deldate` varchar(11) default NULL,
`pickup` varchar(11) default NULL,
`tolerance` varchar(25) default NULL,
`pending` char(3) default NULL,
`record_id` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`prime`)
) TYPE=MyISAM

July 25th, 2005, 4:05 pm

OK, I think I see it. The "cust_id" is a varchar(10) in "items" but an "int" in people.

They should be the same, preferably int. Also, once you've changed it, you should probably not put $cnum in quotes, like this:

Code: [ Download ] [ Select ]

WHERE cust_id=$cnum

That way, MySQL will not do any conversion. Also, as a tip, if "pending" is really just "yes" and "no" you should change it to an ENUM or BOOL. That will save some small amount of space on disk, but more importantly, it will make MySQL go faster.

EDIT: when you drop $cnum into the SQL query string, be certain it only contains numbers. You know how to launder the data, right? Maybe something like this:

Code: [ Download ] [ Select ]

$cnum = preg_replace('/[^0-9]+/', '', $cnum);

-Tony

July 25th, 2005, 8:57 pm

I corrected that issue and am still having the same problem. The first one updates and the second one doesn't do anything. I've ran the query right in to the db and it returned 0 affected rows and didn't update. Now if I take off for it to check against the WHERE condition today it updates.
just:

UPDATE items SET pending='no' WHERE cust_id=1

both queries update thru my php program with this change, but I don't need all the pending for customer number 1 changed to no only the ones matching the date in the format YYYY-MM-DD.

Why shouldn't I be to check it agains a date field?

I've echoed those variables and the date is correct on the page with the update query.

I hope there is something else I can do. Thanks so much for your help thus far,Tony, and the tip

ALSO,
"UPDATE items SET pending='no' WHERE cust_id=$cnum";
[does not work]
"UPDATE items SET pending='no' WHERE cust_id='$cnum' ";
[works]
What all does the single quotes mean?

July 25th, 2005, 9:37 pm

Waaaait. You're saying that this works:

Code: [ Download ] [ Select ]

UPDATE items SET pending='no' WHERE cust_id=1

But this doesn't work:

Code: [ Download ] [ Select ]

UPDATE items SET pending='no' WHERE cust_id=$cnum

Those are exactly the same, except that one uses $cnum. That would tell me that $cnum isn't 1.

Also, another thought. You do know that MySQL returns zero results if there is no match, right? So if you search for WHERE today='2005-05-05' it won't make changes if there isn't anything for that date.

Also, another other thought. You do know that MySQL returns zero results if there is no need to make a change, right? In other words, if it finds a record that does match the cust_id and today, but the pending field is ALREADY set to no, then it doesn't need to do anything, and returns zero records changed.

Quote:

What all does the single quotes mean?

Single or double-quotes mean "this is a string of text, compare the characters for an exact match -- a period, a comma, an extra zero, any extra characters means this isn't a match" Without quotes means, "this a number of some kind, tell me if it is mathematically the same number -- commas, decimal points, those shouldn't affect things."

Basically, as a string, 01 and 1 do not match. Likewise, 1.00 and 1.0 do not match. However, as numbers, 1.00 and 1.0 and 01 and 1 are all the same number, so they all match.

Anyway, if none of this fixes it, you're a little stuck. You'll need to post all the code, and even some of the data in the table. Daunting.

-Tony

July 26th, 2005, 3:02 am

Have you tried running a select statement with your where clause?
ie:

Code: [ Download ] [ Select ]

SELECT * from items WHERE cust_id='$cnum' AND today='$today'

That will at least tell you if there are any rows that fit the where clause. If it returns zero rows, there's your problem.

July 26th, 2005, 7:11 am

YES, the select query was finding the exact data I needed to be updated and Tony,
Both these queries were working

UPDATE items SET pending='no' WHERE cust_id='$cnum'
UPDATE items SET pending='no' WHERE cust_id=1

The one with the php variable in it wasn't working when I didn't enclose it in single quotes. That was the reason I wanted an explanantion of the quotes. The one where I used the customer number was where I inputted the query right in the query window of mysql.

Sorry for the confusion. However, maybe when I made the db change last night from varchar to int it didn't take effect right away because now this morning my script is working and updating the way I need it to and that was the only thing I've changed. I'm going to keep running test to make sure it's working properly because now I'm skeptical of updating.
Thanks for all the help, Tony. I consider this problem fixed. Again, thanks for the help.

July 26th, 2005, 11:20 am

Shouldn't your sql read something like:

Code: [ Download ] [ Select ]

$q = "UPDATE people SET pending='no' WHERE cust_id=" . $cnum . " AND today='". $today."'";

You have to close the string w/ " before you add the variable. I'm not positive it today needs the single quotes or not, but cust_id is an int and shouldn't.

Hope this helps.

July 26th, 2005, 12:01 pm

Why do you have to close the string with " before you add the variable? Isn't " variable interpolated?

-Tony

July 27th, 2005, 6:37 am

I've seen a login query in a book that checks username and password from the database written like that before and I can't remember the reason but there was one. Also, that query worked for me, EternalStudent ,and I needed single quotes around today just like that.

July 27th, 2005, 10:30 am

You don't have a dollar sign for the variables in the "if" statements. With no dollar sign, it is assumed to be a string, which might explain why it never returned an error. Try this:

PHP Code: [ Download ] [ Select ]

 
$sql = "UPDATE items SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
 
$x = mysql_query($sql);
 
if (!$x){
 
   $updateStatus = "Error archiving record: ". mysql_error();
 
} else {
 
   $updateStatus = "Record Archived";
 
}
 
 
 
$q = "UPDATE people SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
 
$r = mysql_query($q);
 
if (!$r){
 
   $us = "Error archiving record: ". mysql_error();
 
} else {
 
   $us = "successfully.";
 
}
 
echo "$orderstatus $us";

1.  
2. $sql = "UPDATE items SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
3.  
4. $x = mysql_query($sql);
5.  
6. if (!$x){
7.  
8.    $updateStatus = "Error archiving record: ". mysql_error();
9.  
10. } else {
11.  
12.    $updateStatus = "Record Archived";
13.  
14. }
15.  
16.  
17.  
18. $q = "UPDATE people SET pending='no' WHERE cust_id='$cnum' AND today='$today'";
19.  
20. $r = mysql_query($q);
21.  
22. if (!$r){
23.  
24.    $us = "Error archiving record: ". mysql_error();
25.  
26. } else {
27.  
28.    $us = "successfully.";
29.  
30. }
31.  
32. echo "$orderstatus $us";

Hope that helps.

July 27th, 2005, 2:52 pm

Thanks Ryanb,
I haven't been on php too long (i love it though) and the first book I read said the most common newbie error is forgetting the dollar sign...I thought I was apast that

It's been corrected.

Many thanks to everyone that's replied.