4 replies to this topic

[Blue Indian]

In the following code segment, the while loop must be buggy because when I search the database for a string like "breakfast blend" I only get back to records, but the while loop below prints out these two records like 10 times or so.

(scratching my head again)

Many, many thanks in advance!!!

<?php    
    session_start();
    
    if ($_SESSION['access'] != 'yes'){
            header('Location:login.php');
        }    

    if ($_POST['Search'] == 'search'){            

        $conn = mysqli_connect(//these parameters are not the problem, omitted for obvious reasons);
        
        $name = $_POST['name'];
        
        $sql = "
                select Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price,
                coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price,
                coffee_shop_name
                from coffee, coffee_shops, categories
                where coffee_category_id = category_id and coffee_name like '%$name%'
                ";
        
        $rs = mysqli_query($conn, $sql); 
        
        if (!$conn) {
            die('Connect Error (' . mysqli_connect_errno() . ') '
                . mysqli_connect_error());
        }
        else {
            echo 'Success... ' . mysqli_get_host_info($conn) . "<br/>";
        }
        
        echo "<table>
                <tr>
                    <td>CoffeeSKU</td><td>Coffee Name</td><td>category_name</td><td>coffee_details</td>
                    <td>coffee_price</td><td>coffee_roast</td><td>coffee_grind</td>
                    <td>coffee_picture</td><td>coffee_weight</td>
                </tr>";
        
        while($row = mysqli_fetch_array($rs)){
            echo "<tr>";
            echo "<td>" . $row['Coffee_SKU'] . "</td>";
            echo "<td>" . $row['Coffee_Name'] . "</td>";
            echo "<td>" . $row['category_name'] . "</td>";
            echo "<td>" . $row['coffee_details'] . "</td>";
            echo "<td>" . $row['coffee_price'] . "</td>";
            echo "<td>" . $row['coffee_roast'] . "</td>";
            echo "<td>" . $row['coffee_grind'] . "</td>";
            echo "<td>" . $row['coffee_picture'] . "</td>";
            echo "<td>" . $row['coffee_weight'] . "</td>";            
            echo "</tr>";
            
        }
        
        echo "</table>";
        
        mysqli_close($conn);
    }
    
?>

[Alexander]

*_fetch_array() will only return a numeric array, try mysqli_fetch_assoc() for your code to work and confirm only two records with mysqli_num_rows()

[Blue Indian]

Nullw0rm,

I tried you method, but I got the same results. For example, I searched for "starbucks breakfast blend" and it found the one table entry for starbucks breakfast blend but printed it out 15 times and num_rows() returned 15. I don't understand how this is possible.

[Orjan]

this is because you only have one connecting statement in your WHERE clause. you connect coffee and categories, but you don't connect your shops table, then it makes one row for each shop combined with all matches of the other where.

SELECT Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price,
         coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price, coffee_shop_name
FROM coffee
JOIN categories ON coffee_category_id = category_id
JOIN coffee_shops ON coffee_shop_id = coffee_shops_id  <-- or whatever connecting info you have here
WHERE coffee_name like '%$name%'

[Blue Indian]

Ah! I see what you mean. Thanks! All good now!