Searching My Database
Moderator: General Moderators
Searching My Database
Hi all. I'm trying to construct a fairly simple search facility for my database using driven menus that are also used for inserting information into the database. My problem is this though:
I can create a search facility but it looks at ALL the $_POSTs which may be blank as I want the user to be able to leave certain fields blank.
I assume I need to do this:
1. Create some variables from the $_POSTs (which I can do)
2. Pass these through PHP to evaluate if any are blank and if so omit them from the search
3. Execute the search.
It's point 2 that's causing the issue - I don't even know where to start. Can anyone help...?
I can create a search facility but it looks at ALL the $_POSTs which may be blank as I want the user to be able to leave certain fields blank.
I assume I need to do this:
1. Create some variables from the $_POSTs (which I can do)
2. Pass these through PHP to evaluate if any are blank and if so omit them from the search
3. Execute the search.
It's point 2 that's causing the issue - I don't even know where to start. Can anyone help...?
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
You could create an array of all empty $_POST values (although if some values could legitimately be zero a few adjustments would have to be made but the general principle is shown below):
The $data array would then hold all form variables which are not empty and you can then construct your SQL conditions from this.
Mac
Code: Select all
// initialise an array to hold the form data for the search
$data = array();
foreach ($_POST as $key => $value) {
// trim the value just in case - don't want spaces counted as data
$value = trim($value);
if (!empty($value)) {
$data[$key] = $value;
}
}
echo '<pre>';
print_r($data);
echo '</pre>';Mac
twigletmac
That works a treat - dismisses NULL values which is exactly what I want. I'll obviously have to spend some time looking into using the foreach for the rest of the site because currently I am declaring each $_POST as an individual variable.
Just one more thing if I may though...
Currently I am saerching using the following:
But clearly this will not work with the foreach.
Am I right in assuming that I need to execute something along these lines:
That works a treat - dismisses NULL values which is exactly what I want. I'll obviously have to spend some time looking into using the foreach for the rest of the site because currently I am declaring each $_POST as an individual variable.
Just one more thing if I may though...
Currently I am saerching using the following:
Code: Select all
// Execute The Search:
$query = "SELECT * FROM MembersData
WHERE `Work_Permit_Rqd`
LIKE '$WorkPermit'Am I right in assuming that I need to execute something along these lines:
$query = "SELECT * FROM MembersData
WHERE $data LIKE 'Field1','Field2','Field3','Field4' etc...?
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
What you would likely do is create dynamic search criteria, so we can continue working with the data array as follows (this assumes that the name of the post element is the same as its database name):
The code will need some refinement to prevent issues if there are no $sql_criteria and if you want any of the criteria to be met rather than all.
Mac
Code: Select all
$sql_criteria = array();
foreach ($data as $key => $value) {
// escape the string to prevent malicious entries and prevent problems
// with quotes
$value = mysql_escape_string($value);
// add the criteria to the array
$sql_criteria[] = "$key LIKE '$value'";
}
// turn the criteria array into a string
$sql_criteria = implode(' AND ', $sql_criteria);
// build SQL query
$sql = "SELECT * FROM MembersData WHERE $sql_criteria";Mac
Twigletmac
Thanks very much for that. And your time.
I'm almost there. Got the table set out and trying to drop the results into the table but I get the following error:
How do I exclude this...?
Thanks very much for that. And your time.
I'm almost there. Got the table set out and trying to drop the results into the table but I get the following error:
I assume this is because the Search button is in the array.Error 01: Unknown column 'Search' in 'where clause'
How do I exclude this...?
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Just add an array of excluded post fields:
Mac
Code: Select all
// you could add any number of excluded fields here
$excluded_post_fields = array('Search');
$sql_criteria = array();
foreach ($data as $key => $value) {
// escape the string to prevent malicious entries and prevent problems
// with quotes
$value = mysql_escape_string($value);
// check to make sure the post field isn't excluded
if (!in_array($key, $excluded_post_fields)) {
// add the criteria to the array
$sql_criteria[] = "$key LIKE '$value'";
}
}i do it like this 
compileRead accepts: an array with arrays like key=value
if the value has % it will do like value instead of = value
the same for <> , < , <= , >, >= , BETWEEN AND
Btw, before i call this method, i always test if all the keys in $what are actually columns in the database table.
compileRead accepts: an array with arrays like key=value
if the value has % it will do like value instead of = value
the same for <> , < , <= , >, >= , BETWEEN AND
Btw, before i call this method, i always test if all the keys in $what are actually columns in the database table.
Code: Select all
// returns a query for read
function compileRead($what)
{
// convert 1 dimension criteria to 2 dimensions
$criteria = array();
if (!is_array($what[0]))
{
$criteria[0] = $what;
}
else
{
$criteria = $what;
}
// build where clause
foreach($criteria as $what)
{
foreach($what as $attribute => $value)
{
// like
if ($preg_match("/^LIKE (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute} LIKE $value";
}
// have %, thus like
elseif ($preg_match("/\%/", $value, $matches))
{
$value = $this->escape($value);
$query .= "{$attribute} LIKE $value";
}
// between
elseif ($preg_match("/^BETWEEN (.*) AND (.*)/", $value, $matches))
{
$value1 = $this->escape($matches[1]);
$value2 = $this->escape($matches[2]);
$query .= "{$attribute} BETWEEN $value1 and $value2";
}
// unequality
elseif (preg_match("/^!= (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute}<>{$value}";
}
// equality
elseif (preg_match("/^== (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute}={$value}";
}
// lesser than or equal
elseif (preg_match("/^<= (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute}<={$value}";
}
// lesser than
elseif (preg_match("/^< (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute}<{$value}";
}
// greater than or equal
elseif (preg_match("/^>= (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{$attribute}>={$value}";
}
// greater than
elseif (preg_match("/^> (.*)/", $value, $matches))
{
$value = $this->escape($matches[1]);
$query .= "{attribute}>{$value}";
}
// default equality
else
{
$value = $this->escape($value);
$query .= "{$attribute}={$value}";
}
$query .= " AND ";
}
$query = rtrim($query, " AND ");
$query .= " OR ";
}
$query = rtrim($query, " OR ");
return $query;
}