Easiest way on server to Count Num Rows that Match Statement

Forum Moderators: open

Message Too Old, No Replies

Easiest way on server to Count Num Rows that Match Statement

Jeremy_H

5:59 pm on Dec 7, 2006 (gmt 0)

Hello,

I'm trying to go as easy on my server as I can.

I'm basically trying to check my server if there is an instance in my table where two things are true.

I'm using the following command:

if(mysql_num_rows(mysql_query("SELECT * FROM table_name WHERE value1='$value1' AND value2='$value2'"))==1){...}

Just looking at this, it seems a little heavy. I'm using a SELECT command, but I really don't need to gain any information, I'm just counting the number of rows that meet my criteria.

The reason I'm trying to go light on my server is that this is the first step to determine how information will be stored. All requests will get this look up, and from this the script will decide how best to handle the user. All transactions on this page will be looked up in my table.

Any modifications or suggestions on how to be nicer to my server is much appreciated.

Thanks

LifeinAsia

6:07 pm on Dec 7, 2006 (gmt 0)

Use "SELECT COUNT(*) AS Foo" instead of "SELECT *"

Foo will be the number of records matching.

Jeremy_H

6:18 pm on Dec 7, 2006 (gmt 0)

Thanks LifeinAsia,

I'm a little confused, would this code be what your recommending:

if(mysql_query("SELECT COUNT(*) AS foo FROM table_name WHERE value1='$value1' AND value2='$value2'")==1){...}

I've taken out the mysql_num_rows and added the COUNT(*) AS foo.

Did I understand correctly?

Thanks

peterdaly

6:35 pm on Dec 7, 2006 (gmt 0)

"Select count(*) from foo" will return a result set of one row with one field. That one field will be your count. You read the resulting value the same way you would a query that returns one row. The query doesn't direct return an int value as if it were a php function call.

phranque

11:56 pm on Dec 7, 2006 (gmt 0)

yes jeremy - your understanding is correct and i believe peterdaly actually changed the query to confuse things a bit.
he is correct however that the query

will return a result set of one row with one field

and in your query that field or key name will be "Foo".

FalseDawn

4:49 pm on Dec 8, 2006 (gmt 0)

I'm basically trying to check my server if there is an instance in my table where two things are true.

An arguably more efficient method is to use EXISTS (if you don't actually need the count of the rows, and your database supports subqueries), such as :

SELECT 1 FROM dual WHERE EXISTS (SELECT * FROM table WHERE value1=myval1 AND value2=myval2)

Jeremy_H

7:16 pm on Dec 8, 2006 (gmt 0)

FalseDawn, I'm intrigued by your approach, but I'm not able to find much information about the EXISTS command. Is this part of the MySQL query?

Would proper code have this structure:

if(mysql_query("SELECT 1 FROM table_name WHERE EXISTS (SELECT * FROM table_name WHERE value1='$myval1' AND value2='$myval2')")){...}

FalseDawn

11:00 pm on Dec 8, 2006 (gmt 0)

Bear in mind that "dual" is a special table (it's actually a throwback from my Oracle days), built into most RDBMSs that contains exactly one row, a "dummy" record, so can be used in SELECTs to return one (or zero) rows.

Your code fragment still looks incorrect - you will need to create the query object first (as has been mentioned), then check if a row has been returned or not.

As far as EXISTS goes - its use is almost exclusive to subqueries.
[dev.mysql.com...]

[edited by: FalseDawn at 11:01 pm (utc) on Dec. 8, 2006]