Re: Query Syntax - WHERE fieldname1="fieldvalue1"ANDfieldname2="fieldvalue2"

Couple of things.. I'm not sure (and too lazy to look it up) if something like "category" is a reserved word, but you can try enclosing your field/table names in backticks (? whatever they're called.. the single quote looking character on the tilde)

SELECT * FROM `classifieds` WHERE `trans_type` = 'For Sale' AND `category` ='Boats'

or...

$query = "SELECT `$field_name` FROM `classifieds` WHERE `$field_name1` =
'$field_value1' AND `$field_name2` ='$field_value2'";

Making sure your variables always have values. As mentioned, echoing out your $query to see what it translates to can be very useful.

Last thing is to make sure your variables don't contain characters that, unescaped, can screw up your query. Stuff like apostrophes and such.

Best way to do this, and ensure some level of security, is to use mysql_real_escape_string().

I don't usually use variables for my field and table names, but I'm guessing you can use this function (and that it's recommended to do so anytime you use a variable in your SQL) for those as well as values.

$field_name = mysql_real_escape_string($field_name);
$field_name1 = mysql_real_escape_string($field_name1);
$field_value1 = mysql_real_escape_string($field_value1);
$field_name2 = mysql_real_escape_string($field_name2);
$field_value2 = mysql_real_escape_string($field_value2);

$query = "SELECT `$field_name` FROM `classifieds` WHERE `$field_name1` =
'$field_value1' AND `$field_name2` ='$field_value2'";

Or do it inline or write a function wrapper to make it easier to read or however you want to do it.


Operation precedence shouldn't be an issue. I usually run into that when dealing with an "OR" situation as well as "AND"s. Since you have a really basic AND-only situation, you shouldn't have to worry about parens.


Maybe that'll help a little. If not, write back with some of your echo'd SQL $query stuff.

-TG





= = = Original message = = =

This did not work. But thank you for trying.

Warning: mysql_query() [http://www.mysql.com/doc]: 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 'FROM classifieds WHERE (trans_type = 'For
Sale') AND (trans_type ='For Sale')' at line 1 in
C:\apache2triad\htdocs\sunlakes\test3.php on line 82




""Luis Moreira (ESI-GSQP)"" wrote in message
news:014e01c79c86$3f34e3d0$bd9eab70$%moreira@esi.pt...
I think you have an "operator\precedence" issue.

Try

$query = "SELECT $field_name FROM classifieds
WHERE
($field_name1 = '$field_value1')
AND
($field_name2 ='$field_value2')
";

This will stop you from eventually ANDing the wrong pair...


-----Original Message-----
From: Mark Abrams [mailto:mark@hospitalsystemsgroup.com]
Sent: ter~a-feira, 22 de Maio de 2007 16:07
To: php-windows@lists.php.net
Subject: [PHP-WIN] Query Syntax - WHERE fieldname1='fieldvalue1'
ANDfieldname2='fieldvalue2'

Query Syntax -



I have a syntax error when I am using an AND in the WHERE clause for a
double field=value restriction



#1 RUNS OK

$query = "SELECT $field_name FROM classifieds WHERE $field_name =
'$field_value'";



$result = mysql_query($query) or die(mysql_error());



#2 FAILS

$query = "SELECT $field_name FROM classifieds WHERE $field_name1 =
'$field_value1' AND $field_name2 ='$field_value2'";



$result = mysql_query($query) or die(mysql_error());



dies with the following message:



Warning: mysql_query() [http://www.mysql.com/doc]: 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 'FROM classifieds WHERE trans_type = 'For
Sale' AND category ='Boats'' at line 1 in C:\apache2triad\htdocs\



Background: I call a function and pass a $field_name = $field_value pair.
This works OK. When I add a second field_name = $field_value pair the query

dies.



Data excerpt from TABLE classifieds:

trans_type = Free
category = Books





Also, Can anyone recommend a good book on mySQL / php? I could not find a
good example of my problem in the online manual http://www.mysql.com/doc.



TIA

Mark

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php