Create Search engine with multiple form fields

--0-1956571748-1214752020=:5919
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,  =0AI have an HTML form which has more then one fields. User can pr=
ecise its search using one or more input fields E.g GigName,Date, Genre,Ven=
ue. =0AThe data is coming from 3 tables.=A0 Below Query brings one tables r=
ecord with each and every row of other two tables. Could any one please hel=
p me sort out the query.  =0Aselect * FROM gig g, venue v, genre ge =A0=
WHERE g.gigName LIKE '%".$gig_name."%' OR g.gig_date LIKE '%".$sdate."%' OR=
ge.name LIKE '%".$genre."%' OR g.ticket_price LIKE '%".$ticket_price1."%' =
OR g.ticket_price LIKE '%".$ticket_price2."%' OR v.venueName LIKE '%".$vnam=
e."%'"  =0AIf I replace OR with AND I dont get any result.    =
=0ARegards  =0ANasreen =0A
--0-1956571748-1214752020=:5919--

Re: Create Search engine with multiple form fields

------=_Part_12495_3846559.1214756442285
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Nasreen,

Well, I think the better solution should be a dynamic query depending on
what you have entered in the form. The switch case is not valid in this
particular case because it will only select one of the fields, so the best
solution should be like this:

$query = "select * FROM gig g, venue v, genre ge"; //$query is the
"incremental" string in which we are building the final query.

//check if there will be a "where section"

if ($number_of_fields_filled_in > 0)
{
$query = $query . " WHERE ";

if (isset($gig_name) && $gig_name != "" && $number_of_fields_filled_in > 0 )
//evaluate whether the variable is set and it is not blank
{
$query = $query . "g.gigName LIKE '%".$gig_name."%'";
$number_of_fields_filled_in--;
if ($number_of_fields_filled_in > 0)
{
$query = $query . " AND ";
}
}
if (isset($sdate) && isvaliddate($sdate) && $number_of_fields_filled_in > 0
) //check if $sdate is a valid date...
{
$query = $query . "g.gig_date LIKE '%".$sdate."%'";
$number_of_fields_filled_in--;
if ($number_of_fields_filled_in > 0)
{
$query = $query . " AND ";
}
}

.... Etc.

}

So you make an "if" statement for every input field checking whether the
variable of that field is set (with php function isset( )) and if it follows
the rule you want to - the name is not blank (first case); the date is valid
according to a function isvaliddate ( )... If everything is right, you
concatenate the query you had with the specific "WHERE"-part for that input
field.

Note that we have a $number_of_fields_filled_in to check how many fields
have been filled in. You can make a function so as to check how many fields
are checked. Also note that I've put "if ($number_of_fields_filled_in > 0)"
and it takes inside EVERY check on the field variables. So if there is not a
single one, you don't make a wrong query. You check how many "WHERE" clauses
will be, and in every "if" statement you enter, you check if the number of
fields entered is > 0 and once the query is completed for that variable, you
check if there will be more statements (so as to add the "AND").

Finally, the only thing you have to do is send the query $query for
processing in the database.

I think this is one of the possible ways to do it. Check if you understand
the code above and try implementing.

Of course there should be more ways to do it, but it is one of the
simpliest.

Regards,

Roberto


On Sun, Jun 29, 2008 at 17:54, Nasreen Laghari
wrote:

