Show records, via PHP and Mysql query, in two tables, with GROUP BY

Question

Show records, via PHP and Mysql query, in two tables, with GROUP BY

Asked 3 years, 8 months ago

Viewed 43 times

-1

Good evening... I have the following doubts
This system aims to bring users from a particular region or workplaces that belonged to a particular city.
If we search for city=4261, according to the code I did, it will return:
However, John has one more place of attendance, which is the Post, but it is not appearing because of the grouping. How do I show all the places that João attends, even if he is from the same city?

Follow the search code

<?php
$form=4261;
$search_user = "SELECT
u.id u_id,u.first_name u_first_name, u.city u_city,  
uw.user uw_user, uw.workplace uw_workplace, 
w.id w_id, w.city w_city, w.name w_name 
FROM users AS u
LEFT JOIN users_has_workplaces AS uw
ON u.id=uw.user 
LEFT JOIN workplaces AS w 
ON w.id=uw.workplace
GROUP BY u_first_name
HAVING COUNT(*) > 0
";
$result_user = mysqli_query($conn, $search_user);
while($row_user = mysqli_fetch_assoc($result_user)){
    echo $row_user['u_first_name']."<br>";
    echo $row_user['w_name'];
    echo "<hr>";
} 
?>

Have you ever thought of making a UNION of the consultations that deal separately from each condition?

– anonimo

2020/10/27 at 12:45
I never used UNION, I will give a study, and see if it solves my problem. Thanks for the suggestion.

– Inhaia

2020/10/27 at 17:17
If you want the two records to appear, why are you using grouping?

– Gnomo Escalate

2020/10/27 at 17:45
You can give GROUP BY in more than one column, in your local case/city of work GROUP BY u_first_name, w_city, w_name, this way will group only the equal results, when the 3 columns are equal.

– Gnomo Escalate

2020/10/27 at 17:52
At this point my problem arose. Because it was the only way (at this time) I found, not to show repeated records. In the search example I published, if I don’t use the grouping, it will return me twice John. I want you to show only once his name, with the two drop-in locations below his name. I do not know if using a parole would be the right way, I would like the opinion of colleagues.

– Inhaia

2020/10/27 at 17:59

1 answer

Browser other questions tagged php mysql mysqli group-by

You are not signed in. Login or sign up in order to post.

by Gnomo Escalate • **378** points · Answer 1 · 2020-10-27T17:57:38+00:00

As I said in the comment, you can try to group by more than one column, so you can show the two results of João.The results will be grouped first by col1(u_first_name) and then by col2(w.city). In Mysql, column preference goes from left to right.

<?php
    $form=4261;
    $search_user = "SELECT u.id u_id, 
           u.first_name u_first_name, 
           u.city       u_city, 
           uw.USER      uw_user, 
           uw.workplace uw_workplace, 
           w.id         w_id, 
           w.city       w_city, 
           w.name       w_name 
    FROM   users AS u 
           LEFT JOIN users_has_workplaces AS uw 
                  ON u.id = uw.USER 
           LEFT JOIN workplaces AS w 
                  ON w.id = uw.workplace
    WHERE uw.workplace = '$form'
    GROUP  BY u_first_name, 
              w.city, 
              w.name
    HAVING Count(*) > 0";

    $result_user = mysqli_query($conn, $search_user);

    while($row_user = mysqli_fetch_assoc($result_user)){
        echo $row_user['u_first_name']."<br>";
        echo $row_user['w_name'];
        echo "<hr>";
    } 
?>