Select mysql database table strings and order by dates

[Anonymous]

I have a database.
The table saves various information about the sale of products.
I need to see under every purchase date the corresponding product (-producti id - product type - price)


nrcrt | productid | product_type | purchase_date | prices
............................................................................
1 | 35 | 101 | 2020-08-24 | 128
2 | 35 | 202 | 2020-08-24 | 150
3 | 35 | 1001 | 2020-08-24 | 224
4 | 35 | 101 | 2020-09-29 | 130
5 | 35 | 202 | 2020-09-29 | 160
6 | 35 | 1001 | 2020-09-29 | 240
............................................................................

I used this query:


$productid='35';

$query_prices = $mysqli->query("SELECT * FROM purchases WHERE productid='$productid' GROUP BY purchase_date, product_type ORDER BY product_type ASC");

$query_prices_date = $mysqli->query("SELECT purchase_date FROM purchases WHERE productid='$productid' GROUP BY purchase_date ORDER BY purchase_date DESC");

while($row_prices = $query_prices->fetch_array()) {

$x=0; while ($x<1) { $row_tar_date=mysqli_fetch_array($query_prices_date,MYSQLI_ASSOC);
if (isset($row_prices_date["purchase_date"])) {
echo '<h6>Purchase date: '.$row_prices_date["purchase_date"].'</h6>'; } else { echo ''; }
$x++; }

echo '<h6>- '.$row_prices["productid"].' - '.$row_prices["product_type"].' - '.$row_prices["prices"].'</h6>';

}


The result was the display of products after the first date from the database

Purchase date: 2020-09-29
- 35 - 101 - 130
Purchase date: 2020-08-24
- 35 - 101 - 128
- 35 - 202 - 150
- 35 - 202 - 160
- 35 -1001 - 224
- 35 -1001 - 240

The result should be as follows:

Purchase date: 2020-08-24
- 35 - 101 - 128
- 35 - 202 - 150
- 35 -1001 - 224

Purchase date: 2020-09-29
- 35 - 101 - 130
- 35 - 202 - 160
- 35 -1001 - 240

 

[Anonymous]

You only need to do one query, to get all purchases ordered by date then product type. You can then track which purchase date you're looking at as you loop through the results.

$productid = 35;

$all_purchases = $mysqli->prepare(
    'SELECT purchase_date, productid, product_type, prices
     FROM purchases
     WHERE productid = ?
     order by purchase_date DESC, product_type ASC'
);

$all_purchases->bind_param('i', $productid);

$all_purchases->execute();

$res = $all_purchases->get_result();

$current_date = null;
while ($row = $res->fetch_assoc()) {
    if ($row['purchase_date'] != $current_date) {
        $current_date = $row['purchase_date'];
        echo '<h6>Purchase date: ' . $current_date . '</h6>';
    }

    echo '<h6>- ' . $row["productid"] . ' - ' . $row["product_type"] . ' - ' . $row["prices"] . '</h6>';
}

In addition to only using one query, I've also set the code up to use a prepared statement. You should get used to doing this as it makes your code far more secure. You might also want to look at using PDO instead of mysqli; your code will be slightly clearer:

$productid = 35;

$all_purchases = $db->prepare(
    'SELECT purchase_date, productid, product_type, prices
     FROM purchases
     WHERE productid = ?
     order by purchase_date DESC, product_type ASC'
);

$all_purchases->execute([35]);

$current_date = null;
foreach ($all_purchases as $row) {
    if ($row['purchase_date'] != $current_date) {
        $current_date = $row['purchase_date'];
        echo '<h6>Purchase date: ' . $current_date . '</h6>';
    }

    echo '<h6>- ' . $row["productid"] . ' - ' . $row["product_type"] . ' - ' . $row["prices"] . '</h6>';
}