A code where every users who has already placed an order in my site gets 1 point whenever there's a new order by any user

[kunalphpdev]

hello folks,

I want to add(modify) a code to my already created php in way that whenever there's a new order by any user, every users who has already placed an order in my site gets 1 point.
I'm attaching the file below as well as the basic information about the database.

there's a table where all orders are stored named 'oders'

where we have 'client_id' and 'order id'

and for storing the users we have a table named 'clients'

thank you in advance 

newodr.txt

Edited December 20, 2022 by kunalphpdev

[Barand]

One post will suffice.

[Barand]

You don't need to change anything.

Just run a query when you need to knw each client's points. The query needs to count thenumber of orders placed after each client's first order and give them a point for each order.

DATA

TABLE: client                   TABLE: orders
+-----------+---------+         +----------+-----------+------------+
| client_id | name    |         | order_id | client_id | order_date |
+-----------+---------+         +----------+-----------+------------+
|         1 | Dancer  |         |        1 |         5 | 2022-12-01 |
|         2 | Prancer |         |        2 |         5 | 2022-12-02 |
|         3 | Dasher  |         |        3 |         7 | 2022-12-03 |
|         4 | Vixen   |         |        4 |         3 | 2022-12-04 |
|         5 | Comet   |         |        5 |         6 | 2022-12-05 |
|         6 | Cupid   |         |        6 |         4 | 2022-12-06 |
|         7 | Donner  |         |        7 |         7 | 2022-12-07 |
|         8 | Blitzen |         |        8 |         2 | 2022-12-08 |
+-----------+---------+         |        9 |         4 | 2022-12-09 |
                                |       10 |         3 | 2022-12-10 |
                                |       11 |         3 | 2022-12-11 |
                                |       12 |         4 | 2022-12-12 |
                                |       13 |         7 | 2022-12-13 |
                                |       14 |         2 | 2022-12-14 |
                                |       15 |         6 | 2022-12-15 |
                                +----------+-----------+------------+

QUERY

SELECT cl.name
     , count(order_id) as points
FROM (
        SELECT c.name
             , c.client_id
             , min(o.order_date) as minord
        FROM client c
             LEFT JOIN 
             orders o USING (client_id)
        GROUP BY c.client_id
    ) cl
    LEFT JOIN
    orders o ON order_date > minord
GROUP BY cl.client_id;

+---------+--------+
| name    | points |
+---------+--------+
| Dancer  |      0 |
| Prancer |      7 |
| Dasher  |     11 |
| Vixen   |      9 |
| Comet   |     14 |
| Cupid   |     10 |
| Donner  |     12 |
| Blitzen |      0 |
+---------+--------+

 

[kunalphpdev]

I didn't understand the query, can you please explain the code little bit.

note : i have to show the points on the user dashboard 

 

[kunalphpdev]

here are some screenshot of 'clients' and 'orders' table

I wanna store the points at dpoints column

 

 

previously i tried this code but the dpoints columns isn't updating 

$stmt = $conn->prepare('SELECT client_id FROM orders');

$stmt->execute();

$users_with_orders = $stmt->fetchAll(PDO::FETCH_COLUMN);

// Add one point to the rewards points for each user who has placed an order

foreach ($users_with_orders as $client_id) {

$stmt = $conn->prepare('UPDATE clientss SET dpoints = dpoints + 1 WHERE id = :client_id');

$stmt->bindParam(':client_id', $client_id);

$stmt->execute();

}

 

 

Edited December 21, 2022 by kunalphpdev
added new info

[Barand]

You should not store derived data (such as totals) in a database. You should get these values by using a query. Otherwise you are storing the data twice (as a total and as the data from which the total is derived) giving the risk of there being two versions of the "truth" should the two get out of synch for any reason.

4 hours ago, kunalphpdev said:

can you please explain the code little bit

The subquery...

(
		SELECT c.name 
             , c.client_id 
			 , min(o.order_date) as minord 
		FROM client c 
             LEFT JOIN  
             orders o USING (client_id) 
		GROUP BY c.client_id 
	) cl

effectively creates a temporary table called "cl" which looks like this, containing the the client and date of their first order...

TEMP TABLE: cl
+---------+-----------+------------+
| name    | client_id | minord     |
+---------+-----------+------------+
| Dancer  |         1 | NULL       |
| Prancer |         2 | 2022-12-08 |
| Dasher  |         3 | 2022-12-04 |
| Vixen   |         4 | 2022-12-06 |
| Comet   |         5 | 2022-12-01 |
| Cupid   |         6 | 2022-12-05 |
| Donner  |         7 | 2022-12-03 |
| Blitzen |         8 | NULL       |
+---------+-----------+------------+

Now the query simplifies to this...

SELECT cl.name 
     , count(order_id) as points 
FROM cl                                 -- our temporary table
    LEFT JOIN 
    orders o ON order_date > minord 
GROUP BY cl.client_id;

which counts, for each client, the number of orders placed since they placed their first order. The LEFT JOIN is to list all clients, even if they have no points.