Default load of all data then filter table with form

Question

I have a page that loads invoices for clients. I added a date picker to search by date but when the page loads it gives no data until a filter is applied from a form. I want the page to load with all data first then if user wants to filter by date, they can select that.

 <form method='post' action='invoices.php' class='sdate'>



    <div class="sdate">

      <input type="text" name="sdate" placeholder="Start Date" >
    </div>


        <div class="sdate">
      <button type="submit" class="sdatebtn">Submit</button>
    </div>


    </form>

database query

                FROM imw_locations l
                INNER JOIN vzw_invoice i
                ON l.AddressCode=i.ItemCustomerFullName
                WHERE l.owner='" . $owner . " ' AND Txndate='".$sdate."'

Full Data of query

<head>


</head>

<?php

session_start();
if (!isset($_SESSION['loggedin'])) {
    header('Location: /index.php');
    exit();
}



?>
<?php 

include "config.php";
$owner = $_SESSION['role'];


$sdate = $_POST['sdate'];
$edate = $_POST['edate'];


?>
<?php include "../../assets/navbar.php" ?>
<div>
<?php echo $sdate;?>
<!----    Date Selections    ---->
 <form method='post' action='invoices.php' class='sdate'>



    <div class="sdate">

    <input type="text" name="sdate" placeholder="Start Date">
    </div>
    <div class="sdate">

    <input type="text" name="edate" placeholder="End Date" >
    </div>


        <div class="sdate">
      <button type="submit" class="sdatebtn">Submit</button>
    </div>


    </form>







<!---- This is the download form ---->
 <form method='post' action='../finance/export/download.php'>
  <input type='submit' value='Export' name='Export'>

  <table class="invoice">
  <thead>
    <tr>

<th>Reference Number</th>
<th>Memo</th>
<th>Trans. Date</th>
<th>Location</th>
<th>Item Amount</th>
<th>Quantity</th>
<th>Vendor</th>

    </tr>
    </thead>
<?php 

     $owner = $_SESSION['role'];
     $query = "SELECT l.AddressCode, 
                l.owner,
                l.City, 
                l.State, 
                l.Zip, 
                i.RefNumber,
                i.memo,
                i.Txndate,
                i.DueDate,
                i.ItemCustomerFullName,
                i.PO_number ,
                i.ItemAmount, 
                i.PO_number,
                i.preorder_,
                i.Quantity,
                i.Vendor,
                i.balance
                FROM imw_locations l
                INNER JOIN vzw_invoice i
                ON l.AddressCode=i.ItemCustomerFullName
                WHERE l.owner='" . $owner . " ' 
                AND Txndate='".$sdate."'


                ";
     $result = mysqli_query($con,$query);
     $user_arr = array();
     while($row = mysqli_fetch_array($result)){
      $ref = $row['RefNumber'];
      $memo = $row['memo'];
      $Txndate = $row['Txndate'];
      $duedate = $row['DueDate'];
      $ItemCustomerFullName = $row['City'];
      $PO_number = $row['PO_number'];
      $ItemAmount = $row['ItemAmount'];
      $preorder_ = $row['preorder_'];
      $Quantity = $row['Quantity'];
      $Vendor = $row['Vendor'];
      $balance = $row['balance'];

   ?>
      <tr>

       <td><?php echo $ref; ?></td>
       <td><?php echo $memo; ?></td>
       <td><?php echo $Txndate; ?></td>
       <td><?php echo $ItemCustomerFullName; ?></td>
       <td><?php echo $ItemAmount; ?></td>
       <td><?php echo $Quantity; ?></td>
       <td><?php echo $Vendor; ?></td>


      </tr>
   <?php
    }
   ?>
   </table>
   <?php 
    $serialize_user_arr = serialize($user_arr);
   ?>
  <textarea name='export_data' style='display: none;'><?php echo $serialize_user_arr; ?></textarea>
 </form>


</div>

