How to filter a PHP search request to a mysql BD?

Question

I current have a search form for a website that makes a search request to a mysql database using a keyword search and select tags. Right now it calls for all the results that the search bar is like. It then uses if statements to filter out the rest of the results bases on their information (If the current row doesnt have the request[searched] data in it then continue; on this while loop so that it skips that row when building the result array).

I know this isnt to good of a method because it loads a bunch of results I dont want then filters it within the while loop. What I am wondering is how I could filter the results within my query request to the DB so I dont need to filter everything after and increasing loading time.

When I tried to just filter the results with mysql this is how I thought maybe to do it, but it hasn't worked yet.

$sql = "SELECT * FROM mls_listings_phrets WHERE Address1 LIKE '%$searched%' or Remarks LIKE '%$searched%'";

$limit = "LIMIT " . ($page - 1)*$perPage . ", $perPage";
$endOfQuery = " ORDER BY id DESC " . $limit;

// if to search all, for sale, or for rent
if($ownershipType === "null"){

}else if($ownershipType === "rent"){
    $sql .= " AND Lease NOT LIKE '%null%'";
}else if($ownershipType === "sale"){
    $sql .= " AND Lease LIKE '%null%'";
}

    $sql .= $endOfQuery;

    $query = mysqli_query($connect, $sql);

try `LIKE '%forSale%'` ... and you're also never appending `$limit` to the end of the SQL ... and `AND WHERE` should just be `AND` — developerwjk, Sep 10 '15 at 16:29
You should use prepared statements. If those variables are user input you are open to SQL injections. — chris85, Sep 10 '15 at 16:32
just updated it, but it still doesnt seem to work just quite yet, as for the prepared statements I am very new with using them. I totally agree with what you are saying I just would like to get this functionality working then I can make its more secure and professional with prepared statements. — Brady Edgar, Sep 10 '15 at 16:52
lease is supposed to be a Null field or it's value will contain the word 'null'? — Lelio Faieta, Sep 10 '15 at 16:57
I think the issue is that the keyword search part of my code is causing the issues of filtering the rest of the query, because when I took it out and did some testing. The query worked fine. So it has to do with all the OR's — Brady Edgar, Sep 10 '15 at 17:02

ankhzet · Accepted Answer · 2015-09-10T17:38:28.993

look at direction of OOP:

class Filter {

    protected $connect;

    protected $perPage = 10;
    protected $page;

    protected $table;
    protected wheres = [];

    function __construct($connection, $table) {
        $this->connection = $connection;
        $this->table = $table;
    }

    function limit($perPage) {
        $this->perPage = $perPage;
        return $this;
    }

    function page($page) {
        $this->page = $page;
        return $this;
    }

    function where($where, $bool = 'AND') {
        $this->where[] = $this->where ? "{$bool} {$where}" : $where;
        return $this;
    }

    function orWhere($where) {
        return $this->where($where, 'OR');
    }

    function query(array $columns = ['*']) {

        $query = "SELECT " . $this->columns($columns) . " FROM {$this->table} ";

        if ($this->wheres) {
            $where = join(' ', $this->wheres);
            $query .= " WHERE " . $where;
        }

        if ($this->perPage) {
            $query .= " LIMIT "

            if ($this->page)
                $query .= ($this->page - 1) * $this->perPage . ", ";

            $query .= $this->perPage;
        }

        return mysqli_query($this->connect, $query);
    }

    function columns(array $columns) {
        $r = [];
        foreach ($columns as $column) {
            $c = [];
            $p = explode(' as ', $column);
            foreach ($p as $part)
                $c[] = '"' . trim($part, ' "') . '"';
            $r[] = join(' as ', $c)
        }
        return join(', ', $r);
    }

    function sanitize($input) {
        return mysql_real_escape_string($input);
    }

}

... and usage:

$filter = new Filter($connection, 'mls_listings_phrets');
$query = $filter->page($page)
                ->where("Remarks LIKE '%" . $filter->sanitize($searched) . "%'");

switch ($ownershipType) {
    case "rent":
    case "sale":
        $not = ($ownershipType == "rent") ? 'NOT' : '';
        $query = $query->where("WHERE Lease {$not} LIKE 'forSale'");
        break;
}

$results = $query->query()

... or even do this:

class ListingsPhretsFilter extends Filter {
    protected $table = 'mls_listings_phrets';
    protected $perPage = 20;

    function remarks($serached) {
        return $this->where("Remarks LIKE '%" . $this->sanitize($searched) . "%'");
    }

    function rent($rent = true) {
        $not = $rent ? 'NOT' : '';
        return $this->where("WHERE Lease {$not} LIKE 'forSale'");
    }
}

.. and things go even easier:

$filter = new ListingsPhretsFilter($connection);
$query = $filter->page($page)
                ->remarks($searched));

switch ($ownershipType) {
    case "rent":
    case "sale":
        $query = $query->rent($ownershipType == 'rent');
        break;
}

$results = $query->query()

Thanks so much for this example! I got every thing with work properly the way I did it now, but I defiantly am going to switch it over to OOP very soon. I have been wanting to properly code in OOP very badly for a while but have just been busy, so this is very helpful to have. Thanks — Brady Edgar, Sep 10 '15 at 17:21
Also, you should take a look at [ORM](http://stackoverflow.com/questions/108699/good-php-orm-library)s, when you get a little bit more familiar with OOP. Same states for frameworks, like yii/laravel/kohana/phalcon/propel. — ankhzet, Sep 10 '15 at 17:57

How to filter a PHP search request to a mysql BD?

1 Answers1