I need a bit of direction as I have realised I am going the wrong way about this.
Tables I have:
Models - ID - Model code - Full model name
County - ID - Full county name
Location - ID - model code - county code - quantity
I am currently selecting and displaying the details from the location table while getting the full model and county names from their respective tables.
This works OK except I will need a line for every model and county - there are 88 counties and 12 models !
$result = mysql_query("
SELECT *
FROM location
LEFT JOIN gallery.models ON location.model = models.mcode
LEFT JOIN gallery.tblcounty ON location.county = tblcounty.fldCountyID
ORDER BY fldCounty")
Output with php using echo “<tr><td>”.$row[‘fldCounty’].“</td><td>”.$row[‘model’].“</td><td >”.$row[‘quantity’].“</td></tr>”;
Aberdeenshire------Model1-----1
Cambridgeshire-----Model2-----2
Cambridgeshire-----Model3-----1
What I want to do is have a list of the models along the top of the table - getting their names from the models table - I have this working OK.
If the county has any models create a row and put the quantity of models below the header, it there are no models in the column display 0
If the county has no models do not display it.
Output required:
County----------Model1----------Model2----------Model3
Aberdeenshire-----1---------------0----------------0
Cambridgeshire----0---------------2----------------1
I am only starting this project and so I can change the tables or create more.
I thought about having a table with every county and every model and putting the quantities into that but it did not seem a very good way to do this.
P.S. I do not want to hard code the model names anywhere except the model tables as with the method I have I only need to modify one table if we add a new model.