left join outputs incorrect first block of data

Question

I'm trying to get data from two tables:

users

and

posts

using

left join

.

$items = '';

$sql = "

SELECT u.id as uid

     , u.name as uname

     , p.id as pid

     , p.date as pdate

     , p.title as ptitle

     , p.user as puser 

  FROM users u 

  LEFT 

  JOIN posts p 

    ON u.id = p.user

 WHERE u.role = 'mod'

 GROUP 

    BY p.title;";  



$stmt = $db->query($sql);

while($row = $stmt->fetch()){

    $date = strtotime($row['pdate']);

    $date = date("d-m-Y", $date);

    $items.= "<div class='itemp' data-id=" . $row['pid'] . " data-user='" . $row['uname'] . "'>" . 

    "<span class='spandate'>" . $date . "</span>" . 

    "<span class='spantitle'>" . $row['ptitle'] . "</span>" .

    "<span class='spanuser'>" . $row['uname'] . "</span>" .

    "</div>\n";

}

echo $items;

Output is ok except first row where I see the date - 1.1.1970 - but there is no any row in

posts

with that date. Plus - in the same row

ptitle

is missing.

Also, is there a better way to create this query, avoiding

as

keyword ?

desired output (single block):

<div class='itemp' data-id=116 data-user='JOHN SMITH'><span class='spandate'>01-12-2017</span><span class='spantitle'>BLUE SKY</span><span class='spanuser'>JOHN SMITH</span></div>

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Also, is there a better way to create this query, avoiding as keyword ?

Yes (but not better in my opinion):

SELECT users.id,
  users.name,
  posts.id,
  posts.date,
  posts.title,
  posts.user
FROM users
LEFT 
JOIN posts  
  ON users.id = posts.user
WHERE users.role = 'mod'
GROUP 
  BY posts.title;

But as mentioned in the comments you have to modify your while loop in this case to get access to your values. But I think this is not a better way of writing this query cause it makes it harder to read.

Regarding your JOIN problem. this depends on what your result should be (I guess you only want users with posts ). So you should take a look at INNER JOIN instead of LEFT JOIN. You will get only results which also have posts entrys. Due to the nature of LEFT JOIN all entrys on the left table (in your case users) will be used, even if they don´t have an entry in the posts table. INNER JOIN will only return results which match results in both tables.

left join outputs incorrect first block of data

Sign up