Fetching rows of data from a table that isn't in another table

Question

It's been long that I've been encountering this problem and I'll be glad if you guys can help me out on what to do.

This is the illustration.

Take for instance, you have a table called matrics and the table has a column called 'matric_num'.

Now, you create a registration page for users where one of the field is to select any matric num and after the user might have selected a matric number, filled the rest of the form and click on the submit button, it should insert the details into database. If another user want to register, the matric number that the previous user selected should not be listed in the option menu.

HERE is what I did.

$query = mysqli_query($con, "SELECT *FROM `users`");
$rows = mysqli_num_rows($query);
echo "<select>";

for($i=1;$i<=$rows;$i++)
{
$fetch_user_matric = mysqli_fetch_array($query);
$matric_num = $fetch_user_matric['matric_num'];

$matric_query = mysqli_query($con, "SELECT *FROM `matrics` WHERE `matric_no` <> '$matric_num'");
$matric_rows = mysqli_num_rows($matric_query);

for($j=1;$j<=$matric_rows;$j++)
{
    $fetch_matric_num = mysqli_fetch_array($matric_query);
    $matric_no = $fetch_matric_num['matric_no'];

    echo "<option value='$matric_no'>$matric_no</option>";
}
}

echo "</select>";

It displayed only the first matric number from the database whereas all matric numbers that hasn't been used by any user should be displayed.

Please what do you think is wrong with the sql query.

THANKS.

What's your actual question? Does this not work? How does it not work? What error do you get? What have you done to troubleshoot this? Your code doesn't check for errors. How do you expect to find errors if you don't look for them? — John Conde, May 12 '15 at 13:14
We need some more info to "fix" your problem, not clear enough — Cr3aHal0, May 12 '15 at 13:15
This question isn't your question, but the answer will solve your problem: http://stackoverflow.com/questions/10512452/php-using-a-variable-inside-a-double-quotes — paulmorriss, May 12 '15 at 13:16
It displayed only the first matric number from the database whereas all matric numbers that hasn't been used by any user should be displayed in the option list so that another user can just select any matric number for registration — , May 12 '15 at 13:17

score 1 · Accepted Answer · answered May 12 '15 at 13:25

1

Just try this query:

SELECT * FROM `matrics` WHERE `matric_no` not in (select `matric_num` from `users`)

answered May 12 '15 at 13:25

harrrrrrry

13,643
2
23
28

Fetching rows of data from a table that isn't in another table

1 Answers1