> Hi Roberto,
>
> You have got my point what I want to do. Do you think I should check and
> see which field's data has been entered and then make a dynamic query
> according to.
>
> Shall I use Switch- Case?? I have 6 fields so which way you think will be
> better to create dynamic query?
>
> Thank you
>
> Nasreen
> ----- Original Message ----
> From: Roberto Costumero Moreno
> To: Nasreen Laghari
> Sent: Sunday, June 29, 2008 4:36:34 PM
> Subject: Re: [PHP-DB] Create Search engine with multiple form fields
>
> Hi,
>
> Let me see if I have understood what you want your application to do.
>
> You have a web form with several input fields, and ONE or SEVERAL can be
> filled in so as to search data, and I suppose you want your script to search
> data matching all filled in the form.
>
> With the actual Query, you are searching every content in the DB which has
> any coincidence with the data filled in the form. No matter which field is
> matching, it shows the complete data.
>
> If you change OR with AND, you are telling the DB to search if there is any
> data on it that matches EVERY field that you've sent. What happens if you
> don't fill any of the fields?
>
> The DB answers that it has no record like that, because comparison between
> the fields in DB and the input field is FALSE.
>
> All you have to do is to retrieve a correct "AND"-query with the parameters
> you've filled in.
>
> As an example imagine you fill only Date and Genre, so your query should
> look like this:
>
> select * FROM gig g, genre ge WHERE g.gig_date LIKE '%".$sdate."%' AND
> ge.name LIKE '%".$genre."%'"
>
> So the script will search all data that has the Date "$sdate" and the Genre
> "$genre".
>
> Tell me if you've understood or if you need more help.
>
> Regads,
>
> Roberto
>
>
>
> On Sun, Jun 29, 2008 at 17:07, Nasreen Laghari
> wrote:
>
>> Hi,
>>
>> I have an HTML form which has more then one fields. User can precise its
>> search using one or more input fields E.g GigName,Date, Genre,Venue.
>> The data is coming from 3 tables. Below Query brings one tables record
>> with each and every row of other two tables. Could any one please help me
>> sort out the query.
>>
>> select * FROM gig g, venue v, genre ge WHERE g.gigName LIKE
>> '%".$gig_name."%' OR g.gig_date LIKE '%".$sdate."%' OR ge.name LIKE
>> '%".$genre."%' OR g.ticket_price LIKE '%".$ticket_price1."%' OR
>> g.ticket_price LIKE '%".$ticket_price2."%' OR v.venueName LIKE
>> '%".$vname."%'"
>>
>> If I replace OR with AND I dont get any result.
>>
>>
>> Regards
>>
>> Nasreen
>>
>>
>>
>
>
>
>

------=_Part_12495_3846559.1214756442285--

Re: Create Search engine with multiple form fields

------=_Part_10207_10634704.1214756830794
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, Jun 29, 2008 at 11:07 AM, Nasreen Laghari
wrote:

> Hi,
>
> I have an HTML form which has more then one fields. User can precise its
> search using one or more input fields E.g GigName,Date, Genre,Venue.
> The data is coming from 3 tables. Below Query brings one tables record
> with each and every row of other two tables. Could any one please help me
> sort out the query.
>
> select * FROM gig g, venue v, genre ge WHERE g.gigName LIKE
> '%".$gig_name."%' OR g.gig_date LIKE '%".$sdate."%' OR ge.name LIKE
> '%".$genre."%' OR g.ticket_price LIKE '%".$ticket_price1."%' OR
> g.ticket_price LIKE '%".$ticket_price2."%' OR v.venueName LIKE
> '%".$vname."%'"
>
> If I replace OR with AND I dont get any result.
>
>
> Regards
>
> Nasreen
>
>
>


I would avoid the ORs and build the statement dynamically making for a
cleaner sql


$sql = "select * FROM gig g, venue v, genre ge WHERE 1 ";

if(!empty($_POST['gig_name']))
{
$sql .= " AND g.gigName LIKE
'%".mysql_real_escape_string(strip_tags($_POST['gig_name'])) ."%' "
}

if(!empty($_POST['sdate']))
{
$sql .= " AND g.gig_date LIKE
'%".mysql_real_escape_string(strip_tags($_POST['sdate']))."% ' "
}

if(!empty($_POST['genre']))
{
$sql .= " AND ge.name LIKE
'%".mysql_real_escape_string(strip_tags($_POST['genre']))."% ' "
}

if((!empty($_POST['ticket_price1']))&&(!empty($_POST['ticket _price2'])))
{
//let's assume its a OR relationship (price is t1 or t2)
$sql .= " AND (g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price 1']))."%' "
$sql .= " OR g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price 2']))."%') "

}else{
// let's assume here its t1 or t2 that is filled in, but not both
if(!empty($_POST['ticket_price1']))
{
$sql .= " AND g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price 1']))."%' "
}

if(!empty($_POST['ticket_price2']))
{
$sql .= " AND g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price 2']))."%' "
}

}

if(!empty($_POST['vname']))
{
$sql .= " AND v.venueName LIKE
'%".mysql_real_escape_string(strip_tags($_POST['vname']))."% ' "
}

mysql_result($sql,$conn);

?>



--

Bastien

Cat, the other other white meat

------=_Part_10207_10634704.1214756830794--

Re: Create Search engine with multiple form fields

Hello,

yesterday I installed PHP 5.2.7 and whenever I use mysql_connect
function Apache (2.2.9) crashes. Mysqli functions work without problem.

Anybody knows why is this happening or has similar experience?

OKi98

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php