Erwin007

Instead of WHERE inventory.inventory_date BETWEEN '$from' AND '$to' I did: WHERE inventory.inventory_date BETWEEN ? AND ? and that works correct. Thanks

Instead of the $from = $_GET['from'] ?? ''; I have this: <?php $from = "2023-06-15"; //$_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $tdata = ""; if (!empty($from) && !empty($to)) { $query = " SELECT products.product_name, sum(inventory.inventory_amount) as amount FROM `inventory` JOIN products on products.product_id = inventory.inventory_productid WHERE inventory.inventory_date BETWEEN '$from' AND '$to' GROUP BY products.product_name"; $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount); The result is correct but I have an error-message: PHP Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in /home/xxxxxx/public_html/ticketapp/inv_rep.php on line 88 Line 88: $result->bind_param('ss', $from, $to); The problem is obviously the $from = "2023-06-15" instead of $_GET['from'] ?? ''; but how to solve this? Thanks.

I just want to know if there are zero rows or not.

I have this code and it always gives me "ticket exists" even when it doesn't, where I go wrong? Thanks. $query = "SELECT * FROM sales WHERE sales_ticketnr = '$ticket' "; $result = mysqli_query($con,$query); if(!empty($result)) { echo '<script>alert("ticket exists")</script>'; } else { echo '<script>alert("ticket does NOT exist")</script>'; }

I am stunned.....it works.....🙂👍 And so simple...when you know it... Appreciate your time, thanks.

Let me try somewhere else for some help.

Unfortunately it doesn't work. After trying for days it's time to give up I guess. I am so close with this SQL: SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id = salesdetails.salesdetails_productid WHERE (sales.sales_date BETWEEN ? AND ?) GROUP BY products.product_name"; But instead of all the products I only need this SQL for productid's 50 till 56. The code after the SQL is this: $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount, $total); Maybe there is something incorrect.

Thanks Gizmola, that did the trick.

Yes that's my understanding too. Maybe the reason for my post....

Hi, Things worked out fine but I still have this (small) problem: The totals are not correct: Barcelo Gran Anejo(13) should be 12 (amount) and 156 (total) Brugal Leyenda(40) should be 7 (amount) and 280 (total) Somewhere in the query I am making a mistake, I hope someone can find it. Thanks. $query = " SELECT products.product_name, sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON (products.product_id = 55 OR products.product_id = 51) WHERE salesdetails.salesdetails_productid = 55 OR salesdetails.salesdetails_productid = 51 AND sales.sales_date BETWEEN ? AND ? GROUP BY products.product_name";

Got it! Thanks again!! 👍🙂

Now the table is all over the width of the screen and still above the input.

