How to build query string?

[Artco News]

From a form I have a list of fields, e.g:

name
address
phone
memo

I know how I do it manually for search query but how do I do it
automatically.

select * from <table_name> where
name = %$_POST['name']% and/or
address LIKE %$_POST['address']% and/or
phone LIKE %$_POST['phone']% and/or
memo LIKE %$_POST['memo']%

but my trouble if one of them is blank. Thanks

[Bruno Desthuilliers]

Artco News wrote:

From a form I have a list of fields, e.g:

name
address
phone
memo

I know how I do it manually for search query but how do I do it
automatically.

select * from <table_name> where
name = %$_POST['name']% and/or
address LIKE %$_POST['address']% and/or
phone LIKE %$_POST['phone']% and/or
memo LIKE %$_POST['memo']%

but my trouble if one of them is blank. Thanks

A simple way to do it :

function getPostVar($varName, $default='')
{
if (isset($_POST[$varName])) {
$var = $_POST[$varName];
}
else {
$var = $default;
}
return $var;
}
function buildWhereClause($name, $adressAndOr,
$address, $phoneAndOr,
$phone, $memoAndOr, $memo)
{
$with_name = strlen($name) > 0;
$with_address = strlen($address) > 0;
$with_phone = strlen($phone) > 0;
$with_memo = strlen($memo) > 0;

$clause = '';

if ($with_name || $with_address || $with_phone || $with_memo) {
$clause = " where ";
if ($with_name) {
$clause .= " name = '$name' ";
if ($with_address) {
$clause .= $adressAndOr;
}
}
if ($with_address) {
$clause .= " address like %'$address' ";
if ($with_phone) {
$clause .= $phoneAndOr;
}
}
/* etc ... */
}
return $clause;
}

/* possible use... */
$where = buildWhereClause(getPostVar('name'), 'and',
getPostVar('address'), 'or',
getPostVar('phone'), 'and',
getPostVar('memo'));

$query = "select * from table_name " . $where;

Note that you should add some code to the getPostVar() function to make
sure no one is trying to attack your system.

HTH
Bruno

[Andy Hassall]

On Mon, 03 Nov 2003 22:01:46 GMT, "Artco News" <ar*******@verizon.net> wrote:

From a form I have a list of fields, e.g:

name
address
phone
memo

I know how I do it manually for search query but how do I do it
automatically.

select * from <table_name> where
name = %$_POST['name']% and/or
address LIKE %$_POST['address']% and/or
phone LIKE %$_POST['phone']% and/or
memo LIKE %$_POST['memo']%

but my trouble if one of them is blank. Thanks

What trouble are you having? You end up with a redundant predicate (like '%%',
which always matches - well, except for NULLs anyway), but it would still work.

How are you determining whether you want AND or OR anyway?

Do you want something like (assuming MySQL where you have to stuff values in
SQL, rather than use placeholders):

<?php
$queryable_fields = array('name', 'address', 'phone', 'memo');

$sql = 'select * from tab where 1=1 ';

foreach ($queryable_fields as $field)
if (isset($_POST[$field]))
$sql .= sprintf("and %s LIKE '%s%' ",
$field,
'%'.addslashes($_POST[$field]).'%');

echo $sql;
?>

(The 1=1 bit being there so you don't have to bother trimming off an 'and' at
one end)

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Jochen Daum]

Hi Artco!
On Mon, 03 Nov 2003 22:01:46 GMT, "Artco News" <ar*******@verizon.net>
wrote:

From a form I have a list of fields, e.g:

name
address
phone
memo

I know how I do it manually for search query but how do I do it
automatically.

select * from <table_name> where
name = %$_POST['name']% and/or
address LIKE %$_POST['address']% and/or
phone LIKE %$_POST['phone']% and/or
memo LIKE %$_POST['memo']%

but my trouble if one of them is blank. Thanks

Have a look at dal.php from the CVS from the project in the signature.

HTH, Jochen
--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/