Jump to content

New SQL creation problem


Erwin007
Go to solution Solved by kicken,

Recommended Posts

And there I was thinking I tackled the most difficult one....wrong!
There is another sales- table in the same DB (sales_larimar) and I need the sales per rep per touroperator also.
I tried this: (in red what I added to the existing query)

SELECT reps.rep_name, p41Sales.totalrum, allSales.total, larimarSales.totallarimar
FROM reps 
INNER JOIN (
        SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
        FROM `sales`
       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
       WHERE (sales.sales_date BETWEEN '$from' AND '$to')
       GROUP BY reps.rep_id
 )
                                                
INNER JOIN (
        SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS total
        FROM `sales_larimar`
       JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
       WHERE (sales_larimar.sales_date BETWEEN '$from' AND '$to')
       GROUP BY reps.rep_id
 )
                                                
 larimarSales ON larimarSales.rep_id = reps.rep_id

                                                
 allSales ON allSales.rep_id = reps.rep_id
                                                
 LEFT JOIN (
         SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
         FROM `sales`
        JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
        WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41'
        GROUP BY reps.rep_name
  )
p41Sales ON p41Sales.rep_id = reps.rep_id";

Attached the table sales_larimar.

I hope you can solve this because I have no idea, this is "heavy" stuff.

sales_larimar.sql.txt

Link to comment
Share on other sites

