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.

[styxlawyer]

You need to debug your final query. Unless you have changed anything since your first post, the query string appears to be built with these two lines of code:



$sql="SELECT * FROM ";
.
.
.
$sql .= " WHERE software_title LIKE '%software_title%' ";


Which will result in the following query:



SELECT * FROM WHERE software_title LIKE '%software_title%'


That line has two obvious errors. Firstly there is no table-name in the query and secondly you are searching for any string which contains the characters software-title within it.

You need to add a table-name in the first line and rewrite the second line so that the value contained in the PHP variable $software_title is used in place of the string software_title.

[ianhaney]

Sorry I have gone a different way with it and using a text input field instead rather than text boxes but got one little issue, each time I load the page I get this notice

Notice: Undefined index: search in /home/broadway/public_html/admin/product-keys-sold/search-by-software-title.php on line 53

but as soon as I do a search, the notice disappears, below is the new coding I have



<form action="" method="post">
<input type="text" name="search">
<input type="submit" name="submit" value="Search">
</form>

<?php

$servername = 'localhost';
$username = 'dbuser';
$password = 'dbpassword';
$dbname = 'dbtable';

$search_value= $_POST['search'];

$con=new mysqli($servername,$username,$password,$dbname);

if($con->connect_error){
echo 'Connection Faild: '.$con->connect_error;

}else{
$sql="select id, software_title, customers_email, DATE_FORMAT(date_purchased, '%d/%m/%Y') AS date_purchased, sent from product_keys_sold where software_title LIKE '%$search_value%' ORDER BY id";

$res=$con->query($sql);

// display records in a table
echo "<table class='records'>";

// 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=$res->fetch_assoc()){
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>';

}

?>


the form works perfect, just need to get rid of that notice, I know I have error reporting on and I know the notice will prob not show when I remove the error reporting but will be nice to get the coding right first

[styxlawyer]

The code you have provided in your last post only contains 54 lines and line 53 is empty so the error must be elsewhere on your page.

[ianhaney]

The code you have provided in your last post only contains 54 lines and line 53 is empty so the error must be elsewhere on your page.

Sorry might be because I not post all the other coding as was HTML and not relevant, in my php file line 53 is the following


$search_value= $_POST['search'];

[ianhaney]

Sorry have solved the issue now

[ianhaney]

Sorry found another issue, the search form is returning fields that does not even have the text in the database record so really confused why the results are being displayed that does not even have the word in

below is the whole code I have



<form action="" method="post">
<input type="text" name="search">
<input type="submit" name="submit" value="Search">
</form>

<?php

$servername = 'localhost';
$username = '';
$password = '';
$dbname = '';

$search_value = (isset($_POST['search']));

$con=new mysqli($servername,$username,$password,$dbname);

if($con->connect_error){
echo 'Connection Failed: '.$con->connect_error;

}else{
$sql="select id, software_title, customers_email, DATE_FORMAT(date_purchased, '%d/%m/%Y') AS date_purchased, sent from product_keys_sold where software_title LIKE '%$search_value%' ORDER BY id";

$res=$con->query($sql);

// display records in a table
echo "<table class='records'>";

// 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=$res->fetch_assoc()){
echo "<tr>";
echo "<td><a href='view-specific-product-key-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>';

}

?>


for example I type windows in the search input field and the database records that get returned does not even have the word windows in?

UPDATE:
I think it is to do with the following line cause even if I click search, the last record disappears and only shows the first two records in the db table



$search_value = (isset($_POST['search']));