Could you show the code that queries the database? Basically, you need to see if date is set, and then include it in the `WHERE` clause of the DB query. What is probably happening is that you query where date is NULL, which returns empty — Ice76, Oct 17 '19 at 17:36
a sidenote. `array($ref,$memo,$Txndate,$duedate,$ItemCustomerFullName,$PO_number,$ItemAmount,$preorder_,$Quantity,$Vendor,$balance);` is essentially equal to `$row`. You can get rid of a good screenful of useless code — Your Common Sense, Oct 17 '19 at 17:43
@YourCommonSense haha yes thank you. I actually had that in from a previous script and neglected to remove it. Thank you — , Oct 17 '19 at 17:46
Also the BETWEEN does not work for me. Should I ask in separate thread? — , Oct 17 '19 at 17:46
between is likely not working because $_POST['edate'] doesn't seem to have a counterpart form field. I am also puzzled why echo $email; in the sdate field. The code looks like a Frankenstein build of several random code parts. is it? — Your Common Sense, Oct 17 '19 at 18:20
I got the BETWEEN working with this: pastebin.com/HFvd6w7Q @YourCommonSense its not really frankensteined lol. I have a few things I have done on my server that I am using similar script. — , Oct 17 '19 at 18:56
Please don't make more work for others by vandalizing your posts. By posting on the Stack Exchange (SE) network, you've granted a non-revocable right, under a [CC BY-SA license](//creativecommons.org/licenses/by-sa/4.0), for SE to distribute the content (i.e. regardless of your future choices). By SE policy, the non-vandalized version is distributed. Thus, any vandalism will be reverted. Please see: [How does deleting work? …](//meta.stackexchange.com/q/5221). If permitted to delete, there's a "delete" button below the post, on the left, but it's only in browsers, not the mobile app. — Makyen, Oct 18 '19 at 16:24

score 2 · Answer 1 · answered Oct 17 '19 at 17:56

2

This is tough to answer as the code you posted does not line up with the query you mentioned above it.

The simplest solution would be to set a variable with a string, and append to it if the date is set. This is a simple solution, as you get more conditions, you can make an array and then do an implode on it, but that could be added later. An example:

$whereClause = "WHERE l.owner = '{$owner}'";
if ( ! empty( $sdate) && $sdate ) {
    $whereClause .= " AND Txndate = '{$sdate}'";
}

As far as your BETWEEN clause, try switching the dates. I am assuming that $edate is end date and $sdate is start date, and the between clause takes starting date first.

Also, PLEASE read this. You have clear examples of security holes, big ones. You are taking user input and directly injecting it into the DB query. That is a HUGE security hole, and the input needs to be verified and cleaned.

answered Oct 17 '19 at 17:56

Ice76

1,143
8
16

3

Would you accept a challenge of writing a secure version? – Your Common Sense Oct 17 '19 at 18:07
I have updated my OP to include all of invoice.php file. Hope this helps. I tried the above but that did not work for me. This gave me a blank page. – Oct 17 '19 at 18:13
@DavidMorin you have to realize that an answer is not supposed to be a deliberately working example that you just mindlessly copy and paste into your code. You are rather supposed to *understand* the way it works and *implement* in your environment. – Your Common Sense Oct 17 '19 at 18:15
1

@YourCommonSense I would! – Ice76 Oct 17 '19 at 18:21
@DavidMorin Please check for error and why the blank page happened. You could also tell me how you implemented my code and I can look, but you should first understand why you got a blank page – Ice76 Oct 17 '19 at 18:22
@YourCommonSense Your absolutely correct. In what way would I implement the above code into my example. I am not understanding how to place this. – Oct 17 '19 at 18:30
1

@DavidMorin Hey, take a look at this. I adjusted it to comply with your between clause, but what it does is determine what the `WHERE` should be, then just paste it into the query. https://pastebin.com/NxznBuEL – Ice76 Oct 17 '19 at 21:00
@Ice76 Close to mine but yours is much cleaner. Thank you sir for that. – Oct 17 '19 at 22:05

Default load of all data then filter table with form

1 Answers1