PHP Multiple Filtering Links

Question

I am working on creating a filtering system for a mySQL database using dropdown menus. My system works when I search using one filter but I'm having a hard time getting them to stack. I am using GET to retrieve the filter they choose and I'm trying to save these and concatenate them to the next selection.

Right now my code gets all the variables possible:

$artistTag = $_GET['artistTag'];
$typeTag = $_GET['typeTag'];
$priceTag = $_GET['priceTag'];
$sizeTag = $_GET['sizeTag'];`

And here's where I'm having the issue:

$query = "SELECT * FROM artData";
$conditions = array();

$filter = '';

if ($artistTag != "") {
    $conditions[] = "artistTag='$artistTag'";
    $artistFilter = "artistTag=".$artistTag."";
    $filter = $artistFilter;
}
if ($typeTag != "") {
    $conditions[] = "typeTag='$typeTag'";
    $typeFilter = "typeTag=".$typeTag."";
    $filter .= "&".$typeFilter."";
}
if ($priceTag != "") {
    $conditions[] = "priceTag='$priceTag'";
    $priceFilter = "priceTag=".$priceTag."";
    $filter .= "&".$priceFilter."";
}
if ($sizeTag != "") {
    $conditions[] = "sizeTag='$sizeTag'";
    $sizeFilter = "sizeTag=".$sizeTag."";
    $filter .= "&".$sizeFilter."";
}

$sql = $query;
if (count($conditions) > 0) {
    $sql .= " WHERE " . implode(' AND ', $conditions);
}

I'm trying to append the links for additional filters with the previous filters using $filter.

<a href="index.php?artistTag='.$row[artistTag].''.$filter.'">'.$row[artist].'</a>

That way the next URL generated would be

index.php?artistTag=picasso&sizeTag=large

This works occasionally but if I select the same filter again it doesn't replace the old one. I am open to suggestions on how to do this but it's important that the filters are applied through the drop-down menu links and not a form. I've tried searching the forums and I can't find a solution thus far. Thank you for taking the time to help!

Edit: Kostas Mitsarakis was kind enough to help me get the code to get the tags previously searched and concatenate them into a prefix for the additional search tags. The problem is when new search tags are added it doesn't overwrite the tag of the same category. It also seems to not add the question mark and ampersand appropriately. This is my current code (simplified):

<DOCTYPE html>
<?php
define('DB_HOST', 'host');
define('DB_NAME', 'name');
define('DB_USER', 'user');
define('DB_PASSWORD', 'password');

$vars = array('artistTag', 'typeTag', 'priceTag', 'sizeTag');
$query = "SELECT * FROM artData ";
$filter = array();
$binds = array();
$i = 0;

$href = '<a href="index.php';

foreach($vars as $key => $value) { //For every attribute you want to take from $_GET
    if ($_GET[$value] != '') { //If the value is not empty
        $filter[] = $value." = :".$value; //Add it for WHERE clause
        $binds[':'.$value] = $_GET[$value]; //And also prepare the value
        if ($i == 0) { //This is for link creation
            $href .= '?'.$value.'='.$_GET[$value];
        } else {
            $href .= '&'.$value.'='.$_GET[$value];
        }
        $i++;
    }
}

try {
    //Make your connection handler to your database
    $conn = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASSWORD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

    if (!empty($filter)) { //If one or more $_GET variables are not empty
        $query .= " WHERE ".implode(' AND ', $filter); //Concatenate at Where clause
    }

    $stmt = $conn->prepare($query); //Prepare the statement
    $stmt->execute($binds); //And bind the values
    $result = $stmt->fetchAll();

    foreach($result as $row) {
        print ''.$row[artist].' - '.$row[id].' - '.$row[priceTag].'</br>';
    }

    $stmt = null; //Close the connection
    $conn = null;
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

?>
<html>
    <head>
        <title>debug mode</title>
    </head>
    <body>
        <?php
        print ''.$href.'artistTag=ricker">Bruce Ricker</a></br>
        '.$href.'artistTag=picasso">Pablo Picasso</a></br>
        '.$href.'artistTag=dali">Salvador Dal&iacute</a></br></br>

        '.$href.'typeTag=drawing">Drawing</a></br>
        '.$href.'typeTag=painting">Painting</a></br></br>

        '.$href.'sizeTag=large">Large</a></br>
        '.$href.'sizeTag=medium">Medium</a></br>
        '.$href.'sizeTag=small">Small</a></br></br>';


        ?>
    </body>
</html>

Where? I was trying to make it easier to concatenate the previous results. — Tim Troiano
– Tim Troiano, Commented Nov 23, 2015 at 22:19

Kostas Mitsarakis · Accepted Answer · 2015-11-24 16:04:16Z

1

You can use PDO with prepared statements for your SELECT query and a helper array for your link creation.

define('DB_HOST', 'localhost');
define('DB_NAME', 'your_database');
define('DB_USER', 'your_username');
define('DB_PASSWORD', 'your_password');

$vars = array('artistTag', 'typeTag', 'priceTag', 'sizeTag');
$query = "SELECT * FROM artData ";
$filter = array();
$binds = array();
$i = 0;

$href = '<a href="index.php';

foreach($vars as $key => $value) { //For every attribute you want to take from $_GET
    if ($_GET[$value] != '') { //If the value is not empty
        $filter[] = $value." = :".$value; //Add it for WHERE clause
        $binds[':'.$value] = $_GET[$value]; //And also prepare the value
        if ($i == 0) { //This is for link creation
            $href .= '?'.$value.'='.$_GET[$value];
        } else {
            $href .= '&'.$value.'='.$_GET[$value];
        }
        $i++;
    }
}

try {
    //Make your connection handler to your database
    $conn = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASSWORD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

    if (!empty($filter)) { //If one or more $_GET variables are not empty
        $query .= " WHERE ".implode(' AND ', $filter); //Concatenate at Where clause
    }

    $stmt = $conn->prepare($query); //Prepare the statement
    $stmt->execute($binds); //And bind the values
    $result = $stmt->fetchAll();

    foreach($result as $row) {
        echo $row['id'].'<br />';
    }

    $stmt = null; //Close the connection
    $conn = null;
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

EDIT:

To check if get variable is in use you can use something like the following (it needs to be modified).

$values = array(
    'artistTag'=>array(
        array(
            'url'=>'ricker',
            'text'=>'Bruce Ricker',
        ),
        array(
            'url'=>'picasso',
            'text'=>'Pablo Picasso',
        ),
        array(
            'url'=>'dali',
            'text'=>'Salvador Dal&iacute',
        ),
    ),
    'typeTag'=>array(
        array(
            'url'=>'drawing',
            'text'=>'Drawing',
        ),
        array(
            'url'=>'painting',
            'text'=>'Painting',
        ),
    ),
    'sizeTag'=>array(
        array(
            'url'=>'large',
            'text'=>'Large',
        ),
        array(
            'url'=>'medium',
            'text'=>'Medium',
        ),
        array(
            'url'=>'small',
            'text'=>'Small',
        ),
    ),
);

foreach($values as $key => $value) {
    if (empty($_GET[$key])) {
        foreach($value as $s_key => $s_value) {
            echo $href.'?'.$key.'='.$s_value['url'].'>'.$s_value['text'].'</a>';
        }
    }
}

edited Nov 24, 2015 at 16:04

answered Nov 23, 2015 at 22:27

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

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

21 Comments

Tim Troiano Over a year ago

Thank you for the quick response! This looks like exactly what I'm trying to do. I'm working on implementing it now and I'll let you know how it goes.

Kostas Mitsarakis Over a year ago

Yes, I think it's what you need. If I missed anything modify it to get the results you want.

Kostas Mitsarakis Over a year ago

Check the updated answer at: foreach($result as $row)

Tim Troiano Over a year ago

Perfect! That worked for printing out the results. I'm just working on using the $href variable correctly now.

Tim Troiano Over a year ago

As soon as I get it working properly I'll mark the answer as solved!

|

Collectives™ on Stack Overflow

PHP Multiple Filtering Links

1 Answer 1

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related