You put your join in the wrong place.  You put your new join in between the an existing join's table name and it's join condition, which is not valid.  You need to add your join either to the end of your query or between the existing joins (after one join's conditions, before the next join).

 

Link to comment
Share on other sites

Tried like you wrote:
 SELECT reps.rep_name, p41Sales.totalrum, allSales.total, larimarSales.totallarimar
FROM reps 
                                                
INNER JOIN (
        SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
        FROM `sales`
       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
       WHERE (sales.sales_date BETWEEN '$from' AND '$to')
       GROUP BY reps.rep_id
)
                                                
allSales ON allSales.rep_id = reps.rep_id
                                                
LEFT JOIN (
       SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
       FROM `sales`
      JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
      JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
      WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41'
      GROUP BY reps.rep_name
)
                                                
p41Sales ON p41Sales.rep_id = reps.rep_id
                                                
INNER JOIN (
      SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS total
      FROM `sales_larimar`
     JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
     WHERE (sales_larimar.sales_date BETWEEN '$from' AND '$to')
     GROUP BY reps.rep_id
 )

larimarSales ON larimarSales.rep_id = reps.rep_id";

Result was zero.


                                                                                            

Edited by Erwin007
Link to comment
Share on other sites

Does your new sub query return results for the range?  If not, you'll need to left join it for you to still get a result set. 

Your column names also need to match.  You're selecting larimarSales.totallarimar, but the sub-query defines the column as total.

 

Link to comment
Share on other sites

Again I celebrated too early, even in the query before this last one is an error.
SELECT reps.rep_name, p41Sales.totalrum, allSales.total
FROM reps 
     INNER JOIN (
     SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
     FROM `sales`
    JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    WHERE (sales.sales_date BETWEEN '$from' AND '$to')
    GROUP BY reps.rep_id
 )

 allSales ON allSales.rep_id = reps.rep_id

LEFT JOIN (
        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
        FROM `sales`
       JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
       WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41'
      GROUP BY reps.rep_name
  )
  p41Sales ON p41Sales.rep_id = reps.rep_id";

The problem is that the sales where there is sales of product_id 41 are skipped.
The argument AND salesdetails.salesdetails_productid = '41' is probably taking away the sales with all other than product_id 41.

Link to comment
Share on other sites

I don'tunderstand your latest problem. That posted query gives

mysql> SELECT reps.rep_name, p41Sales.totalrum, allSales.total
    -> FROM reps
    -> INNER JOIN
    ->      (
    ->      SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->      FROM `sales`
    ->      JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->      WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->      GROUP BY reps.rep_id
    ->      )
    ->      allSales ON allSales.rep_id = reps.rep_id
    -> LEFT JOIN
    ->       (
    ->       SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->       FROM `sales`
    ->       JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->       WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->       GROUP BY reps.rep_name
    ->       )
    ->       p41Sales ON p41Sales.rep_id = reps.rep_id;
+----------+----------+-------+
| rep_name | totalrum | total |
+----------+----------+-------+
| Cely     |       76 |   206 |
| Heike    |      192 |   502 |
+----------+----------+-------+

Those are the results you said you should get.

Link to comment
Share on other sites

1 hour ago, Barand said:

I don'tunderstand your latest problem. That posted query gives

mysql> SELECT reps.rep_name, p41Sales.totalrum, allSales.total
    -> FROM reps
    -> INNER JOIN
    ->      (
    ->      SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->      FROM `sales`
    ->      JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->      WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->      GROUP BY reps.rep_id
    ->      )
    ->      allSales ON allSales.rep_id = reps.rep_id
    -> LEFT JOIN
    ->       (
    ->       SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->       FROM `sales`
    ->       JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->       WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->       GROUP BY reps.rep_name
    ->       )
    ->       p41Sales ON p41Sales.rep_id = reps.rep_id;
+----------+----------+-------+
| rep_name | totalrum | total |
+----------+----------+-------+
| Cely     |       76 |   206 |
| Heike    |      192 |   502 |
+----------+----------+-------+

Those are the results you said you should get.

Yes, this is 100% correct, I am sorry.
The problem is in the last query these numbers were all wrong again.
I can't get this one to work:

SELECT reps.rep_name, p41Sales.totalrum, allSales.total, larimarSales.totallarimar
FROM reps 
                                                
 INNER JOIN (
       SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
       FROM `sales`
      JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
      WHERE (sales.sales_date BETWEEN '$from' AND '$to')
      GROUP BY reps.rep_id
  )
                                                
  allSales ON allSales.rep_id = reps.rep_id
                                                
  LEFT JOIN (
        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
        FROM `sales`
       JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
       WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41'
       GROUP BY reps.rep_name
 )
  p41Sales ON p41Sales.rep_id = reps.rep_id
                                                
  LEFT JOIN (
         SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar
         FROM `sales_larimar`
        JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
        WHERE (sales_larimar.sales_date BETWEEN '$from' AND '$to')
        GROUP BY reps.rep_id
   )
                                                                                            
   larimarSales ON larimarSales.rep_id = reps.rep_id";

 

 

Edited by Erwin007
Link to comment
Share on other sites

Changing to tour_operator_id = 5 (for consistency with othe queries, plus my reps table has no reps with "12") gives

mysql> SELECT reps.rep_name
    ->      , COALESCE(p41Sales.totalrum, 0) as totalrum
    ->      , COALESCE(allSales.total, 0) as total
    ->      , COALESCE(larimarSales.totallarimar, 0) as totallarimar
    -> FROM reps
    ->   INNER JOIN (
    ->        SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->        FROM `sales`
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->        GROUP BY reps.rep_id
    ->        )
    ->        allSales ON allSales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->        FROM `sales`
    ->        JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->        GROUP BY reps.rep_name
    ->        )
    ->        p41Sales ON p41Sales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar
    ->        FROM `sales_larimar`
    ->        JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->        GROUP BY reps.rep_id
    ->       )
    ->       larimarSales ON larimarSales.rep_id = reps.rep_id;
+----------+----------+-------+--------------+
| rep_name | totalrum | total | totallarimar |
+----------+----------+-------+--------------+
| Cely     |       76 |   206 |          104 |
| Heike    |      192 |   502 |          237 |
+----------+----------+-------+--------------+

Have your reps for operator 12 sold any product 41s?

BTW, added the COALESCE()s for you.

Link to comment
Share on other sites

13 hours ago, Barand said:

Changing to tour_operator_id = 5 (for consistency with othe queries, plus my reps table has no reps with "12") gives

