How to check if select query returns no rows php

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

Then if you do mysql_num_rows[$b] it will return 0 and if you attempt to fetch an array from the query then mysql will throw an error. So the value of b is still the query execute command but it just won't execute and instead will throw an error.

nav33n 472 Purple hazed! Team Colleague Featured Poster

In the table there is no value of c and hence nothing is returned
What is the value of $b?

$b will still hold a result resource. Check Return values here..
//in.php.net/function.mysql-query

OmniX 21 Practically a Master Poster

I dont think that is correct because when I make the variable that holds the result == 0 / NULL it dosent work.

How could you demonstrate that:

$a = "SELECT * FROM a WHERE b = c";
$b = mysql_query[$a];
if[$b == 0] {
 echo "No rows retrieved";
} else {
 echo "Rows retrieved";
}

So anything something like this possible?

THanks, Regards X

buddylee17 216 Practically a Master Poster

You have to get the number of rows.

$a = "SELECT * FROM a WHERE b = c";
$b = mysql_query[$a];
$num_rows = mysql_num_rows[$b];
if[$num_rows == 0] {
 echo "No rows retrieved";
} else {
 echo "Rows retrieved";
}

Is that what you were asking?

OmniX 21 Practically a Master Poster

Your skipping a step :P
Because you cant preform num_rows if $b returns no rows and throws an error.
Been trying to come up with a solution but it is annoying me - hopefully I figure it out soon :]

buddylee17 216 Practically a Master Poster

Actually, I modified your query to test the code in my db prior to posting and it outputted "No rows retrieved'". Also, when I run the code like so:

$sql = 'SELECT * FROM `absent faculty table` WHERE absentid = "blah"'; 
$result=mysql_query[$sql,$conn];
$num_rows = mysql_num_rows[$result];
echo $num_rows;

it returns 0 every time.

Have you got an error in your query?

nav33n 472 Purple hazed! Team Colleague Featured Poster

You got it all wrong. $b doesn't return any rows. It just return result resource, which can be used in
* mysql_num_rows to know how many rows were returned.
* mysql_fetch_array/mysql_fetch_assoc/mysql_fetch_row/mysql_fetch_object to get the values of the rows .

OmniX 21 Practically a Master Poster

nav33n is correct it is returning a result resouce [but I was meaning to say if that result resource returns no rows].

buddylee17 that returned 0 always? Intresting... My query works because when it does "retrieve a row" there is no error but when "dosent retrieve a row" there is an error :[

Anyways to check if the result_resource contains "no rows" so then dosent throw an error?

Hmmm... Actually I think I know what may be the problem I think because I am calling the mysql_num_rows from a function then directly?

Ill bbs, after I have tinkered. Thanks for the input. :]

Ya solved it in .5 seconds with your help, ha [the function was causing the errors].

Thanks Guys

function rows[$a]	{
 $b = mysql_num_rows[$a] or die["Error: "];
 return $b;
}

using 'rows' instead 'mysql_num_rows' throws the error.
Anyone can help me fix my code? Thanks

nav33n 472 Purple hazed! Team Colleague Featured Poster

Hmmm... Actually I think I know what may be the problem I think because I am calling the mysql_num_rows from a function then directly?

May be. Are you passing this result resource to the function ? If you post relevant code, it would be very helpful !

OmniX 21 Practically a Master Poster

Im on dial up nav, your beating me before I can even fix my posts! haha

nav33n 472 Purple hazed! Team Colleague Featured Poster

OmniX 21 Practically a Master Poster

the post, not the function :[

Im just resorting to use the straight mysql but I prefer to call my function, so any ideas how I can fix it?

nav33n 472 Purple hazed! Team Colleague Featured Poster

The function is fine. The only place where you can go wrong is while passing the argument to the function. Make sure $a is a valid result resource.

function getRows[$result_resource] {
 return mysql_num_rows[$result_resource];
}
$query = "select * from table where column='somevalue'";
$result = mysql_query[$query];
$totalRows = getRows[$result];
echo $totalRows;

Oh, btw, If your query isn't a valid one, ie., if the table or the column doesn't exist, it will return an error ! :]

Cheers!
Nav

OmniX 21 Practically a Master Poster

Ya but nav my query is fine it just the result resource returns no "rows" and hence throws an error when it goes through that function. Dont forget I have "or die" statement connected to catch and throw errors. Thats what is prolly getting me into trouble? [but it shouldnt because the result resouce is not an error just contains no rows?]... lol im confused now *headache* :[

nav33n 472 Purple hazed! Team Colleague Featured Poster

What exactly is the error message :-/

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

Your skipping a step :P
Because you cant preform num_rows if $b returns no rows and throws an error.
Been trying to come up with a solution but it is annoying me - hopefully I figure it out soon :]

Hi, I have just done a few tests to see what the mysql_query[] function really returns on the technical side and it seems all it returns is instructions on how to access the data within the mysql database. Just thought I would let you's all know. And that I believe is what classifies it as a recourse rather than a result.

OmniX 21 Practically a Master Poster

This is the logic "I think" it is doing.

