|
|
Posted by emmanuel cloutier on 11/18/05 21:49
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" <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]
|