MySQL SELECT COUNT with if statement - What is the best practice?

Question

I'm getting inconsistent results with the way I'm doing this so I thought I'd ask the geniuses here what you think. Here is what I'm doing...

$ccquerycount = "SELECT COUNT(*) as `count` FROM payments_cc_info WHERE user_id=$userid";
....
....
if ($row['count'] == "1") {
} else {
}

Is there a better way to design the if statement to achieve consistent results?

Considering you never execute the query, this should always be false. I don't see how it's not consistent. — Waleed Khan, Jan 03 '13 at 03:18
Also, if you *did* execute the query, you'd be open to SQL injection. Parametrize your queries. — Waleed Khan, Jan 03 '13 at 03:19
I did execute, I just didn't include that line. Sorry. I was more concerned with the way the if statement was worded. — Budove, Jan 03 '13 at 03:20
Edited the code a bit. If its not what you want revert it back. — Shiplu Mokaddim, Jan 03 '13 at 03:25

Mudassir Hasan · Answer 1 · 2013-01-03T03:44:32.683

0

Try this

  $ccquerycount = "SELECT * FROM payments_cc_info WHERE user_id=$userid";
    $result = mysql_query($ccquerycount)
    if (mysql_num_rows($result)==1) {
    } else {
    }

mysql_num_rows() Get number of rows in result

Warning mysql_ extension is being deprecated . Instead, the MySQLi or PDO_MySQL extension should be used .

edited Jan 03 '13 at 03:44

answered Jan 03 '13 at 03:25

Mudassir Hasan

28,083
20
99
133

1

This is wrong. If you want to use `mysql_num_rows()`, then you'll have to change the query. Because you're doing a `SELECT COUNT(*)` which will always return 1 row. If you change your query to a `SELECT *` then `mysql_num_rows` is what you expect. – wakooka Jan 03 '13 at 03:35
1

[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/a/14110189/1723893). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – NullPoiиteя Jan 03 '13 at 03:43

score 0 · Answer 2 · answered Jan 03 '13 at 03:25

I am assuming that you are executing the query, retrieving the result set, fetching the row and extracting the value before the if statement (if not, then perhaps you need to do that first! Here is a start for PHP-MySql.

In case if you have already done that and forgot to mention this in your post (innocent until proven guilty I always say!! :) ), then could you provide us with more data on what are the different datasets when you got inconsistent results?

Dipesh Parmar · Answer 3 · 2013-01-03T04:25:53.617

You can also add if condition inside of your mysql query like below.

SELECT 
   rating,
   trade_service_id,
   ifnull(round(sum(belonging_product_ratings.rating)/count(*),0),0) AS avg,
   count(*) AS count
FROM
   belonging_product_ratings WHERE trade_service_id != 0
GROUP BY
   belonging_product_ratings.trade_service_id

other alternative is as below

select
   BelongingProductRating.*,
   User.*
from
   belonging_product_ratings AS BelongingProductRating
LEFT OUTER JOIN
   users AS User ON
   IF( BelongingProductRating.rated_user_id != '$userID', BelongingProductRating.rated_user_id, BelongingProductRating.user_id) = User.id

You can say if condition inside mysql query is same as ternary if.
You can set query according to your php variable too.

I am sharing this code which i was ended up. all you need to do is set it according to your table fields and conditions. feel free to ask.

MySQL SELECT COUNT with if statement - What is the best practice?

3 Answers3