Fetching multiple rows from Database using php

Question

I am building a bit of a filter function that fetches items meeting certain criteria. I'm using AJAX, and a bit of basic php, I search for items which match a certain ID. My problem is, as soon as there are rows that have duplicate id's, nothing is displayed. I want all rows with the matching ID to be displayed. I cannot seem to see a duplicate of this question, however if there is a solution out there I would be most grateful. I have presented my code below:

My filter.php:

<!DOCTYPE html>
<html>
<head>

</head>
<body>

<?php
include('dbconnect.php');

$q = intval($_GET['q']);

mysqli_select_db($conn,"database");
$search="SELECT * FROM table WHERE id = '".$q."'";
$result = mysqli_query($conn,$search);

echo "<table>
<tr>
<th>Merchant</th>
<th>Product</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['merchant'] . "</td>";
    echo "<td>" . $row['product'] . "</td>";
    echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>
</body>
</html>

And this is the front side of things:

    <div>
        <ul>
            <li><a href="#" class="header">Option 1</a></li>
            <li onclick="showProduct(this.value)" value="1"><a>Product1a</a></li>
            <li onclick="showProduct(this.value)" value="2"><a>Product 2a</a></li>
            <li><a href="#" class="header">Option 2</a></li>
            <li onclick="showProduct(this.value)" value="1"><a>Product 1b</a></li>
            <li onclick="showProduct(this.value)" value="2"><a>Product 2b</a></li>

        </ul>
    </div>

    <!--SEARCH OUTPUT-->
    <div id="products">></div>

Which is supported by the following script:

<script>
    function showProduct(str) {
        if (str=="") {
            document.getElementById("products").innerHTML="";
            return;
        } 
        if (window.XMLHttpRequest) {
        // code for IE7+, Firefox, Chrome, Opera, Safari
         xmlhttp=new XMLHttpRequest();
        } else { // code for IE6, IE5
            xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
            }
         xmlhttp.onreadystatechange=function() {
         if (this.readyState==4 && this.status==200) {
        document.getElementById("products").innerHTML=this.responseText;
        }
    }
    xmlhttp.open("GET","filter.php?q="+str,true);
    xmlhttp.send();
}

</script>

score 0 · Answer 1 · answered Jun 19 '18 at 15:46

If your id field in the database is an integer field, then your query should be

$search="SELECT * FROM table WHERE id = ".$q." ";

I removed the single quotes around the id variable.

Try running the query in phpmyadmin or console and see what it returns.

score 0 · Answer 2 · answered Jun 19 '18 at 15:52

Try:

1) Display errors:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

from: How do I get PHP errors to display?

2) Echo the query and try it directly in phpmyadmin to see if you get an error.

echo $search;

Fetching multiple rows from Database using php

2 Answers2