Now I have this; the table above the input and the table needs to be under the input. <?php include "header.php"; $from = $_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $employee = $_GET['employee_id']; $tdata = ""; if (!empty($from) && !empty($to)) { $query = " SELECT employees.employee_name FROM `employees` WHERE employees.employee_id = '$employee'"; $result = mysqli_query($con,$query); while($row = mysqli_fetch_assoc($result)){ $naampje = $row['employee_name']; } $query = " SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id = salesdetails.salesdetails_productid WHERE sales.sales_employeeid = '$employee' AND (sales.sales_date BETWEEN ? AND ?) GROUP BY products.product_name"; $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount, $total); $totaltotal = 0; while($row = $result->fetch()) { $totaltotal += $total; $amount_neat = number_format($amount); $total_neat = number_format($total); $totaltotal_neat = number_format($totaltotal); $tdata .= "<tr> <td>$product_name</td> <td>$amount_neat</td> <td>$total_neat</td> </tr>"; } $tdata .= "<tr class='tot'> <td></td> <td></td> <td>$totaltotal_neat</td> </tr>"; ?> <div class="app-content content"> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <div class="col-md-12" > <table class="table dataTable" id="myTable2"> <thead> <tr> <th>Product</th> <th>Amount</th> <th>US$</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </div> </div> </section> </div> </div> </div> </body> <?php } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { text-align: right; } td { text-align: right; } td:first-child { text-align: left; } th:first-child { text-align: left; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <div class="app-content content"> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="GET"> <div class="row"> <p style="font-size:18px; font-weight: bold"> Sales per Product</p> <div class="col col-md-2"> <label for='from'>Date From:</label> <input type="date" value="<?= $from ?>" name="from" id="from" class="form-control" required> </div> <div class="col col-md-2"> <label for='to'>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control" required> </div> <div class="col col-md-3"> <label for="employee_id">Employee:</label> <select class="form-control" name="employee_id" id="employee_id"> <?php $query = " SELECT * FROM employees WHERE employee_active = 1 ORDER BY employee_name asc"; $result = mysqli_query($con,$query); $i =1; ?> <option value="choose employee">Choose Employee</option> <?php while($row = mysqli_fetch_assoc($result)){ ?> <option value="<?php echo $row['employee_id']?>" <?php if(isset($_GET['employee_id']) && $_GET['employee_id'] == $row['employee_id']){ echo 'selected'; }?> > <?php echo $row['employee_name']?> </option> <?php } ?> </select> </div> <div class="col col-md-1"> <label for='submit'>&nbsp;</label> <input type='submit' class="form-control" > </div> <script src="app-assets/vendors/js/vendors.min.js"></script> <script src="app-assets/vendors/js/charts/apexcharts.min.js"></script> <script src="app-assets/js/core/app-menu.min.js"></script> <script src="app-assets/js/core/app.min.js"></script> <script src="app-assets/js/scripts/customizer.min.js"></script> <script src="app-assets/js/scripts/pages/dashboard-ecommerce.min.js"></script> <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js" ></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js" ></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.bootstrap5.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.responsive.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/responsive.bootstrap5.js"></script> <script src="app-assets/js/scripts/pages/app-invoice-list.min.js"></script> <script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script> <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.print.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> <script> $(document).ready( function () { $('#myTable').dataTable({ "ordering": false }) $('#myTable2').dataTable({ "ordering": false, "dom": 'Btip', "buttons": ['print'] }); const from = $('#from').val().split('-').reverse().join('-'); const to = $('#to').val().split('-').reverse().join('-'); const employee="<?php echo $naampje; ?>"; $('#myTable2').append('<caption style="caption-side: top; align:center"><span style="font-size: 1.3REM; color: #000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sales for employee '+ employee +' from '+ from +' to '+ to +'</span></br></caption>') }); </script> </html>

The problem is the table doesn't come under the input and covers the whole screen. I don't know where exactly from where to copy or move it.

I am trying but it won't work. It's my lack of knowledge of HTML I guess. I tried with endif but no result.

Everything is working 100% but I want to make the user interface a little nicer. When I open the page I get the screen with already the table part visible. Is it possible to only have the input-part and the table-part comes after clicking submit? Here is the code: <?php include "header.php"; $from = $_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $employee = $_GET['employee_id']; $tdata = ""; if (!empty($from) && !empty($to)) { $query = " SELECT employees.employee_name FROM `employees` WHERE employees.employee_id = '$employee'"; $result = mysqli_query($con,$query); while($row = mysqli_fetch_assoc($result)){ $naampje = $row['employee_name']; } $query = " SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id = salesdetails.salesdetails_productid WHERE sales.sales_employeeid = '$employee' AND (sales.sales_date BETWEEN ? AND ?) GROUP BY products.product_name"; $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount, $total); $totaltotal = 0; while($row = $result->fetch()) { $totaltotal += $total; $amount_neat = number_format($amount); $total_neat = number_format($total); $totaltotal_neat = number_format($totaltotal); $tdata .= "<tr> <td>$product_name</td> <td>$amount_neat</td> <td>$total_neat</td> </tr>"; } $tdata .= "<tr class='tot'> <td></td> <td></td> <td>$totaltotal_neat</td> </tr>"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { text-align: right; } td { text-align: right; } td:first-child { text-align: left; } th:first-child { text-align: left; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <div class="app-content content"> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="GET"> <div class="row"> <p style="font-size:18px; font-weight: bold"> Sales per Product</p> <div class="col col-md-2"> <label for='from'>Date From:</label> <input type="date" value="<?= $from ?>" name="from" id="from" class="form-control" required> </div> <div class="col col-md-2"> <label for='to'>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control" required> </div> <div class="col col-md-3"> <label for="employee_id">Employee:</label> <select class="form-control" name="employee_id" id="employee_id"> <?php $query = " SELECT * FROM employees WHERE employee_active = 1 ORDER BY employee_name asc"; $result = mysqli_query($con,$query); $i =1; ?> <option value="choose employee">Choose Employee</option> <?php while($row = mysqli_fetch_assoc($result)){ ?> <option value="<?php echo $row['employee_id']?>" <?php if(isset($_GET['employee_id']) && $_GET['employee_id'] == $row['employee_id']){ echo 'selected'; }?> > <?php echo $row['employee_name']?> </option> <?php } ?> </select> </div> <div class="col col-md-1"> <label for='submit'>&nbsp;</label> <input type='submit' class="form-control" > </div> </div> </form> </div> </div> <div class="card"> <div class="card-datatable table-responsive card-body"> <div class="col-md-12" > <table class="table dataTable" id="myTable2"> <thead> <tr> <th>Product</th> <th>Amount</th> <th>US$</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </div> </div> </section> </div> </div> </div> </body> <script src="app-assets/vendors/js/vendors.min.js"></script> <script src="app-assets/vendors/js/charts/apexcharts.min.js"></script> <script src="app-assets/js/core/app-menu.min.js"></script> <script src="app-assets/js/core/app.min.js"></script> <script src="app-assets/js/scripts/customizer.min.js"></script> <script src="app-assets/js/scripts/pages/dashboard-ecommerce.min.js"></script> <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js" ></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js" ></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.bootstrap5.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.responsive.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/responsive.bootstrap5.js"></script> <script src="app-assets/js/scripts/pages/app-invoice-list.min.js"></script> <script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script> <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.print.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> <script> $(document).ready( function () { $('#myTable').dataTable({ "ordering": false }) $('#myTable2').dataTable({ "ordering": false, "dom": 'Btip', "buttons": ['print'] }); const from = $('#from').val().split('-').reverse().join('-'); const to = $('#to').val().split('-').reverse().join('-'); const employee="<?php echo $naampje; ?>"; $('#myTable2').append('<caption style="caption-side: top; align:center"><span style="font-size: 1.3REM; color: #000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sales for employee '+ employee +' from '+ from +' to '+ to +'</span></br></caption>') }); </script> </html> I hope I explained my problem understandable. Thanks

Yes, that was the solution, great! Thank you very much, I now can continue.

I got the margin plus dates working but now I have to get the value of employee_id into $employee. Here is the code: <?php include "header.php"; $from = $_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $employee = $_GET['view']; $tdata = ""; if (!empty($from) && !empty($to)) { echo $query = " SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id = salesdetails.salesdetails_productid WHERE sales.sales_employeeid = '$employee' AND (sales.sales_date BETWEEN ? AND ?) GROUP BY products.product_name"; $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount, $total); $totaltotal = 0; while($row = $result->fetch()) { $totaltotal += $total; $tdata .= sprintf("<tr> <td>%s</td> <td>%d</td> <td>%d</td> </tr> ", $product_name, $amount, $total); } $tdata .= sprintf("<tr class='tot'> <td>%s</td> <td>%s</td> <td>%d</td> </tr> ", '', '', $totaltotal); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { text-align: right; } td { text-align: right; } td:first-child { text-align: left; } th:first-child { text-align: left; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <div class="app-content content"> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="GET"> <div class="row"> <p style="font-size:18px; font-weight: bold"> Sales per Product</p> <div class="col col-md-2"> <label for='from'>Date From:</label> <input type="date" value="<?= $from ?>" name="from" id="from" class="form-control" required> </div> <div class="col col-md-2"> <label for='to'>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control" required> </div> <div class="col col-md-3"> <label for="name">Employee:</label> <select class="form-control" name="sales_repid" id="rep"> <?php $query = " SELECT * FROM employees WHERE employee_active = 1 ORDER BY employee_name asc"; $result = mysqli_query($con,$query); $i =1; ?> <option value="choose employee">Choose Employee</option> <?php while($row = mysqli_fetch_assoc($result)){ ?> <option value="<?php echo $row['employee_id']?>" <?php if(isset($_GET['view']) && $_GET['view'] == $row['employee_id']){ echo 'selected'; }?> > <?php echo $row['employee_name']?></option> <?php } ?> </select> </div> <div class="col col-md-1"> <label for='submit'>&nbsp;</label> <input type='submit' class="form-control" > </div> </div> </form> </div> </div> <div class="card"> <div class="card-datatable table-responsive card-body"> <div class="col-md-12" > <table class="table dataTable" id="myTable2"> <thead> <tr> <th>Product</th> <th>Amount</th> <th>US$</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </div> </div> </section> </div> </div> </div> </body> The problem is when choosing an employee, I am trying but nothing works, anybody maybe an idea? Thanks.

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

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

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

Got it: const from = $('#from').val().split('-').reverse().join('-'); Thanks for your help.

Somewhere in here after creating the table: <script src="app-assets/vendors/js/vendors.min.js"></script> <script src="app-assets/vendors/js/charts/apexcharts.min.js"></script> <script src="app-assets/js/core/app-menu.min.js"></script> <script src="app-assets/js/core/app.min.js"></script> <script src="app-assets/js/scripts/customizer.min.js"></script> <script src="app-assets/js/scripts/pages/dashboard-ecommerce.min.js"></script> <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js" ></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js" ></script> <script src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js" ></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.bootstrap5.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/dataTables.responsive.min.js"></script> <script src="app-assets/vendors/js/tables/datatable/responsive.bootstrap5.js"></script> <script src="app-assets/js/scripts/pages/app-invoice-list.min.js"></script> <script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script> <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.print.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> Plus this script: <script> $(document).ready( function () { $('#myTable').dataTable({ "ordering": false }) $('#myTable2').dataTable({ "ordering": false, "dom": 'Btip', "buttons": ['print'] });

https://datatables.net/extensions/buttons/examples/html5/titleMessage.html

Unfortunately I only get it to work in Javascript that's why I try to get the dates right. This table goes to the client so has to be flawless with a title with correct dates.