PHP Language - how to

Author

how to - search

ashok

2006-09-26, 3:57 am

I am working on a search. The form has 11 parameters. User can fill in none
or some or all parameters in the form. If I make all the variations of what
form fields are filled, it comes out to be 100+ and making 100+ query is not
optimal. Is there something that can be used (like a wildcard) so that if
user doesn't fills a field, the query will fetch all rows for that field as
if "all" was selected in that field.

ZeldorBlat

2006-09-26, 6:56 pm

ashok wrote:
> I am working on a search. The form has 11 parameters. User can fill in none
> or some or all parameters in the form. If I make all the variations of what
> form fields are filled, it comes out to be 100+ and making 100+ query is not
> optimal. Is there something that can be used (like a wildcard) so that if
> user doesn't fills a field, the query will fetch all rows for that field as
> if "all" was selected in that field.

You don't write out every possible combination -- you build up the
query as you go. For example:

$sql = "select * from foo where ";

if(!empty($fieldA))
$sql .= "a = $fieldA and ";
if(!empty($fieldB))
$sql .= "b = $fieldB and ";
if(!empty($fieldC))
$sql .= "c = $fieldC and ";
....

Of course you'll need to figure out how to get rid of the last "and"
but that's the basic idea.

ashok

2006-09-26, 6:57 pm

Thanks a lot.
I too found something similar and adapted it to my needs. It's great and
really works.
I sorted the AND this way (if it helps others)

if ($_GET['name'] != '')
{
$query_rsresult .= "AND name_gallery LIKE '".$_GET['name']."%' ";
}

Thanks.

"ZeldorBlat" <zeldorblat@gmail.com> ???????/???????? ? ???????? ?????????:
news:1159284042.888146.118050@i3g2000cwc.googlegroups.com...
>
> ashok wrote:
>
> You don't write out every possible combination -- you build up the
> query as you go. For example:
>
> $sql = "select * from foo where ";
>
> if(!empty($fieldA))
> $sql .= "a = $fieldA and ";
> if(!empty($fieldB))
> $sql .= "b = $fieldB and ";
> if(!empty($fieldC))
> $sql .= "c = $fieldC and ";
> ...
>
> Of course you'll need to figure out how to get rid of the last "and"
> but that's the basic idea.
>

usenet@isotopeREEMOOVEmedia.com

2006-09-26, 6:57 pm

On Tue, 26 Sep 2006 20:35:44 +0400, "ashok" <ashok@replyhere.com> wrote:

>Thanks a lot.
>I too found something similar and adapted it to my needs. It's great and
>really works.
>I sorted the AND this way (if it helps others)
>
>if ($_GET['name'] != '')
>{
>$query_rsresult .= "AND name_gallery LIKE '".$_GET['name']."%' ";
>}
<snip>

That's begging for SQL injection attack. GET (and POST and COOKIE) values must
be sanitized before being used in a query.

For starters, read up on mysql_real_escape_string and
http://en.wikipedia.org/wiki/SQL_injection_attack

PleegWat

2006-09-27, 3:57 am

In article <05ajh25p9c7a00u0ahruoro1k7mfhdnpr4@4ax.com>, says...
> On Tue, 26 Sep 2006 20:35:44 +0400, "ashok" <ashok@replyhere.com> wrote:
>
> <snip>
>
> That's begging for SQL injection attack. GET (and POST and COOKIE) values must
> be sanitized before being used in a query.

I've been wondering about that. Safety as a point of origin aside, how
can dangerous SQL be inserted in a SELECT query when multiquery is
either not supported or off by default?
--
PleegWat
Remove caps to reply

ashok

2006-09-27, 6:58 pm

<usenet@isotopeREEMOOVEmedia.com> ???????/???????? ? ???????? ?????????:
news:05ajh25p9c7a00u0ahruoro1k7mfhdnpr4@
4ax.com...
> On Tue, 26 Sep 2006 20:35:44 +0400, "ashok" <ashok@replyhere.com> wrote:
>
> <snip>
>
> That's begging for SQL injection attack. GET (and POST and COOKIE) values
> must
> be sanitized before being used in a query.
>
> For starters, read up on mysql_real_escape_string and
> http://en.wikipedia.org/wiki/SQL_injection_attack

hmm, gmm, ok, thanks for pointing it out. will sanitize it all.

Koncept

2006-10-12, 6:59 pm

In article <1159284042.888146.118050@i3g2000cwc.googlegroups.com>,
ZeldorBlat <zeldorblat@gmail.com> wrote:

> You don't write out every possible combination -- you build up the
> query as you go. For example:
>
> $sql = "select * from foo where ";
>
> if(!empty($fieldA))
> $sql .= "a = $fieldA and ";
> if(!empty($fieldB))
> $sql .= "b = $fieldB and ";
> if(!empty($fieldC))
> $sql .= "c = $fieldC and ";
> ...
>
> Of course you'll need to figure out how to get rid of the last "and"
> but that's the basic idea.

<?php
$qElems = array();
$query = "SELECT * FROM `table` WHERE ";
foreach( $_GET as $k => $v ){
switch ( $k ) {
// don't forget to prepare (sanitize) $v
case 'fieldA':
$qElems[] = "`a` LIKE '%$v%'";
break;
case 'fieldB':
$qElems[] = "`b` LIKE '%$v%'";
break;
// cont ...
}
}
if($qElems){
echo $query = $query . implode(" OR ", $qElems);
} else {
// error message
}
?>

--
Koncept <<
"The snake that cannot shed its skin perishes. So do the spirits who are
prevented from changing their opinions; they cease to be a spirit." -Nietzsche

Sponsored Links