Searching My Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Searching My Database

Post by Harlequin »

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...?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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):

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>';
The $data array would then hold all form variables which are not empty and you can then construct your SQL conditions from this.

Mac
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

Thanks twigletmac

I'm just working on something else right now. Will crack on wit this later this morning - Many thanks 8)
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

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:

Code: Select all

// Execute The Search:
   $query = "SELECT * FROM MembersData
   WHERE `Work_Permit_Rqd`
   LIKE '$WorkPermit'
But clearly this will not work with the foreach.

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...?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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):

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";
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
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

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:
Error 01: Unknown column 'Search' in 'where clause'
I assume this is because the Search button is in the array.

How do I exclude this...?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Just add an array of excluded post fields:

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'";
    }
}
Mac
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

That seems to have done the trick twigletmac.

I've also managed to drop the results into a table and can echo any field I choose which is nice, means I don't have to declare the variables before hand which is really kewl.

Many thanks twigletmac.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

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.



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;
    }
Post Reply