Generating reports based on user input in php

Question

I have a form to generate reports. In my form only the from_date and to_date fields are mandatory. If the user selects only from_date and to_date then I need to generate all the sales between those dates. If he/she wants to generate cash & credit wise or party wise or agent wise reports (these fields are on the right side of the form), then I should be able to generate reports customized that way also. I'm not able to write the logic to create the SQL query. Thank You!

HTML:

<form class="row" id="reports" style="width: 100%; margin-bottom: 1%">
    <div class="pull-left clearfix">
        <label for="from" class="lab-sm">From:</label>
        <input type="date" id="from" name="from" value="<?php echo date("2016-09-20"); ?>">
        <label for="to" class="lab-sm">To:</label>
        <input type="date" id="to" name="to" value="<?php echo date("Y-m-d"); ?>">
        <div>
        <label for="to" class="lab-sm">Inv:</label>
            <select name="purchase" id="purchase" class="inp-sm">
                <option value="INV">All</option>
            </select>
        </div>
    </div>
    <div class="pull-right clear-left" style="position: relative;">
        <div>
            <select name="payment" id="payment" class="inp-lg">
                <option value="">Cash & Credit Sales</option>
                <option value="Cash">Cash</option>
                <option value="Credit">Credit</option>
            </select>
        </div>
        <div>
            <select name="party" id="party" class="inp-lg">
                <option value="">-- All Parties --</option>
                <? $query = $con->query("SELECT la_head FROM ledger_accounts"); ?>
                <? while($row = mysqli_fetch_array($query)) { ?>
                <option value="<?php echo $row['la_head']; ?>"><? echo $row['la_head']; ?></option>
                <? } ?>
            </select>
        </div>
        <div>
            <select name="agent" id="agent" class="inp-lg">
                <option value="">-- All Agents --</option>
                <? $query = $con->query("SELECT la_agent FROM ledger_accounts"); ?>
                <? while($row = mysqli_fetch_array($query)) { ?>
                <option value="<?php echo $row['la_agent']; ?>"><? echo $row['la_agent']; ?></option>
                <? } ?>
            </select>
        </div>
        <!-- submission -->
        <div style="position: relative; left: 44px">
            <input type="submit" value="Generate">
            <input type="hidden" name="reports" value="sales_reports">
        </div>
    </div>
</form>

PHP:

if (ISSET($_POST['reports']) && $_POST['reports']  === 'sales_reports') {

$from_date = $con->real_escape_string($_POST['from']);
$to_date = $con->real_escape_string($_POST['to']);
$payment_type = $con->real_escape_string($_POST['payment']);
$party = $con->real_escape_string($_POST['party']);
$agent = $con->real_escape_string($_POST['agent']);

$query = "SELECT *
        FROM sales
        INNER JOIN ledger_accounts ON sales.la_id = ledger_accounts.la_id
        INNER JOIN inventory_items ON sales.item_no = inventory_items.item_no
        WHERE inv_date >= ? AND inv_date <= ?
              AND sales.payment_type = COALESCE(?, sales.payment_type) 
              AND ledger_accounts.la_head = COALESCE(?, ledger_accounts.la_head)
              AND ledger_accounts.la_agent = COALESCE(?, ledger_accounts.la_agent)";
    $stmt = $con->prepare($query);
    $stmt->bind_param('sssss', $from_date, $to_date, $payment_type, $party, $agent);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        echo '<tr>';
        echo '<td>'.$row['inv_date'].'</td>';
        echo '<td>'.$row['inv_no'].'</td>';
        echo '<td>'.$row['la_head'].'</td>';
        echo '<td>'.$row['la_address'].'</td>';
        echo '</tr>';
}

wait -that is confusing - you typed `I don't know how to write the query.` and then shortly after: `Here is my Query`...are you looking for a query, or something else (e.g. logic)? — Sᴀᴍ Onᴇᴌᴀ, Oct 18 '16 at 01:59
You have not clearly stated your problem. What is wrong/not working with your current code (other than it is likely susceptible to SQL-injection attacks)? — Nick, Oct 18 '16 at 02:12
Also, you have `payment_type` in your `WHERE` clause twice - I imagine that was not your intent..? — Nick, Oct 18 '16 at 02:14
@Nicarus I need to generate the reports based on the user input. See the form below in the image, only `from_date` and `to_date` are mandatory. and remaining fields are optional so how to prepare the query for this logic? — Naresh, Oct 18 '16 at 02:20
Are you asking how to handle the cases where `$payment_type`, `$party`, and/or `$agent` are `NULL` (not selected by the user)? — Nick, Oct 18 '16 at 02:21

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

If you are expecting that you may not get a value for all parameters, you can do the following using COALESCE():

$query = "SELECT *
    FROM sales
    INNER JOIN party ON sales.party_id = party.party_id
    INNER JOIN items ON sales.item_no = items.item_no
    WHERE inv_date >= ? AND inv_date <= ?
          AND payment_type = COALESCE(?,payment_type) 
          AND party = COALESCE(?,party)
          AND agent = COALESCE(?,agent);";

$statement = $dbConn->prepare($query);
$statement->bind_param('sssss',$from_date,$to_date,$payment_type,$party,$agent);

COALESCE() works by providing the first non-null value. So, if the parameter passed in is NULL, then the above will just match whatever is in the field you are filtering on (party = party).

You want to parameterize you query, too, as to avoid potential SQL-injection attacks. I have applied that to the code above as well.

Good resources:
http://php.net/manual/en/mysqli-stmt.bind-param.php

How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:32

Community

1
1

answered Oct 18 '16 at 02:32

Nick

7,103
2
21
43

please check I've updated the query with your suggestions. I'm not getting any result – Naresh Oct 18 '16 at 03:42
Do you know how to step through your code? The query above is correct, so some potential issues would be that the parameters being passed are not null, but maybe an empty string - or - your table simply has no records that match the query. Unfortunately, I cannot debug your website for you. – Nick Oct 18 '16 at 04:06
I don't know what you want me to do with your HTML code. Knowing what is in the tables is more helpful and I am not going to debug your code. – Nick Oct 18 '16 at 15:41

Generating reports based on user input in php

1 Answers1