Erwin007

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

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

Fantastic! It works! Thank you very much. Unfortunately I have no clue about the coalesce function, I am trying but so far no exito.

Maybe the reps table comes in handy reps.sql.txt

It almost works. It doesn't show the reps that sold something other than product id=41. )the amount at product must be 0 but the total sales has an amount. if you put the from date 2023-04-01 and to-date 2023-04-30 you see 2 more reps (Jose Mendez and Honeydi) which didn't sell productid 41 but they sold some other stuff.

Yes, I want to know the sales for productid 41 and the total sales for that rep. I just posted the 2 queries I made and they work flawless, but "combining" them...pppffffttt

I have a query to find out the total sales for that rep for that period and it works 100%: SELECT reps.rep_name, 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_name I have anothe query where I find the total sales of productid 41 for that rep in that period and it also works 100%: SELECT reps.rep_name, 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 "Combining" these 2 has been a nightmare the last 20hours....

Because US$ 502 is the total sales for Heike. The 1st columns is the sales for productid 41, the second column is the total sales for that rep in that period. Attached a report of sales per rep divided in products with the total

I hope you mean this: Selection from 1st of May till 15 of May. The amount of 416 must be 502

sales.sql.txt salesdetails.sql.txt

I have sales.sql and salsdetails.sql...how to upload or send?

I have no idea unfortunately.

Ok, I will try again: This report I need: REP: Product(41): TOTAL SALES: Cely 76 206 Heike 192 502 The Report I get (with my query): REP: Product(41): TOTAL SALES: Cely 76 206 Heike 192 416 This is my query: SELECT reps.rep_name, sum(sales.sales_totaldollars) as total, sum(salesdetails.salesdetails_pricedollars) as totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr AND salesdetails.salesdetails_productid = '41' 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_name"; The error (the missing 86 at total sales for Heike) is that is "skips" the 2 tickets that don't have the product id 41. The date I choose here is from May 8 till May 8, but it works 100% (just like the GROUP BY part) with other periods, also the part JOIN reps on... works 100%. Somewhere in this JOIN salesdetails on... is my error. I hope it's a little bit more clear.

It probably can't be done...

I have 2 queries: Query1 = SELECT reps.rep_name, 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_name Query2 = SELECT reps.rep_name, sum(salesdetails.salesdetails_pricedollars) as totalrum FROM sales JOIN salesdetails on salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr AND salesdetails.salesdetails_productid = ‘41’ 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_name Separately the result is 100%, combined it is not. Anybody?

Hi, I solved the abovementioned problem but have now another problem but it's kind of complex, let me try: There are 5 tables in the DB: reps: rep_id, rep_name, rep_touroperator_id and rep_active touroperators: touroperator_id, touroperator_name products: product_id, product_name sales:

Unfortunately it doesn't work. Only the reps with reps_touroperatorid 5 count. In my query that part worked. I do not get any result with your query.

Table reps: - rep_id - rep_name - rep_touroperatorid Table sales: - sales_id - sales_repid - sales_totaldollars my query so far: SELECT reps.rep_name, sum(sales.sales_totaldollars) as total FROM `sales` JOIN reps on reps.rep_touroperator_id = '5' WHERE (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_name and this as code: $result = mysqli_query($con,$query); $total = 0; while($row = mysqli_fetch_assoc($result)){ $total+=$row['total'] It is working but per rep I get the total of all sales for that period of time instead of the total sales per rep. I need something like this: Juan 3,500 Maria 2,000 Peter 1,500 But I get this: Juan 7,000 Maria 7,000 Peter 7,000 I hope I gave enough info to solve this. Thanks

it looks like $admin does not have the value 1 (or whatever I put in the database) What am I doing wrong? It's driving me absolutely (more) nuts.

I tried your suggestion but still the same result. In the table the admin is a varchar with length = 1, but I tried tinyint also with the same result.

Thanks for the advice but first I need to have it working the "vulnerable" way

HI, thanks for your reply, unfortunately it doesn't work

Hi, hate to ask but after hours of trying I can't figure it out. Probably something incredible small... if(isset($_POST['login'])){ extract($_POST); $query = "SELECT * FROM users WHERE `username` = '$username' AND password ='$password'"; $result = mysqli_query($con,$query); $num_row = mysqli_num_rows($result); if( $num_row > 0){ $row = mysqli_fetch_assoc($result); $id = $row['id']; $_SESSION['$username'] = $row; $_SESSION['user'] = $username; $admin = $row['admin']; if( $admin = 1 ){ header("location:index_admin.php"); }else{ header("location:index.php"); } }else{ header("location:login.php?msg=error"); } it doesn't matter if the user has admin 1 or 0 it always goes to the index_admin.php

Yes, it worked, thanks again.

I celebrated too fast.... what exactly is "reps r", "sales s" and r.reps_id" in:? FROM reps r JOIN sales s ON r.rep_id = sales_rep_id