MySQL query won't accept PHP variables

[Sandd]

The following query and count works fine with an explicit WHERE site="dall" using the actual text of a db field.

$result = mysql_query('SELECT COUNT(*) FROM inv WHERE site="dall"');
$numdtsystems = mysql_result($result,0,0);

but if I attempt to use a variable, I get nothing returned.

$sname="dall";
$result = mysql_query('SELECT COUNT(*) FROM inv WHERE site=$sname');
$numdtsystems = mysql_result($result,0,0);

Doesn't MySQL accept PHP variables as query parameters?

[jatar_k]

variables don't resolve between single quotes
[php.net...]

you have 2 options

concatenate
$result = mysql_query('SELECT COUNT(*) FROM inv WHERE site=' . $sname);

or swap your quotes, which would be better as you need to send single quotes around the string to mysql

$result = mysql_query("SELECT COUNT(*) FROM inv WHERE site='$sname'");

the best way to write your lines of code would be like this though

$sname="dall";
$q = "SELECT COUNT(*) FROM inv WHERE site='" . $sname . "'";
$result = mysql_query($q);
$numdtsystems = mysql_result($result,0,0);

this seperates the query construction and helps with debugging, if needed. I prefer concatenation because it is necessary when you use array notation, so you might as well always use it.

[Sandd]

Neither of those methods work. I even cut/pasted your code. I've tried tons of things...nothing yet.

$sname = 'dall';
echo $sname; //variable prints out as > dall

$result = mysql_query("SELECT COUNT(*) FROM inventory WHERE site='" . $sname . "'");
$numdtsystems = mysql_result($result,0,0);

I've tried the following queries and probably a few more not shown.

query("...WHERE site=$sname");
query("...WHERE site='$sname'");
query("...WHERE site=\'$sname\'");
query("...WHERE site=".'$sname'.");

Nothing works yet.

[jatar_k]

well the code you have there is now correct for the query

$result = mysql_query("SELECT COUNT(*) FROM inventory WHERE site='" . $sname . "'");
$numdtsystems = mysql_result($result,0,0);

you could try leaving out the last 0 for your mysql_result function as it isn't required and you are only getting one result

$numdtsystems = mysql_result($result,0);

have you tried the query in mysql?
Do you know that it returns anything?

also you should split those two lines and echo your query to make sure it is properly constructed. You could then paste that output as is into phpmyadmin, commandline or whatever you manage mysql with.

$q = "SELECT COUNT(*) FROM inventory WHERE site='" . $sname . "'";
echo 'query is: ',$q;
$result = mysql_query($q);

you could also add an or die line to see if there is an error coming from mysql

$q = "SELECT COUNT(*) FROM inventory WHERE site='" . $sname . "'";
echo 'query is: ',$q;
$result = mysql_query($q) or die("the query returned an error: " . mysql_error());