Posted by Jim Michaels on 01/10/06 23:32

"emmanuel cloutier" <manucloutie@videotron.ca> wrote in message
news:viqff.22263$AZ3.220442@wagner.videotron.net...
> Here an algorithme in pseudo code:
>

you mean like this:

$SQL="";
$SQLSELECT="";
$SQLWHERE="";
$Results="";

foreach ($_POST as $key => $value){
if(""!=$key) {
if (""!=$SQLSELECT){
$SQLSELECT .= ',';
$SQLWHERE .= ' AND ';
$Results .= ',';
}
$SQLSELECT .= $key;
$SQLWHERE .= "$key LIKE $value%";
$Results .= "$key = $value";
}
}

if (""!=$SQLSELECT){
$Results= "Result for $Results";
$SQL ="SELECT $SQLSELECT WHERE $SQLWHERE";
}

but I hardly think that starting the column list with a comma is the best
solution. (see the 2nd if statement) key probably won't ever be empty will
it? This is a classic fencepost problem where the $key/$value pair is the
fencepost, and the comma (,) is the fence. the fence must start and end with
a fencepost to be syntactically correct. And that makes it harder to
program. One possible solution is to store things in arrays and to use
implode(). maybe something like this:

$SQL="";
$Results="";
$selects=Array();
$wheres=Array();
$results=Array();

foreach ($_POST as $key => $value){
if(""!=$key) {
$selects[]= $key;
$wheres[]= "$key LIKE $value%";
$results[]= "$key = $value";
}
}
$select=implode(',', $selects);
$wher=implode(' AND ', $wheres);
$result=implode(',', $results);
if (0!=count($selects)){
$Results= "Results for $result";
$SQL ="SELECT $select WHERE $wher";
}



> I'am new in php so......
>
> I now, mon anglais est comme ci, comme �a!
>
> "Bob Stearns" <rstearns1241@charter.net> a �crit dans le message de news:
> IsSdf.72322$RG4.12279@fe05.lga...
>> C. David Rossen wrote:
>> > Hello:
>> >
>> > I am struggling with creating a search on multiple criteria (fields) of
> a
>> > MySql database.
>> > I have a search form that has 5 different criteria, each with a value
>> > of
> one
>> > of the fields
>> > in the database. Here is what I have done but it doesn't work the way I
>> > want:
>> >
>> > Declared Variables:
>> > $Field1=$_POST['Field1'];
>> > $Field2=$_POST['Field2'];
>> > $Field3=$_POST['Field3'];
>> > $Field4=$_POST['Field4'];
>> > $Field5=$_POST['Field5'];
>> >
>> > Query:
>> > $result = mysql_query("select * from tablename where Field1 LIKE
> '%$Field1%'
>> > AND Field2 LIKE '%$Field2%' AND Field3 LIKE '$Field3%' AND Field4 LIKE
>> > '$Field4%'
>> > AND Field4 LIKE '$Field4%' AND Field5 LIKE '$Field5%'ORDER BY LastName
>> > ASC");
>> >
>> > This gets the results:
>> > while($r=mysql_fetch_array($result))
>> > {
>> > $Field1=$r["Field1"];
>> > $Field2=$r["Field2"];
>> > $Field3=$r["Field3"];
>> > $Field4=$r["Field4"];
>> > $Field5=$r["Field5"];
>> > $Field6=$r["Field6"];
>> > $Field7=$r["Field7"];
>> > $Field8=$r["Field8"];
>> > $Field9=$r["Field9"];
>> > $Field10=$r["Field10"];
>> > $Field11=$r["Field11"];
>> > $Field12=$r["Field12"];
>> > $Field13=$r["Field13"];
>> >
>> > There are 13 fields in the table, but the form only searches on the 5
>> > in
> the
>> > query. I want the
>> > user to be able to only search on the fields they want. For example,
> even
>> > though there are 5
>> > fields in the form, they may only want to search on 2, or 3, etc. What
>> > I
>> > don't understand is how
>> > you account for this in the query. The above query assumes they are
> going to
>> > search on all 5
>> > fields in the form, but what if they don't. I also want to put at the
> top of
>> > the output something
>> > like: "Results for Field1="selection1", Field2="selection2", etc.
>> > Again,
> not
>> > sure how you account
>> > for the fields they did not care to search on. Not sure how you do all
> of
>> > this. If you can give
>> > me some advice or point me to some resources, I would greatly
>> > appreciate
> it.
>> > Thanks.
>> >
>> > David
>> >
>> >
>>
>> Something like the following should work:
>>
>>
>> $sql = "select * from tablename where ";
>> if(!empty($Field1)) {
>> $sql .= "Field1 like $Field1%";
>> $opr = " AND ";
>> }
>> if(!empty($Field2)) {
>> $sql .= "$opr Field2 like $Field2%";
>> $opr = " AND ";
>> }
>> if(!empty($Field3)) {
>> $sql .= "$opr Field3 like $Field3%";
>> $opr = " AND ";
>> }
>> if(!empty($Field4)) {
>> $sql .= "$opr Field4 like $Field4%";
>> $opr = " AND ";
>> }
>> if(!empty($Field5)) {
>> $sql .= "$opr Field5 like $Field5%";
>> $opr = " AND ";
>> }
>> $result = mysql_query($sql);
>>
>> If you want any more fields, look into arrays, both in your html and
>> your php.
>
>

[Back to original message]