MySQL SELECT Query - Exluding certain rows

I have a database with a bunch of tire brands and information. I have a table called Accepted_Brands which has the shop_id and the brand_id so it tells me which shop carries what brand.

The tire_brands table just has an id and a name, listing all of the tire brands.

On a page for that specific shop, I have a list of all the tire brands that they sell.. and there is an option to add a new brand.

The add a new brand popup just has a dropdown and here is my code:

    if ($result = $mysqli->query('SELECT id, name FROM tire_brands ORDER BY name')) { 

        while( $row = $result->fetch_assoc()){ 
            $tire_name = $row['name'];
            $tire_id = $row['id'];

            echo "<option value=\"".$tire_id."\">".$tire_name."</option>";
        }

So it shows all of tire brands in a drop down which they can choose to add it to their accepted list. The problem is that it shows all of them, even if it is selected already. So I can add two of the same brands.

Is there a way to exclude it from the SELECT query,开发者_开发问答 or another way so that the drop down can only show the list of brands that they are NOT using?

Thanks!

you have to use a subquery like this:

 if ($result = $mysqli->query('SELECT id, name FROM tire_brands where id NOT IN (select shop_id from Accepted_Brands) ORDER BY name')) { 

        while( $row = $result->fetch_assoc()){ 
            $tire_name = $row['name'];
            $tire_id = $row['id'];

            echo "<option value=\"".$tire_id."\">".$tire_name."</option>";
        }

this is just an example, I don't know how you insert data on your Accepted_Brands and how you can understand if a id, brand is already in your Accepted_Brands table

You could modify your query to be something like :

SELECT id, name 
  FROM tire_brands 
  WHERE id NOT IN (SELECT brand_id FROM Accepted_Brands WHERE shop_id = current_shop)

try this:

SELECT id, name FROM tire_brands 
WHERE id NOT IN (SELECT tire_brand_id FROM Accepted_Brands WHERE shop_id=XXX)
ORDER BY name

SELECT id, name FROM tire_brands WHERE name NOT IN (....,....,....) ORDER BY name

...with .... representing the comma delimited names of tires you pass in.

Try

SELECT t.id, t.name FROM tire_brands as t, Accepted_Brands as a WHERE t.id <> a.brand_id ORDER BY name

so you exclude the Brand thats already set from your resultset.

You'd need to do a more complex query to look up the brands that the store already carries and exclude those from the master brands list you're filling into the dropdown. Without knowing your table structures, the following is only an example:

SELECT id, name
FROM tire_brands
WHERE (id NOT IN (
    SELECT brand_id
    FROM store_carried_brands
    WHERE store_id = XXX
))
ORDER BY name

And just to be sure, do some server-side checking when the 'add a brand' form is submitted, and only add that brand to the store if it's not already in their carried brands list. Even if you don't filter the available brands with the above query, you can STOP the duplicates from being created by checking if adding this 'new' brand would in fact create a dupe.

If you're mostly concerned with them adding the same brand twice, another thing you could do would be to add a unique index on the 'accepted_brands' table, so the same combination of 'tire_id' and 'shop_id' can't be added.

Something like:

ALTER TABLE accepted_brands ADD UNIQUE INDEX (tire_id, shop_id);