PHP, MySQL and Associative Array Problems...?

Question

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?

Have you tried using `mysqli_fetch_array($result)` instead of `mysqli_fetch_array($result, MYSQLI_ASSOC)`? — Irvin, Nov 22 '16 at 08:22
A benefit of mysqli is that it allows prepared statements! See SQL injection. — Strawberry, Nov 22 '16 at 08:24
`while($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) { if(!empty($row) && $count < 10) { //Your Code Here }}` — RJParikh, Nov 22 '16 at 08:28
@G.Mansour `...LIKE '"."%".$q."%"."'"` is the same as `...LIKE '%".$q."%'"`, it's just unnecessary stringconcation. — Qirel, Nov 22 '16 at 08:30

score 1 · Accepted Answer · edited May 23 '17 at 10:33

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

score 1 · Answer 2 · answered Nov 22 '16 at 09:10

Please try again...    

<!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)) {
            if($count >=10)
                break;
            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>

PHP, MySQL and Associative Array Problems...?

2 Answers2

The easier approach