Select query in MySQL

If you run a separate request for each message sent, which is similar to what you are trying to do, we are just guessing based on the two messages you requested that return a single string ...

To use this pattern, you need to add an additional predicate to the clause WHERE

to determine which post

comment count you want to return. For example:

SELECT SUM(c.total_comment) AS comment
  FROM user_comment c
 WHERE c.status = 0 
   AND c.image_id = 2

      

        
        
        
      

    

If image_id

there are no rows for the given , the aggregate SUM()

will return NULL. You can handle this in your code and convert it to zero, or you can handle this in SQL so that it returns zero and not NULL. You can use a convenient function IFULL()

...

SELECT IFNULL(SUM(c.total_comment),0) AS comment
  FROM user_comment c
 WHERE c.status = 0 
   AND c.image_id = 2

      

        
        
        
      

    

If the first argument of the IFNULL function is NULL, the function returns the second argument, otherwise it returns the first.

IFNULL(a,b) 

      

        
        
        
      

    

is short for MySQL for ANSI

CASE WHEN a IS NULL THEN b ELSE a END

      

        
        
        
      

    

This answers the question you asked. How to get an invoice for a specific job.

The other answers here address the issue with this approach ... running a separate request for each post is NOT the most efficient or most scalable design.

Each query requires feedback from the database, the database needs to parse the SQL text, validate the syntax, perform semantic validation, look up dictionaries to make sure the identifiers are valid, the database user has the necessary privileges, decide on the execution plan, and then run the query, prepare the result set and return to the caller. And this superstructure folds in a narrow loop.

Some other answers are addressed here. Instead of running a separate query for each image_id

, they get the count in one fell swoop. They do this by adding a column image_id

to the SELECT list and adding GROUP BY image_id

... to the query to return a result like this

image_id  comment
--------  -------
       2        3
       5        2

      

        
        
        
      

    

This will return all non-zero values ​​to you. BUT ... you don't run this in a loop that fetches rows from a columnar table ... you must first run this query and store the results in an array, and then for each post you paginate, you can find the array to find counter. If it is not found, then the count is zero.

You can also return null values ​​and store them just like a query:

SELECT p.id
     , IFNULL(SUM(total_comment),0) AS comment 
 FROM post p
 LEFT
 JOIN user_comment c 
   ON c.image_id = p.id
  AND c.status = 0
GROUP BY p.id

      

        
        
        
      

    

But that would be extra strings with zero, still searching for an array, the only difference is that you find a match ... really nothing else.

BUT ... you really don't want to do this at all.

What the query is really hinting at is the idea that you don't need a SEPRATE query to get the comment count; you can get this in ONE QUALITY that returns strings from post

.

Just add an outer join to the table user_comment

, add a clause GROUP BY

to the unique identifier in the table, post

and return that SUM () aggregate expression to the SELECT list along with the columns you rotate from the table post

. For example:

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , IFNULL(SUM(c.total_comment),0) AS comment 
 FROM post p
 LEFT
 JOIN user_comment c 
   ON c.image_id = p.id
  AND c.status = 0
GROUP BY p.id
ORDER BY p.Post_Date DESC, p.id DESC

      

        
        
        
      

    

This is usually a much more scalable design to return results in a single query, rather than launching a boat with separate queries.

Alternatively, usually not as efficient as outer join, you should use a correlated subquery in the SELECT list:

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , ( SELECT IFNULL(SUM(c.total_comment),0)
           FROM user_comment c
          WHERE c.image_id = p.id
            AND c.status = 0
       ) AS comment
 FROM post p
ORDER BY p.Post_Date DESC, p.id DESC

      

        
        
        
      

    

Or we could also use the outer join operation for the inline view (but this is more applicable in the more general case when there are more joins involved and we don't want to double or triple the count, although this returns the same result in this case (assuming that id

is unique in the table post

)

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , IFNULL(t.total_comment,0) AS comment
  FROM post p
  LEFT
  JOIN ( SELECT c.image_id 
              , SUM(c.total_comment) AS total_comment
           FROM user_comment c
          WHERE c.image_id = p.id
          GROUP BY c.image_id
       ) t
    ON t.image_id = p.id
 ORDER BY p.Post_Date DESC, p.id DESC

      

        
        
        
      

    

Followup

Based on the PHP code you posted, point ... you only need the SQL SINGLE statement .

Just return the resulting "total_comment" value for each post along with each post line, just change the query (as I pointed out in my answer above, there are several possible ways to get this result. Just tell the database to return this post (And for a more deterministic result, add a sentence ORDER BY

to the query.)

For example:

$sql="SELECT p.Id
           , p.Post_Title
           , p.Post
           , p.Filename
           , p.Post_Date
           , p.Post_By
           , IFNULL(SUM(c.total_comment),0) AS total_comment
        FROM post p
        LEFT
        JOIN user_comment c 
          ON c.image_id = p.id
         AND c.status = 0
       GROUP BY p.id
       ORDER BY p.Post_Date DESC, p.id DESC"; 

$stmt=$conn->prepare($sql);
$stmt->execute();

while($post = $stmt->fetch(PDO::FETCH_OBJ)):
    echo "<hr>";
    echo "<br>" . $post->Id;
    echo "<br>" . $post->Post_Title;
    echo "<br>" . $post->Post;
    echo "<br>" . $post->Filename;
    echo "<br>" . $post->Post_Date;
    echo "<br>" . $post->Post_By;
    echo "<br>" . $post->total_comment;
endwhile;

      

        
        
        
      

    

This is a very basic example that just displays the return values ​​without all the HTML you really want to output. It serves to demonstrate that you do not need to run your code with a second or third SQL statement.

And (obviously) this example does not cover escaping potential HTML characters stored in database columns, nor does it handle PDO error handling, either setAttribute(PDO::ATTR_ERRORMODE,PDO::ERRMODE_EXCEPTION)

, and the corresponding try / catch / finally blocks, or adding separate return checks from calls prepare

and execute

. They are omitted to avoid pattern obfuscation.