Home > Archive > PHP Language > May 2006 > Help with variable interpolation in WHERE clause
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with variable interpolation in WHERE clause
|
|
|
| I am a MySQL and PHP newbie. I am having trouble getting the $w variable in
my code below passed to mysql. When I use the value of $w directly in the
Where clause, the correct rows are returned. However, when I try to use the
variable in the Where clause, either an error occurs or no rows are
returned. Any thoughts greatly appreciated!
I am using php-4.4.2 and MySQL-4.1.18-0 on Suse 9.1
Jerry
<html>
<body>
<?php
$link = mysql_connect('localhost:3306', 'mysql');
mysql_select_db('test_ermd') or die('Could not select database');
$w = 'exec';
// I have tried all the variations below to get the value of $w to process
in the WHERE clause
//------------------------------------------------------------------------------------------
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = "exec"';
//--This works: all rows with 'exec' in GRP column are selected
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = $w';
//--Invalid query: Unknown column '$w' in 'where clause'
//--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = $w
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \"$w\"';
//--Invalid query: 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 '\"$w\"' at line 1
//--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = \"$w\"
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \$w';
//--Parse error: syntax error, unexpected T_STRING
in /usr/local/bin/apache2/htdocs/test_ermd.php on line 34
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = "\$w"';
//--No rows are selected
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \'$w\'';
//--No rows are selected
//------------------------------------------------------------------------------------------
echo 'Raw query: ' . $query;
$result = mysql_query($query);
if (!$result) {
Â_Â_Â_$messageÂ_Â_=Â_'InvalidÂ_query:Â_'
Â_.Â_mysql_error()Â_.Â_"<br>";
Â_Â_Â_$messageÂ_.=Â_'WholeÂ_query:Â_'Â_.Â_$query;
Â_Â_Â_die($message);
}
while ($row = mysql_fetch_assoc($result)) {
Â_Â_foreachÂ_($rowÂ_asÂ_$keyÂ_=>Â_$value)Â_{
Â_Â_Â_Â_ifÂ_($value)Â_{
Â_Â_Â_Â_Â_Â_echoÂ_"$value";
Â_Â_Â_Â_Â_Â_echoÂ_"<br>";
Â_Â_Â_Â_}
Â_Â_}
Â_Â_echoÂ_"<p>";
}
mysql_free_result($result);
mysql_close($link);
?>
</body>
</html>
// ========================================
============================
| |
| David Haynes 2006-05-18, 7:57 am |
| Jerry wrote:
> I am a MySQL and PHP newbie. I am having trouble getting the $w variable in
> my code below passed to mysql. When I use the value of $w directly in the
> Where clause, the correct rows are returned. However, when I try to use the
> variable in the Where clause, either an error occurs or no rows are
> returned. Any thoughts greatly appreciated!
>
> I am using php-4.4.2 and MySQL-4.1.18-0 on Suse 9.1
>
> Jerry
>
> <html>
> <body>
> <?php
> $link = mysql_connect('localhost:3306', 'mysql');
> mysql_select_db('test_ermd') or die('Could not select database');
> $w = 'exec';
> // I have tried all the variations below to get the value of $w to process
> in the WHERE clause
> //------------------------------------------------------------------------------------------
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = "exec"';
> //--This works: all rows with 'exec' in GRP column are selected
>
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = $w';
> //--Invalid query: Unknown column '$w' in 'where clause'
> //--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
> EMAIL FROM officers WHERE GRP = $w
>
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = \"$w\"';
> //--Invalid query: 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 '\"$w\"' at line 1
> //--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
> EMAIL FROM officers WHERE GRP = \"$w\"
>
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = \$w';
> //--Parse error: syntax error, unexpected T_STRING
> in /usr/local/bin/apache2/htdocs/test_ermd.php on line 34
>
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = "\$w"';
> //--No rows are selected
>
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
> FROM officers WHERE GRP = '$w'';
> //--No rows are selected
> //------------------------------------------------------------------------------------------
>
> echo 'Raw query: ' . $query;
> $result = mysql_query($query);
> if (!$result) {
> $message = 'Invalid query: ' . mysql_error() . "<br>";
> $message .= 'Whole query: ' . $query;
> die($message);
> }
> while ($row = mysql_fetch_assoc($result)) {
> foreach ($row as $key => $value) {
> if ($value) {
> echo "$value";
> echo "<br>";
> }
> }
> echo "<p>";
> }
> mysql_free_result($result);
> mysql_close($link);
> ?>
> </body>
> </html>
> // ========================================
============================
The problem here is that you are using single quotes i.e. ' for your
query string. Single quotes mean 'do not interpret any of the contents
of the string'. You want to use double quotes:
$query = "SELECT TITLE, NAME FROM OFFICERS WHERE GRP = '$w'";
I like to space out my queries so that I can edit them later on in a
much easier way and makes the SQL easier to read/understand. So I would
have written this in one of two ways:
$query = "SELECT "
."TITLE, "
."NAME "
."FROM "
."OFFICERS "
."WHERE GRP = '$w' ";
or
$query = <<<EOF
SELECT
TITLE,
NAME
FROM
OFFICERS
WHERE GRP = '$w'
EOF
You probably also want to add an 'ORDER BY' clause along the lines of
'ORDER BY NAME' or 'ORDER BY INST, NAME'
-david-
| |
|
| David Haynes wrote:
> Jerry wrote:
>
> The problem here is that you are using single quotes i.e. ' for your
> query string. Single quotes mean 'do not interpret any of the contents
> of the string'. You want to use double quotes:
>
> $query = "SELECT TITLE, NAME FROM OFFICERS WHERE GRP = '$w'";
>
> I like to space out my queries so that I can edit them later on in a
> much easier way and makes the SQL easier to read/understand. So I would
> have written this in one of two ways:
>
> $query = "SELECT "
> ."TITLE, "
> ."NAME "
> ."FROM "
> ."OFFICERS "
> ."WHERE GRP = '$w' ";
>
> or
> $query = <<<EOF
> SELECT
> TITLE,
> NAME
> FROM
> OFFICERS
> WHERE GRP = '$w'
> EOF
>
> You probably also want to add an 'ORDER BY' clause along the lines of
> 'ORDER BY NAME' or 'ORDER BY INST, NAME'
>
> -david-
That did the trick, thanks! I'll also adopt your layout of query statements:
makes them readable.
Jerry
|
|
|
|
|