Available products by manufacturer display in select element

[Nickmadd]

Hey guys I'm really confused at the minute, I have products in my database (It's MySQL) I want to display attributes from the products in a filtering form.

 

I currently have select elements in my template and I want to display all current manufacturers that are listed in the manufacturers column of my database.

 

How can I display the manufacturers once in the select element without displaying them multiple times and in alphabetical order? For example I might have 4 products that are Sony but I don't want the brand to display 4 times.

 

Any resources or examples on how to do this?

 

Thanks

[fastsol]

First you'll obviously need to query the database for the info and use the GROUP BY to group by the manufacturer.  Then it's fairly simple to use the array of returned results to build the select box dynamically.

// Query your db, my example uses PDO
$results = $db->query("SELECT manufacturer FROM products GROUP BY manufacturer"); // Just an example
$results->fetchAll(PDO::FETCH_ASSOC);

//Now echo out a select tag and make sure to give it a name
echo '<select name="brands">';
 
//Now we use a foreach loop and build the option tags
foreach($results as $r)
{
echo '<option value="'.$r.'">'.$r.'</option>';
}
 
//Echo the closing select tag
echo '</select>';

With that said, honestly you should have a separate table that holds the manufacturers and their info.  Then you use a column in the products table like brand_id that holds the primary key number of the brands from the other table that corresponds with the product at hand.  Now you simply query the brands table instead and will never have to worry about duplicate output cause there won't be any duplicate entries in the brands table (at least there shouldn't be since a brand in itself is unique).  This concept is called table normalization, it's a very important thing to know and understand and I highly suggest doing this before moving forward with your project.

[Nickmadd]

First you'll obviously need to query the database for the info and use the GROUP BY to group by the manufacturer.  Then it's fairly simple to use the array of returned results to build the select box dynamically.

// Query your db, my example uses PDO
$results = $db->query("SELECT manufacturer FROM products GROUP BY manufacturer"); // Just an example
$results->fetchAll(PDO::FETCH_ASSOC);

//Now echo out a select tag and make sure to give it a name
echo '<select name="brands">';
 
//Now we use a foreach loop and build the option tags
foreach($results as $r)
{
echo '<option value="'.$r.'">'.$r.'</option>';
}
 
//Echo the closing select tag
echo '</select>';

With that said, honestly you should have a separate table that holds the manufacturers and their info.  Then you use a column in the products table like brand_id that holds the primary key number of the brands from the other table that corresponds with the product at hand.  Now you simply query the brands table instead and will never have to worry about duplicate output cause there won't be any duplicate entries in the brands table (at least there shouldn't be since a brand in itself is unique).  This concept is called table normalization, it's a very important thing to know and understand and I highly suggest doing this before moving forward with your project.

 

Thanks for the reply! I am a front end developer so most of this is new to me so I am sorry if I fail to understand.

 

So let's say I create a table with all of the manufacturers that I could possibly have in the store at one time, how would I stop all of these manufacturers from displaying if they are not in stock? Obviously I don't want manufacturers displaying as an option in the select element if I don't have them in stock? 

 

Thanks, Nick.

[fastsol]

You're thinking to broad in that respect.  It's unlikely that and entire brands worth of products is out of stock unless you literally only have one product per brand which makes no sense.  It's very likely that you will have certain products that are out of stock though.  So I put a "active" column in the products table and use a boolean value to decide if it's active or not.  You could certainly do the same concept for the brands table but unless you want to turn off the entire brands worth of products you wouldn't generally turn off a brand, but rather just the products themselves that are out of stock.

 

To do a products type website you generally woud have at least 5 tables to construct it properly.  You could have more depending on how you abstract other details about the products them selves.

[Nickmadd]

You're thinking to broad in that respect.  It's unlikely that and entire brands worth of products is out of stock unless you literally only have one product per brand which makes no sense.  It's very likely that you will have certain products that are out of stock though.  So I put a "active" column in the products table and use a boolean value to decide if it's active or not.  You could certainly do the same concept for the brands table but unless you want to turn off the entire brands worth of products you wouldn't generally turn off a brand, but rather just the products themselves that are out of stock.

 

To do a products type website you generally woud have at least 5 tables to construct it properly.  You could have more depending on how you abstract other details about the products them selves.

 

I am actually listing vehicles however I said brands to try and create a scenario that most PHP devs would be familiar with sorry about that, it's for my family's dealership so manufacturers and models are constantly in stock and out of stock.

 

So by the looks of things it seems I will need a table for models as well which I could see being an absolute nightmare because there are 100's of models of cars ha.

 

Would you say the best bet is to create a table that contains all of the manufacturers and then create table's named after each manufacturer and include all of the model's that the manufacturer supplies?

 

Thanks, Nick.

[fastsol]

Would you say the best bet is to create a table that contains all of the manufacturers and then create table's named after each manufacturer and include all of the model's that the manufacturer supplies?

No, the concept is the same whether it's cars or whatever.  Granted you can design it however you want, but you'll find that in the long run you'll face many more challenges when wanting to modify how you get certain data or want to manipulate that data in different ways than you originally thought.  You may think "oh I just want to get that data and display it on the page quickly" but then in 2 months you go "boy it would be nice to also do XXX with that data" but now it's a pain cause the overall design structure is flawed.

 

In your case using cars, you would want I think 4 overall tables to control all this:

vehicle_makes

vehicle_models

vehicle_years

products

 

The produtcs table would then have a column of like year_model_id that woudl be a referrence to the id column of the vehicle_years table so that you can relate the product (in your case a specific car that is for sale of a 2002 chevy silverado) to a specific make, model and year value in the other tables.  Now this does start to get a little complex joining all these tables together if you haven't ever done this kind of thing but it's really the best way to go about it for the long run.

 

If you ask me nicely in a PM I might be able to give you such a database file that already has the vehicle parts done.