mysql> SELECT reps.rep_name
    ->      , COALESCE(p41Sales.totalrum, 0) as totalrum
    ->      , COALESCE(allSales.total, 0) as total
    ->      , COALESCE(larimarSales.totallarimar, 0) as totallarimar
    -> FROM reps
    ->   INNER JOIN (
    ->        SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->        FROM `sales`
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->        GROUP BY reps.rep_id
    ->        )
    ->        allSales ON allSales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->        FROM `sales`
    ->        JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->        GROUP BY reps.rep_name
    ->        )
    ->        p41Sales ON p41Sales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar
    ->        FROM `sales_larimar`
    ->        JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
    ->        WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->        GROUP BY reps.rep_id
    ->       )
    ->       larimarSales ON larimarSales.rep_id = reps.rep_id;
+----------+----------+-------+--------------+
| rep_name | totalrum | total | totallarimar |
+----------+----------+-------+--------------+
| Cely     |       76 |   206 |          104 |
| Heike    |      192 |   502 |          237 |
+----------+----------+-------+--------------+

Have your reps for operator 12 sold any product 41s?

BTW, added the COALESCE()s for you.

Hi, thanks for your solution unfortunately my result is empty.
Attached the "new" tables and yes there are reps with touroperator_id 12 which sold product 41.
I cleaned up the reps table a bit.

reps.sql.txt sales.sql.txt sales_larimar.sql.txt salesdetails.sql.txt touroperators.sql.txt

Link to comment
Share on other sites

With your new data...

mysql> SELECT reps.rep_name
    ->      , COALESCE(p41Sales.totalrum, 0) as totalrum
    ->      , COALESCE(allSales.total, 0) as total
    ->      , COALESCE(larimarSales.totallarimar, 0) as totallarimar
    -> FROM reps
    ->  INNER JOIN (
    ->       SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->       FROM `sales`
    ->       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->       WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->       GROUP BY reps.rep_id
    ->       )
    ->       allSales ON allSales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->        FROM `sales`
    ->        JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->        GROUP BY reps.rep_name
    ->       )
    ->       p41Sales ON p41Sales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->         SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar
    ->         FROM `sales_larimar`
    ->         JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->         WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->         GROUP BY reps.rep_id
    ->        )
    ->        larimarSales ON larimarSales.rep_id = reps.rep_id;
+---------------+----------+-------+--------------+
| rep_name      | totalrum | total | totallarimar |
+---------------+----------+-------+--------------+
| Julio SIL*    |       44 |   724 |          787 |
| Gaby SIL*     |       37 |   563 |          648 |
| Hans SIL*     |      327 |  1629 |          211 |
| Carlos SIL    |       32 |   214 |          100 |
| Smerling SIL* |       28 |    90 |            0 |
+---------------+----------+-------+--------------+

Is that what you expect to see?

Link to comment
Share on other sites

24 minutes ago, Barand said:

With your new data...

mysql> SELECT reps.rep_name
    ->      , COALESCE(p41Sales.totalrum, 0) as totalrum
    ->      , COALESCE(allSales.total, 0) as total
    ->      , COALESCE(larimarSales.totallarimar, 0) as totallarimar
    -> FROM reps
    ->  INNER JOIN (
    ->       SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total
    ->       FROM `sales`
    ->       JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->       WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->       GROUP BY reps.rep_id
    ->       )
    ->       allSales ON allSales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->        SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum
    ->        FROM `sales`
    ->        JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->        JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->        WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
    ->        GROUP BY reps.rep_name
    ->       )
    ->       p41Sales ON p41Sales.rep_id = reps.rep_id
    ->
    ->   LEFT JOIN (
    ->         SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar
    ->         FROM `sales_larimar`
    ->         JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1
    ->         WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
    ->         GROUP BY reps.rep_id
    ->        )
    ->        larimarSales ON larimarSales.rep_id = reps.rep_id;
+---------------+----------+-------+--------------+
| rep_name      | totalrum | total | totallarimar |
+---------------+----------+-------+--------------+
| Julio SIL*    |       44 |   724 |          787 |
| Gaby SIL*     |       37 |   563 |          648 |
| Hans SIL*     |      327 |  1629 |          211 |
| Carlos SIL    |       32 |   214 |          100 |
| Smerling SIL* |       28 |    90 |            0 |
+---------------+----------+-------+--------------+

