0

I'm trying to build a demo website showcasing the food and/or products in your home ie. fridge or freezer.

I've got most of it down, dynamically creating a list with the items, but I would like to have the duplicates count up like 1x [product] instead of just appearing multiple times in the list like this: current result.

My query and stuff looks like this - all inside a for loop.

$sql = "SELECT *  FROM goods WHERE person_id = " . $_SESSION['ID'] . " AND room LIKE '" . $rooms[$count] . "'";

if ($result = mysqli_query($conn, $sql)){
    if (mysqli_num_rows($result) > 0){

        $output = "<ul>";

        while ($row = mysqli_fetch_array($result)){

            $output .= "<li class='varer'> " . $row['name'] . "</li>";

        }
        $output .= "</ul>";
    }
}

Hope you can help - I'm not that experienced with PHP :)

Community
  • 1
  • 1
Jakrox
  • 11
  • 2
  • 1
    Use `GROUP BY` and `COUNT(*)` – Barmar May 19 '20 at 20:47
  • 2
    Your code is vulnerable to **sql injection** so use **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk May 19 '20 at 20:48

2 Answers2

0

Check the following code where I show you how to have a group by to have the quantity per item and also, how to use query with prepared statements to avoid sql injection:

// prepare and bind
$stmt = $conn->prepare("SELECT COUNT(*) AS QUANTITY, * FROM goods WHERE person_id = ? AND room LIKE ? BROUP BY name");
$stmt->bind_param("ii", $_SESSION['ID'], $rooms[$count]);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result

if ($result) {
    $output = "<ul>";

    while ($row = $stmt->fetch_assoc()) {        
            $output .= "<li class='varer'> " . $row['QUANTITY'] . "x " . $row['name'] . "</li>";
    }

    $output .= "</ul>";
}
Hamza Makraz
  • 88
  • 2
  • 8
0

(using prepared statements)

$sql = "SELECT room,count(*) FROM goods WHERE person_id = ? AND room LIKE ? ";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $_SESSION['ID'], $rooms[$count]);
$stmt->execute();
result = $stmt->get_result();


if( $result->num_rows > 0 ){
    $output = "<ul>";
    while ($row = $result->fetch_assoc()){
        $output .= "<li class='varer'> " . $row['name'] . "</li>";
    }
    $output .= "</ul>";
}
$stmt->close();