Read data from two tables with conditions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pathway
Forum Newbie
Posts: 2
Joined: Wed Dec 01, 2004 6:31 am

Read data from two tables with conditions

Post 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
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post 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.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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");
?>
pathway
Forum Newbie
Posts: 2
Joined: Wed Dec 01, 2004 6:31 am

Post 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
Post Reply