PHP / Postgres One Output per row

user_10095381 0
Viewed 44

I'm trying to get an one line output for each row my statements iterates to but it outputs like 100 times for every "ticket" it finds. In the current SQL Queue there are only 4 possible tickets which match the criteria. 

$ticketIdQuery = pg_query($db, "SELECT ticket.assignee_id, ticket.subject FROM ticket INNER JOIN ticket_assignee_mapping ON ticket.assignee_id=ticket_assignee_mapping.assignee_id WHERE status_id = $firstLevelId");

if($ticketIdQuery)
{
    while ($ticketIdQueryFetch = pg_fetch_array($ticketIdQuery))
    {
        print_r($ticketIdQueryFetch);
    }
}

Is there a way to limit the output to one for each row it finds

select postgresql arrays php
1 Answers

When your JOINed tables have one-to-many relationships and you only select a limited number of columns, you can't see why you are getting so many results. So rather than use DISTINCT, I suggest using a GROUP BY clause in order to specify the columns you're actually interested in. If you also add a COUNT(*) to your SELECT, you can keep tweaking your query until the record count totals 1 for each row returned. Try this:

$ticketIdQuery = pg_query($db, "SELECT ticket.assignee_id, ticket.subject, COUNT(*) FROM ticket INNER JOIN ticket_assignee_mapping ON ticket.assignee_id=ticket_assignee_mapping.assignee_id WHERE status_id = $firstLevelId GROUP BY ticket.assignee_id, ticket.subject");

If you still have questions, please post the table definitions for ticket and ticket_assignee_mapping.

user_14641090
Related