filter data using checkbox php and mysqli

[ianhaney]

Hi

I need some help, I am trying to filter data using checkboxes, php and mysqli but can't seem to get it working

I followed a guide I found on Google but don;t seem to work, below is the code I have

index.php coding

<p>
<form id="search_form">
    <div class="well">
    <h4 class="text-info">Search by Size</h4>
    <input value="Microsoft Office Pro Plus 2010" class="sort_rang" name="software_title[]"  type="checkbox"> Microsoft Office Pro Plus 2010
    <input value="Microsoft Office Pro Plus 2013" class="sort_rang" name="software_title[]"  type="checkbox"> Microsoft Office Pro Plus 2013
    </div>
    </form>
</p>

<div class="ajax_result">
<?php if(isset($all_row) && is_object($all_row) && count($all_row)): $i=1;?>
<?php foreach ($all_row as $key => $software_title) { ?>       
<div class="col-sm-3 col-md-3">
<div class="well">
<h2 class="text-info"><?php echo $software_title['software_title']; ?></h2>
<p><span class="label label-info">Size : 
<?php echo $software_title['software_title']; ?></span></p>
</div>
</div>        
<?php } ?>
<?php endif; ?>

<?php
// connect to the database
include('connect-db.php');

    $db=new mysqli('localhost','','','');
    $all_row=$db->query("SELECT * FROM ");

// get the records from the database
if ($result = $mysqli->query("SELECT id, software_title, customers_email, DATE_FORMAT(date_purchased, '%d/%m/%Y') AS date_purchased, sent FROM  ORDER BY id"))
{
// display records if there are records to display
if ($result->num_rows > 0)
{
// display records in a table
echo "<table class='records' id='software'>";

// set table headers
echo "<tr>
<th>ID</th>
<th>Software Title</th>
<th>Customers Email</th>
<th>Date Purchased</th>
<th>Sent</th>
<th colspan='2'>Actions</th>
</tr>";

while ($row = $result->fetch_object())
{
// set up a row for each record
echo "<tr>";
echo "<td><a href='view-specific-product-keys-sold.php?id=" . $row->id . "'>".$row->id . "</a></td>";
echo "<td>" . $row->software_title . "</td>";
echo "<td>" . $row->customers_email . "</td>";
echo "<td>" . $row->date_purchased . "</td>";
echo "<td>" . $row->sent . "</td>";
echo "<td><a href='add-update-keys-sold.php?id=" . $row->id . "'>Edit</a></td>";
echo "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>";
echo "</tr>";
}

echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
echo "No results to display!";
}
}
// show an error if there is an issue with the database query
else
{
echo "Error: " . $mysqli->error;
}

// close database connection
$mysqli->close();

?>

<a href="add-update-keys-sold.php">Add New Product Key Sold</a>

<script type="text/javascript">
$(document).on('change','.sort_rang',function(){
   var url = "search-filter-results.php";
   $.ajax({
     type: "POST",
     url: url,
     data: $("#search_form").serialize(),
     success: function(data)
     {                  
        $('.ajax_result').html(data);
     }               
   });
  return false;
});
</script>

search-filter-results.php coding

<?php
    $db=new mysqli('localhost','','','');
    $sql="SELECT * FROM ";
    extract($_POST);
    if(isset($software_title)) 
        $sql.=" WHERE software_title IN (".implode(',', $software_title).")";
    $all_row=$db->query($sql);
?>
<?php if(isset($all_row) && is_object($all_row) && count($all_row)): $i=1;?>
    <?php foreach ($all_row as $key => $software_title) { ?>       
    <div class="col-sm-3 col-md-3">
        <div class="well">
            <h2 class="text-info"><?php echo $software_title['software_title']; ?></h2>
            <p><span class="label label-info">Size :
             <?php echo $software_title['software_title']; ?></span></p>          
        </div>
    </div>
   <?php } ?>
<?php endif; ?>

I check the checkboxes on the index.php but nothing happens

Thank you in advance

[DyDr]

I have two questions -

1) What debugging have you done to find the problem yourself? What part of the code is working and exactly at what point is the code not working and what result occurs at that point rather than the expected result?

2) What does your database statement error handling, assuming you have any in place, report about the queries you are running, particularity the one in search-filter-result.php file?

[ianhaney]

Hi

Thank you for the reply, I have put error coding in but both php files do not display any errors

[DyDr]

The IN() part of the sql query in search-filter-result.php is incorrect for string values and would be returning an sql error.

[ianhaney]

The IN() part of the sql query in search-filter-result.php is incorrect for string values and would be returning an sql error.

Oh right, weird as is not producing any sql errors

what would I need to change the IN(0 part to as is text and not a INT value

[ianhaney]

I changed the IN line to the following in my search-filter-results.php but still does not work

$sql .= " WHERE " . implode(' AND ', $software_title);

[ianhaney]

Been playing round with it some more and changed the coding now to the following

$sql .= " WHERE software_title LIKE '%software_title%' ";

it works but it matches the text if is software title is in the words but need to be for what checkboxes I am selecting, hope that makes sense

[DyDr]

The IN() part of the sql query in search-filter-result.php is incorrect for string values and would be returning an sql error.

Each string value is surrounded by single-quotes, so that it's treated as a literal string, rather than a msyql keyword or identifier.

[ianhaney]

Each string value is surrounded by single-quotes, so that it's treated as a literal string, rather than a msyql keyword or identifier.

That's what I got now or am I still wrong

$sql .= " WHERE software_title LIKE '%software_title%' ";

[DyDr]

That query may have single-quotes where they are needed, but that's not the query format that does what you want.