Filter search

[alexliebt]

I am trying to create a filter search script.
My plan is to select the first filter, after the filtering to select the second filter. But, the $_SESSION doesn’t store the information sent by form.

http://techtest.great-site.net/check_product_filtered1.php?number=0

<?php
    session_start();
    include "connection.php";
    echo "<link href='/css/table.css' rel='stylesheet' type='text/css'>";


function droplist($filter) { //function
    include "connection.php";
    // communication with SQL
    $sql = "SELECT DISTINCT {$filter} FROM warehouse";
    $result = $conn -> query($sql);

    echo "<form action='{$_SERVER['PHP_SELF']}' method='GET'>";
    echo "<select name='{$filter}' id='{$filter}' onchange='this.form.submit()'>
          <option>{$filter}</option>";
        foreach ($conn -> query($sql) as $row) {
    echo "<option value=$row[$filter]>$row[$filter]</option>";
        }
    echo "</select>";
    echo "<noscript><input type='submit' value='submit'></noscript>";
    echo "</form";
    
    mysqli_close($conn);
} //function



 
    $_SESSION["location"] = $_GET["location"];
    $_SESSION["number"]   = $_GET["number"];



    $sql = "SELECT * FROM warehouse WHERE 1=1";
        if($_SESSION["location"] !="") {
            $sql .= " AND location='{$_SESSION['location']}'";
            echo $_SESSION["location"]; 
        }

        if($_SESSION["number"] !="") {
            $sql .= " AND number='{$_SESSION['number']}'";
            echo $_SESSION["number"];
        } 

    $result = $conn -> query($sql);
//Display data from MySql
if ($result->num_rows > 0) {              //the function num_rows() checks if there are more than zero rows returned.
                                          // output data of each row
?>
        <table class='table'>
            <tr id='header'>
                <th> <?php droplist("location"); ?> </th>
                <th> <?php droplist("number");   ?> </th>
                <th> <?php droplist("product");  ?> </th>
                <th> <?php droplist("packing");  ?> </th>
                <th>quantity</th>
                <th>net</th>
                <th>unit</th>
                <th>gross</th>
                <th>batch</th>
                <th>pallet</th>
                <th>height</th>
                <th>date</th>
            </tr>
<?php
   while($row = $result->fetch_assoc()) { //the function fetch_assoc() puts all the results into an associative array that we can loop through
                                          //The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

    echo "<tr id='tr'>";
        echo "<td>" .$row["location"]. "</td>";
        echo "<td>" .$row["number"]. "</td>";
        echo "<td>" .$row['product']. "</td>";
        echo "<td>" .$row['packing']. "</td>";
        echo "<td>" .$row['quantity']. "</td>";
        echo "<td>" .$row['net']. "</td>";
        echo "<td>" .$row['unit']. "</td>";
        echo "<td>" .$row['gross']. "</td>";
        echo "<td>" .$row['batch']. "</td>";
        echo "<td>" .$row['pallet']. "</td>";
        echo "<td>" .$row['height']. "</td>";
            $date=date_create($row['date']);
        echo "<td>" .date_format($date,'M Y'). "</td>";
    echo "</tr>";

   }
echo "</table>";

} else {
  echo "none results";
}
 $conn->close();


?>

[phdr]

The most immediate problem is because you have a separate form for each select/option menu and you have no validation logic for each input. When any one of the forms is submitted, only the value from that form is set and by unconditionally copying the input values to session variables you are clearing all the session variables except the one for the current form.

Rather than write more logic to get the current arrangement to work, simply use a single form, with all the select/option menus inside of it, and eliminate the need for the session variables.

Also, you need to use a data driven design, where you have a data structure (array) that defines what general purpose code will do, so that you are not writing out repeated logic for each input.

See the following example code -

<?php
session_start();

// define an array of fields. this will be looped over to dynamically build the markup in the html document and when building the sql query statement
$fields = [];
$fields['location'] = ['label'=>'Location','filter'=>true];
$fields['number'] = ['label'=>'Number','filter'=>true];
$fields['product'] = ['label'=>'Product','filter'=>true];
$fields['packing'] = ['label'=>'Packing','filter'=>true];
$fields['quantity'] = ['label'=>'Quantity'];
$fields['net'] = ['label'=>'Net'];
$fields['unit'] = ['label'=>'Unit'];
$fields['gross'] = ['label'=>'Gross'];
$fields['batch'] = ['label'=>'Batch'];
$fields['pallet'] = ['label'=>'Pallet'];
$fields['height'] = ['label'=>'Height'];
$fields['date'] = ['label'=>'Date'];

