sweetname sweetname - 14 days ago 4
Ajax Question

PHP, MySQL and Associative Array Problems...?

I'm following along with the example from w3schools to extract some rows from a mysql db and return the results in a table format, but I'm only getting blank rows (although the correct number of them, oddly enough).

Here is what I have in my php script:

<!DOCTYPE html>
<html>
<head>
<style>
table
{
width: 100%;
border-collapse: collapse;
}
table, td, th
{
border: 1px solid black;
padding: 5px;
}
th
{
text-align: left;
}
</style>
</head>

<body>
<?php
$q = $_GET['q'];

$con = mysqli_connect('localhost', 'root', '123', 'drugsatfda');
if (!$con) {die('Could not connect: ' . mysqli_error($con));}

mysqli_select_db($con, "drugsatfda");
$sql="SELECT * FROM products WHERE DrugName LIKE '"."%".$q."%"."'";
$result = mysqli_query($con,$sql);

$count = 0;

echo "<table>
<tr>
<th>Application Number</th>
<th>Product Number</th>
<th>Form</th>
<th>Strength</th>
<th>Reference Drug</th>
<th>Drug Name</th>
<th>Active Ingredient</th>
</tr>";

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $count < 10) {
echo "<tr>";
echo "<td>" . $row['ApplNo'] . "</td>";
echo "<td>" . $row['ProductNo'] . "</td>";
echo "<td>" . $row['Form'] . "</td>";
echo "<td>" . $row['Strength'] . "</td>";
echo "<td>" . $row['ReferenceDrug'] . "</td>";
echo "<td>" . $row['DrugName'] . "</td>";
echo "<td>" . $row['ActiveIngredient'] . "</td>";
echo "</tr>";
$count++;
}

echo "</table>";

mysqli_close($con);
?>
</body>
</html>


Can someone please help me see where I'm going wrong?

Answer
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $count < 10) {

When doing this, you get two conditions, so $row = mysqli_fetch_array($result, MYSQLI_ASSOC) just becomes an expression returning a boolean true, so while $count is less than 10, you get while (true && true).

The solution is to break; when the loop reaches 10 instead.

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    if ($count >= 10) // If we reach 10 iterations, break the loop
        break;

    echo "<tr>";
    echo "<td>" . $row['ApplNo'] . "</td>";
    /* and so on */ 
    echo "</tr>";
    $count++;
}

You can verify this by doing

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $count < 10) {
    var_dump($row);
}

Which will output bool(true); for each iteration, until there are no more rows to fetch (when mysqli_fetch_array() returns null), or when $count is 10 or greater - whichever comes first.


The easier approach

An alternative, is simply just to fetch 10 rows. You can add a LIMIT clause to your SQL, like

$sql="SELECT * FROM products WHERE DrugName LIKE '%".$q."%' LIMIT 10";

This will fetch only 10 rows, meaning that you can just loop through it normally, without having to count

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    echo "<tr>";
    echo "<td>" . $row['ApplNo'] . "</td>";
    /* and so on */ 
    echo "</tr>";
}

It should also be noted that your code is currently wide open to SQL injections, and you should use prepared statements to guard yourself against this.

References

Comments