CODEWITHSUNDEEP
phpmysql
Kim Andersson
Kim Andersson

Reputation: 251

Select from 2 tables not working with php mysql

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?

Upvotes: 0

Views: 213

Answers (1)

David
David

Reputation: 218877

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.

Upvotes: 1

Related Questions