Struggling with Multiple Criteria Query

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

Re: Struggling with Multiple Criteria Query

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.

Re: Struggling with Multiple Criteria Query

Here an algorithme in pseudo code:

var $SQL;
var $SQLSELECT ;
var $SQLWHERE ;
var $Results;

foreache ($Field in $_POST[]){
if(!empty($Field)) {
if (!empty($SQLSELECT){
$SQLSELECT .= ',';
$SQLWHERE .= ' AND ';
$Results .= ',';
}
$SQLSELECT .= "$Field=>Name";
$SQLWHERE .= "$Field=>Name LIKE $Field=>Value";
$Results .= .= "$Field=>Name = $Field=>Value";
}
}

if (!empty($SQLSELECT){
$Results= "Resulte for $Results";
$SQL ="SELECT " . $SQLSELECT . $SQLWHERE;
}

I'am new in php so......

I now, mon anglais est comme ci, comme ça!

"Bob Stearns" 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.

Re: Struggling with Multiple Criteria Query

"emmanuel cloutier" 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" 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.
>
>