SQL query, JOIN another table syntax error

Question

My tables:

Contents: content_id, publisher_id, title, category, trend

Users: id, name, rank

I am tring to JOIN to Users and get name and rank

My code:

$query = $conn->prepare
     ("select * from contents 
     FULL OUTER JOIN users ON contents.publisher_id = users.id 
     WHERE trend =1 order by content_id desc limit 20");
$query->execute(array());

};

$data = array();
while($result= $query->fetch(PDO::FETCH_ASSOC)) {
$data[] = array("content_id"    =>$result['content_id'], 
                "publisher_id"  =>$result['publisher_id'],
                "publisher_name"=>$result['name'],
                "publisher_rank"=>$result['rank],
                "title"         =>$result['title'],
                "category"      =>$result['category'],
                "type"          =>$result['type']
                );

};
echo json_encode($data);

Please give a data set of examples and the exact output you desire from the query. — user1735921, Sep 24 '17 at 09:07
Also mysql not support FULL OUTER JOIN. https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — B. Desai, Sep 24 '17 at 09:14
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 24 '17 at 09:15

score 0 · Answer 1 · answered Sep 24 '17 at 10:00

Try this one. Hope this help

$query = $conn->prepare("select * from contents JOIN users ON contents.publisher_id = users.id 
     WHERE trend = 1 order by content_id desc limit 20");
$query->execute();

$data = array();
while($result= $query->fetch(PDO::FETCH_ASSOC)) {
$data[] = array("content_id"    =>$result['content_id'], 
                "publisher_id"  =>$result['publisher_id'],
                "publisher_name"=>$result['name'],
                "publisher_rank"=>$result['rank'],
                "title"         =>$result['title'],
                "category"      =>$result['category'],
                "type"          =>$result['type']
                );

};
echo json_encode($data);

SQL query, JOIN another table syntax error

1 Answers1