What value is returned from an empty sql query?

[OmniX]

Sorry been out of commision for a few months.
Got a newbe question.

What value is returned from an sql query that returns no rows?

Example:

$a = "SELECT * FROM a WHERE b = c";
$b = my_sql_query($a);

Note:
Table Name - a
Table Field - b
Table Field Value - c

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

Thanks, Regards X

PS: I am assuming 0 or NULL but nothing seems to be working, thanks.

[cwarn23]

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]

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..
http://in.php.net/function.mysql-query

[OmniX]

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]

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]

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]

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]

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]

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]

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]

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

[nav33n]

:D So, its fixed now ?

[OmniX]

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]

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]

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]

What exactly is the error message :-/

[cwarn23]

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]

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]

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]

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]

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: <br />" . mysql_error());

Shouldnt make a difference to the variable?

[cwarn23]

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]

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]

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.<br>It should be something like: <b>FROM `table`</b>");
        }
    $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 ("<b>Query Performed:</b> SELECT * FROM `".$table."`".$where."<br><br>".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]

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]

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]

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