CODEWITHSUNDEEP
phpmysql
Arun
Arun

Reputation: 655

Search filtering using PHP and MySQL

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.
}

Upvotes: 0

Views: 1562

Answers (1)

Oskar Persson
Oskar Persson

Reputation: 6753

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();

Upvotes: 1

Related Questions