Is that what you expect to see?

Yes, exactly that! 😀
And I have it now......the comma before COALESCE fell away after copy-paste....how dumb of me.
But this is it, thank you very much!!!

Edited by Erwin007
Link to comment
Share on other sites

I've been looking at the table structures.

TOUROPERATORS     (The one you got right.)

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| touroperator_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| touroperator_name   | varchar(255) | NO   |     | NULL    |                |
| touroperator_active | tinyint(1)   | NO   |     | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

REPS

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| rep_id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| rep_name            | varchar(255) | NO   |     | NULL    |                |
| rep_touroperator_id | int(255)     | NO   | MUL | 0       |                |   should be INT,  should be indexed
| rep_active          | tinyint(1)   | NO   |     | 0       |                |
| rep_ot              | tinyint(1)   | NO   |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

SALES

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| sales_ticketnr       | varchar(255) | YES  | UNI | NULL    |       |   should be INT, should be primary key
| sales_date           | varchar(10)  | YES  |     | NULL    |       |   should be type DATE
| sales_employeeid     | varchar(255) | YES  | MUL | NULL    |       |                  INT,  should be indexed
| sales_repid          | varchar(255) | YES  | MUL | NULL    |       |                  INT,  should be indexed
| sales_totaldollars   | varchar(255) | YES  |     | NULL    |       |                  INT
| sales_paiddollars    | varchar(255) | YES  |     | NULL    |       |                  INT
| sales_paidpesos      | varchar(255) | YES  |     | NULL    |       |                  INT
| sales_paideuros      | varchar(255) | YES  |     | 0       |       |                  INT
| sales_paidcreditcard | varchar(255) | YES  |     |         |       |                  INT
| sales_active         | tinyint(4)   | NO   |     | 1       |       |
+----------------------+--------------+------+-----+---------+-------+

SALESDETAILS

+----------------------------+------------+------+-----+---------+----------------+
| Field                      | Type       | Null | Key | Default | Extra          |
+----------------------------+------------+------+-----+---------+----------------+
| salesdetails_id            | int(255)   | NO   | PRI | NULL    | auto_increment | 255 digits is a lot of ids, if you could store a nuber that big! INT(11)
| salesdetails_salesticketnr | text       | NO   |     | NULL    |                | INT(11), not TEXT,  should be indexed
| salesdetails_amount        | int(255)   | NO   |     | NULL    |                | INT(11)
| salesdetails_productid     | int(255)   | NO   | MUL | NULL    |                | INT(11),  should be indexed
| salesdetails_pricedollars  | int(255)   | NO   |     | NULL    |                | INT(11)
| salesdetails_active        | tinyint(1) | NO   |     | 1       |                |
+----------------------------+------------+------+-----+---------+----------------+

SALESARIMAR

+--------------------+---------+------+-----+---------+----------------+
| Field              | Type    | Null | Key | Default | Extra          |
+--------------------+---------+------+-----+---------+----------------+
| sales_id           | int(11) | NO   | PRI | NULL    | auto_increment |
| sales_date         | text    | NO   |     | NULL    |                | DATE type
| sales_ticketnr     | int(11) | NO   |     | NULL    |                | should be indexed
| sales_employeeid   | int(11) | NO   |     | NULL    |                | should be indexed
| sales_repid        | int(11) | NO   |     | NULL    |                | should be indexed
| sales_totaldollars | int(11) | NO   |     | NULL    |                |
+--------------------+---------+------+-----+---------+----------------+

 

Link to comment
Share on other sites

Hi,

Just now I saw the db corrections, thanks!

As I am now changing to the "prepared statement" execution I run into a problem.
Maybe you can see the problem rightaway in this code:

