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
– ceejayoz, Commented Jan 3, 2013 at 4:21

ceejayoz · Accepted Answer · 2013-01-03 04:15:17Z

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 3, 2013 at 4:15

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:48:53Z

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, 2017 at 11:48

CommunityBot

11 silver badge

answered Jan 3, 2013 at 4:25

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Comments

Saurabh · Accepted Answer · 2013-01-03 04:28:01Z

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>';
                }

Collectives™ on Stack Overflow

MySQL COUNT query returning rows when the database is empty

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related