Searching Values in Mysql Db by passing multiple values of same parameter

Question

I want to generate a report of certain criteria from my website, The website is an an web application used to save breakdown time and details of the machines in a company. Here i want to generate an report based on breakdown type, breakdown machine , break down attended by etc.,.

So i have created an search form to do so and i have used dynamic dropdown menu from my database and successfully created a code that will select multiple values as well like (Under Machine- we can select machine names like machine1,machine2 and so on) and after i press search it shows only the result for the last selected value but i want to show the results(details) of all the selected values.

**form.php**

<?php
//index.php
include('database_connection.php');
$country = '';
$query = "
SELECT type FROM reporttype
";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$country .= '<option value="'.$row["type"].'">'.$row["type"].'</option>';
}
?>
<div class="container">
<h2 align="center"></h2><br /><br />
<form method="post" id="insert_data" action="insert.php">
<label class="col-md-2 bdlabel">Break Down</label>
<select name="country" id="country" class="col-md-10 form-control action">
<option value="">Choose a Category</option>
<?php echo $country; ?>
</select>
<br />
<select name="city" id="city" multiple class="form-control">
</select>
<br />
<input type="hidden" name="hidden_city" id="hidden_city" />
<div class="form-group">
<div class="datepic">
<label class="col-md-2 bdlabel bdfrom">From</label>
<input type="date" name="bdfrom" class="col-md-4">
</div>
<div class="datepic1">
<label class="col-md-2 bdlabel bdto">To</label>
<input type="date" name="bdto" class="col-md-4">
</div>
</div>
<br/>
<input type="submit" name="search" id="action" class="btn btn-info" 
value="Search" />
</form>
</div>
<script>
// JavaScript Document
$(document).ready(function(){
$('#city').lwMultiSelect();
$('.action').change(function(){
if($(this).val() != '')
{
var action = $(this).attr("id");
var query = $(this).val();
var result = '';
if(action == 'country')
{
result = 'city';
}
$.ajax({
url:'fetch.php',
method:"POST",
data:{action:action, query:query},
success:function(data)
{
$('#'+result).html(data);
if(result == 'city')
{
$('#city').data('plugin_lwMultiSelect').updateList();
}
}
})
}
});
});
</script>

insert.php

<?php
//insert.php
include('db.php');
if(isset($_POST['search']))
$city=$_POST['city'];
$from=$_POST['bdfrom'];
$to=$_GET['bdto'];
{
$raw_results = mysql_query("SELECT * FROM breakdownentry
WHERE bdmwc in ('$city') || bdmname in ('$city') || bdaname in ('$city') ") 
or die(mysql_error());         
if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following         
while($results = mysql_fetch_array($raw_results)){             
echo "<p>".$results['bdmwc']."</p>";
}
}
else{ 
echo "No results";
}
}
?>

Actual Form

Output I got

Db table value

What i want to show is wire drawing and nail but only nail value is been shown.

Please indent your code properly. It's pretty unreadable as it is. — M. Eriksson, May 09 '18 at 12:56
After reformatting your code `echo $city` and show us what is in that variable — RiggsFolly, May 09 '18 at 13:00
Then put your query in a variable like `$sql` and echo that and see what is being sent to the database for execution — RiggsFolly, May 09 '18 at 13:01
Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, May 09 '18 at 13:04
Thanks @RiggsFolly yeah i was new to php and stackoverflow too, when i copy pasted my code it showed indend error and asked to leave four whitespaces before every line of code ... sorry for that. — B.NIRMAL KUMAR, May 09 '18 at 13:06

Olgert Elezi · Answer 1 · 2018-05-09T13:19:24.597

2

To get multiple values on the form you should add [] at the end of the name attribute

For your examples:

<select name="city[]" id="city" multiple class="form-control">

edited May 09 '18 at 13:19

answered May 09 '18 at 13:07

Olgert Elezi

19
3

**Good answers** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. So an example of the code to change would be a good idea – RiggsFolly May 09 '18 at 13:08
Notice: Undefined index: bdto in C:\xampp\htdocs\cmms\insert.php on line 7 Notice: Array to string conversion in C:\xampp\htdocs\cmms\insert.php on line 13 Notice: Array to string conversion in C:\xampp\htdocs\cmms\insert.php on line 13 Notice: Array to string conversion in C:\xampp\htdocs\cmms\insert.php on line 13 No results – B.NIRMAL KUMAR May 09 '18 at 13:26
Yes because you use post as requested method, just change $_GET['bdto'] to this $_POST['bdto'] on line 7 insert.php – Olgert Elezi May 09 '18 at 13:35

score 0 · Accepted Answer · answered May 12 '18 at 10:01

Finally found an answer , i have to use implode function to get the multiple values through array. After using implode function in the search query i was able to get the multiple values and got my desired results. By the by thanks @RiggsFolly and @OlgertElezi for your help ,the square braces in select tag helped to pass an array value .

I have attached my working code with this . Again Thanks evryone

