Welcome to WebmasterWorld Guest from 34.229.126.29

Forum Moderators: open

Message Too Old, No Replies

SQL Search within results

searching mysql searches

     
11:32 am on Jul 20, 2007 (gmt 0)

New User

10+ Year Member

joined:July 20, 2007
posts:1
votes: 0


Hi all,

Just found your site and I reckon Im going to be a regular user, nice one.

Im trying to sort my head around a problem thats probably very easy but its got me a little lost in its implementation.

I want to do an initial search on a table and output the results (thats straight forward) eg

SELECT * FROM members WHERE location = 'England'

This will result in hundreds of results, I then want the user to click on an item they like the look of and then click other searches from there so say they click a person who is blonde, they can then click the hair icon and refine the search to just blonde people, making the search become:

SELECT * FROM members WHERE location = 'England' AND hair = 'blonde'

Narrowing down the members to just blonde ones and producing the new results. Now they can go into another profile and maybe decide that the person is too short so clicks the "taller" option to refine the search again to:

SELECT * FROM members WHERE location = 'England' AND hair = 'blonde' AND height > '10' (thats because Im storing the heights etc in a drop down)

The problem Im having is knowing how to tell it to refine the string, can I pass the original search as a variable across to the results and then the profile page and again through to the search page?

The easiest way to describe what Im after is the google search within results.

Thanks again,

10:23 pm on July 29, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


This sounds like more of a scripting issue than a database one.

You will need to construct your SQL statements in PHP or ASP or whatever language you are using, depending on your users' criteria. If you're going to have them clicking screen to screen, rather than fill out all criteria in a single form, then I would append these to the HTML querystring.

e.g.
www.example.com/critieria.php?hair=blonde

Then if they want somebody with blue eyes, just append that to the existing querystring when they click to the next page:

.../critiera.php?hair=blonde&eyes=blue

If you're using PHP, for instance, you can then retrieve these criteria and then concatenate them into a SQL statement, such as:

$hairColor = $_GET['hair'];
$eyeColor = $_GET['eyes'];

$sql = "SELECT * FROM tableName WHERE hairColor =\"". $hairColor ."\" AND eyeColor =\"". $eyeColor . "\"";

To be safe, you shouldn't give the database user that's executing these SELECT statements anything but SELECT permissions. Also you should look into sanitizing the criteria so that SQL injection cannot occur.
--------------

A little off topic, but you should consider normalizing your database if you haven't already. It's generally not a good idea to put "England" or "blonde" into a table every time a new user is added.

Instead you should have separate tables like Country and HairColor. Each record in the Country table would have CountryID and CountryName columns. Similarly, the HairColor table would have HairColorID and HairColorName. Then, when a user with blonde hair from England joins, you can update the Hair column in the Users table to = (the HairColorID corresponding to "blonde" in the HairColorTable) and along the same lines for the country.

This allows you to pull countries and hair colors and heights and everything else out of your database to populate your drop-down selections rather than hard coding them into your forms.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members