[RESOLVED] 5 column table search

[asucrews]

i working a a database that the main table have five columns in it. i been task with writing the select statement so we can serach the table with anyone, all or any combitation of the columns. i believe i have the all coved, but can anyone see one missing?

select * from `fabric` where `fiber` = $fiber
select * from `fabric` where `diameter` = $diameter
select * from `fabric` where `length` = $length 
select * from `fabric` where `end` = $end
select * from `fabric` where `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter
select * from `fabric` where `fiber` = $fiber and `length` = $length 
select * from `fabric` where `fiber` = $fiber and `end` = $end
select * from `fabric` where `fiber` = $fiber and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `length` = $length
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `length` = $length and `end` = $end
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `length` = $length and `end` = $end and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `length` = $length and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `end` = $end
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `end` = $end and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `diameter` = $diameter and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `length` = $length and `end` = $end
select * from `fabric` where `fiber` = $fiber and `length` = $length and `end` = $end and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `length` = $length and `vendor` = $vendor
select * from `fabric` where `fiber` = $fiber and `end` = $end and `vendor` = $vendor

[dagon]

OUCH!
here is something a lot better to start with:

$fields=array('fiber','diameter','length','end','vendor');

$q="select * from `fabric` where ";

foreach($fields as $s){

 if(!empty($_POST[$s]){
  $q.= $s ."= '".$_POST[$s]."' AND ";
 }

}
//todo:
//clean up extra AND at end
//sanitise input

[asucrews]

wow that just dang sexy.....humm time to think....

[asucrews]

so this want i got so far

$fields=array('fiber','diameter','length','end','vendor'); 

$q="select * from `fabric` where "; 
foreach($fields as $s){ 
	if(!empty($_POST[$s]){
		$q.= $s ."= '".$_POST[$s]."' AND "; 	
	} 
} 

$strlen = strlen($q);
$q = substr($q,($strlen-5));

the sanitise input part is taking me a bit longer 🙁

[dagon]

I'll make the end bit a little quicker

$q = substr($q,0,-5);

you don't need to calculate the length

[asucrews]

so not as long as i was thinking....

$fields=array('fiber','diameter','length','end','vendor'); 

$q="select * from `fabric` where "; 
foreach($fields as $s){ 
	if(!empty($_POST[$s]){
		$s2 = $mysqli->real_escape_string($_POST[$s]);
		$q.= $s ."= '".$s2."' AND "; 	
	} 
} 

$strlen = strlen($q);
$q = substr($q,($strlen-5));

[bradgrafelman]

Well if we're optimizing for brevity...

$fields=array('fiber','diameter','length','end','vendor');
$where = array();

foreach($fields as $s){ 
    if(!empty($_POST[$s]) 
        $where[] = $s . "= '". $mysqli->real_escape_string($_POST[$s]) ."'";
} 

$q = "select * from `fabric` where " . implode(' AND ', $where);

[asucrews]

neat! two options, always good to learn something new everyday