clark-kim clark-kim - 1 year ago 59
MySQL Question

Select from 2 tables not working with php mysql

I have two different tables of the following structure:


id | userId | pupID | groupId


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?

Answer Source

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.fname, pupils.lname
  INNER JOIN grouprel ON = grouprel.pupId
  pupils.userid = ?
  AND grouprel.groupId = ?
  AND pupils.status = 0
  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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download