Hello,
Question time again...
I've come across a problem. I have 4 tables in a MySQL DB:
manus, models, specs1, specs2.
The specs1 table has around 60 fields in it. Several that simply contain a "Yes" or a "No." Instead of repeating Yes or No over and over I set up another table (specs2) which contains "Yes" and "No" and a temp_id field. In the specs1 table instead of putting Yes or No, it has a 1 or a 2. It then links to specs2 for the Yes or No.
Now my problem is accessing specs2 for all the fields in specs1 and getting the Yes and No's for each, and doing it all in only 1 query.
Example of DB layout:
manus
[manu_id][manu]
---1-------manu1
---2-------manu2
models
[model_id][manu_id][model]
---1----------2-------model1
---2----------2-------model2
---3----------1-------model3
---4----------2-------model4
specs1
[spec1_id][model_id][locking][cooling][heating]
---1----------2 -------1--------1---------2
---2----------3 -------2--------1---------2
---3----------1 -------1--------1---------2
---4----------1 -------1--------2---------1
specs2
[spec2_id][temp_id][answer]
---1----------1-------Yes
---2----------2-------No
Code that doesn't work:
<?
$db=mysql_connect("localhost","user","pass") or die ("cant connect");
mysql_select_db("test",$db) or die ("cant change");
$models=mysql_query("select * from manus, models, specs1, specs2 WHERE models.manu_id=manus.manu_id AND specs1.model_id=models.model_id AND specs1.locking=specs2.temp_id AND specs1.cooling=specs2.temp_id AND specs1.heating=specs2.temp_id") or die ("cant get em");
while($rows=mysql_fetch_array($models))
{
$manu=$rows["manu"];
$model=$rows["model"];
$locking=$rows["answer"];
$cooling=$rows["answer"];
$heating=$rows["answer"];
echo "$manu - $model - $locking - $cooling - $heating<BR>";
}
?>
I'm wondering if this is even possible to do in 1 query? Would the only way to do it in 1 query would be to make a table for each field and have the Yes and No in each? If so that wouldn't work since I've got around 20 fields that just need a Yes or No.
Another note, I've got several more fields that would be set-up to access a couple other tables the same way as I'm trying to access the Yes/No table.
ex. I've got fields heating_type_1, heating_type_2, heating_type_3. I'd set-up another table with the possible values for each, and all 3 heating types would access it to get their values exactly like the above code example.
If anyone could answer if this type of access can be done in one query, or even suggest another DB design to get around the repeating data for the 60 fields in specs1, that'd be great!
TIA,
Jason
