Select from 2 tables not working with php mysql

Question

I have two different tables of the following structure:

grouprel

id | userId | pupID | groupId

pupils

id | userId | fname | lname

pupId in groulrel is equal to id in pupils.

I want to fetch pupils from a different group and then order them by fname, lname.

Now I have two queries like this:

$q = "SELECT * FROM grouprel WHERE userid = ". $userid ." AND groupId = ". $_GET['id'] ."";
$r = mysqli_query($mysqli, $q);

while ($rows = mysqli_fetch_object($r)) {
    $query = "SELECT id, fname, lname FROM pupils WHERE userid = ". $userid ." AND id = ". $rows->pupId ." AND status = 0 ORDER BY fname, lname";
    $result = mysqli_query($mysqli, $query);

    while($row = mysqli_fetch_object($result)) {
        echo stuff...           
    }
}

This works, but it doesn't order the names alphabetically like I want to.

How could I fix this?

How does it order them? That ORDER BY clause looks fine. (Note: Be aware that your code is wide open to SQL injection.) — David
– David, Commented Aug 13, 2016 at 11:03
Take the actual runtime query that you're building and execute it manually on your database. It's ordering by the ORDER BY clause. I very much doubt you've discovered that the ORDER BY clause in MySQL doesn't work at all. Someone would have noticed that. — David
– David, Commented Aug 13, 2016 at 11:16
Are you sure you have something to order in your second query? My guess is you get exactly one or zero rows there (that are obviously executed in the order of your first query, since you loop through them in that order). I think what you are trying to do implement here is a join. — Solarflare
– Solarflare, Commented Aug 13, 2016 at 11:30

David · Accepted Answer · 2016-08-13 11:33:33Z

1

This is iterating over the first query:

while ($rows = mysqli_fetch_object($r)) {

And this iterates over each instance of the second query:

while($row = mysqli_fetch_object($result)) {

So if the first query returns 1,2,3, and each iteration of the second query returns A,B, then your output would be:

1 A
1 B
2 A
2 B
3 A
3 B

The second query is ordering by the ORDER BY clause you gave it. But you are ordering the entire output by the first query.

Ultimately, why do you need these separate queries at all? Executing a database query in a loop is almost always the wrong idea. It looks like all you need is one query with a simple JOIN. Guessing on your logic, something like this:

SELECT
  pupils.id, pupils.fname, pupils.lname
FROM
  pupils
  INNER JOIN grouprel ON pupils.id = grouprel.pupId
WHERE
  pupils.userid = ?
  AND grouprel.groupId = ?
  AND pupils.status = 0
ORDER BY
  fname, lname

It may take a little tweaking to match exactly what you're looking for, but you can achieve your goal with a single query instead of multiple separate queries. Then the results of that query will be ordered the way you told MySQL to order them, instead of the way you told PHP to order them.

answered Aug 13, 2016 at 11:33

David

221k42 gold badges247 silver badges338 bronze badges

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

1 Comment

Kim Andersson Over a year ago

Wow, thanks for that excellent answer! It works as it is supposed now!

Collectives™ on Stack Overflow

Select from 2 tables not working with php mysql

grouprel

pupils

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

grouprel

pupils

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related