'Data duplicated in table filled with PHP

i am completely new to this world and i am trying to get more confident with PHP and MYSQL, so i am playing with a small web application just to fetch and retrieve data with MYSql and PHP. I created a table in HTML and my goal is to retrieve this data from a mysql table with PHP The problem is that the data are displayed twice... Can you help me understand where is error ? Below the code :

<?php
session_start();
include_once("database.php");
$db = $conn;
$query = " SELECT categoria FROM categoria_prodotto";
$result = $db->query($query);
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <link rel="stylesheet" href="styles.css">
    <title>test</title>
</head>

<body>
    <section id="main-page">
        <div class="link">
            <span> Create a new purchase table </span>
        </div>

        <table>
            <tr>
                <td> category </td>
                <td> product </td>
            </tr>


            <?php while ($row = mysqli_fetch_array($result)) :
                foreach ($row as $temp) {
                    $query1 = "SELECT `nome` FROM `supermarket`.`lista_prodotto` WHERE `categoria_prodotto` = '$temp' ORDER BY `categoria_prodotto` DESC";
                    $result1 = $db->query($query1);
            ?>
                    <tr>
                        <td><?php echo $row[0]; ?> </td>


                        <td>
                            <?php while ($row1 = mysqli_fetch_array($result1)) :
                                echo $row1[0]; ?>

                    <?php endwhile;
                        }
                    endwhile; ?>
                        </td>
                    </tr>



        </table>



        <div class="link">
            <a href="new_product.php"> <span> Store a new product </span> </a>
        </div>




    </section>
</body>


</html>

////

And here the result in browser with two row with same data duplicated each time

[here][1]

Thank you in advance for helping me to troubleshoot my problem :)


  [1]: https://i.stack.imgur.com/XEBsi.jpg
phpmysqlhtml-table


Solution 1:[1]

You have some unnecessary loops, also if you use the object oriented version of the MySQLI API its a lot easier to read. I also change the query to use a Prepared parameterised query that you bind data to before the executing the query, much safer and protects you against SQL Injection

<?php
session_start();
include_once("database.php");
$db= $conn;
$query = "SELECT categoria FROM categoria_prodotto";
$result = $db->query($query);
?>
    
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <link rel="stylesheet" href="styles.css">
    <title>test</title>
</head>
<body>
    <section id="main-page">
        <div class="link">
        <span> Create a new purchase table </span>
        </div>
   
        <table>
        <tr>
            <td> category </td> 
            <td> product </td>
        </tr>

<?php 
while($categoria_prodotto = $result->fetch_assoc()):
    $query1 = "SELECT `nome` 
               FROM `supermarket`.`lista_prodotto` 
               WHERE `categoria_prodotto` = ?
               ORDER BY `categoria_prodotto` DESC";
    $stmt1 = $db->prepare($query1);
    $stmt1->bind_param('s', $categoria_prodotto['categoria']);
    $stmt->execute();

    $result1= $stmt->get_result();
    $supermarket = $result1->fetch_assoc();
?>
        <tr>
            <td><?php echo $categoria_prodotto['categoria']; ?></td>
            <td><?php echo $supermarket['nome']; ?></td>
?>
<?php 
endwhile;
?>
            </td>
            </tr>
    </table>
</section>  
</body>
</html>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RiggsFolly

Related Questions