Function to search a mysql database in php

Question

I am trying to create a function to search through my database with particular criteria.

function searchOrders($keywords, $criteriaId, $dateEntered, $statusID)

That is the function I intend to use, I am wanting to build a query to search for each of those variables but also them combined. I am wondering what is the best way to do this?

e.g. They don't enter a keyword but the do enter a criteriaId or they enter both of them but leave out the date and states.

Any ideas would be appreciated as I've never had to build a search feature yet.

use PDO and create the database-statement with parameters and this assign these parameters your variable-values. — bestprogrammerintheworld, Mar 31 '13 at 07:41
[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) — Shoe, Mar 31 '13 at 07:43
Here is the database design...not all of it, but what's relevant and also what the search looks like. http://i.imgur.com/167kP8A.jpg — Butterflycode, Mar 31 '13 at 07:54
Does PHP (or really, PDO) support a "Criteria Builder" API? Such exist on many ORM/DALs - at least in Java/.NET. Otherwise, it has to be specialized query generation if "and" and "or" logic must be dynamic and intermixed. — , Mar 31 '13 at 07:57
Just to mention, I got the answer first about a estructured function and @theghostofc edit your answer and put something similar to what I have after I had published. — SoldierCorp, Mar 31 '13 at 08:14

Vivek Jain · Accepted Answer · 2013-03-31T08:10:42.770

0

This is easy. Just get your request variables from the querystring. If they're not there, set them to "" or NULL....

$keywords = $_GET["keywords"];
$criteriaId = $_GET["criteriaId"];
$dateEntered = $_GET["dateEntered"];
$statusID = $_GET["statusID"];

... and call the function:

$orders = searchOrders($keywords, $criteriaId, $dateEntered, $statusID);

In the searchOrders function, you may check if value for any parameter is "" (blank) or NULL and create your SQL query accordingly.

Edit:

The trick here is to play with where and and keywords. (Assuming you need only a where / and condition query and include a parameter if its value is not blank or NULL)

public function searchOrders($keywords, $criteriaId, $dateEntered, $statusID)
{   
    $query = "select * from orders";
    $condition = " where ";

    if (!is_null($keywords)) {
        $query .= $condition . "keywords like '$keywords'";
        $condition = " and ";
    }

    if (!is_null($criteriaId)) {
        $query .= $condition . "criteriaId = $criteriaId";
        $condition = " and ";
    }

    if (!is_null($dateEntered)) {
        $query .= $condition . "dateEntered <= #$dateEntered#";
        $condition = " and ";
    }

    if (!is_null($statusID)) {
        $query .= $condition . "statusID = $statusID";
    }

    return mysql_query($query) or die (mysql_error);
}

Hope this helps!

Vivek

edited Mar 31 '13 at 08:10

answered Mar 31 '13 at 07:51

Vivek Jain

3,811
6
30
47

1

I know how to get the data, I just don't quite know how to build the query – Butterflycode Mar 31 '13 at 07:53
You might like to provide more information about the table structure or resultant query with all valid parameters. Eg. Given all 4 valid parameters, would your query look like select * from orders where keywords like '$keywords' and criteriaId = $criteriaId and dateEntered <= #$dateEntered# and statudID = $statusID ... or something similar? – Vivek Jain Mar 31 '13 at 07:57
Table structure is in a comment up in the question. What if one of them is null and not entered, wouldn't it be searching for criteriaID 0 then? – Butterflycode Mar 31 '13 at 07:59
I have edited my answer for NULL values. If this applies, you may modify the condition to include "" (blank) also – Vivek Jain Mar 31 '13 at 08:13
1

[Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/q/12859942). They are no longer maintained and the deprecation process has begun, see the [red box](http://php.net/mysql-connect). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli); [this article](http://php.net/mysqlinfo.api.choosing) will help you decide which. If you choose PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). – vascowhite Mar 31 '13 at 09:11
Thanks @vascowhite, the code was only for reference. It is advised to use PDO or any other ORM framework, at least for new code. – Vivek Jain Mar 31 '13 at 09:29
@theghostofc sorry, but I don't see that warning in your answer, you don't mention the dangers of using unsanitised user input either, which is what your code is doing. – vascowhite Mar 31 '13 at 10:12
@vascowhite, my intention was to give the asker a starting point, not a copy-paste solution. Still, thanks for sharing your concern. I'll keep your points in mind while answering questions. – Vivek Jain Mar 31 '13 at 15:34

score 0 · Answer 2 · answered Mar 31 '13 at 07:58

For example with simple mysql, you build some like this:

call a function to get some data

$data = selectSome("ID_User = '". $iduser ."' AND Situation != 0", 'table_example', 'Id_User, Name, Description, null, 'ID_User DESC', null);

/*Process $data */

Function

public function selectSome($SQL, $table = null, $fields = "*", $group = null, $order = null, $limit = null)
{       
    if (!is_null($group)) {
        $SQL .= " GROUP BY ". $group;
    }

    if (is_null($order)) { 
        $SQL .= "";     
    } elseif ($order === "DESC") {
        $SQL .= " ORDER BY $this->primaryKey DESC";
    } elseif (!is_null($order)) {  
        $SQL .= " ORDER BY ". $order;
    } elseif ($order === "") { 
        $SQL .= " ORDER BY $this->primaryKey";
    }

    if ($limit) {
        $SQL .= " LIMIT ". $limit;
    }

    $query = "SELECT $fields FROM $table WHERE $SQL";

    return mysql_query($query) or die (mysql_error);
}

It is a basic example of which can change some values and get your desired function and change MYSQL to PDO or MYSQLI.

Thanks for your help this gave me some good ideas along with theghostofc's answer. Greatly appreciate it! — Butterflycode, Mar 31 '13 at 08:22

score 0 · Answer 3 · answered Mar 31 '13 at 08:17

try something like this i dont know much about it but here's where i found it

SELECT name, price, warranty_available, exclusive_offer FROM Products UNION ALL SELECT name, price, guarantee_available, exclusive_offer FROM Services

Union Query

score -1 · Answer 4 · answered Mar 31 '13 at 07:49

-1

try something like mine:

$sql = mysql_query("select * from colleges where Campus_Name like '%$term%' or Campus_Address like '%$term%' or 
    Campus_State like '%$term%' or Campus_City like '%$term%' or Campus_Zip like '%$term%' ");

answered Mar 31 '13 at 07:49

Dillan

35
9

The problem I see there is it's using or, I want it to be able to check for multiple fields. e.g. if keywords is entered and a criteriaID is entered it matches both conditions not just one of them. – Butterflycode Mar 31 '13 at 07:55
If it's null I wouldn't want it to be included however. so AND doesn't quite work. – Butterflycode Mar 31 '13 at 08:06
1 min let me look through my code and the answer to the null is do an if else statement – Dillan Mar 31 '13 at 08:07
Problem is, it's going to be a lot of if statements and on top of that if one is null but the other isn't etc – Butterflycode Mar 31 '13 at 08:09
sorry can't help you but i did do qucik reserch and skimmed through some text google union query – Dillan Mar 31 '13 at 08:15

Function to search a mysql database in php

4 Answers4

Edit: