在SQL中使用Case

Question

My sql table is like

posts-> id  postedby   timestamp
        1     1        12716826

comments-> id     postid     comby     tstamp      condition
            1       1           1         12716826      me
            2       1           1         12716826      all
            1       1           2         12716826      me
            1       1           3         12716826      all

My question is when i make a query to fetch all comments of post 1 . i want following condition

The comment with condition me should be visible to only me or post owner

I made a query like $myid=2; $postbyid=1;

$res=$db->query("SELECT * from comments where postid='$postbyid' 
                order by tstamp desc");
while ($row=$res->fetch_assoc())
{
if($row['comby']==$uid || $row['comby']=='$postbyid')
{
$data[]=$row;
}

else
{

if($row['condition']!='me')
{
$data[]=$row;
}
}
}

This may be wrong. Is there a way do this using SQL CASE?

like

 SELECT * from comments where postid='$postbyid' CASE WHEN comby='$myid' 
THEN SHOW ALL WHEN combyid='$postbyid' THEN SHOW ALL else dont 
show this row order by tstamp desc

Answer 1

You just need to tweak your query conditions:

  SELECT *
    FROM comments
   WHERE postid = '$postbyid'
     AND comby IN ('$myid', '$postbyid')
ORDER BY tstamp DESC

I didn't get what you want with condition field, but from that query you can add another AND condition to satisfy the visibility restriction.

Note that your code is vulnerable to SQL injection attacks, seems like that you're using mysqli module to work with database, so take a look at prepared statements section of mysqli manual: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Good luck!