Page 1 of 1

Read data from two tables with conditions

Posted: Wed Dec 01, 2004 6:34 am
by pathway
Good morning everyone,

I need some help with the following:

I have two tables; the first contains an ID and another four coulmns with values 0 or 1 (available services).
The second table contains an auto ID and one column containg the description of services.

what i am trying to do, with php, is read the data from the first table, (only those that are true) and then display the corresponding value as read from the second table.


table 1

ID | srv1 | srv2 | srv3 | srv4 |
1 | 0 | 1 | 1 | 0 |
2 | 1 | 0 | 0 | 1 |


table 2

ID | description |
srv1 | aaa |
srv2 | bbb |
srv3 | ccc |
srv4 | ddd |

I tryied some things with arrays but cant do the true or false check.
I would appreciate your help,

Thank you in advance

John

Posted: Wed Dec 01, 2004 8:54 am
by hedge
What would you like the final output to look like? I think this can be done in a query.

Can you redesign your table1? that's really the problem, the structure of that table is wrong. svr1,svr2 etc. should be rows not columns, then you could use a simple join to the second table.

Posted: Wed Dec 01, 2004 8:54 am
by qads
use table joins, something like this will work.

Code: Select all

<?php
$query = mysql_query("select table.f1,table.f2, table2.f3 from table,table2 where table2.ID = table.ID");
?>

Posted: Thu Dec 02, 2004 2:55 am
by pathway
Thank you all for your responce,


It does work like below (thanks gavwvin),



SELECT * from relevant rows in table one, then:

$description_results=mysql_query("SELECT * FROM table2");
while($rows=mysql_fetch_array($description_results))
{
//this creates an array like $descriptions['srv1']="aaa" etc
$descriptions[$rows['ID']]=$rows['description'];
}

while($row=mysql_fetch_array($table1_results))
{
echo "Plan: ".$row['ID']."<br />";
foreach($row as $column_name=>$value)
{
if($column_name!="ID")
{
if($value==1)
{
echo $description[$column_name];
}
}
}
}



Have a nice day, everyone