By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,050 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,050 IT Pros & Developers. It's quick & easy.

How to build query string?

P: n/a
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

Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jul 17 '05 #2

P: n/a
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)
Jul 17 '05 #3

P: n/a
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/
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.