Select mysql database table strings and order by dates

Codes here !

Moderators: egami, macek, gesf

Post Reply
mamaleone74
New php-forum User
New php-forum User
Posts: 1
Joined: Tue Oct 27, 2020 10:30 pm

Tue Oct 27, 2020 10:34 pm

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
simonbrahan
php-forum Fan User
php-forum Fan User
Posts: 137
Joined: Mon Jun 08, 2020 2:00 am
Contact:

Wed Oct 28, 2020 6:50 am

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.

Code: Select all

$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:

Code: Select all

$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>';
}
Post Reply