CODEWITHSUNDEEP
php
Alaa Gamal
Alaa Gamal

Reputation: 1125

get count of posts based on count(*)

i am trying to get number of posts that i have

Here is my query

$Query="
    SELECT t.*,u.*,c.*
    FROM posts as t
    LEFT JOIN relations as r on r.post_id = t.post_id
    LEFT JOIN users as u on t.auther_id = u.auther_id
    LEFT JOIN categories as c on c.cate_id = r.cate_id
    GROUP BY t.post_id
";

    $Query=mysql_query($Query);
    $numberOfPosts=mysql_num_rows($Query);

This query is works very well

but i am trying to convert it, i want make it faster

i want use count(*) instead of t.*

because when i use t.*, it gets the full data of posts and categories

but i want to get count only, So i decided to use count(*) but i don't know how to use it with query like this

Edit

i've replaced SELECT t.*,u.*,c.* with SELECT count(t.*)

But i got mysql Error Warning: mysql_fetch_assoc(): supplied argument

Edit 2:

i am trying SELECT count(t.post_title)

I Got this results

Array ( [count(t.post_id)] => 10 )

But i have only 2 posts!

Upvotes: 0

Views: 85

Answers (4)

JYelton
JYelton

Reputation: 36512

$Query="
    SELECT t.*,u.*,c.*
    FROM posts as t
    LEFT JOIN relations as r on r.post_id = t.post_id
    LEFT JOIN users as u on t.auther_id = u.auther_id
    LEFT JOIN categories as c on c.cate_id = r.cate_id
    GROUP BY t.post_id
";

    $Query=mysql_query($Query);
    $numberOfPosts=mysql_num_rows($Query);

Let's take a step back and analyze this query for a moment.

You're selecting everything from three out of four tables used in the query. The joins create some logic to limit what you select to the proper categories, authors, etc. At the end of the day you are getting a lot of data from the database, then in PHP simply asking it how many rows were returned (mysql_num_rows). Instead, what @Dagon is trying to suggest in comments, is that you have MySQL simply count the results, and return that.

Let's refactor your query:

$query = "
    SELECT COUNT(t.post_id) AS qty
    FROM posts as t
        LEFT JOIN relations AS r ON r.post_id = t.post_id
        LEFT JOIN users AS u ON t.auther_id = u.auther_id
        LEFT JOIN categories AS c ON c.cate_id = r.cate_id
        GROUP BY t.post_id
";

    $result = mysql_query($query);
    $result_row = mysql_fetch_assoc($result);
    $numberOfPosts = $result_row['qty'];

(You could also use Barattlo's custom execute_scalar function to make it more readable.)

I would need to see your table structures to be of more help on how to optimize the query and get the desired results.

Upvotes: 2

Sven
Sven

Reputation: 70863

You should probably simply use

SELECT count(*) as postingcount FROM posts

Why?

Because you do not have a WHERE clause, so there are no restrictions. Your JOINS do not ADD more rows to the resultset, and in the end your GROUP BY merges every duplicate occurance of a post_id that might have occurred because of joining back into one row. The result should only be counted, so assuming that the real number you want to know is the number of data sets inside the table posts, you do not need any join, and doing count(*) really is a very fast operation on tables in MySQL.

Remember to check if mysql_query returns false, because then you have to check mysql_error() and see why your query has an error.

Upvotes: 0

thatidiotguy
thatidiotguy

Reputation: 8991

You want to do

SELECT count(t.id) AS count FROM ....

//do query with PDO or whatever you are using

$rows = mysql_fetch_assoc();
$num_rows = $rows['count'];

Upvotes: 0

Teena Thomas
Teena Thomas

Reputation: 5239

try doing this:

  $Query="
  SELECT count(t.*) as count_all
  FROM posts as t
  LEFT JOIN relations as r on r.post_id = t.post_id
  LEFT JOIN users as u on t.auther_id = u.auther_id
  LEFT JOIN categories as c on c.cate_id = r.cate_id
  GROUP BY t.post_id
  ";

$Query=mysql_query($Query);
$numberOfPosts=mysql_num_rows($Query);

Upvotes: 0

Related Questions