Mysql product and part number data and values

[binarywebs]

Hello,

 

MYSQL version 5.0.92-community

 

My problem is that I have a database driven website which lists products and their options which have variable field headers for each product e.g Product A may have Capacity and Height which are relevant fields where as Product B has Wattage and Lamp Type which are used to describe the different spec of each product…..as shown below

 

 

My database currently has one big mysql table for the options for the product which has lots of columns across which I know is not a very productive way of doing it. Ideally I would like to have a more relational way of doing this with maybe a couple of tables like below….

 

Options Table

Option_to_product_id

Part_id

Option_name

Option_value

 

Part Table

Part_id

Part_number

Product_id

 

I’m sure there must be some fancy way of getting the above table views in mysql but I run into problems with getting the column names and also no showing columns that are blank. Does anyone know how to display this in mysql with something like the below?

 

Select columns(un-empty) from t_options to left join tparts tp on tp.id = to.id where product_id = 55

 

I know the above sql needs to be a lot more complex  but hopefully the above will give you an idea.

 

Thanks

 

Daniel

 

 

[sunfighter]

Is the example of Product A and B the way you want things displayed?

 

It would be nice to know what you have now - url to site please.

 

and maybe a picture of what you want.

 

Are you trying to show every item in one table? How did you want that broken up?

[fenway]

I don't understand the question.

[binarywebs]

Hi Thanks for both replying.

 

Yes The example of product A and B is how I want them to be displayed in my web page. I have one big table with about 35 columns but each product probably only uses 4 of these columns so there is a lot of empty fields in the table.

 

I have got around the problem of having to have lots of if statements to show only relevant columns for each product. I did this by itering through the key and values of each array returned from the database.....as show below;

 

$r = mysqli_query($dbc, "select * from tFullVariables where productId = $pid");

if(mysqli_num_rows($r) > 0)

{

$mybody = "";

//starts the table and header row

$head='<table id="prodoptions" cellspacing="1" border="1"><tr class="head">';

//$c is the counter so that the header is only show once

$c = 1;

while($myrecord = mysqli_fetch_array($r, MYSQLI_ASSOC))

{

$mybody .= '<tr>';

//loops through each key and value of the results array returned by the database

foreach($myrecord as $key => $value)

{

//if the value of the field is empty or 0 then dont show the column or field

if($value <> "" && $value <> " " && $value <>  "0.00" && $key <> 'id' && $key <> 'productId' )

{

if($c ==1)

{

$head .=  '<th>'.$key.'</th>';

}

$mybody .= '<td>'.$value.'</td>';

}

}

$mybody .= '</tr>';

$c=2;

}

//displays the column headers

echo $head.'</tr>';

//displays all of the relevant fields

echo $mybody.'</table>';

}

 

 

 

 

If any one can think of a way of doing the above in a relational way using multiple tables instead of one big table that would be good.

 

Thanks

[fenway]

If they share a subset of the fields, then perhaps you should have a table with these fields.