[SOLVED] PHP array not empty, contains 'Resource id #2'

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

[SOLVED] PHP array not empty, contains 'Resource id #2'

Post 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
Last edited by hairyjim on Fri Jun 09, 2006 9:50 am, edited 2 times in total.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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");
   }
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post by hairyjim »

Whoops sorry, I meant I was looking for fields that do have a User ID.

Any thoughts on the Resource ID thing?
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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?
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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)
}
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post 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.
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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?
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post 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.
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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.
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post 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
}
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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);
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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

8O damn too late
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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! :D
Post Reply