Page 1 of 1
[SOLVED] PHP array not empty, contains 'Resource id #2'
Posted: Fri Jun 09, 2006 4:50 am
by hairyjim
Hi all.
Something I have never come across before and I am not sure what to do.
Code: Select all
$sql = "SELECT sUserId, xRequest FROM hs_request WHERE sUserid != '' AND interaction_entry = FALSE";
$result = mysql_query( $sql );
$numrows=mysql_num_rows($result);
if ($numrows == 0)
{
exit("No records to update");
}
The above code just drags some records out of a table, I was hoping that if there were no records then just exit the script. Seems that when there are no records then I get 'Resource id #2'
Anyone enlighten me on this?
Oh one final thing. I do a Where sUserid != '', is this a bad thing? I am basically looking for all the fields that do have a userid entered in them. Is this the right way of checking if they are empty?
Jim
Posted: Fri Jun 09, 2006 5:04 am
by Oren
If you are looking for "all the fields that do not have a userid entered in them", then why
!= ?
I'm not sure I understood you, but I'll try anyway.
Try this:
Code: Select all
$sql = "SELECT sUserId FROM hs_request WHERE sUserid == '' AND interaction_entry = FALSE LIMIT 1";
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);
if ($numrows == 0)
{
exit("No records to update");
}
Posted: Fri Jun 09, 2006 5:14 am
by hairyjim
Whoops sorry, I meant I was looking for fields that do have a User ID.
Any thoughts on the Resource ID thing?
Posted: Fri Jun 09, 2006 5:28 am
by Oren
Well, before I post anything, can you please tell me what exactly you want to do.
Edit: Why do you select the xRequest field too?
Posted: Fri Jun 09, 2006 5:45 am
by hairyjim
All I am doing is pulling records from the table that have a userID and have not had an interaction logged against them. The xRequest is an ID of a support incident used further down the script.
What is important here rather than what this snippet of a very large script is used for is that when there are no results returned from the SQL I get 'Resource ID # 2' in the array.
Thus the check on the number of rows returned is not executed since it returns a row containing 'Resource ID # 2', even though there are no records matching the SQL criteria.
Jim
Posted: Fri Jun 09, 2006 6:07 am
by Maugrim_The_Reaper
What array? $numrows should be holding either an integer or the boolean FALSE.
$result will be a resource - to fetch a row you would need to call
Code: Select all
$row = mysql_fetch_assoc($result);
or to collect all rows
Code: Select all
while ($row = mysql_fetch_assoc($result)) {
var_dump($row); // echo each row (each will be an array of field names and values)
}
Posted: Fri Jun 09, 2006 6:07 am
by bokehman
Quoted from the manual:
mysql_query: Return Values
For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error.
For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.
Posted: Fri Jun 09, 2006 6:27 am
by hairyjim
My test data was setup so there were no results to return.
I expected no results from the SQL query, thus $result to have no rows in it. Therefore I also expected the if statement to be executed since I also expected $numrows to be 0 - i.e there were no rows returned.
Instead I got the 'Resource ID # 2' returned in the result which set $numrows to 1.
Am I just completly misisng something here?
Posted: Fri Jun 09, 2006 7:02 am
by bokehman
hairyjim wrote:Am I just completly misisng something here?
Looks like it. If it was a SELECT query
$result will either contain
bool(false) (this means the query failed, possibly due to a parse error or non-existant element) or it will contain a resource id that will be used by php to interact with mysql when using any other function with regard to that query.
Posted: Fri Jun 09, 2006 7:37 am
by hairyjim
That does slightly confuse me. Please forgive my ineptitude.
Please let me step through my idiocy.
Code: Select all
$sql = "SELECT sUserId, xRequest FROM hs_request WHERE sUserid != '' AND interaction_entry = FALSE";
$result = mysql_query( $sql );
$numrows=mysql_num_rows($result);
So in the above I expect $numrows to be 0 because I set the data up so there would be no results to return. Therefore I expected the below code to execute because $numrows = 0.
Code: Select all
if ($numrows == 0)
{
exit("No records to update");
}
BUT from what I understand is that you are telling me is that on a succesful query a resource ID row is also returned regardless if there is actually any table data to return. So my if statement would not execute.
Have I understood correctly?
Sorry about my lack of understanding.
Posted: Fri Jun 09, 2006 7:56 am
by bokehman
As long as the query was good (i.e. no parse errors, the table and columns exist and the grammer is correct) if the query is a SELECT query it will return a
resourse id.
Maybe the following will make it clearer:
Code: Select all
$query = <<<QUERY
SELECT sUserId, xRequest
FROM hs_request
WHERE sUserid != '' AND interaction_entry = FALSE
QUERY;
$result = mysql_query( $query ) or die(mysql_error);
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_assoc($result))
{
# do something with $row
}
}
else
{
# no results returned
}
Posted: Fri Jun 09, 2006 8:02 am
by Oren
You understood only part of it. Here are the two possible scenarios:
1. $result holds boolean false
2. $result holds resource id
If everything is ok, then the second scenario should take place. Thus, when calling to
mysql_num_rows($result); (and assuming the table is empty as you said) -
$numrows would hold '0', and in this case the code inside the if () statement should be executed.
Try to run this code and tell us the results:
Code: Select all
$sql = "SELECT sUserId, xRequest FROM hs_request WHERE sUserid != '' AND interaction_entry = FALSE";
$result = mysql_query($sql);
echo mysql_num_rows($result);
Posted: Fri Jun 09, 2006 9:46 am
by hairyjim
Hmmm. How odd.
When I now echo out the num_rows I get 0. Which is correct.
Strange the behaviour I was bleating on about seems ot have vanished, perhaps I had an error elsewhere before this code that caused the stranged behaviour.
Thanks for all the suggestions, you all have indeed enlightened me.
Posted: Fri Jun 09, 2006 9:49 am
by nathanr
here, have a function!
Code: Select all
function do_select($query) {
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$result_rows = mysql_num_rows($result);
if ($result_rows !== 0) {
for ($r=0;$r<$result_rows;$r++) {
$output[$r] = mysql_fetch_assoc($result);
}
} else {
$output = false;
}
mysql_free_result($result);
return $output;
}
$sql = "SELECT sUserId, xRequest FROM hs_request WHERE sUserid != '' AND interaction_entry = FALSE";
$result = do_select($sql);
if (!$result) {
exit("No records to update");
}
returns false if there are no results..
also try changing the sql to
Code: Select all
$sql = "SELECT sUserId, xRequest FROM hs_request WHERE LENGTH(sUserid) > 0 AND interaction_entry = FALSE";
or indeed change sUserid to be NULL column and use IS NULL

damn too late
Posted: Fri Jun 09, 2006 9:56 am
by hairyjim
Thanks nathanr.
Your post is appreciated, I will hopefully take your function idea. Also your input oin the SQl syntax was great - Thankyou!
