What's happening to the var?

in a mysql query - please show me my error

[neophyte]

Hello All -

Really tearing my hair out over this because it's always worked before.

I have a db query script that connects to the db, passes a query, then returns the desired records AND the row count (as an array) as shown here:

*******

function dbQuery($query)
{
include ('db.connect.inc.php');

$retArr = array('recs','rows');
$retArr['recs'] = mysql_query($query, $db);
$retArr['rows'] = mysql_num_rows($retArr['recs']);
mysql_close($db);
return $retArr;
}
*******

The query I'm sending is:

$roomsQuery = "SELECT * FROM reservations WHERE ConfirmNum_FK = $confirmNum";

Which then get's fed to the function:

$rooms = dbQuery($roomsQuery);

from which I SHOULD be able to access the number of rows found:

echo $rooms['rows'];

*******

"$confirmNum" is a var that holds an alpha/numeric string something like "cielo11336".

What is ALWAYS happening, is that I'm getting the error: "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource..."

I'm pulling (what's left of) my hair out over this BECAUSE if I change the query to feed a literal instead of a var -

"SELECT * FROM reservations WHERE ConfirmNum_FK = 'cielo11336'";

... all is well: echo $rooms['rows'] displays the expected "5" for the number of rows which contain cielo11336.

I have echoed out the $confirmNum var before it goes into the query string, and the contents of the var is always cielo11336 as needed so I don't know what's going on.

The biggest nightmare is that this small problem has brought the entire project to a screeching halt as I can't move forward until I've solved it.

Can anyone see the error of my ways... I sure can't.

All assistance, as always, GREATLY appreciated.

Neophyte

[neophyte]

Forgot to mention:

the error is always triggered from the db query script right at the mysql num rows line:

>>$retArr['rows'] = mysql_num_rows($retArr['recs']);<<

As I mentioned prevously, I've used this query script for many months without any bumps in the road, but I also must admit, I've hardly ever had the need to access the 'rows' index until now.

Neophyte

[Psychopsia]

Hi neophyte

Try disabling the "mysql_close($db);" line. As I can see the include is making a new connection on very query and closing it, it supposed to make just one connect per script.

Maybe something like this:

include ('db.connect.inc.php');

function dbQuery($query)
{
global $db;

$retArr = array('recs','rows');
$retArr['recs'] = mysql_query($query, $db);
$retArr['rows'] = mysql_num_rows($retArr['recs']);
return $retArr;
}

// At the end of the script
mysql_close($db);

Happy new year! :)

[neophyte]

Psychopsia -

Thanks for the observation about closing the database - that's something I never considered. Have corrected as indicated but, unfortunatly, still no dice ... same problem.

Since putting the literal of the var in the query works, the only thing that I can think of is that something is "happening" to the content of the var during the query. This is what's got me stumped.

I've even copied and pasted the code directly from the db.connect and db.query file just above what follows below to eliminate the possibility that somethings happening to the return array, but still get the same problem.

I'm posting the business end of this script (pretty short, really) so maybe you - or someone else - can see where I'm going wrong:

********************

require_once ('../_include/db.query.inc.php');

$buttonHit = "";

foreach($_POST as $key => $value)
{
$arr = explode("_", $key);
$buttonHit = $arr[0];// $buttonHit is "View" or "Edit"
$rowId= $arr[1];// $rowId is the "Row Id"
$confirmNum = $arr[2];// $confirmNum is the confirm number of the record chosen
}

if (!empty($buttonHit))
{
echo $confirmNum; // this works - confirm num from foreach block above IS in $confirmNum

$roomsReserved = "SELECT * FROM reservations WHERE ConfirmNum_FK = $confirmNum";

$rooms = dbQuery($roomsReserved);

echo $rooms['rows'];

exit(); //exit just to see if the rows show what I want
}

********************

Once again, when I put the literal comparison ('cielo11336') in the query string, (i.e. $roomsReserved = "SELECT * FROM reservations WHERE ConfirmNum_FK = 'cielo11336'";) everything works.

But if I feed the comparison into the query string via a var ($confirmNum) it throws the MYSQL_NUM_ROWS error.

Geez, ya know, it's times like these that I wished I had become a gardener.

Thanks again - and in advance - for all assistance.

Neophyte

[eelixduppy]

echo $roomsReserved before using the function. It seems that there is an error in your query. You can also try:

$retArr['recs'] = mysql_query($query, $db) or die(mysql_error());

to check this.

[rjwmnews]

My guess is that the SQL syntax does not properly 'quote' the variable. If you use the PEAR extensions, quote() -- [$db is the instantiated PEAR subclass instance]

$db->quote($confirmNum) // deprecated
$db->quoteSmart($confirmNum) // updated

will properly quote a variable, depending if the corresponding field is numeric or string type.

You can manually quote the variable if you know that the field is type string. The first example places a single quote inside the double quote characters and the latter escapes the enclosed variable double quote characters -- both should be functionally identical.

$roomsQuery = "SELECT * FROM reservations WHERE ConfirmNum_FK = '" . $confirmNum . "'";

$roomsQuery = "SELECT * FROM reservations WHERE ConfirmNum_FK = \"$confirmNum\"";

[neophyte]

eelixduppy and rjwmnews -

Thanks for your help. Did echo back the query and all was well there, but when I quoted the var, num_rows worked! Yeeeesh!

The ConfirmNum_FK column is of type varchar which - I'm assuming - is the reason the quotes were needed, so the moral of the story is that if I'm constructing a search/comparison query which targets a varchar or text field using a var, then I must quote this var within the query string?

As a test, I've changed the ConfirmNum_FK field to int(11), stripped out the "cielo" part of the string - so now the var only contained 11336 - and re-ran the query without quotes and now it also worked.

So, that's correct... correct? When building a query string that will target a varchar or text field in a db, the var which is passed must be quoted?

Neophyte

PS: Thank you both for your guidance!

[eelixduppy]

>>>must be quoted

Yep :)

Glad you got it resolved!