// pass the (single) database connection into the function as a call-time parameter
function droplist($filter,$pdo) { //function
	// note: because the column name can be anything at this point, such as 'number', which is a reserved word (MySql), add back-ticks to prevent breaking the sql syntax
	// almost every SELECT query needs an ORDER BY term in it so that the result is in a specific order
	$sql = "SELECT DISTINCT `$filter` FROM warehouse ORDER BY `$filter`";
	$stmt = $pdo->query($sql);

	// you need a single form with all the form fields in it, not a separate form for each field
	// the 1st choice needs an empty value so that you can distinguish it from an actual selected value
	echo "<select name='$filter' onchange='this.form.submit()'>
	<option value=''>$filter</option>
	";
	foreach($stmt as $row)
	{
		// only output an option if the value is not null
		if(!is_null($row[$filter]))
		{
			// the currently selected choice, if any, needs to be 'sticky'
			$sel = isset($_GET[$filter]) && (trim($_GET[$filter]) !== '') && (trim($_GET[$filter]) == $row[$filter]) ? ' selected' : '';
			// the value needs to be quoted to prevent broken html
			echo "<option value='$row[$filter]'$sel>$row[$filter]</option>
			";
		}
	}
	echo "</select>
	";
	echo "<noscript><input type='submit' value='submit'></noscript>
	";
} //function


// only create ONE database connection on a page
require "connection.php";

// produce the data needed to display the page

// dynamically build the WHERE terms by looping over the $fields array
// you also need to use a prepared query at this point to prevent sql special characters in the values from breaking the sql query syntax (which is how sql injection is accomplished)
// you would also want to switch to the much simpler PDO extension, which is used in this example code
$where_terms = [];
$params = [];
$cols = [];

foreach($fields as $field=>$arr)
{
	$cols[] = "`$field`";
	
	// is this field a filter
	if($arr['filter'] ?? false)
	{
		// get the input value
		$val = isset($_GET[$field]) ? trim($_GET[$field]) : '';
		if($val !== '')
		{
			// a true value, add to query
			$where_terms[] = "`$field` = ?";
			$params[] = $val;
		}
	}
}

// build the WHERE ... syntax
$where = !empty($where_terms) ? 'WHERE ' . implode(' AND ',$where_terms) : '';

// list the columns you are selecting
$sql = "SELECT ".implode(',',$cols)." FROM warehouse $where";

// examine the query/params
echo $sql; echo '<pre>'; print_r($params); echo '</pre>';

// the following uses the PDO extension
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$result_data = $stmt->fetchAll();

// start the html document here...
// not sure where your html document markup is. only the css link is shown in the posted code
?>

<link href='/css/table.css' rel='stylesheet' type='text/css'>

<form>
<table class='table'>
<tr id='header'>
<?php
foreach($fields as $field=>$arr)
{
	echo "<th>{$arr['label']}";
	// is this field a filter
	if($arr['filter'] ?? false)
	{
		echo "<br>"; droplist($field,$pdo);
	}
	echo "</th>\n";
}
?>
</tr>
<?php
//Display data from MySql
if(!$result_data)
{
	// because the code for the fail case is much shorter than the success code, invert the condition being tested and put the failure code first
	echo "<tr><td colspan='12'>No data to display.</td></tr>";
} else {
	// output data of each row
	foreach($result_data as $row) {
		// ids must be unique, i.e. the id='tr' and if you are not using the id, don't output it at all
		// since the id that was in the following <tr ...> was probably for styling, you should use a css class selector instead
		echo "<tr>";
		echo "<td>" .$row["location"]. "</td>";
		echo "<td>" .$row["number"]. "</td>";
		echo "<td>" .$row['product']. "</td>";
		echo "<td>" .$row['packing']. "</td>";
		echo "<td>" .$row['quantity']. "</td>";
		echo "<td>" .$row['net']. "</td>";
		echo "<td>" .$row['unit']. "</td>";
		echo "<td>" .$row['gross']. "</td>";
		echo "<td>" .$row['batch']. "</td>";
		echo "<td>" .$row['pallet']. "</td>";
		echo "<td>" .$row['height']. "</td>";
		$date=date_create($row['date']);
		echo "<td>" .date_format($date,'M Y'). "</td>";
		echo "</tr>
		";
	}
}
?>
</table>
</form>
<?php
// finish the html document here...