num_rows in postgres always return 1

Question

I'm trying to do a SELECT COUNT(*) with Postgres.

What I need: Catch the rows affected by the query. It's a school system. If the student is not registered, do something (if).

What I tried:

$query = pg_query("SELECT COUNT(*) FROM inscritossimulado
            WHERE codigo_da_escola = '".$CodEscola."'
            AND codigo_do_simulado = '".$simulado."'
            AND codigo_do_aluno = '".$aluno."'");

if(pg_num_rows($query) == 0)
{
    echo "Error you're not registered!";
}
else
{
    echo "Hello!";
}

Note: The student in question IS NOT REGISTERED, but the result is always 1 and not 0.

For some reason, when I "show" the query, the result is: "Resource id #21". But, I look many times in the table, and the user is not there.

I don't know PHP, but the query will indeed return exactly one row. You need to get the **value** returned by the query not the number of rows returned by it. — , May 15 '14 at 21:16
Unless you want an uncomfortable conversation with [lil' Bobby Tables' parents](http://bobby-tables.com/), read http://www.php.net/manual/en/security.database.sql-injection.php . Look at PDO and parameterized statements. — Craig Ringer, May 16 '14 at 02:53

score 3 · Accepted Answer · answered May 15 '14 at 21:18

3

You are counting the number of rows in the answer, and your query always returns a single line.

Your query says: return one row giving the number of students matching my criteria. If no one matches, you will get back one row with the value 0. If you have 7 people matching, you will get back one row with the value 7.

If you change your query to select * from ... you will get the right answer from pg_num_rows().

answered May 15 '14 at 21:18

unpythonic

4,020
19
20

2

I would assume it's more efficient to retrieve the value of the `count(*)` instead. If for some reason the conditions return thousands of users, that will really make this slow. – May 15 '14 at 21:23

score 3 · Answer 2 · edited May 23 '17 at 12:31

3

Actually, don't count at all. You don't need the count. Just check for existence, which is proven if a single row qualifies:

$query = pg_query(
  'SELECT 1
   FROM   inscritossimulado
   WHERE  codigo_da_escola = $$' . $CodEscola . '$$
   AND    codigo_do_simulado = $$' . $simulado. '$$
   AND    codigo_do_aluno = $$' . $aluno . '$$
   LIMIT  1');

Returns 1 row if found, else no row. Using dollar-quoting in the SQL code, so we can use the safer and faster single quotes in PHP (I presume).

The problem with the aggregate function count() (besides being more expensive) is that it always returns a row - with the value 0 if no rows qualify.

But this still stinks. Don't use string concatenation, which is an open invitation for SQL injection. Rather use prepared statements ... Check out PDO ...

edited May 23 '17 at 12:31

Community

1
1

answered May 15 '14 at 22:57

Erwin Brandstetter

605,456
145
1,078
1,228

Don't use dollar-quoting to enclose PHP variables. Nothing tells that these variables don't contain `$$` and that just creates a new opportunity for SQL injections, one that is not avoided by the recommended quoting method: `pg_escape_string`. – Daniel Vérité May 16 '14 at 14:25
@DanielVérité: `$$` or `'`, same principal, that's why I suggested prepared statements. – Erwin Brandstetter May 16 '14 at 18:30

num_rows in postgres always return 1

2 Answers2