[Resolved] search database

[kmf]

Hi,

I was wondering if anyone could help me. I have a search form on a page I'm creating, which has around 30 fields on it (in relation to computer equipment). Basically the user wants to be able to search by any of these fields and not necessarily filling in all the fields before beginning the search. So perhaps one time they will only search by inventory number, another time by key number and ip address etc. I am trying to write the php which will search using the terms supplied.

However all I can manage so far is this:

$sql = "SELECT * FROM equipment WHERE (inv_no=$inv_no,key_no=$key_no,manufacturer=$manufacturer,warranty_exp=$warranty_exp,description1=$description1,description2=$description2,model_no=$model_no,serial_no=$serial_no,location=$location,qub_class=$qub_class,date=$date,price=$price,responsibility_ctr=$responsibility_ctr,cost_centre=$cost_centre,location_code=$location_code,last_modified=$last_modified,modified_by=$modified_by,entry_date=$entry_date,supplier=$supplier,order_no=$order_no,pat_test=$pat_test,pat_due=$pat_due,test_status=$test_status,ip_address=$ip_address,ethernet_address=$ethernet_address,system_name=$system_name,network_card=$network_card,video_card=$video_card,dual_boot=$dual_boot,os=$os,memory=$memory,disk_size=$disk_size,disk_type=$disk_type,scsi_card=$scsi_card,other_cards=$other_cards,antivirus=$antivirus,maintenance=$maintenance)"; 

This creates errors when the user has not supplied all the fields. Can anyone help me to create a select statement which will search for only those fields supplied by the user and if the field is blank then it is not included in the search?

Thank you very much,
Karen

[yelvington]

Construct your conditions conditionally.

$query = 'SELECT * from table WHERE ';

if($someinput)
$query .= " somecolumn = '$someinput' ";

Rinse, repeat as necessary.

[kmf]

Hi, sorry it took me so long to reply, I've spent this time trying to get this to work before i came back to you!

Basically I can manage to get the code working for 2 variables which are passed in, but once I try to do another 'if' statement I get a
"Parse error: parse error, unexpected $" error.

As far as I am aware with my limited knowledge, I would guess that means it is not possible to do more than two if statements in a row?

I'll show you my code to let you see what I was trying to do:

$sql='SELECT * FROM equipment WHERE (';

if($inv_no)
$sql .= " inv_no = $inv_no AND ";

if($key_no) 	
$sql .= "key_no = $key_no AND ";

if($manufacturer){ 
$sql .= "manufacturer = $manufacturer AND";

if($warranty_ex){ 
$sql .= "warranty_exp = $warranty_ex AND";

if($desc1){ 
$sql .= "description1 = $desc1 AND";


if($desc2){ 
$sql .= "description2 = $desc2 AND";


if($model_no){ 
$sql .= "model_no = $model_no AND";


if($serial_no){ 
$sql .= "serial_no = $serial_no AND";


if($location){ 
$sql .= "location = $location AND";


if($class){ 
$sql .= "qub_class = $class AND";


if($date){ 
$sql .= "date = $date AND";


if($price){ 
$sql .= "price = $price AND";


if($resp_ctr){ 
$sql .= "responsibility_ctr = $resp_ctr AND";


if($cost_ctr){ 
$sql .= "cost_ctr = $cost_ctr AND";


if($location_code){ 
$sql .= "location_code = $location_code AND";


if($entry_date){ 
$sql .= "entry_date = $entry_date AND";


if($supplier !="Choose+an+Option"){ 
$sql .= "supplier = $supplier AND";


if($order_no){ 
$sql .= "order_no = $order_no AND";


if($pat_test){ 
$sql .= "pat_test = $pat_test AND";


if($test_status){ 
$sql .= "test_status = $test_status AND";


if($ip_address){ 
$sql .= "ip_address = $ip_address AND";


if($ethernet_address){ 
$sql .= "ethernet_address = $ethernet_address AND";


if($sys_name){ 
$sql .= "sys_name = $sys_name AND";

if($network_card){ 
$sql .= "network_card = $network_card AND";

if($video_card){ 
$sql .= "video_card = $video_card AND";

if($os){ 
$sql .= "os = $os AND";

if($dual_boot){ 
$sql .= "dual_boot = $dual_boot AND";

if($memory){ 
$sql .= "memory = $memory AND";

if($disk_size){ 
$sql .= "disk_size = $disk_size AND";

if($scsi_card){ 
$sql .= "scsi_card = $scsi_card AND";

if($disk_type){ 
$sql .= "disk_type = $disk_type AND";

if($other_cards){ 
$sql .= "other_cards = $other_cards AND";

if($antivirus){ 
$sql .= "antivirus = $antivirus AND";

if($maintenance){ 
$sql .= "maintenance = $maintenance AND";

// this substring function is just to remove the last 'AND' from the query, since it has to generated conditionally

$length=strlen($string); 
$sql = substr($sql, 0, $length-4);

$sql .=" )";

Sorry its so long, but a lot of it is very similar, with just variable names differing. Basically I will pass in a url like this:

?inv_no=123&key_no=&manufacturer=&warranty_ex=&
desc1=computer1&desc2=&model_no=&serial_no=&
Category=None&Rooms=None&ownername=&class=&
date=12.01.04&price=&resp_ctr=&cost_ctr=&location_code=
&entry_day=&entry_month=&entry_year=
&supplier=Choose+an+Option&order_no=&ptd_day=
&ptd_month=&ptd_year=&pat_day=&pat_month=
&pat_year=&test_status=yes&ip_address=&ethernet_address=
&sys_name=&network_card=&video_card=&os=unix&os_extra=
&dual_boot=&memory=&disk_size=&scsi_card=&disk_type=
&other_cards=&antivirus=&maintenance=&Submit=Search

Where only some of the values will be inserted, thees are conditionally chosen by the user.

I was wondering, first if anyone managed to understand my problem 😃 and secondly can you suggest any help? I really am aiming to get this finished by the end of today! Heehee, no pressure on myself! 🙁

thanks a lot!

[yelvington]

Your first two tests look like this:

if($inv_no)
$sql .= " inv_no = $inv_no AND ";

All the rest have an unclosed brace:

if($manufacturer){
$sql .= "manufacturer = $manufacturer AND";

[kmf]

yeah, sorry, meant to come back and say I got it sorted yesterday!! can't believe it was a stupid curly bracket causing me all that grief! 🙂

Thanks for the reply anyway!