Select data from one table using other table field?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gertrudis
Forum Newbie
Posts: 14
Joined: Wed Jan 27, 2010 8:15 pm

Select data from one table using other table field?

Post by gertrudis »

Hi to all.
I really don't know how to explain what i want, but I'll try:

I have two table:
the first one with the data I want to select example: Name, lastname, phone, state
the second with the option i want to select from the first one, db_option:
id state client
100 NY N
100 NJ Y
100 GA N
100 FL N
101 TX Y
101 LA Y
101 AL N
102 AZ Y
102 NM Y
102 CO N
103 TX Y
103 WA Y
this is the select i'm using: "SELECT * FROM clients WHERE client='Y' and (state='CA' OR state='WA' OR state='CO') ", but i have to change all cities any time i need new state and what i want is to change only the id option to use in the sqlselect

But what I need is to store on array or anything the second table, so i don't have to change the code, only the table data with new id and option:

idtouse=100
myarray=tablefields: that will hold the table like: (state='CA' OR state='WA' OR state='CO)


"SELECT * FROM clients WHERE client='Y' and (myarray) "

I need help or point me to right direction please, thanks
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select data from one table using other table field?

Post by mikosiko »

one easy alternative

Code: Select all

SELECT * FROM clients WHERE client='Y' 
  AND state IN (SELECT state FROM db_option WHERE id = $id)
$id is your parameter
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Select data from one table using other table field?

Post by Eran »

Better to use a join and not a dependent subquery -

Code: Select all

SELECT clients.* FROM clients
INNER JOIN db_options ON db_options.state=clients.state
WHERE clients.client='Y' AND db_options.id=$id
gertrudis
Forum Newbie
Posts: 14
Joined: Wed Jan 27, 2010 8:15 pm

Re: Select data from one table using other table field?

Post by gertrudis »

thanks a lot
Post Reply