RE: [PHP-DB] Update Query Help...
> -Original Message- > From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] > Sent: 10 December 2002 20:02 > > I have definitely isolated the problem. If I change > the column name > that the match is being performed on from 'id-sys' to > 'id_sys' I can execute > the query without having to worry about quoting the column name and > everything works. I think that I am just going to leave the > column name > changed and take this as a lesson never to use '-' in a > column name again. Well, if you stop to think about this for a second, it may occur to you to wonder how MySQL is supposed to tell the difference between the column name id-sys, and the expression id-sys (i.e. the value of the id column minus the value of the sys column). The answer is that you have to demarcate a column name like this that is ambiguous -- and in MySQL that's with ` back ticks. (Other databases have other means of doing this -- or just restrict column names to avoid the issue!) Cheers! Mike - Mike Ford, Electronic Information Services Adviser, Learning Support Services, Learning & Information Services, JG125, James Graham Building, Leeds Metropolitan University, Beckett Park, LEEDS, LS6 3QS, United Kingdom Email: [EMAIL PROTECTED] Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
> I have definitely isolated the problem. If I change the column name > that the match is being performed on from 'id-sys' to 'id_sys' I can execute > the query without having to worry about quoting the column name and > everything works. I think that I am just going to leave the column name > changed and take this as a lesson never to use '-' in a column name again. > Thanks again for the help. Maybe we all learned something from > this. If you want to use a column name like id-sys, which you shouldn't, though, as you've learned, you can enclose it in backticks, like `id-sys`. Note that those are not single quotes, they are backticks, to the left of the number 1 key. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query Help...
I have definitely isolated the problem. If I change the column name that the match is being performed on from 'id-sys' to 'id_sys' I can execute the query without having to worry about quoting the column name and everything works. I think that I am just going to leave the column name changed and take this as a lesson never to use '-' in a column name again. Thanks again for the help. Maybe we all learned something from this. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 2:00 PM To: NIPP, SCOTT V (SBCSI); '1LT John W. Holmes'; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn > I understand that the column name does not normally need quotes, but > without quotes on the column name I get a mysql error message about no > column named 'id'. Unfortunately, I cannot even get the UPDATE statement > working outside of PHP through an direct SQL statement. Everything looks > like it should work, but the affected columns is always zero, and obviously > the atime column is not getting the timestamp. > This is quite frustrating. Thanks again for the help. Hopefully > someone will come up with something to help me get this working. > > -Original Message- > From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 10, 2002 1:45 PM > To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Update Query Help... > > > > $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; > > $update = "UPDATE accounts SET atime='NOW()' WHERE > > \"id-sys\"='".$tmp."'"; > > echo $update; > > $result1 = mysql_query($update, $Prod) or die(mysql_error()); > > echo mysql_affected_rows(); > > Try: > > $update = "UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'"; > > NOW() is a function, don't enclose it within quotes and make it a string. > You don't put quotes around column names, either, only string values. > > ---John Holmes... > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn > I understand that the column name does not normally need quotes, but > without quotes on the column name I get a mysql error message about no > column named 'id'. Unfortunately, I cannot even get the UPDATE statement > working outside of PHP through an direct SQL statement. Everything looks > like it should work, but the affected columns is always zero, and obviously > the atime column is not getting the timestamp. > This is quite frustrating. Thanks again for the help. Hopefully > someone will come up with something to help me get this working. > > -Original Message- > From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 10, 2002 1:45 PM > To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Update Query Help... > > > > $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; > > $update = "UPDATE accounts SET atime='NOW()' WHERE > > \"id-sys\"='".$tmp."'"; > > echo $update; > > $result1 = mysql_query($update, $Prod) or die(mysql_error()); > > echo mysql_affected_rows(); > > Try: > > $update = "UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'"; > > NOW() is a function, don't enclose it within quotes and make it a string. > You don't put quotes around column names, either, only string values. > > ---John Holmes... > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query Help...
I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... > $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; > $update = "UPDATE accounts SET atime='NOW()' WHERE > \"id-sys\"='".$tmp."'"; > echo $update; > $result1 = mysql_query($update, $Prod) or die(mysql_error()); > echo mysql_affected_rows(); Try: $update = "UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'"; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
SCOTT, > I am attempting to UPDATE a table and I have having absolutely ZERO > success. Here is the query: > $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; > $update = "UPDATE accounts SET atime='NOW()' WHERE > \"id-sys\"='".$tmp."'"; > echo $update; > $result1 = mysql_query($update, $Prod) or die(mysql_error()); > echo mysql_affected_rows(); > Please help me figure out why this is not working. I have tried > quoting the column name 'id-sys' every way I can think of, but nothing > works. The column is initially NULL, and I am attempting to update just the > single column with a timestamp. Thanks in advance. NOW() is a function and should not be part of a quoted string. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query Help...
is it a mysql field type of datetime? if so, you may want to use this instead $timestamp=date("Y-m-j H:i:s"); $update = "UPDATE accounts SET atime='$timestamp' WHERE id-sys='$tmp'"; that puts the current timestamp in mysql format for entry into your table. Cheers Jason -Original Message- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:35 PM To: '[EMAIL PROTECTED]' Subject: [PHP-DB] Update Query Help... I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; $update = "UPDATE accounts SET atime='NOW()' WHERE \"id-sys\"='".$tmp."'"; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
> $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; > $update = "UPDATE accounts SET atime='NOW()' WHERE > \"id-sys\"='".$tmp."'"; > echo $update; > $result1 = mysql_query($update, $Prod) or die(mysql_error()); > echo mysql_affected_rows(); Try: $update = "UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'"; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Query Help...
I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid']."-".$_POST['system'][$a]; $update = "UPDATE accounts SET atime='NOW()' WHERE \"id-sys\"='".$tmp."'"; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php