FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


help me create a query





badai
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.
rvec
you can just add more conditions with AND
example:
SELECT * FROM TABLE1 WHERE NAME LIKE '%$name%' AND phone LIKE '%$phone%' ORDER BY NAME ASC

Also to use a search in both tables at once you can use JOIN (there are some more kind of joins you could try out but since phone should always be filled in I think left join should do).
pollux1er
I think there is an INNER JOIN you should write somewhere in your query. Let me check for it. I'ill tell you later!
nmoutaa
Hi,
The query is :

SELECT name, table1.phone , e_mail, yahoo_id, google_id, icq
FROM TABLE1 inner join table2
on table1.phone = table2.phone
WHERE NAME LIKE '%$name%' ORDER BY NAME ASC)


and I'm sure that it works, cause I make queries every day Smile

bye.
rvec
why inner join?
The phone number is the unique value so is always present, shouldn't left join give the same result?

And your query only works if he is searching for a name. If searching for a combination of name and one of the other values he has to use AND to combine the different where clauses.
nmoutaa
Hi,
So we have table1 like this :
name | phone | e-mail |
n1 00001 mail1
n3 00003 mail3

and table 2 like this :
phone, yahoo id, google id, ICQ#
00001 yahoo1 google1 icq1
00002 yahoo2 google2 icq2

and here a results of all possible
name, phone, e-mail, yahoo id, google id, ICQ#
1 - table1 left outer join table2 -> n1 00001 mail1 yahoo1 google1 icq1
n3 00003 mail3

2 - table1 inner join table2 -> n1 00001 mail1 yahoo1 google1 icq1
3- table1 right outer join table2 -> n1 00001 mail1 yahoo1 google1 icq1
00002 yahoo2 google2 icq2
rvec
you can't get two tables like that if the application works correctly. That's like having to validate all the input from the database before AND after putting it in. If you validate all the input and make sure everything works as it should before putting it in the database there is no way you can have a missing n2 in table 1. So I still think left outer join would be the way to go.
badai
well, i just query the first table, filter the result, store it in array, then query the second table. done.
rvec
badai wrote:
well, i just query the first table, filter the result, store it in array, then query the second table. done.

You didn't read through the posts above?
What you are doing now is almost the same as an outer join.
badai
too complicated for me to understand......
nmoutaa
lol
too complicated, but you should understand it Very Happy
pollux1er
With inner join it is easy to understand it, because the two tables have a relationship for the query.
Aidmar
Im noob in MySql, but if you simple make "select * from table1,table2...", it was not work???

Forgive My Bad English...
Related topics
Is there such a wsiwyg editor for sql
Site Builder
Workplaces ready for day without immigrant staff
War of The Frihosters is OVER [CLOSE THIS TOPIC]
NEED HELP IN VIEWS AND TRIGGERS
Clan NDA HELP NEEDED FOR WEBSITE
Playstation Portable site. TodayStation.
Graphic and Website Design
Any Gorillaz Fans Here?
Help Me set css for my blog
Webmasters needed - Lock please
NASA Plans To have A Station On The Moon
Let start a business working together.
suprise
Software for create gif89 Morph Movie Music
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.