help me create a query

 
Post new topic   Reply to topic    Aprelium Forum Index -> Databases
View previous topic :: View next topic  
Author Message
badai
-


Joined: 24 Apr 2003
Posts: 78

PostPosted: Thu Mar 06, 2008 5:00 pm    Post subject: help me create a query Reply with quote

i have two tables.

first table got 3 fields:
name, phone, e-mail

phone is primary key

second table got 4 fields:
phone, yahoo id, google id, ICQ#

phone is foreign key that link to first table. there is no primary key in second table. so altogether i have 6 fields in 2 tables (name, phone, e-mail, yahoo id, google id, ICQ#)

i want to search someone from my table with all 6 parameters (not necessarily all, but user have option to search by no parameter, 1, 2, 3, 4, 5, or all 6 parameters from the form), and display their name only in ascending order.

the form have 6 text fields, and a submit button. if user submit the form empty, then i'll just list all the name. now the user would fill up any number of field and i would have to search from both tables.

the condition to use when searching is LIKE (if only 1 table it would be SELECT * FROM TABLE1 WHERE NAME LIKE '%$name%' ORDER BY NAME ASC)

so, now i have 6 parameters and 2 tables. how do i extend this query to include all 6 parameters? can anyone help me write the query?

thanks. i hope i'm clear enough that you guys could understand me.
Back to top View user's profile Send private message
olly86
-


Joined: 25 Apr 2003
Posts: 993
Location: Wiltshire, UK

PostPosted: Thu Mar 06, 2008 10:46 pm    Post subject: Reply with quote

I'm not going to write the whole thing for you, but to include multiple tables in the query you should use this approach.

SELECT 1.name FROM table1 1, table2 2 WHERE 1.name LIKE "%name%" ORDER BY 1.name ASC.
_________________
Olly
Back to top View user's profile Send private message
aprelium
-


Joined: 22 Mar 2002
Posts: 6800

PostPosted: Wed Mar 19, 2008 6:48 pm    Post subject: Re: help me create a query Reply with quote

badai,

We suggest looking for some tutorials on the Web that explain how to use JOIN in SQL (that's what you're asking for).

But to make things easier, you can start with such a query;

Code:
SELECT x.name, x.phone, x.email, y.yahoo, y.icq, y.google FROM table1 AS x, table2 AS y WHERE (x.phone = y.phone) AND (x.name LIKE "%jack%") AND (other conditions go here) ORDER BY x.name ASC


This query is not optimal and its speed could be improved if you use a JOIN to replace the (x.phone = y.phone) test.
_________________
Support Team
Aprelium - http://www.aprelium.com
Back to top View user's profile Send private message Send e-mail
badai
-


Joined: 24 Apr 2003
Posts: 78

PostPosted: Wed Mar 19, 2008 7:37 pm    Post subject: Reply with quote

it's ok. i already solved it. query the first table, put it in an array, filter it, use for loop to query the 2nd table.

done.
Back to top View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Aprelium Forum Index -> Databases All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB phpBB Group