MySQL COUNT query returning rows when the database is empty

Question

Is there something wrong with the way I'm doing this because I emptied the database table to test this code and it's returning a value of "1" for $cc_rows...

$ccquerycount = "SELECT COUNT(*) FROM payments_cc_info WHERE user_id=$userid";
                $ccresult = mysql_query($ccquerycount) or die(mysql_error());
                $cc_rows = mysql_num_rows($ccresult);

                echo $cc_rows;

                if ($cc_rows > 0) {
                echo '<div class="message_success" align="center">Credit Card info on file.<br />Edit settings to change.</div>';
                } else {
                echo '<div class="message_error" align="center">No Credit Card info on file.<br />Edit settings to change.</div>';
                }

Incidentially, I hope you know what you're doing PCI-wise if you're storing credit card data, and you really shouldn't be using `mysql_*` functions, they've been deprecated. — ceejayoz, Jan 03 '13 at 04:21

score 3 · Answer 1 · answered Jan 03 '13 at 04:15

3

You'll get one row, with one column, and that column has a value of zero.

You need to either do SELECT * FROM payments_cc_info WHERE user_id=$userid and count the rows there, or fetch the value of COUNT(*) and check against it.

answered Jan 03 '13 at 04:15

ceejayoz

176,543
40
303
368

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

Insted of count, Exists is faster :)

You may try the following too.

SELECT IF(EXISTS(SELECT * FROM payments_cc_info WHERE user_id=$userid),1,0) AS result

Reference post: Best way to test if a row exists in a MySQL table.

edited May 23 '17 at 11:48

Community

1
1

answered Jan 03 '13 at 04:25

bonCodigo

14,268
1
48
91

score 0 · Answer 3 · answered Jan 03 '13 at 04:28

Since we are using mysql_num_rows it will always return value "1" with count() query but important to notice is that 1 is the total number of rows returned by the query not the value of count().

Use of mysql_fetch_row should solve the problem.

$ccquerycount = "SELECT COUNT(*) FROM payments_cc_info WHERE user_id=$userid";
$ccresult = mysql_query($ccquerycount) or die(mysql_error());

$row = mysql_fetch_row($ccresult);
$cc_rows = $row[0];

mysql_close($con);

echo $cc_rows;

if ($cc_rows > 0) {
                echo '<div class="message_success" align="center">Credit Card info on file.<br />Edit settings to change.</div>';
                } else {
                echo '<div class="message_error" align="center">No Credit Card info on file.<br />Edit settings to change.</div>';
                }

MySQL COUNT query returning rows when the database is empty

3 Answers3