CODEWITHSUNDEEP
phphtmlmysqlsqldatabase
user3537990
user3537990

Reputation:

Only getting one record returned

So I'm trying to retrieve a list of likes. Here's my code 

//Echo total likes of #68
$stmt1 = $con->prepare("SELECT COUNT(*) FROM likes WHERE liked_post_id = :id;");
$stmt1->bindValue(':id', $id, PDO::PARAM_STR);
$stmt1->execute();
$count = $stmt1->rowCount();
echo $count;

I'd assume that this would return all likes. But I only get 1 returned. Here's the structure 

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| liked_post_id   | varchar(255)     | YES  |     | NULL    |                |
| liked_post_user | varchar(255)     | YES  |     | NULL    |                |
| liked_post_ip   | varchar(255)     | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

And a quick query 

+----+---------------+-----------------+---------------+
| id | liked_post_id | liked_post_user | liked_post_ip |
+----+---------------+-----------------+---------------+
| 77 | 68            | alex            | 127.0.0.1     |
| 78 | 68            | andrew          | 127.0.0.1     |
+----+---------------+-----------------+---------------+

So instead of getting 1 i'd expect to get 2, since two users liked it. Any ideas? Also I'm not getting any errors.

Upvotes: 2

Views: 62

Answers (3)

Jorge Fuentes González
Jorge Fuentes González

Reputation: 11809

This is the query result (SELECT COUNT(*) FROM likes WHERE liked_post_id = :id;):

+----------+
| COUNT(*) |
+----------+
| 2        |
+----------+

rowCount(); return the number of rows returned, and SELECT COUNT(*) FROM likes WHERE liked_post_id = :id; will return only 1 row, a row with the number of likes.

You must read the contents of that row or do a complete SELECT this way SELECT * FROM likes WHERE liked_post_id = :id;. The later is less optimized, but if you are going to make a complete select in some part of your code you can reuse the returned content, so you make less queries to the database.

Upvotes: 0

Barmar
Barmar

Reputation: 780984

Do this:

$stmt1 = $con->prepare("SELECT COUNT(*) AS count FROM likes WHERE liked_post_id = :id;");
$stmt1->bindValue(':id', $id, PDO::PARAM_STR);
$stmt1->execute();
$row = $stmt1->fetch(PDO::FETCH_ASSOC);
echo $row['count'];

The query returns 1 row, and the contents of that row is the count of matching rows in the table. You need to fetch the row to read that value.

Upvotes: 1

J A
J A

Reputation: 1766

COUNT() is an aggregate function and therefore, returning the number of rows as result. If you'd like to get the list, you should replace COUNT(*) with *.

SELECT likes.* FROM likes WHERE liked_post_id = :id

Upvotes: 0

Related Questions