LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   MySQL Search and then Filter? (http://www.linuxquestions.org/questions/programming-9/mysql-search-and-then-filter-705506/)

microchipper 02-17-2009 08:18 PM

MySQL Search and then Filter?
 
Hi all,

I am trying to create something like a dating website and I'm having a little trouble creating the search feature. I have two tables "Iam" and "Iamlookingfor." When user_one comes in to search for other members, he fills out Iam and Iamlookingfor. Here's where the problem comes in... When I making a query Iamlookingfor, I'll just go into the Iam database and pull out all the results that match. However, I also want to double check that all those results' Iamlookingfor match user_one's Iam. How can I pull out the results and then filter those results a second time?

Thanks!

win32sux 02-17-2009 08:34 PM

Quote:

Originally Posted by microchipper (Post 3447698)
I am trying to create something like a dating website and I'm having a little trouble creating the search feature. I have two tables "Iam" and "Iamlookingfor." When user_one comes in to search for other members, he fills out Iam and Iamlookingfor. Here's where the problem comes in... When I making a query Iamlookingfor, I'll just go into the Iam database and pull out all the results that match. However, I also want to double check that all those results' Iamlookingfor match user_one's Iam. How can I pull out the results and then filter those results a second time?

Can't you use WHERE for this?

microchipper 02-18-2009 04:40 AM

Hmm... I'm still really really new to MySQL so I'm not entirely sure :). Iam and Iamlooking for are two separate tables (not columns), so I'm not sure how to get out of one table when I say FROM $tablename.

Maybe I can combine the two tables into a temp table or something...

jlinkels 02-18-2009 04:46 AM

So you create a query based in Iamlookingfor, you execute that query on Iam, and want to double check that the records you get returned from Iam are compliant to your query? That is what I read.

That is not loigical, a query is a query, and it returns what you ask, no need for a second check.

Or do you want to check if what Iam returns in the query matches with the Iamlookingfor record which was created by the same user returned from Iam? That sounds much more logical.

Or isn't it a requirement that every user in Iam has an entry in Iamlookingfor?

jlinkels

jschiwal 02-18-2009 05:04 AM

I would recommend a book "MySQL Crash Course". You need to normalize your database. You may have a clients table, with references to the address table, etc. Designing your database, normalizing it should be the first step before you begin working on the website.

Test it out using sql queries on some dummy entries. Then begin thinking about designing your website.

jlinkels 02-18-2009 05:28 PM

Quote:

Originally Posted by microchipper (Post 3448064)
Hmm... I'm still really really new to MySQL so I'm not entirely sure :).

That was not completely clear. I am with jschiwal though. Learn SQL first, then design your database, then implement your web page. However, the mysql web site offers a tutorial-by-example. http://dev.mysql.com/doc/refman/5.0/en/tutorial.html

That is good enough to get you going working on databases.

For designing databases, you need to take another step.
http://dev.mysql.com/tech-resources/...alization.html
http://www.devarticles.com/c/a/MySQL...gn-Techniques/

I advice against using a GUI client for learning this stuff. Once you design a web page, you need to put in the SQL statements manually anyway. It is better if you learned it that way.

It is really simple, the learning curve is not steep. Over time you'll get a hold of creating really smart and really, really smart queries.

jlinkels

microchipper 02-19-2009 04:17 AM

awesome. i'll definitely check out the guide. thanks for the suggestions.

i think i have also figured out how to do it. apparently, you can jump to other tables by adding a period... e.g. iam.userid and iamlookingfor.userid. is this right?

jlinkels 02-19-2009 04:28 AM

That is correct, but there is much more to it.

jlinkels

jschiwal 02-19-2009 10:28 AM

The mysql client uses the standard getline, so things like autocompletion and even [CTRL]-r work. You can enter the first few characters of a field and press tab.

If you use amarok at home, you can run it using a MySQL backend. Then you can practice on it's tables.
My favorite query is:
SELECT title, url
FROM podcastepisodes
WHERE isNew=1;


All times are GMT -5. The time now is 09:33 PM.