$query = "
        SELECT employees.employee_name, reps.rep_name, sales.*
        FROM `sales`
        JOIN employees ON employees.employee_id = sales.sales_employeeid
        JOIN reps ON reps.rep_id = sales.sales_repid
        WHERE (sales.sales_date BETWEEN ? AND ?)
        ORDER BY sales.sales_date, sales.sales_ticketnr";
        
        $result = mysqli_prepare($con,$query);
        $result->bind_param('ss', $from, $to);
        $result->execute();
        $result->bind_result($sales_date, $sales_ticketnr, $employee_name, $rep_name, $sales_totaldollars, $sales_paiddollars, $sales_paidpesos, $sales_paideuros, $sales_paidcreditcard);
        
        $totaltotaldollars = 0;
        $totalpaiddollars = 0;
        $totalpaidpesos = 0;
        $totalpaideuros = 0;
        $totalpaidcreditcard = 0;
        
        while($row = $result->fetch()) {
        
            $totaltotaldollars += $sales_totaldollars;
            $totalpaiddollars += $sales_paiddollars;
            $totalpadipesos += $sales_paidpesos;
            $totalpaideuros += $sales_paideuros;
            $totalpaidcreditcard += $sales_paidcreditcard;
            $represent = explode(" ", $rep_name);
            
            
            $tdata .= sprintf("<tr>
                                <td>%s</td>
                                <td>%s</td>
                                <td>%s</td>
                                <td>%s</td>
                                <td>%d</td>
                                <td>%d</td>
                                <td>%d</td>
                                <td>%d</td>
                                <td>%d</td>
                               </tr>
                            ", $sales_date, $sales_ticketnr, $employee_name, $represent[0], $sales_totaldollars, $sales_paiddollars, $sales_paidpesos, $sales_paideuros, $sales_paidcreditcard );
        }
        
        $tdata .= sprintf("<tr class='tot'>
                            <td>%s</td>
                            <td>%s</td>
                            <td>%s</td>
                            <td>%s</td>
                            <td>%d</td>
                            <td>%d</td>
                            <td>%d</td>
                            <td>%d</td>
                            <td>%d</td>
                           </tr>
                        ", ' ', ' ', ' ', ' ', $totaltotaldollars, $totalpaiddollars, $totalpaidpesos, $totalpaideuros, $totalpaidcreditcard );

 

Link to comment
Share on other sites

Your result binding is off.

The first column in your SELECT clause is the employee name but you are binding that to sales_date when you bind the results. They need to be bound in the order they are selected.

Put this line of code just before you create your database connection...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

DON'T use "*" in your select clauses, almost always specify the column names - it better documents what the query is doing.

DO use code blocks (<> button) when posting code in the forums.

Link to comment
Share on other sites

2 hours ago, Barand said:

Your result binding is off.

The first column in your SELECT clause is the employee name but you are binding that to sales_date when you bind the results. They need to be bound in the order they are selected.

Put this line of code just before you create your database connection...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

DON'T use "*" in your select clauses, almost always specify the column names - it better documents what the query is doing.

DO use code blocks (<> button) when posting code in the forums.

Thanks, it works now.
And next time I will use this <>block function.

 

Link to comment
Share on other sites

10 minutes ago, mac_gyver said:

this might be a good time to switch to the much simpler and more modern PDO extension. you can simply and directly fetch and use data from a prepared query in the same way as for a non-prepared query.

Can you show/explain please how this would work with the code I placed?

 

Link to comment
Share on other sites

after you have made a connection using the PDO extension, in a variable named $pdo (so that anyone looking at the code will get a hint at which extension it is using), the database specific code would be -

// prepare the query. this returns a PDOStatment object, hence the variable named $stmt
$stmt = $pdo->prepare($query);
// execute the query, supplying an array of input values to the ->execute() call that correspond to the positional ? place-holders in the sql query statement
$stmt->execute([$from, $to]);

at the point of fetching the data from the query -

while($row = $stmt->fetch())
{
    // use elements in $row, e.g. $row['employee_name']
    
}

typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions (this is now the default in php8+)
	PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc, so that you don't need to specify it in each fetch statement
	];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.