Jump to content

How to pass parameter from HTML to PHP?

Erwin007

By Erwin007
in PHP Coding Help

 Share

Recommended Posts

Erwin007 Regular Member

Erwin007

Posted
Posted

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.

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted (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 by Erwin007
Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

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)

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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

 

Link to comment
Share on other sites
cyberRobot Prolific Member

cyberRobot

Posted
Posted

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.

Link to comment
Share on other sites
Barand Prolific Member

Barand

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

Link to comment
Share on other sites
cyberRobot Prolific Member

cyberRobot

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

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted
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.

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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>

 

Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

some points about the code/query -

  1. when you have a list of ORed terms in a query, use a single IN() comparison instead.
  2. don't put quotes around numbers.
  3. be consistent. one number in the query isn't quoted.
  4. use 'require' for things your php code must have for it to work.
  5. 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.
  6. you should validate your resulting web pages at validator.w3.org
  7. any dynamic value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting.
Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted
23 hours ago, mac_gyver said:

some points about the code/query -

  1. when you have a list of ORed terms in a query, use a single IN() comparison instead.
  2. don't put quotes around numbers.
  3. be consistent. one number in the query isn't quoted.
  4. use 'require' for things your php code must have for it to work.
  5. 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.
  6. you should validate your resulting web pages at validator.w3.org
  7. 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?

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted (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 by Erwin007
Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

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.

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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>

 

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted
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. 👍

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted
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.

Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted (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 by Erwin007
Link to comment
Share on other sites
Erwin007 Regular Member

Erwin007

Posted
  • Author
Posted (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 by Erwin007
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.