How to effectively replace (mysql_result(mysql_query()) in PDO?

Question

As my process is almost complete for rewriting web with PDO instead of mysql_* commands I am now testing my changed functions. And It seems that my changed function for mysql_result(mysql_query() always returns true, why is that? Lets see original and changed code:

if (mysql_result(mysql_query("SELECT COUNT(*) FROM account WHERE id='".$_SESSION["user_id"]."' AND online=1"), 0)>0) { return true; } else return false;

And changed code here:

$stmt = $db_login->prepare("SELECT COUNT(*) FROM account WHERE id=:id AND online=1"); $stmt->bindValue(':id', $_SESSION["user_id"], PDO::PARAM_INT); $stmt->execute(); $results_login = $stmt->fetch(PDO::FETCH_ASSOC); $rows = count($results_login); if ($rows > 0) { return true; } else return false;

So what is wrong with is why it always returns true even when column has online=0? Thank you

@tereško so in my case it would be $rows = count(array(0)); ? — Byakugan
– Byakugan, Commented Jun 10, 2012 at 11:17
please, go and learn php, instead of blindly copy-pasting code — tereško
– tereško, Commented Jun 10, 2012 at 11:20
In my learning books there is always mysql_* commands :( so I am trying to learn PDO and system of it on my own :) and I already know that count returns big number from the answe so i dont get it why you posted it here :) — Byakugan
– Byakugan, Commented Jun 10, 2012 at 11:27
@Byakugan: If you see somthing is in error, try to locate the error. Not just broadly asking. I can understand however, if you change to something new, that it is harder to locate the point of error. But nevertheless, try to find "the one point" how the error happens with debugging, like using var_dump($results_login); and such. — hakre
– hakre, Commented Jun 10, 2012 at 12:13
OK thnk you will do :) I just thought it could happen to other people and could be usefull to somebody in future :) — Byakugan
– Byakugan, Commented Jun 10, 2012 at 12:38

deceze · Accepted Answer · 2012-06-10 10:30:55Z

1

$stmt->fetch fetches one row from the result set. What you get out of that is an array containing all the selected columns, looking something like this:

array( 'COUNT(*)' => 42 )

A count() on that array will always result in 1.

You need to check the contents of the fetched row:

if ($result_login['COUNT(*)'] > 0)

It's best to alias this column to a nicer name:

SELECT COUNT(*) AS `count` ...

Then:

if ($result_login['count'] > 0)

answered Jun 10, 2012 at 10:30

deceze♦

525k89 gold badges807 silver badges954 bronze badges

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

3 Comments

Byakugan Over a year ago

When I want this to be real rows count what should I use? For example looking for how many times online=1 in every accounts and compairing them what should I use? For intance when I search if there is more than 50 accounts online what check function I should use instead of this mysql_result?

deceze Over a year ago

You are already SELECTing the COUNT(). This will always result in one row with the data you want. That means you are letting the database do the counting. The alternative would be to simply SELECT all rows (no COUNT()), then check using rowCount how many rows you fetched. That's quite a wasteful operation though.

Byakugan Over a year ago

What would be the case if I choose SELECT * FROM command and want to know number of rows in result - can I just use $result_login['*'] ? Thank you

Collectives™ on Stack Overflow

How to effectively replace (mysql_result(mysql_query()) in PDO?

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related