Erwin007 Posted May 22, 2023 Share Posted May 22, 2023 Hi, I have this code: <form method="POST" action="response.php"> <label>Margin %:</label> <input type="number" name="ikke" id="ikke"> <label>Date From:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="start" id="start" class="form-control"> <label>Date To:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="to" id="to" class="form-control"> <?php if(isset($_GET['to'])){ $from = $_GET['from']; $to = $_GET['to']; ?> <thead> <tr> <th>Date From:</th> <th>Date To:</th> <th>Margin:</th> </tr> </thead> <tbody> <tr> <td><?php echo $from;?></td> <td><?php echo $to;?></td> <td><?php echo $ikke;?></td> </tr> </tbody> </form> <script> $("#to").change(function(){ var view = $(this).val(); var from = $('#start').val(); var to = $('#to').val(); window.location.href = "test.php?from="+from+"&&to="+to; }) </script> And it is working 100% except I can not get the value of ikke. The response.php does nothing in relation with this code, it just "runs through" it. I hope somebody can solve this for me, thanks in advance. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 22, 2023 Share Posted May 22, 2023 Horrible arrangement of code. No table header or closer No submit button Script in the wrong place. Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 22, 2023 Author Share Posted May 22, 2023 (edited) 10 minutes ago, ginerjm said: Horrible arrangement of code. No table header or closer No submit button Script in the wrong place. <?php include "header.php"; ?> <div class="app-content content "> <div class="content-overlay"></div> <div class="header-navbar-shadow"></div> <div class="content-wrapper container-xxl p-0"> <div class="content-header row"></div> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="POST" action="response.php"> <div class="row"> <p style="font-size:18px; font-weight: bold"> xxxxxxx </p> <div class="col col-md-2"> <label>Margin %:</label> <input type="number" name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label>Date From:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="start" id="start" class="form-control"> </div> <div class="col col-md-2"> <label>Date To:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="to" id="to" class="form-control"> </div> <?php if(isset($_GET['to'])){ $from = $_GET['from']; $to = $_GET['to']; ?> <div class="col-md-12" > <table class="table dataTable" id="myTable2"> <thead> <tr> <th><b><u>Rep:</u></b></th> <th></th> <th></th> <th><b><u>Sales:</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Cigars:</u></b></th> <th><b><u>30%</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Rum/Souv.:</u></b></th> <th><b><u>30%</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Comm.:</u></b></th> <th></th> <th></th> </tr> </thead> <tbody class="tbody"> <?php $query = " 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' OR salesdetails.salesdetails_productid = '56' OR salesdetails.salesdetails_productid = '55' OR salesdetails.salesdetails_productid = '54' OR salesdetails.salesdetails_productid = '53' OR salesdetails.salesdetails_productid = '52' OR salesdetails.salesdetails_productid = '51' OR salesdetails.salesdetails_productid = '50' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id"; $result = mysqli_query($con,$query); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; while($row = mysqli_fetch_assoc($result)){ $CigarMargin = 0.455; $RumMargin = 1; $tot = round($row['total'] * $CigarMargin); $rums = round($row['totalrum'] * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars = $totalcigars + $cigars; $totalcigarscomm = $totalcigarscomm + $cigarscomm; $totalrums = $totalrums + $rums; $totalrumscomm = $totalrumscomm + $rumscomm; $totalsales = $totalsales + $tot; $totalcomm = $totalcomm + $comm; $stringText = $row['rep_name']; $represent = explode(" ", $stringText); ?> <tr> <td><?php echo $represent[0]; ?></td> <td></td> <td></td> <td><?php echo $tot;?></td> <td></td> <td></td> <td></td> <td><?php echo $cigars; ?></td> <td><?php echo number_format($cigarscomm,2,'.','') ;?></td> <td></td> <td></td> <td></td> <td><?php echo $rums;?></td> <td><?php echo number_format($rumscomm,2, '.','');?></td> <td></td> <td></td> <td></td> <td><?php echo number_format($comm,2,'.','');?></td> <td></td> <td></td> </tr> <?php } ?> <tr> <td></td> <td></td> <td><b>Total:</b></td> <td><b><?php echo $totalsales; ?></b></td> <td></td> <td></td> <td></td> <td><b><?php echo $totalcigars; ?></b></td> <td><b><?php echo number_format($totalcigarscomm,2,'.',''); ?></b></td> <td></td> <td></td> <td></td> <td><b><?php echo $totalrums; ?></b></td> <td><b><?php echo number_format($totalrumscomm,2,'.',''); ?></b></td> <td></td> <td></td> <td></td> <td><b><u><?php echo number_format($totalcomm,2,'.',''); ?></u></b></td> <td></td> <td></td> </tr> </tbody> </table> </div> <?php } ?> </div> </form> </div> </div> </section> </div> </div> </div> <?php include "footer.php"; ?> <script> $("#to").change(function(){ var view = $(this).val(); var from = $('#start').val(); var to = $('#to').val(); window.location.href = "test.php?from="+from+"&&to="+to; }) </script> Edited May 22, 2023 by Erwin007 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 22, 2023 Share Posted May 22, 2023 A little bit div-happy perhaps? So you have a form with a POST method. Just how is that form's contents getting to your processing script? And what is that JQ code accomplishing? (I don't use JQ so don't now what it is doing) Quote Link to comment Share on other sites More sharing options...
Barand Posted May 22, 2023 Share Posted May 22, 2023 Which page is supposed to process the from and to dates? Your form would send those values (and the ikke value) to "response.php" using POST method - if it were ever to be submitted (with a submit button or via a script) Your jquery sends the values date values only to "test.php" using GET method (ie in the query string). Why both the form and the jquery? Also if you use the GET method, you wshould process the received data from $_GET; with POST method you should process $_POST data. Use the GET method when you re GETTING data to display. Use POST when you want to do something with side-effects (like updating a database or logging in etc) Construct you pages with the php first followed by the html ... <?php process POST data - validation and db updates process GET data - build output from any queries ?> html output Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted May 22, 2023 Share Posted May 22, 2023 You should be able to duplicate some of the jQuery code to get the new field and pass the information: <script> $("#to").change(function(){ var view = $(this).val(); var ikke = $('#ikke').val(); //get "ikke" value var from = $('#start').val(); var to = $('#to').val(); window.location.href = "test.php?from="+from+"&&to="+to+"&&ikke="+ikke; //updated to pass "ikke" value }) </script> Be aware that the form code can potentially be tampered with by a malicious user. So, you'll want to take some extra precautions when using the data. For example, you could make sure "ikka" actually contains a number before executing the window.location.href redirect. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 22, 2023 Share Posted May 22, 2023 1 minute ago, cyberRobot said: you could make sure "ikka" actually contains a number before executing the window.location.href redirect. Whether you do that or not, you should still validate user input on the server (ie PHP) as there is no guarantee it came via your form. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted May 22, 2023 Share Posted May 22, 2023 1 minute ago, Barand said: you should still validate user input on the server (ie PHP) Thanks for clarifying. I was just about to modify my post. The validation should happen on both ends. @Erwin007 With that said, the jQuery portion probably isn't necessary. Is there a reason you're not passing the information using the default behavior of the <form> tag? If the values need to be passed using GET, you could modify the method attribute. Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 22, 2023 Author Share Posted May 22, 2023 1 hour ago, Barand said: Which page is supposed to process the from and to dates? Your form would send those values (and the ikke value) to "response.php" using POST method - if it were ever to be submitted (with a submit button or via a script) Your jquery sends the values date values only to "test.php" using GET method (ie in the query string). Why both the form and the jquery? Also if you use the GET method, you wshould process the received data from $_GET; with POST method you should process $_POST data. Use the GET method when you re GETTING data to display. Use POST when you want to do something with side-effects (like updating a database or logging in etc) Construct you pages with the php first followed by the html ... <?php process POST data - validation and db updates process GET data - build output from any queries ?> html output This code I got from somebody else. I think the script is there to avoid creating a submit-button, no idea. I will try without the Javascript to get it working. Thanks for your time. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 22, 2023 Share Posted May 22, 2023 And try and figure out why you have so many div tags in there. And when we say 'submit button' we don't actually mean a button tag, usually an input tag. Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 23, 2023 Author Share Posted May 23, 2023 What I understand this comes from a (dashboard) template. This is my 1st project with PHP which I paid for and to learn from but the Javascript is quite confusing. How should I change this specific code into the "normal" way with GET or POST? Thanks in advance. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 23, 2023 Share Posted May 23, 2023 Something like this <?php require 'db_inc.php'; // USE YOUR OWN $con = myConnect('db2'); // CONNECTION CODE $ikke = $_GET['ikke'] ?? '0.455'; $from = $_GET['from'] ?? date('Y-m-d', strtotime('first day of this month')); $to = $_GET['to'] ?? date('Y-m-d'); $query = " 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 ? AND ?) 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 ? AND ?) AND salesdetails.salesdetails_productid = '41' OR salesdetails.salesdetails_productid = '56' OR salesdetails.salesdetails_productid = '55' OR salesdetails.salesdetails_productid = '54' OR salesdetails.salesdetails_productid = '53' OR salesdetails.salesdetails_productid = '52' OR salesdetails.salesdetails_productid = '51' OR salesdetails.salesdetails_productid = '50' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id"; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; $tdata = ""; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { background-color: black; color: white; } td { text-align: right; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <form method="GET"> <div class="col col-md-2"> <label>Margin %:</label> <input type="text" value="<?= $ikke ?>"name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </div> <div class="col col-md-2"> <label>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control"> </div> <div> <input type='submit'> </div> </form> <div class="app-content content "> <table border='1' class="table dataTable" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </body> </html> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 23, 2023 Share Posted May 23, 2023 some points about the code/query - when you have a list of ORed terms in a query, use a single IN() comparison instead. don't put quotes around numbers. be consistent. one number in the query isn't quoted. use 'require' for things your php code must have for it to work. for a <label> tag to work, you must either put the correspond form field somewhere between the opening and closing label tags, with the label text, or you must put a for='...' attribute inside the opening label tag and a corresponding id='...' attribute inside the form field. you should validate your resulting web pages at validator.w3.org any dynamic value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting. Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 23 hours ago, mac_gyver said: some points about the code/query - when you have a list of ORed terms in a query, use a single IN() comparison instead. don't put quotes around numbers. be consistent. one number in the query isn't quoted. use 'require' for things your php code must have for it to work. for a <label> tag to work, you must either put the correspond form field somewhere between the opening and closing label tags, with the label text, or you must put a for='...' attribute inside the opening label tag and a corresponding id='...' attribute inside the form field. you should validate your resulting web pages at validator.w3.org any dynamic value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting. Hi, thanks for your suggestions and sorry for my ignorance but are these for the code from Barand or mine? Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 (edited) On 5/23/2023 at 9:02 AM, Barand said: Something like this <?php require 'db_inc.php'; // USE YOUR OWN $con = myConnect('db2'); // CONNECTION CODE $ikke = $_GET['ikke'] ?? '0.455'; $from = $_GET['from'] ?? date('Y-m-d', strtotime('first day of this month')); $to = $_GET['to'] ?? date('Y-m-d'); $query = " 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 ? AND ?) 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 ? AND ?) AND salesdetails.salesdetails_productid = '41' OR salesdetails.salesdetails_productid = '56' OR salesdetails.salesdetails_productid = '55' OR salesdetails.salesdetails_productid = '54' OR salesdetails.salesdetails_productid = '53' OR salesdetails.salesdetails_productid = '52' OR salesdetails.salesdetails_productid = '51' OR salesdetails.salesdetails_productid = '50' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id"; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; $tdata = ""; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { background-color: black; color: white; } td { text-align: right; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <form method="GET"> <div class="col col-md-2"> <label>Margin %:</label> <input type="text" value="<?= $ikke ?>"name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </div> <div class="col col-md-2"> <label>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control"> </div> <div> <input type='submit'> </div> </form> <div class="app-content content "> <table border='1' class="table dataTable" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </body> </html> I am trying to figure out your code and have some questions: - when I open this it starts immediately with executing, how can I avoid that? - I removed the parameters right from $ikke, $from and $to but it won't work anymore, maybe that has to do with my 1st question? Thank you very much for your help, I appreciate it a lot. I need to be able to choose both dates, none should be "fixed". Edited May 24, 2023 by Erwin007 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 24, 2023 Share Posted May 24, 2023 my comments apply to all the code. since barand started with your code and added things to demonstrate how to make it work - input conditioning, default input values, a prepared query, a working get method, sticky, search form, and left the remainder as is, yes, these comments apply to the result of adding these programming practices to the starting code. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 24, 2023 Share Posted May 24, 2023 Remove the default values and only process if ikke and dates have been provided. Revised: <?php require 'db_inc.php'; // USE YOUR OWN $con = myConnect('db2'); // CONNECTION CODE $ikke = $_GET['ikke'] ?? ''; $from = $_GET['start'] ?? ''; $to = $_GET['to'] ?? ''; $tdata = ""; if (!empty($from) && !empty($to) && !empty($ikke)) { $query = " 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 ? AND ?) 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 ? AND ?) AND salesdetails.salesdetails_productid IN (41,50,51,52,53,54,55,56) GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id "; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { background-color: black; color: white; } td { text-align: right; } label { background-color: black; color: white; border: 1px solid white; display: inline-block; width: 80px; padding: 4px; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <form method="GET"> <div class="col col-md-2"> <label for='ikke'>Margin %:</label> <input type="text" value="<?= $ikke ?>" name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label for='start'>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </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"> </div> <div> <input type='submit'> </div> </form> <div class="app-content content "> <table border='1' class="table dataTable" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </body> </html> Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 5 minutes ago, mac_gyver said: my comments apply to all the code. since barand started with your code and added things to demonstrate how to make it work - input conditioning, default input values, a prepared query, a working get method, sticky, search form, and left the remainder as is, yes, these comments apply to the result of adding these programming practices to the starting code. Ok, thanks, I have already followed your lead. 👍 Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 31 minutes ago, Barand said: Remove the default values and only process if ikke and dates have been provided. Revised: <?php require 'db_inc.php'; // USE YOUR OWN $con = myConnect('db2'); // CONNECTION CODE $ikke = $_GET['ikke'] ?? ''; $from = $_GET['start'] ?? ''; $to = $_GET['to'] ?? ''; $tdata = ""; if (!empty($from) && !empty($to) && !empty($ikke)) { $query = " 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 ? AND ?) 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 ? AND ?) AND salesdetails.salesdetails_productid IN (41,50,51,52,53,54,55,56) GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id "; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } th { background-color: black; color: white; } td { text-align: right; } label { background-color: black; color: white; border: 1px solid white; display: inline-block; width: 80px; padding: 4px; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <form method="GET"> <div class="col col-md-2"> <label for='ikke'>Margin %:</label> <input type="text" value="<?= $ikke ?>" name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label for='start'>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </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"> </div> <div> <input type='submit'> </div> </form> <div class="app-content content "> <table border='1' class="table dataTable" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </body> </html> Unfortunately I can't get it to work; it looks like it does nothing after pressing submit. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 24, 2023 Share Posted May 24, 2023 It was working when it left the shop. Have you entered the Margin and dates? Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 7 minutes ago, Barand said: It was working when it left the shop. Have you entered the Margin and dates? "Yours" works, my (little) adapted version does not, I will keep looking for my error. Thanks. Quote Link to comment Share on other sites More sharing options...
dodgeitorelse3 Posted May 24, 2023 Share Posted May 24, 2023 What is your "little adapted version"? Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 (edited) 31 minutes ago, dodgeitorelse3 said: What is your "little adapted version"? The code from Barand needs to "fit in" the Dashboard template and that I simply can not get to work (after so many hours)...☹️ I came to the conclusion that I spend my money on the wrong programmer after you guys came up with your corrections and solutions. I don't want to put all the code here and let you guys do all the work, but after more than 50hours total working on this I am at the point of given up. Also the code is for a company and I can not just put it online so I have to redact a lot everytime. I wish I had checked for this forum before I went to freelancer.com, but it is what it is. Here is my code: <?php include "header.php"; $ikke = $_GET['ikke'] ?? ''; $from = $_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $tdata = ""; if (!empty($from) && !empty($to) && !empty($ikke)) { $query = " 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 ? AND ?) 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 ? AND ?) AND salesdetails.salesdetails_productid IN (41,56,55,54,53,52,51,50) GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id "; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } td { text-align: right; } .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"> <div class="col col-md-1"> <label for='ikke'>Margin %:</label> <input type="text" value="<?= $ikke ?>" name="ikke" id="ikke" class="form-control"> </div> <div class="col col-md-2"> <label for='start'>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </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"> </div> <div class="col col-md-1"> <input type='submit' class="form-control" > </div> </div> </form> </div> </div> </section> </div> </div> <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" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </div> </div> </section> </div> </div> </div> </body> </html> I have the feeling somewhere in the HTML part is my error, could be in the header and/or the footer, I am really at a loss. Edited May 24, 2023 by Erwin007 Quote Link to comment Share on other sites More sharing options...
Erwin007 Posted May 24, 2023 Author Share Posted May 24, 2023 (edited) I got it!!!! It was this start instead of from part in the input!!!! At least I now can change all the other reports and after that I will definitely have some other problems...but that's for later. Thanks again everybody for your help and advice. Edited May 24, 2023 by Erwin007 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 24, 2023 Share Posted May 24, 2023 I fixed the start/from issue in my last code Input HTML My revised code Yours Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.