<?php
                                    //insert.php
                                        include('db.php');
                                        if(isset($_POST['search'])) 
                                            $category=($_POST["country"]);                                  
                                            $machine=($_POST["city"]);
                                            $from=($_POST["bdfrom"]);
                                            $to=($_POST["bdto"]);

                                                $raw_results = mysql_query("SELECT * FROM breakdownentry WHERE ((bddate BETWEEN '$from' AND '$to')) && (bdmname IN ('".implode("','",$machine)."') || bdmwc IN ('".implode("','",$machine)."') || bdaname IN ('".implode("','",$machine)."')) ") or die(mysql_error());

                                                if(mysql_num_rows($raw_results) > 0){
                                    ?>
                                    <div class="widget-content2 resulttable">
                                        <label class="tableheadtb"><?php echo $category.' Break Down details';?></label>
                                        <div class="table-responsive">
                                            <table class="table table-bordered ">
                                                <?php

                                                    // if one or more rows are returned do following
                                                    $limit = 10;  
                                                    if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };  
                                                        $start_from = ($page-1) * $limit;  

                                                        $sql = "SELECT * FROM breakdownentry LIMIT $start_from, $limit";  
                                                        $rs_result = mysql_query ($sql);    
                                                        $a=$start_from + 0;
                                                        $efslno=$start_from + 1354;

                                                ?>

                                                 <thead>
                                                        <tr style="background-color:#FDFE90">
                                                        <th><strong>S.No</strong></th>
                                                        <th><strong>Complaint-Date</strong></th>
                                                        <th><strong>B/D S.No</strong></th>
                                                        <th><strong>Machine ID</strong></th>
                                                        <th><strong>Machine Name</strong></th>
                                                        <th><strong>Work Centre</strong></th>
                                                        <th><strong>B/D ID</strong></th>
                                                        <th><strong>Attended by(ID)</strong></th>
                                                        <th><strong>Attended by Name</strong></th>
                                                        <th><strong>B/D Type</strong></th>                                                  
                                                        <th><strong>B/D Time (Hours)</strong></th>
                                                        <th><strong>B/D Time (Minutes)</strong></th>
                                                        <th><strong>B/D Time (Total)</strong></th>
                                                        <th><strong>B/D Details</strong></th>
                                                        <th><strong>Work Done</strong></th>
                                                        <th><strong>Complaint Status</strong></th>                  
                                                    </tr>
                                                </thead>

                                                <?php   
                                                    while($results = mysql_fetch_array($raw_results)){ 

                                                ?>                                                                                              
                                                <tbody>

                                                    <tr>
                                                        <td><?php echo ++$a;?></td>
                                                        <td><?php echo $results['bddate'];?></td>
                                                        <td><?php 
                                                                $slno = ++$efslno;
                                                                echo $slno;?>
                                                        </td>
                                                        <td><?php echo $results['bdmid'];?></td>
                                                        <td><?php echo $results['bdmname'];?></td>
                                                        <td><?php echo $results['bdmwc'];?></td>
                                                        <td><?php echo $results['bdbdid'];?></td>
                                                        <td><?php echo $results['bdtid'];?></td>
                                                        <td><?php echo $results['bdtname'];?></td>
                                                        <td><?php echo $results['bdbdname'];?></td>                                                         
                                                        <td><?php 
                                                                $bdbreakoccur1=$results['bdbreakreport'];
                                                                $bdamotime1=$results['bdamotime'];
                                                                $time=mysql_fetch_array(mysql_query("SELECT (HOUR (TIMEDIFF ('$bdamotime1','$bdbreakoccur1')))"));                                                              
                                                                echo $time[0] ;
                                                            ?>
                                                        </td>
                                                        <td><?php 
                                                                $bdbreakoccur1=$results['bdbreakreport'];
                                                                $bdamotime1=$results['bdamotime'];
                                                                $time=mysql_fetch_array(mysql_query("SELECT (MINUTE (TIMEDIFF ('$bdamotime1','$bdbreakoccur1')))"));                                                                
                                                                echo $time[0] ;
                                                            ?>
                                                        </td>
                                                        <td><?php 
                                                                $bdbreakoccur1=$results['bdbreakreport'];
                                                                $bdamotime1=$results['bdamotime'];
                                                                $time=mysql_fetch_array(mysql_query("SELECT ((HOUR (TIMEDIFF ('$bdamotime1','$bdbreakoccur1'))*(60)) + (MINUTE (TIMEDIFF('$bdamotime1','$bdbreakoccur1'))))/60"));
                                                                echo $time[0] ;
                                                            ?>
                                                        </td>
                                                        <td><?php echo $results['bdacause'];?></td>                                                         
                                                        <td><?php echo $results['bdawdc'];?></td>
                                                        <td><?php echo $results['bdaradio'];?></td>
                                                    </tr>
                                                </tbody>
                                                <?php           
                                                    }
                                                ?>
                                            </table>
                                        </div>
                                        <?php  
                                            $sql = "SELECT COUNT(id) FROM breakdownentry";  
                                            $rs_result = mysql_query($sql);  
                                            $row = mysql_fetch_row($rs_result);  
                                            $total_records = $row[0];  
                                            $total_pages = ceil($total_records / $limit);  
                                            $pagLink = "<div class='widget-foot'>";  
                                            for ($i=1; $i<=$total_pages; $i++) {  
                                                $pagLink .= "<a href='break-down-list.php?page=".$i."'>".$i."</a>";  
                                                };  
                                            echo $pagLink . "</div>";
                                            }
                                            else{ // if there is no matching rows do following
                                                    echo "<p class='ep'>No Results to Show</p>";
                                            }  
                                        ?>
                                    </div>

Searching Values in Mysql Db by passing multiple values of same parameter

2 Answers2