Need to have simple "search" form filter records from database

[mac007]

Hello, all:

 

I am trying to do like a mini-search script, where records are filtered based on submitted-form values. Basically, sample submitted-fields may be like: agent and skillname. Thing is, I want to have it so one can search from a combination of both terms or just by itself.  In other words, one could enter just "John" and all the Jonhs would come up, or just enter "tv" under skillname and all the TV skillnames would then show up. But then, let's say I also want to be able to further filter this search, like submit both "john" and "tv" and only those records that have those in common woudl show up.

 

Thing is, I will need to add even more filtering fields (like agentid, date, type, etc)... I setup this if-else script here, and seems to work. But it's pretty amateurish I know, so there must be a better way to have it do the filtering. Appreciate any help...

$agent = $_GET['agent'];
$skillname = $_GET['skillname'];

if (!empty($_GET['agent']) && !empty($_GET['skillname']))
{
$query_Recordset1 = "SELECT * FROM ucn WHERE agentid = '$agent' AND skillname = '$skillname' ORDER BY startdate DESC";
} 
elseif (!empty($_GET['agent'])) {
$query_Recordset1 = "SELECT * FROM ucn WHERE agentid = '$agent' ORDER BY startdate DESC";
}
elseif (!empty($_GET['skillname'])) {
$query_Recordset1 = "SELECT * FROM ucn WHERE skillname = '$skillname' ORDER BY startdate DESC";
}
else 
{
$query_Recordset1 = "SELECT * FROM ucn ORDER BY startdate DESC";
}

[syed]

You should dynamically build the sql string. Loop through your $_GET variable and build the sql string.

 

$param = $_GET;

$i = 0;

$sql = "SELECT * FROM ucn WHERE";

 

foreach ( $param as $key => $value ){

 

    if ( $i == 0 ){

          $sql . = " " . $key . "='" . $value . "'";

    }else{

          $sql . = " AND " . $key . "='" . $value . "'";

    }

    $i++;

}

 

I've not tested the above code but basically, if you give all your textbox's the same name as your table columns, you can then loop through the $_GET variable, appending the key which is your table column name and the value which is your search criteria.

 

This is assuming your GET variable only contains search criteria ( column names). You need play around with the code. Hope it helps.

[Bendude14]

You shouldn't be using $_GET at all when using it to query a database especially when you are not escaping your data.

 

Consider using POST and mysql_real_escape_string() on all values retrieved...

 

Ben

[mac007]

I see what you are saying Syed... I will try your code a bit later today. Hope I can make it work... makes so much sense.

Also, you are right Ben, I'll switch that to POST after I get my code finalized. Many times it's easier for me to use GET when I'm in testing mode...

 

Let you guys know how it goes...