LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 02-17-2009, 08:18 PM   #1
microchipper
LQ Newbie
 
Registered: Feb 2009
Posts: 3

Rep: Reputation: 0
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!
 
Old 02-17-2009, 08:34 PM   #2
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Quote:
Originally Posted by microchipper View Post
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?
 
Old 02-18-2009, 04:40 AM   #3
microchipper
LQ Newbie
 
Registered: Feb 2009
Posts: 3

Original Poster
Rep: Reputation: 0
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...
 
Old 02-18-2009, 04:46 AM   #4
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,102

Rep: Reputation: 494Reputation: 494Reputation: 494Reputation: 494Reputation: 494
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
 
Old 02-18-2009, 05:04 AM   #5
jschiwal
Guru
 
Registered: Aug 2001
Location: Fargo, ND
Distribution: SuSE AMD64
Posts: 15,733

Rep: Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654
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.
 
Old 02-18-2009, 05:28 PM   #6
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,102

Rep: Reputation: 494Reputation: 494Reputation: 494Reputation: 494Reputation: 494
Quote:
Originally Posted by microchipper View Post
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
 
Old 02-19-2009, 04:17 AM   #7
microchipper
LQ Newbie
 
Registered: Feb 2009
Posts: 3

Original Poster
Rep: Reputation: 0
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?
 
Old 02-19-2009, 04:28 AM   #8
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,102

Rep: Reputation: 494Reputation: 494Reputation: 494Reputation: 494Reputation: 494
That is correct, but there is much more to it.

jlinkels
 
Old 02-19-2009, 10:28 AM   #9
jschiwal
Guru
 
Registered: Aug 2001
Location: Fargo, ND
Distribution: SuSE AMD64
Posts: 15,733

Rep: Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654
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;
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I'm trying to get PHP to filter results from a MySQL query!!! Tom "Techno" Earl Programming 4 07-18-2011 06:49 AM
Make HCL easier to filter/search Ynot Irucrem LQ Suggestions & Feedback 3 10-03-2008 12:35 AM
Filter Search Results wwnexc Linux - Software 1 05-08-2006 11:06 AM
multiple search on mysql gmarais Programming 3 03-10-2004 08:59 AM
mySQL LIKE search is case sensitive? icepig Linux - Software 4 11-14-2003 04:29 PM


All times are GMT -5. The time now is 11:06 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration