Search filtering using PHP and MySQL

Question

I am writing a search filter that will take a list of criteria, query the database, and return an array of usernames that match. Here is essentially what I'm doing:

if (!empty($_GET['cat'])) {
    $category = urldecode($_GET['cat']);
    $category_query = "SELECT DISTINCT username FROM tutor_subjects_taught WHERE category = '{$category}'";
    $category_process = mysql_query($category_query);
    while ($row2 = mysql_fetch_assoc($category_process)) {
        $usernames[] = $row2['username'];
    }
}

This takes the set category from the URL and queries the database for usernames that match this and puts it into an array.

Now, what I need to do is use additional "filters" to narrow down those usernames. My issue is: if the $usernames array is already set, how can I create a looped query that will check out each username and then return a subset array that matches?

For example, let's say $usernames is already set by the previous bit of code because we already have a $_GET['cat'] variable set. Now we add another $_GET variable "rev":

if (!empty($_GET['rev']) && isset($usernames)) {
    //Need to create loop here that will take all usernames in array from the previous block of code, check them for rev matching specific number, and return those that match into the $usernames array.
}

Let me see if I got this right. You want to query the database for a filter, create an array of the result and then do the same thing with another filter and the created array. And then do that over and over for each filter? — Oskar Persson
– Oskar Persson, Commented Jan 9, 2013 at 19:07
Basically if one filter is set in the URL, I want to query the users based on that criteria and return an array of their usernames. Then, if another filter is also subsequently set, I want to query the database for each username already pulled and see if those users match the new criteria as well. I want to then return an array of users that match both criteria. Let's say we start with 5 users who teach "Math" and then a new filter is set "With at least 5 reviews". The system should take each username pulled that does math and check if they have at least 5 reviews. Only those will be returned. — Arun
– Arun, Commented Jan 9, 2013 at 19:25

Oskar Persson · Accepted Answer · 2013-01-09 19:30:29Z

1

First off, you shouldn't use mysql. Use mysqli instead. This improves the security of your website. This will make it harder for hackers to use SQL injection.

For the filtering you could make it all in one query by setting up all the filters at the beginning. Make a $filter variable where you save all the values.

Example:

$filters = array();

if (!empty($_GET['cat'])) {
    $category = urldecode($_GET['cat']);
    $filter[] = 'category = ' . $category;
}

if (!empty($_GET['rev'])) {
    $anotherFilter = urldecode($_GET['rev']);
    $filters[] = 'anotherFilter = ' . $anotherFilter;
}

$filter = implode(' AND ', $filters);
$filter = $mysqli->real_escape_string($filter);

$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$stmt = $mysqli -> prepare("SELECT DISTINCT username FROM tutor_subjects_taught WHERE '$filter'");
$stmt -> execute();
$stmt -> bind_result($username);
/* fetch values */
    while ($stmt->fetch()) {
        printf ("%s\n", $username);
    }
$stmt -> close();
$mysqli -> close();

answered Jan 9, 2013 at 19:30

Oskar Persson

6,85517 gold badges71 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arun Over a year ago

Thanks for the answer. I'm not too familiar with MySQLi but I can check into it. The issue with this method for the way I have things set up is that most of the filters will be pulling information from different tables so querying just tutor_subjects_taught won't be enough. Any way you would suggest querying multiple tables using your method?

Oskar Persson Over a year ago

Use joins. Good tutorial here

Collectives™ on Stack Overflow

Search filtering using PHP and MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related