Because the result resouce contains 'no rows' and then goes to the function to preform the mysql_num_rows it cannot[due to having no rows - I dont know why] and then goes to the or die statement and throws the "Error".

I think if you try the below code it will justify the above logic:

function getRows[$result_resource] {
 $row = mysql_num_rows[$result_resource] or die["Error"];
 return $row;
}
$query = "select * from table where column='somevalue'";
$result = mysql_query[$query];
$totalRows = getRows[$result];
echo $totalRows;

So any ideas on a solution [minus not using the function :D]?

THanks

nav33n 472 Purple hazed! Team Colleague Featured Poster

Because the result resouce contains 'no rows' and then goes to the function to preform the mysql_num_rows it cannot[due to having no rows - I dont know why] and then goes to the or die statement and throws the "Error".

No. result resource simply wouldn't know if it returns "No rows" or "Rows". Does the above function work for you ? If it doesn't, then, I am sorry, I don't know what else to say.

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

Try the proper error reporting and is as follows:

function getRows[$result_resource] {
 $row = mysql_num_rows[$result_resource] or die[mysql_error[]];
 return $row;
}
$query = "select * from table where column='somevalue'";
$result = mysql_query[$query];
$totalRows = getRows[$result];
echo $totalRows;

Post what error that throws and will give you better info on how to solve it.

OmniX 21 Practically a Master Poster

How does it not know to return "rows" or "no rows"?
It should return either mysql_num_rows or die?

The function works fine in every other project I have used it but I dont know why this is not working?

How would you construct the function?
That preforms the same processes?

I was cutting abit of code out but when i use:

or die["Error: 
" . mysql_error[]];

Shouldnt make a difference to the variable?

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

How would you construct the function?
That preforms the same processes?

I would design the function so that instead of insterting the result into the function, you would insert the query string which can then be validated for errors. So I shall make that function that even the slopiest programmer can use.

...
Shouldnt make a difference to the variable?

No that shouldn't make a difference

OmniX 21 Practically a Master Poster

Ya I can make a function that implements the query as well but I require a function that only calls the mysql_num_rows and or die [catch errors?].

Wait you given me an idea, you know what would be cool?

I know I need a function that just uses mysql_num_rows.

But what would be call is a function that can be adapted so you send in your string query into the function and then you have options of returning query, count, array, assoc, etc?

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

Ya I can make a function that implements the query as well but I require a function that only calls the mysql_num_rows and or die [catch errors?].

Wait you given me an idea, you know what would be cool?

I know I need a function that just uses mysql_num_rows.

But what would be call is a function that can be adapted so you send in your string query into the function and then you have options of returning query, count, array, assoc, etc?

I just might try that. But for the moment I have created a function that counts the number of rows and has a validator for anything before the WHERE clause. But you can still use the where clause. Below is an example:

function getrows[$query]
    {
    $query=str_replace["
",' ',$query]; //must start at beginning of line
    if [preg_match['/FROM[\h`\'\"]+[[^`\'\"]+]/i',$query]] {
        preg_match_all['/FROM[\h`\'\"]+[[^`\'\"]+]/i',$query,$tables];
        $table=preg_replace['/FROM[\h`\'\"]+/i','',$tables[0][0],1];
        unset[$tables];
        } else {
        die ["You have not specified your select table correctly.
It should be something like: FROM `table`"];
        }
    $querytype=strtoupper[preg_replace['/[^A-Za-z]/i','',preg_replace['/[[[^a-zA-Z]+]?[A-Za-z]+].*/i','$1',$query]]];
    $where='';
    if [preg_match['/WHERE/i',$query]] {
    $wheres=preg_split['/WHERE/i',$query];
    $where=' WHERE '.$wheres[1];
    unset[$wheres];
    }
    $getrecourse=mysql_query["SELECT * FROM `".$table."`".$where] or die ["Query Performed: SELECT * FROM `".$table."`".$where."

".mysql_error[]];
    if [$querytype=='SELECT' || $querytype=='SHOW'] {
    $result=mysql_num_rows[$getrecourse];
    } else {
    $result=mysql_affected_rows[$getrecourse];
    }
    return $result;
    }


//database connections here

//now to use it
echo getrows["Select * 
FROM     `'table'`
WHERE 'column'= value And column2 =valueb
 "];

OmniX 21 Practically a Master Poster

Query String is the Input I gather?

So on that assumption, you are preforming validation checks on that string and then break it down into variables?

Then you preform the my_sql commands.

Sounds good, something that can start people's ideas.

Still no one has come up with a single mysq_num_rows function? :[

cwarn23 387 Occupation: Genius Team Colleague Featured Poster

Still no one has come up with a single mysq_num_rows function? :[

What do you meen. I just did in my previous post. And it is essensial to have the mysql query text in the function because the SELECT and SHOW query's need the mysql_num_rows[] function while all other query's need the mysql_affected_rows[] function. And the only way to determin which one needs to be used is to see what operator was used in the original mysql query text.

OmniX 21 Practically a Master Poster

Thanks for the help but I require a solution like nav and myself where trying to work with.

I will just use straight mysql_num_rows in the mean time and when I get some spare time work on your function for complex sql needs.

Thanks, Regards X