Jump to content

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

cainam29

By cainam29
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

cainam29 Member

cainam29

Posted
Posted

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'; 
?>
Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

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 by mac_gyver
Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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 by Barand
Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.