How to filter Select option to ALL

Question

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.

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 just 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, UPDATED to show the code from Adam Silenko

<?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 (here is answer on your question)
    $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='retrieve_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>";  
?>

`still trying to learn new SQL` is not an excuse for using out-of-date api. — u_mulder, Dec 18 '16 at 11:17
First get rid of `mysql_***` functions, and use either `mysqli_***` or PDO (no reason to ignore). — trincot, Dec 18 '16 at 11:20
If this is a working example of your code, then you have lots of work to do. if you are storing `$_POST['status']` in `$status`, then why are you referencing again to `$_POST['status']` in your code. if you are having `$sql_status = "status = '".mysql_real_escape_string($_POST['status'])."'";` then `WHERE status = '" . $sql_status` will generate syntax error in your sql. Never use `mysql_*`. use `mysqli` or `PDO`. after this line `echo "CRQ Count: $numrow";` you are just opening a `{`, what is that?! — EhsanT, Dec 18 '16 at 15:50

score 0 · Accepted Answer · edited May 23 '17 at 10:30

0

Add condition with status to where clause in sql string only when option value is not equal to 'ALL', you should use [parametrized query]

example:

$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 (here is answer on your question)
    $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 "CRQ Count: $numrow</br>";

foreach ($result as $row => $info) {
  echo  $info['column1'] . ', ' . $info['column1'] . ', ' . $info['status'] . '; </br>';
}

read this php array as HTML table

edited May 23 '17 at 10:30

Community

1
1

answered Dec 18 '16 at 12:50

Adam Silenko

3,025
1
14
30

Hi Adam, my aplogies but I'm new to this PHP coding, would you be able to show me how it can be done? – testyummy Dec 18 '16 at 13:58
you should read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php , and this: http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection?rq=1 and [PDO manulal](http://php.net/manual/en/book.pdo.php) – Adam Silenko Dec 18 '16 at 16:33
I've tried to implement your code, but I am getting the error 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);$numrow = mysql_num_rows($result);` and Line 98 refers to `while($info = mysql_fetch_array($result))`. – testyummy Dec 19 '16 at 14:16
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 ) )` as well as 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. – testyummy Dec 19 '16 at 14:18
I've updated my original post to show how I integrated your code into my fetch code. – testyummy Dec 19 '16 at 14:24
Do you read PDO manual?? `$stmt->fetchAll();` returns array containing all of the result set rows, so, in `$result` variable you have array, don't use MySQL function on it.... Read PDO manual. You can use [`feach`](http://php.net/manual/en/pdostatement.fetch.php) or [`feachALL`](http://php.net/manual/en/pdostatement.fetchall.php) methods to get results of query. – Adam Silenko Dec 19 '16 at 16:15
I've updated the original fetch code. Selecting Options 1-4 individually works fine and is showing results in the table. But when I am selecting `ALL` as the filter which is supposed to show Status options 1-4 and its corresponding rows does not work. I tried to print the result and it just gives me `Array ( ) No results found.` – testyummy Dec 20 '16 at 07:36
1

nvm...i was able to figured it out. It's an issue with ` – testyummy Dec 20 '16 at 11:02

How to filter Select option to ALL

1 Answers1