filter data using checkbox php mysqli

[ianhaney]

Hi

 

I am trying to filter data pulled in from the database but can't seem to get it working, I want it to show filtered product titles when I check a checkbox, I am using php, mysqli and ajax

 

I am not getting any errors displaying, below is my coding

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="//code.jquery.com/jquery-1.11.3.min.js"></script>

<?php
    $db=new mysqli('localhost','dbuser','dbpassword','dbname');
    $all_row=$db->query("SELECT * FROM tablename");
?>

<form id="search_form">
    <div class="well">
    <h4 class="text-info">Search by Size</h4>
    <input value="32" class="sort_rang" name="software_title[]"  type="checkbox"> Microsoft Office Professional Plus 2010
    <input value="36" class="sort_rang" name="software_title[]"  type="checkbox"> Microsoft Office Professional 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">Software Title : 
<?php echo $software_title['software_title']; ?></span></p>                      
<hr>
</div>
</div>        
<?php } ?>
<?php endif; ?>
</div>

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

Below is my ajax-search.php coding

<?php
    $db=new mysqli('localhost','dbuser','dbpassword','dbname');
    $sql="SELECT * FROM dbtablename";
    extract($_POST);
    if(isset($size)) 
        $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">Software Title :
             <?php echo $software_title['software_title']; ?></span></p>         
            <hr>
        </div>
    </div>
   <?php } ?>
<?php endif; ?> 

Can anyone help me please?

 

Thank you in advance

Edited September 23, 2016 by ianhaney

[ianhaney]

I found out it was to do with the IN line in ajax-search.php file so changed it to the following but is still not working

$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

[ginerjm]

Have you actually echo'ed out the query statement before running it so you can see what is being built?

[ianhaney]

Sorry just done that and is returning the following

 

SELECT * FROM product_keys_sold

[cyberRobot]

It looks like the second part of the query isn't being added. Where does $size come from in you if statement?

if(isset($size))
    $sql.=" WHERE software_title IN (".implode(',', $software_title).")";

 

If $size isn't set, the $sql line below it will not execute.

[ginerjm]

Sorry just done that and is returning the following

 

SELECT * FROM product_keys_sold

As cyberRobot is pointing out, you have some debugging to do.

[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 = 'password';

$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

[Barand]

Perhaps you could get rid of the error the same way as you did when you previously had a problem like that earlier this year.

 

https://forums.phpfreaks.com/topic/300771-from-to-date-search-form-php-mysqli/?do=findComment&comment=1530935

 

Do we have to keep helping you repeatedly with the same type of error?

[ginerjm]

Looks like you don't know how to program yet.

 

When you execute a script it runs from top to bottom, skipping functions until they are actually called from the rest of the main code. In your posted code you are (badly) echo'ing out some html and then executing some PHP code that is not going to run error-free.

 

1 - put your html at the END so that your php code gets to do what it needs to do first.

2 - LOOK AT YOUR CODE!!! You have a line that looks for a POST array element. Well? Did you do the submit yet to generate that POST array? So far you have done some output but where did you give the user a chance to enter something into that form? He HASN'T even seen it yet!!!

3 - Before you try to process incoming values make sure they have been sent first. Check if your submit button has triggered this script or if the REQUEST_METHOD has been set to POST and you actually have some of your input fields. You are simply referencing it without knowing where you are in the process.

 

4 - begin your script with a check to see if you have to process a submit or if you have to simply send out the entry form and wait for the submit to happen. Then let the user do his thing and hit the submit button. When your script executes again it will go thru this same code and see what to do then.