Filter with Radio Buttons

[Andy Rivers]

Hey eveybody,

 

I have a single page which links to a small database with 9 records relating to cars.

In the sidebar I have radio buttons with the option to filter by date (asc /desc) or by color (red, green, blue).

 

When a radio button is selected and submitted a message is echoed to tell me which radio button was selected.

The records displayed however, do nothing, they don't seem to get filtered.

 

Here is some of the code: 

 

<?php
//connect to the database
$dbc = mysqli_connect('host', 'user', 'password', 'cars') or die('Error connecting to MySQL Server.');

//If RadioButton Clicked Sort the Database by dateadded Asc / Desc
if(isset($_POST['submit']) && isset($_POST['dateorder']) && !empty($_POST['dateorder'])){
        if($_POST['dateorder'] == 'dateasc'){
             //Run query for dateasc
echo "You have selected :".$_POST['dateorder'];
$query = "SELECT * FROM cardetails ORDER BY caradded asc";
        }elseif($_POST['dateorder'] == 'datedesc'){
             //Run query for datedesc
echo "You have selected :".$_POST['dateorder'];
$query = "SELECT * FROM cardetails ORDER BY caradded desc";
        }
}else{
$query = "SELECT * FROM cardetails ORDER BY id asc";
}


//If RadioButton Clicked Sort the Database by Color Red, Green, Blue
if(isset($_POST['submit']) && isset($_POST['color']) && !empty($_POST['color'])){
        if($_POST['color'] == 'red'){
             //Run query for red color
echo "You have selected :".$_POST['color'];
$query = "SELECT * FROM cardetails WHERE color = 'red'";
        }elseif($_POST['color'] == 'green'){
             //Run query for green color
echo "You have selected :".$_POST['color'];
$query = "SELECT * FROM cardetails WHERE color = 'green'";
        }elseif($_POST['color'] == 'blue'){
             //Run query for blue color
echo "You have selected :".$_POST['color'];
$query = "SELECT * FROM cardetails WHERE color = 'blue'";
        }
}else{
     $query = "SELECT * FROM cardetails ORDER BY id asc";
}

$result = mysqli_query($dbc, $query) or die('Error Refreshing the page: ' . mysqli_error($dbc));

The form looks like this: 

 

<div id="leftcolumnwrap">
        <div id="leftcolumn">
<h2>Trial Filters</h2>
<form method="post" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
<p>Filter by Date:</p> 
<input type="radio" name="dateorder" value="dateasc"><label for="dateasc">A - Z</label><br>
<input type="radio" name="dateorder" value="datedesc"><label for="datedesc">Z - A</label><br>
<br><hr>
<p>Filter by Colour:</p>
<input type="radio" name="color" value="red"><label for="red">Red</label><br>
<input type="radio" name="color" value="green"><label for="green">Green</label><br>
<input type="radio" name="color" value="blue"><label for="blue">Blue</label>
<br><br>
<input name="submit" value="Submit" type="submit">
<br><br></form>
        </div>
        </div>

Any ideas, what can I do to sort this? Thanks for any help in advance, Andy ;-)

 

[mac_gyver]

your dateorder and color filters are doing two different things that you need to combine into ONE sql query statement. the dateorder is determining what the ORDER BY term should be. the color is determining what the WHERE term should. the logic for each of those should be (safely) building the two parts of the sql query statement, then produce the entire sql query statement by incorporating those terms into it.

 

next, you should be using a method='get' form, since this is determine what the page will display and your form should be 'sticky' and select any existing radio button choice.

 

the easiest way of making the form 'sticky' is to dynamically produce the form fields. if you make an array holding the choices, you can just loop over the array to produce the form fields and cause any currently selected choice to be checked. you can than also use this same defining array to validate that the input data is only and exactly one of the permitted choices.

 

i'll post some example code showing these suggestions, if i have some time.

[Andy Rivers]

Cool, thanks, for the reply.  Example code would be great if you got the time, as I am not really sure how to go about some of that stuff.

Thanks, Andy ;-) 

[Jacques1]

The logic already makes no sense, because your second “filter” always overrides the first one. Even if the second filter isn't used at all, the query from the first one will be overriden with the default query.

 

There's also a lot of duplicate code (why would you write three queries for three colors?).

 

I think you should plan the code before you start typing. To make sure we agree on the facts:

• There's a base query.
• The date “filter” affects the ORDER BY clause.
• The color filter affects the WHERE clause.
• Both filters can be selected simultaneously (at least I see no reason for preventing that).

So why don't you start with a variable for the WHERE condition(s) and a variable for the ORDER BY clause? Depending on the request parameters, you change those variables. At the end, you assemble the query.

[mac_gyver]

example code - 

// define the possible choices for each set of filters -

$dateorder = array();
$dateorder['asc'] = 'Ascending';
$dateorder['desc'] = 'Descending';

// note: you would query for the unique colors from your data to produce the choices, rather than hard-coding them
// you may want an 'All' choice that would leave this WHERE term out of the query to match all colors. the 'All' choice would also be used as  the default value if nothing is selected
$colors = array();
$colors['red'] = 'Red';
$colors['green'] = 'Green';
$colors['blue'] = 'Blue';

//connect to the database
$dbc = mysqli_connect('host', 'user', 'password', 'cars') or die('Error connecting to MySQL Server.');

$dateord = isset($_GET['dateorder']) ? $_GET['dateorder'] : 'asc'; // condition the input and set a default order
$dateord = isset($dateorder[$dateord]) ? $dateord : ''; // validate the input as being only and exactly one of the permitted values

$orderby_term = "ORDER BY id asc"; // note: asc is the default and doesn't generally need to be specified
if($dateord)
{
    $orderby_term = "ORDER BY caradded $dateord";
}

$color = isset($_GET['color']) ? $_GET['color'] : ''; // condition the input
$color = isset($colors[$color]) ? $color : ''; // validate the input as being only and exactly one of the permitted values

$where_term = "";
if($color)
{
    $where_term = "WHERE color = '$color'";
}

$query = "SELECT * FROM cardetails $where_term $orderby_term";
$result = mysqli_query($dbc, $query) or die('Error Refreshing the page: ' . mysqli_error($dbc));

// the rest of your code using the data from the query...

?>

<div id="leftcolumnwrap">
<div id="leftcolumn">
<h2>Trial Filters</h2>
<form method="get">
<p>Order by Date:</p>
<?php
// note: the $value and $label (any dynamic data) being output, in the following two sections of code, needs to have htmlentities() applied to them, coding left up to you as an exercise 
foreach($dateorder as $value=>$label)
{
    $checked = isset($dateord) && $dateord == $value ? ' checked' : '';
    echo "<input type='radio' name='dateorder' id='dateorder_$value' value='$value'$checked><label for='dateorder_$value'>$label</label><br>\n";
}
?>
<br><hr>
<p>Filter by Colour:</p>
<?php
foreach($colors as $value=>$label)
{
    $checked = isset($color) && $color == $value ? ' checked' : '';
    echo "<input type='radio' name='color' id='color_$value' value='$value'$checked><label for='color_$value'>$label</label><br>\n";
}
?>
<br><br>
<input value="Submit" type="submit">
<br><br></form>
</div>
</div>

[Andy Rivers]

wow, thanks mac_gyver, I shall test this out and see what happens.  Jacques1 is correct, the code is badly structured and I thought the pagination was having an effect on the script aswell.  

 

Thanks for your help, Andy ;-)