MySQL Forums
Forum List  »  PHP

Trying to do Inner Join, plus DISTINCT on a column
Posted by: Simon Lewis
Date: November 12, 2019 10:09AM

I am building a ticketing system for a website.
The admin side needs to see all tickets, and then to be able to filter them.

It's split into two DB Tables.
One which contains the ID of the ticket, their Userid, and a few other bits.
The ticketmessages table contains the 'conversation'.

So when they get to 'tickets' they need to see ALL the distinct tickets, and their current status, set by the latest ticket in ticketmessages.status.

At the moment this is not working.

$query = ("SELECT tickets.userid, tickets.id, ticketmessages.topicid, ticketmessages.topic, ticketmessages.comments, ticketmessages.sentby, ticketmessages.messagefrom, ticketmessages.messageto, ticketmessages.dateupdated, ticketmessages.status FROM tickets INNER JOIN ticketmessages ON tickets.id = ticketmessages.topicid WHERE ticketmessages.status =:statusfilter GROUP BY tickets.id ORDER BY datecreated DESC");
$result = $pdo->prepare($query);
$result->execute(array(':statusfilter' => $statusfilter));
}
$num_tickets = $result->rowCount();

This is the code. $statusfilter is assigned into a session.
If they choose to clear the filter, it does so and runs a different query with that in it.

I Get no errors. But the issue is, how do you get a DISTINCT ROW based on the 'tickets.id' it 'ticketmessages.topic' (which as you can see, are the same thing)?


$query = ("SELECT DISTINCT tickets.id, ticketmessages.topic, ticketmessages.comments, ticketmessages.sentby, ticketmessages.messagefrom, ticketmessages.messageto, ticketmessages.dateupdated, ticketmessages.status FROM tickets INNER JOIN ticketmessages ON tickets.id = ticketmessages.topicid GROUP BY tickets.id ORDER BY datecreated DESC ");
$result = $pdo->query($query);

All that this query does it load ONE single row from the database, which happens to have a status is "closed".

IT should show ALL tickets, but only one row per ticket. There are current 2.

Options: ReplyQuote


Subject
Written By
Posted
Trying to do Inner Join, plus DISTINCT on a column
November 12, 2019 10:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.