Searching multiple fields and tables

[ragrim]

Hi,

 

Im not sure if this is the right section for this post so aplogies ifit is not.

 

Im building a search form for m DB and the section im searching is across 3 tables with approx 20 fields. im looking for some advice on the best way to go about it.

 

At the moment i have a search form that posts to a search script, currently my script is like this.

 

This isnt the full code, just a sample to make it easier to read what i am doing.

 

$subid = $_POST['sub'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$address = $_POST['address'];
$suburb = $_POST['suburb'];

mysql_query("SELECT * FROM userinfo WHERE subid LIKE '%$subid%' AND FirstNAme LIKE '%$fname%' AND LastName LIKE '%lname%'");

 

excuse the syntax its probably not correct, im at work and dont hve access to my code.

 

Now this query seems to work fine for the most part, but problem i have is if one of the search fields is NULL i dont get the right results. Im assuming what i need to do is find out which fields are null, then build the query wth the fields that are NOT NULL.

 

Looking for some advice on how to go about this, i have an idea of building the query as a string from php variables and if statements but just incase there is another way to do it i thought i would ask.

 

Cheers.

[gizmola]

Currently you are using AND.  It's not clear to me from your example, because your code only illustrates one table, and a handful of fields, but in general, yes there is no point on including empty criteria so you'll need to detect that.

Any query that includes LIKE '%SOMETHING%' will tablescan.  No indexes can be used.  This will perform worse and worse as your databases increase in size, however it might be acceptable for you if your tables will be small, and your mysqldb is performance tuned.  There are alternatives to what you're doing like the use of mysql fulltext search, or specialized search engines like sphinx.  Those solutions also have the advantage of allowing you to search for phrases.