How to filter Select option to ALL and Show Data Based on ALL Select Option

cainam29

I have seen a lot of demos or sort of the same questions with the Select All option. But what I want is to just have a drop down that will allow me to select All option and not showing the entire Select box. Individual Select option works but not when I tried to use ALL as an option.

Here is my sample HTML:

<select name="status" id="status" style="width: 224px;">
<option value="" selected="selected">Please select...</option>
<option value="All">All</option>
<option value="Option1">Option1</option>
<option value="Option2">Option2</option>
<option value="Option3">Option3</option>
<option value="Option4">Option4</option>
</select>

From the code above I would want to filter the result using the All option.

So this is the page is where I filter and show the results in a table,

<script>
$(document).ready(function(){
$("#results").show();
});
</script>

<script type="text/javascript">
$(document).ready(function(){
$("#RetrieveList").on('click',function() {
var status = $('#status').val();
var date = $('#Date').val();
var date1 = $('#Date1').val();
$.post('retrieve_status.php',{status:status, date:date, date1:date1}, function(data){
$("#results").html(data);
});
return false;
});
});
</script>

<form id="form2" name="form2" method="post" action="">
<table width="941" border="0" align="center">
<tr>
<th width="935" colspan="9" scope="col">Status:
<select name="status" id="status" style="width: 224px;">
<option value="" selected="selected">Please select...</option>
<option value="All">All</option>
<option value="Option1">Option1</option>
<option value="Option2">Option2</option>
<option value="Option3">Option3</option>
<option value="Option4">Option4</option>
</select>
Start Date:<input type="text" name="Date" id="Date" size="8"/>
End Date:<input type="text" name="Date1" id="Date1" size="8"/>
<input name="action" type="submit" id="RetrieveList" value="Retrieve List" />
</th>
</tr>
</table>
</form>

<div id="results">
</div>

And this is how I fetch the data,

<?php 
require 'include/DB_Open.php'; 

$status = $_POST['status']; 
$date = $_POST['date']; 
$date1 = $_POST['date1']; 

if ($_POST['status'] == 'ALL') 
{ 
    $sql_status = '1'; 
} 
else 
{ 
    $sql_status = "status = '".mysql_real_escape_string($_POST['status'])."'"; 
} 

$sql="SELECT column1, column2, status 
        FROM tracker 
        WHERE status = '" . $sql_status . "' AND scheduled_start_date BETWEEN '" . $date . "' AND '" . $date1 . "' 
        ORDER BY scheduled_start_date"; 
$myData = mysql_query($sql); 

//to count if there are any results 
$numrow = mysql_num_rows($myData); 

if($numrow == 0) 
{ 
    echo "No results found."; 
} 
else 
{ 
    echo "CRQ Count: $numrow"; 
} 

{ 
echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> 
<tr> 
<th align='center'><strong>Column1</strong></th> 
<th align='center'><strong>Column2</strong></th> 
<th align='center'><strong>Status</strong></th> 
</tr>";  

while($info = mysql_fetch_array($myData))  

{ 
    echo "<form action='retrieve_status.php' method='post'>"; 
    echo"<tr>";  

    echo  "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; 
    echo  "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>";  

    echo  "<td align='center'>" . $info['status'] . "<input type=hidden name=status value=" . $info['status'] . " </td>"; 
    echo "</tr>";  

    echo "</form>"; 
} 
} 
echo "</table>";  

include 'include/DB_Close.php'; 
?>

NogDog

Check how you spell "All" as the form value, but test for "ALL" in the PHP code.

Derokorian

Also check that if you don't select "ALL" (which you can't, as NogDog pointed out) your query will end up with a syntax error, namely it will look like "status = status = 'Option1'"

cainam29

So here would be the updated code to fetch data,

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// check data before use it and convert from string to expected type, use try, not like here:
$status = $_POST['status'];
$date = $_POST['date'];
$date1 = $_POST['date1'];
// use valid data to select rows
try {
    //1. connect to MySQL database
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

//2. set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//3. create query string
$sql = 'SELECT column1, column2, status FROM tracker WHERE 1=1';
if ($status != 'ALL') $sql .= ' AND status = :st'; 
$sql .= ' AND scheduled_start_date BETWEEN :d1 AND :d2';

//4. prepare statement from query string
$stmt = $conn->prepare($sql);

//5. bind optional parameters
if ($status != 'ALL') $stmt->bindParam(':st', $status);

//6. bind parameters
$stmt->bindParam(':d1', $date);
$stmt->bindParam(':d2', $date1);

//7. execute statement
$stmt->execute();

//8. returns an array containing all of the result set rows 
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//get count of rows
$numrow = count($result);

//print array - there is many solution to print array,
//to debug you can do: 
//print_r($result);

} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;

if($numrow == 0) 
  echo "No results found.";
else 
  echo "Count: $numrow</br>";
{
echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> 
<tr> 
<th align='center'><strong>Column1</strong></th> 
<th align='center'><strong>Column2</strong></th> 
<th align='center'><strong>Status</strong></th> 
</tr>";  

foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status_test1.php' method='post'>";
echo"<tr>"; echo  "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; 
echo  "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>";  

echo  "<td align='center'>" . $info['status'] . "<input type=hidden name=status value=" . $info['status'] . " </td>"; 
echo "</tr>";  

echo "</form>"; 
} 
} 
echo "</table>";  

?>

And Select Options 1-4 works when selected individually but not when selecting ALL (which means selecting all options at the same time),

<select name="status" id="status" style="width: 224px;">
<option value="" selected="selected">Please select...</option>
<option value="All">All</option>
<option value="Option1">Option1</option>
<option value="Option2">Option2</option>
<option value="Option3">Option3</option>
<option value="Option4">Option4</option>
</select>

I tried to print the result and I am just getting

Quote
Array ( ) No results found.

Status select options 1-4 must be showing in the table If I filter Status to ALL but I am not getting anything. I wonder where the issue is now.

cainam29

This is fixed now. I never had thought that there was an All vs ALL issue in the code. All is working fine.

Derokorian

cainam29;11059203 wrote:
All is working fine.

Just being pedantic here, but All as in the option or as in the whole script? If the issues are resolved, please consider using the thread tools at the top of the thread to mark the thread resolved.