Error in sql Syntax

[tshey]

Hi Everyone,
I have an issue with processing a php form. The values seleted are suppose to correspond with the fields in an items table. There are 4 values that are passed as a form variable, but an error comes back: 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 'AND subcat3_id = 4 AND cat_id = 13' at line 1

The code is as follows:

mysql_select_db($database_vacuumshop, $vacuumshop);
$query_items = sprintf("SELECT * FROM items WHERE subcat_id = %s AND subcat2_id = %s AND subcat3_id = %s AND cat_id = %s", 
$subcat_items,$subcat2_items,$subcat3_items, $subcat4_items);
$items = mysql_query($query_items, $vacuumshop) or die(mysql_error());
$row_items = mysql_fetch_assoc($items);
$totalRows_items = mysql_num_rows($items);

Am I in the right forum or would I be better in php forum?

 

[tshey]

I think I have it figured out do I add ' ' these to the subcat(s)_id=' %s'

 

[TonyGroves]

That's exactly it. And you would also need to make sure that any apostrophe characters in those fields are escaped - "Bush's brain" should be changed to "Bush\'s brain" (not that there is such a thing; that's just a hypothetical example).

 

[r937]

no, that's not exactly it, sorry

if subcat_id, subcat2_id, etc, are numeric columns, then you should not put quotes around the values that you are comparing them to

instead of

WHERE subcat_id = '2' AND subcat2_id = '4' AND ...

you should use this instead --

WHERE subcat_id = 2 AND subcat2_id = 4 AND ...

though mysql will permit the quotes (which it really shouldn't, but that's a different rant for another day), but even though mysql does permit the invalid syntax, you should try to write your queries with correct syntax, otherwise when you start to work with any other database system you will have a whole lot of bad habits to break first

r937.com | rudy.ca

 

[TonyGroves]

He didn't say they were all numeric values; the fact that he's getting a syntax error, and the fact that he's using "%s" as the sprintf argument type, would suggest that they're not. If there's any possibility that they mightn't be, the safe option is to use quotes.

 

[r937]

i understand what you're saying, but we are going to have to disagree

use quoted strings for character columns and unquoted numbers for numeric columns, don't just use strings because the column might be a character column (it either is or it isn't)

besides, suppose one of those sprint variables was actually missing -- then the syntax generated would be

WHERE subcat_id = '2' AND subcat2_id = '' AND ...

in this case, you are comparing a numeric column to an empty string, which mysql converts to 0, i.e. sloppiness compounded by sloppiness, and the error goes away!!

no, the real answer here is to figure out why the error was being produced without the quotes





r937.com | rudy.ca

 

[TonyGroves]

The only person who can confirm the nature of the supplied values and the column types is the OP, so there's not much point speculating about it. The reality is that, with the information supplied to date, my solution is the only one likely to work.