PHP / Postgres One Output per row

Question

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

Hi Dnz, welcome to Stack Overflow. The reason for getting all of the records for each row as you put it, is due to the join in your SQL. You could try using a DISTINCT clause in your where such that you only get the distinct returned. — Jim Grant
– Jim Grant, Commented Feb 25, 2020 at 14:50

redlami · Accepted Answer · 2020-02-25 20:05:24Z

1

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.

answered Feb 25, 2020 at 20:05

redlami

713 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP / Postgres One Output per row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related