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ᴇᴌᴀ
– Sᴀᴍ Onᴇᴌᴀ, Commented Oct 18, 2016 at 1: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
– Nick, Commented Oct 18, 2016 at 2:12
Also, you have payment_type in your WHERE clause twice - I imagine that was not your intent..? — Nick
– Nick, Commented Oct 18, 2016 at 2: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
– Naresh, Commented Oct 18, 2016 at 2:20

Community · Accepted Answer · 2017-05-23 10:32:30Z

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, 2017 at 10:32

CommunityBot

11 silver badge

answered Oct 18, 2016 at 2:32

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Naresh Over a year ago

please check I've updated the query with your suggestions. I'm not getting any result

Nick Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Generating reports based on user input in php

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related