New SQL creation problem

kicken · May 17, 2023

Use LEFT JOIN instead of inner join for the joints to the sub-queries. You'll get NULL for reps with no sales. You can convert that null to a 0 in the select clause if you want by using COALESCE.

Erwin007 · May 17, 2023

Fantastic! It works!
Thank you very much.

Unfortunately I have no clue about the coalesce function, I am trying but so far no exito.

Edited May 17, 2023 by Erwin007

Erwin007 · May 17, 2023

oh man......and that for a zero 🤢

Erwin007 · May 17, 2023

Finally solved it with: <?php echo number_format($row['totalrum']);?>

Erwin007 · May 17, 2023

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

kicken · May 17, 2023

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).

Erwin007 · May 17, 2023

Tried a dozen possibilities but can't get it to work.

Erwin007 · May 17, 2023

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 May 17, 2023 by Erwin007

kicken · May 17, 2023

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.

Erwin007 · May 17, 2023

Yes, it is working now 100%.
Thank you very much and repect for your SQL knowledge, quite impressive.

Edited May 17, 2023 by Erwin007

Erwin007 · May 17, 2023

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.

Barand · May 17, 2023

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.

Erwin007 · May 18, 2023

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 May 18, 2023 by Erwin007

Barand · May 18, 2023

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.

Erwin007 · May 18, 2023

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

Barand · May 18, 2023

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?

Erwin007 · May 18, 2023

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 May 18, 2023 by Erwin007

Barand · May 18, 2023

The only change from before is that the tour operator is now 12, so I don't know why you got no results. :confused:

Barand · May 18, 2023

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    |                |
+--------------------+---------+------+-----+---------+----------------+

Erwin007 · May 25, 2023

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 );

Barand · May 25, 2023

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.

mac_gyver · May 26, 2023

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.

Erwin007 · May 26, 2023

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.

Erwin007 · May 26, 2023

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?

mac_gyver · May 26, 2023

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);

Sign In

New SQL creation problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information