CODEWITHSUNDEEP
phpmysqlcountrow
user701510
user701510

Reputation: 5763

how to count the number of rows in SQL table using specific criteria?

I have two columns of data. One column is called "like" which holds either the number 1 or 0. The other column is called "object" which can hold any word. I want to count the number of rows where the number 1 in the "like" column coincides with a certain word in the "object" column.

I have tried

$numrow1 = mysql_query("SELECT * FROM tablename WHERE like = 1 AND object = '$object' "); 
$numlikes = mysql_num_rows($numrow1);

but I got a syntax error. Any help would be appreciated.

Upvotes: 0

Views: 1716

Answers (2)

Bjoern
Bjoern

Reputation: 16304

LIKE is a sql string comparison operator, maybe even object. If you have a field with a reserved name, then you have to directly address it or escape it.

Reformat your SQL statement inside the mysql_query like this:

SELECT * 
FROM tablename AS T 
WHERE T.like = 1 
      AND T.object = '$object'

Upvotes: 2

user330315
user330315

Reputation:

If you only want to get the number of rows using, count() is much more efficient:

SELECT count(*) 
FROM tablename 
WHERE `like` = 1 
AND object = '$object'

You did not show us the error message, but I gues it's because like is a reserved word, and therefor you need to quote the column name (I would strongly recommend to use a different name to avoid those problems in the future).

If you are running MySQL in ANSI compliant mode, you can also use the standard double quotes to escape the column name:

SELECT count(*) 
FROM tablename 
WHERE "like" = 1 
AND object = '$object'

I'm not sure, but object could be a reserved word as well, so that might need quoting too.

Upvotes: 2

Related Questions