CODEWITHSUNDEEP
phpmysqlsqlpdo
Eddie
Eddie

Reputation: 337

PDO - Join Queries and output correct data

I'm currently trying to make very basic support tickets.

I have two database tables support and supportreplies support has columns id, userid, message, creationdate and supportreplies has id, supportid, userid, comment, messagedate

Table layout:

1) support (id, userid, message, creationdate)
2) supportreplies (id, supportid, userid, comment, messagedate)

Then the join.

$sqlt=$dbht->prepare("SELECT *
FROM supportreplies 
LEFT JOIN support 
ON supportreplies.supportid=support.id
GROUP BY supportid ORDER BY supportid DESC");

Then outputting the tickets like this:

foreach ($sqlt->fetchAll() as $rows){
echo $rows['message']; 
echo $rows['creationdate'];
echo $rows['message'];
echo $rows['comment'];
echo $rows['messagedate'];
}

Now this is working to an extent, except that it only loads the first comment for each ticket. I'm lost at what to try next, how can I get this working?

Upvotes: 0

Views: 91

Answers (2)

Nathan Robb
Nathan Robb

Reputation: 193

What you're looking for is something that needs to be done in two different queries. One needs to SELECT all the tickets, and then for each ticket, run another query to get all the replies with a supportreplies.supportid that matches a support.id.

This can be done natively in MySQL using something called Subqueries: https://dev.mysql.com/doc/refman/5.0/en/subqueries.html

You will have to find the way to implement this for your code though ;)

Upvotes: 1

Sculper
Sculper

Reputation: 755

The problem is your GROUP BY clause. by grouping your records by supportid, you're effectively "grouping" all results for each supportid into one record.

Is there a reason that you need your results grouped? If not, you could simply remove the clause:

$sqlt=$dbht->prepare("SELECT *
FROM supportreplies 
LEFT JOIN support 
ON supportreplies.supportid=support.id
ORDER BY supportid DESC")

Upvotes: 0

Related Questions