RE: [PHP] error when trying to delete a record
[snip] Is the above statement true when the id field is numeric (which it surely is in this case)? I get the expected results (in mySQL) when using statements like SELECT name FROM table WHERE id=1 with no single quotes round it. Putting quotes round integer values is counter-intuitive - is it necessary in some cases? [/snip] If the id field is numeric and the $id is not set the query will return a syntax error due to the query being seen as this... SELECT foo FROM bar WHERE id = ; The problems multiply when the id field is of the auto-increment type. Some RDBMS have settings that will allow conditionals, such as WHERE id=1, to be evaluated as TRUE or FALSE, which accounts for my previous statement. id, in this case, may be a bad example because most developers would like a more qualified conditional match before a deletion occurs. I would never let my development team use the id (or whatever it was called, especially an auto-increment field) for record retrieval or deletion. In general you would not have to use quotes around fields that are INTs, FLOATs, etc. P.S. Paul, click reply-all when sending messages back to this list. I know it is counter-intuitive, but it is how this one works. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] error when trying to delete a record
From: Paul Waring [mailto:[EMAIL PROTECTED]
> On Mon, Jul 11, 2005 at 03:25:33PM +0100, Mark Rees wrote:
> > with no single quotes round it. Putting quotes round
> > integer values is counter-intuitive - is it necessary
> > in some cases?
>
> If the field is a numeric type (e.g. INT) as opposed to
> numeric data being stored in a character field (e.g. a
> telephone number) then it is not only unnecessary to quote
> the value, it's also incorrect useage of SQL, although
> you'd probably get away with it in most database systems.
I agree. What's best is to ensure the val is of the proper type before
sending it to the db. Try casting it first; if the value is blank, it'll
cast as 0 (though that may not be optimal, either, if your record could
be 0):
$query = "DELETE FROM sheet1 WHERE id = " . (int)$id;
Also probably best to check if it's empty, something such as:
if (!empty($id)) {
$query = 'DELETE FROM sheet1 WHERE id = ' . (int)$id;
} else {
echo 'Argument $id was empty';
}
HTH!
--
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED] (617) 886-5539
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] error when trying to delete a record
On Mon, Jul 11, 2005 at 03:25:33PM +0100, Mark Rees wrote: > with no single quotes round it. Putting quotes round integer values is > counter-intuitive - is it necessary in some cases? If the field is a numeric type (e.g. INT) as opposed to numeric data being stored in a character field (e.g. a telephone number) then it is not only unnecessary to quote the value, it's also incorrect useage of SQL, although you'd probably get away with it in most database systems. Paul -- Rogue Tory http://www.roguetory.org.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] error when trying to delete a record
""Jay Blanchard"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] [snip] $query= "DELETE FROM sheet1 WHERE id=$id"; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 [/snip] try... $query= "DELETE FROM sheet1 WHERE id = '".$id."' "; Note the single quotes around conditional data. Imagine if $id = 1 and you did your original query, it would read... $query= "DELETE FROM sheet1 WHERE id=1"; Which is where id = TRUE. You could end up deleting all of the records in the database. --- Is the above statement true when the id field is numeric (which it surely is in this case)? I get the expected results (in mySQL) when using statements like SELECT name FROM table WHERE id=1 with no single quotes round it. Putting quotes round integer values is counter-intuitive - is it necessary in some cases? --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] error when trying to delete a record
Ross wrote:
I dunno if my text book is out of date or I have made a syntax error but I
am trying to delete a record with
$query= "DELETE FROM sheet1 WHERE id=$id";
$result= mysql_query($query);
if($result){
echo "it was deleted";
}
else
echo mysql_error();
}
and I get the followign sql error
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line 1
echo out $id and/or your query to ensure it is what you think it is.
--
John C. Nichel
ÜberGeek
KegWorks.com
716.856.9675
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] error when trying to delete a record
[snip] $query= "DELETE FROM sheet1 WHERE id=$id"; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 [/snip] try... $query= "DELETE FROM sheet1 WHERE id = '".$id."' "; Note the single quotes around conditional data. Imagine if $id = 1 and you did your original query, it would read... $query= "DELETE FROM sheet1 WHERE id=1"; Which is where id = TRUE. You could end up deleting all of the records in the database. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] error when trying to delete a record
I dunno if my text book is out of date or I have made a syntax error but I
am trying to delete a record with
$query= "DELETE FROM sheet1 WHERE id=$id";
$result= mysql_query($query);
if($result){
echo "it was deleted";
}
else
echo mysql_error();
}
and I get the followign sql error
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line 1
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
