Choosing data from one array based on data from a second array

[aeolidia]

I'm creating a tool for building project proposals for clients, with pricing.

 

I have a database with two tables: services and clients.

 

The services table looks like this:

 

| code        | name        | cost |
+-------------+-------------+------+
| logo_design | Logo design | 10   |
| web_design  | Web design  | 20   |

 

The clients table looks like this:

 

| id | client  | logo_design | web_design |
+----+---------+-------------+------------+
| 1  | Walrus  | yes         | yes        |
| 2  | Narwhal | no	     | yes        |

 

How would I link the results from these two tables so that I only pull out the services each person wants from the database?

 

Here is what I have so far:

 

<?
$sql = "SELECT * FROM services";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

  echo $row{'name'} . ': $' . $row{'cost'} . '<br />';

} ?>

 

This, of course, displays all the services, with all the prices, like so:

 

Logo design: $10

Web design: $20

 

I want to add something here so that based on the selected client, we see only the services/prices they selected (ol' Narwhal's page would not show the "Logo Design" line). I've got the data from "clients" pulled in to this page as well, but don't know how to relate it to "services."

 

Thanks in advance! Let me know if I've left out any info that would help you to point me in the right direction.

[Muddy_Funster]

Look into MySQL CASE

[aeolidia]

Thanks for the suggestion. I'm very new at this, so please bear with me.

 

I know that I want my "case" to be to show only the rows in services that have a matching "yes" in clients, but I'm having a hard time figuring out how to phrase it.

 

All the CASE examples I'm finding have me directly supplying the "then" value, but I am instead just trying to get things to display or not. What would my "then" value be?

 

Here is my sad little stab at starting with this:

 

SELECT services.name,services.cost 
  CASE services.code
    WHEN client.logo_design [and here I'm itching to put ='yes' which I don't think I can do] 
    THEN [and here I want to say "display the logo_design row from services"]
FROM services, clients

 

I want to avoid listing out every single service code, if possible, since I'd like to be able to add new services to both tables without altering the code in my PHP documents (and the services table is going to have dozens of services).

 

I'm hoping for something more like:

 

"When services.code matches any field name in clients and has a "yes" value for the particular client, display it."

 

I have the feeling I'm completely misunderstanding this, and would love a bit more handholding on what the proper way to do this is. Perhaps there is a better way to structure the clients table? Thank you!