How to pass parameter from HTML to PHP?

Erwin007 · May 22, 2023

Hi,

I have this code:

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

ginerjm · May 22, 2023

Horrible arrangement of code.

No table header or closer

No submit button

Script in the wrong place.

Erwin007 · May 22, 2023

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";
?>

Edited May 22, 2023 by Erwin007

ginerjm · 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)

Barand · 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

cyberRobot · 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.

Barand · 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.

cyberRobot · 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.

Erwin007 · 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.

ginerjm · 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.

Erwin007 · 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.

Barand · 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>

mac_gyver · 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.

Erwin007 · 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?

Erwin007 · May 24, 2023

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

mac_gyver · 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.

Barand · 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>

Erwin007 · 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. 👍

Erwin007 · 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.

Barand · May 24, 2023

It was working when it left the shop. Have you entered the Margin and dates?

image.png.519b9f1c487bcdc1b324f5f235de7e09.png

Erwin007 · 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.

dodgeitorelse3 · May 24, 2023

What is your "little adapted version"?

Erwin007 · May 24, 2023

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

Erwin007 · May 24, 2023

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

Barand · May 24, 2023

I fixed the start/from issue in my last code

Input HTML

image.png.68f116daaaf7fccf9fd9069d0d8191fb.png

My revised code

image.png.828b05778d3a147b03901fbc166f7e27.png

Yours

image.png.69f8a422a18bc6e7a581928833c59d5e.png

Sign In

How to pass parameter from HTML to PHP?

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