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'; 
?>

[mac_gyver]

to filter the status by ALL values would mean to either leave the entire status term out of the sql query statement OR cause the status term to always be a true value.

 

the code you have posted isn't even producing the correct status terms when a value is selected. fix that problem first, and it should be clear how to make the ALL filter work. echo $sql to see what it actually is for each possibility.

 

next, the php msyql_ extension is obsolete and has been removed from php for about one year. you need to use the php PDO extension, and use a prepared query to supply the data values to the sql query statement.

Edited December 19, 2016 by mac_gyver

[cainam29]

Thanks for the response mac_gyver.

 

So here's what I tried, I updated my code to fetch data to below,

<?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 {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //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';

    // prepare sql
    $stmt = $conn->prepare($sql);

    //bind parameters
    if ($status != 'ALL') $stmt->bindParam(':st', $status);
    $stmt->bindParam(':d1', $date);
    $stmt->bindParam(':d2', $date1);

    $stmt->execute();

    //do some output
    $result = $stmt->fetchAll();
    print_r($result);
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
//to count if there are any results
$numrow = mysql_num_rows($result);

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($result))  
{ 
    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'; 


$conn = null;
?>

But I am getting the error below when I am selecting ALL,

 

Array ( )

Warning: mysql_num_rows() expects parameter 1 to be resource, array given in C:*\retrieve_status_test1.php on line 69

No results found.

Warning: mysql_fetch_array() expects parameter 1 to be resource, array given in C:*\retrieve_status_test1.php on line 98

 

 

 

Line 69 refers to 

$numrow = mysql_num_rows($result);

Line 98 refers to 

while($info = mysql_fetch_array($result))

And when i select any of the option I am getting the message,

 

Array ( [0] => Array ( [column1] => test5 [0] => test5 [column2] => test5 [1] => test5 [status] => Successful [2] => Successful ) ) 

 

And the same error as selecting ALL,

Warning: mysql_num_rows() expects parameter 1 to be resource, array given in C:*\retrieve_status_test1.php on line 69

No results found.

Warning: mysql_fetch_array() expects parameter 1 to be resource, array given in C:*\retrieve_status_test1.php on line 98

 

I kind of lost from here and do not know how to proceed anymore.

[Barand]

You started out well with PDO statements, but then you suddenly revert back to mysql_ functions. You can't do that. You have to stick with PDO functions.

 

As you have stored the data in an array with fetchAll() you can now output the results with a foreach() loop on the array.

Edited December 19, 2016 by Barand

[cainam29]

Thanks Barand,

 

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 

 

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.

[Barand]

<option value="All">

if ($status != 'ALL')

Spot the difference?

[mac_gyver]

doing this would also help in finding the problem -

echo $sql to see what it actually is for each possibility.

[cainam29]

 

Spot the difference?

 

Thanks Barand. That actually did the trick, I never had thought that there was an All vs ALL issue in the code.

[benanamen]

Now you just need to fix the bad DB design.