PHP foreach loop to populate dropdown list with values that exclude certain results

Question

I have two tables. Enrollment and Product. I want to list Product on a <select>.

Within this <select>, I only want certain Product items to appear, whereby the condition is to read from Enrollment table's ProductID which is a foreign key to Product table.

How does one exclude certain results in a <select> that had already existed in a different table?

<?php
    $sql = 'SELECT * FROM product ORDER BY ProductID ASC';
    $result_select = mysql_query($sql);
    $rows = array();
    while($row = mysql_fetch_array($result_select))
    $rows[] = $row;
    echo "<div class=\"spanstyle\">Add course/product:<select name='add_product'>";
    echo "<option selected>Choose here</option>";
    foreach ($rows as $row) {
        echo "<option value='" . $row['ProductID']."'>" . $row['ProductName']."</option>";
    }
    echo "</select></div>";
    $select1 = $_POST['add_product'];
    if (!strpos($select1, 'Choose here')) {
        $sql3="INSERT into enrollment (StudentID, ProductID) VALUES ($StudentID, $select1)";
        mysql_query($sql3);
        }
?>

Definitely perform this filtering in mysql. The best way to do this will only require 1 query call. — mickmackusa
– mickmackusa ♦, Commented Jun 8, 2017 at 3:42
do not use old mysql api - deprecated in 5.x, removed in 7.x — Kazz
– Kazz, Commented Jun 8, 2017 at 3:47
WARNING: If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and has been removed in PHP 7. A replacement like PDO is not hard to learn and a guide like PHP The Right Way helps explain best practices. Make sure your user parameters are properly escaped or you will end up with severe SQL injection bugs. — tadman
– tadman, Commented Jun 8, 2017 at 5:48

pxslip · Accepted Answer · 2017-06-08 03:30:40Z

1

First reaction is that you need to modify your SQL query here, not the PHP loop. Something like (and this is a quick first shot so don't trust it without testing)

SELECT * FROM product WHERE ProductID NOT IN (SELECT ProductID from enrollments) ORDER BY ProductID ASC

This would exclude any row in product whose ProductID also appears in the enrollments table.

answered Jun 8, 2017 at 3:30

pxslip

2991 silver badge6 bronze badges

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

3 Comments

Anonymous Vagrant Over a year ago

Yes, it makes sense, but I don't get any result back at the select option. That's because I have another data column that triggers the result back to me StudentID. The sql statement does not read into which student I am currently reading from.

Anonymous Vagrant Over a year ago

Here is my edited sql statement going by ur logic of changing the sql statement.

SELECT * FROM product WHERE ProductID NOT IN (SELECT ProductID from enrollment WHERE StudentID = "$StudentID") ORDER BY ProductID ASC

I got incorrect results.

Anonymous Vagrant Over a year ago

Fixed it. I have to use double quotes for my sql statement that is called from the variable.

"SELECT * FROM product WHERE ProductID NOT IN (SELECT ProductID from enrollment WHERE StudentID = '$StudentID') ORDER BY ProductID ASC";

Collectives™ on Stack Overflow

PHP foreach loop to populate dropdown list with values that exclude certain results

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related