14 Replies Latest reply on Jan 13, 2018 2:13 PM by Prince Mike

    Advanced Search form using PHP and MySQL

    Prince Mike Level 1

      Hello All,

       

      Please could anyone who has succeeded in developing an advanced search form be of help to me here.

       

      For days now, I have been trying to write a MySQL code to filter records based on one or two or all criterias but I have not succeeded at all.

       

      When I use the AND operator, it works perfectly but when I use OR, I get wrong result.

       

      Below is my code for 3 fields which includes From, To dates and Revenue Categories. I am yet to add filters for Revenue Head's, Payment Channels and Banks.

       

      SELECT pmt_id, pmentcode, revcat, revitem, FORMAT(amnt, 2) AS Amt, bnkname, DATE_FORMAT(tbl_payments.pdate,'%M %e, %Y') AS DatePaid

      FROM tbl_payments

      WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate)

      ORDER BY revcat, revitem ASC

       

      When I select a Revenue Category and date range using ONLY WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate), I get the right result, i.e. the selected Revenue Category within the selected date range.

       

      But when I select the same Revenue Category and date range using

      WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate), I dont get the same result. Rather the system filters out all records of the selected Revenue Category irrespective of the selected date range. It shows all records of the selected Revenue Category from the first date of entry (February 2017) to the last date of entry (January 2018) meanwhile my selected date range is From: January 11 - To: January 11 2018.

       

      When I try WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (pdate >= sdate AND pdate <= edate), It shows all records within the selected date range From: January 11 - To: January 11 2018 irrespective of the selected Revenue Category. It, however, does filter out the records of the Revenue Category but it also adds other Revenue Categories that were not selected.

       

      Please I need help on how to code the system to filter out the selected options alongside with the selected date range. Please note that Range Range selection is mandatory in all the search which means that even if you a searching for only one option, say a Bank or you are searching for a multiple of 2 or 3 different items, say Revenue Category, Payment Channel and a Bank, a date range must be included.

       

      Thank you

        • 1. Re: Advanced Search form using PHP and MySQL
          B i r n o u Adobe Community Professional

          did you try using the right pair of parenthese in within the second OR statement... because you use an OR and an AND

          OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate)

          • 2. Re: Advanced Search form using PHP and MySQL
            Prince Mike Level 1

            Yes this is how it is in my SQL statement OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate). Here is the full line

             

            WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate)

            • 3. Re: Advanced Search form using PHP and MySQL
              B i r n o u Adobe Community Professional

              say that your query is

              WHERE (first statement) OR (second statement)

              what I mean is that in the very first statement you just have AND boolean... so whatever the way it will get vback expected reading

              but in the second statement you have three condition separate by two different boolean... so just to be sure to read what you expect did you try using parentheses to be sure...

              as

              (condition 1 OR condition 2 AND condition 3)

              could lead to different reading depending on what you are expecting

              ((condition 1 OR condition 2) AND condition 3)

              vs

              (condition 1 OR (condition 2 AND condition 3))
              • 4. Re: Advanced Search form using PHP and MySQL
                osgood_ Level 8

                You say that its mandatory for the date range to be supplied so you must have a fail-safe mechanism in place for that to be provided.

                 

                You must have something like:

                 

                $sdate = $_POST['sdate'];

                $edate = $_POST['edate'];

                $revenue_category = $_POST['$revenue_category'];

                $banks = $_POST['banks'];

                 

                $query = "SELECT * FROM tbl_payments WHERE pdate >= '$sdate' AND pdate <= '$edate'";

                 

                Then treat your other form data as optional - adding them to the query if they are 'isset'

                 

                if(isset($revenue_category)){

                $query .= "AND revenue_category LIKE '%".$revenue_category."%'";

                }

                if(isset($banks)){

                $query .= "AND banks LIKE '%".$banks."%'";

                }

                • 5. Re: Advanced Search form using PHP and MySQL
                  Prince Mike Level 1

                  Thank you so much Birnou,

                   

                  I used parentheses. The entire statement (both first and second parts) are exactly like this (first statement) OR (second statement)  but the second part is like you wrote

                   

                  (condition 1 OR condition 2 AND condition 3)

                   

                  as seen below

                   

                  WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (revcat LIKE colrevcat OR pdate >= sdate AND pdate <= edate)***

                   

                  Hope it's wrong right?

                   

                  Should I rewrite it this way

                   

                  ((condition 1 OR condition 2) AND condition 3)

                  vs

                  (condition 1 OR (condition 2 AND condition 3))

                   

                   

                   

                  ***Please also note that pdate >= sdate AND pdate <= edate is date range

                  • 6. Re: Advanced Search form using PHP and MySQL
                    Prince Mike Level 1

                    Hi Osgood,

                     

                    Thank you for your response.

                     

                    That is another awesome dimension.

                     

                    Please this is a new aspect of coding to me. I don't want to do trial an error in this. Could you please help me write the code in full so I just copy and paste and then make a few modifications where necessary.

                     

                    The other two optional fields are $revitem for Revenue Items and $pmthod for Payment Method.

                     

                    $sdate = $_POST['sdate'];

                    $edate = $_POST['edate'];

                    $revenue_category = $_POST['$revenue_category'];

                    $banks = $_POST['banks'];

                     

                    $query = "SELECT * FROM tbl_payments WHERE pdate >= '$sdate' AND pdate <= '$edate'";

                     

                    if(isset($revenue_category)){

                    $query .= "AND revenue_category LIKE '%".$revenue_category."%'";

                    }

                    if(isset($banks)){

                    $query .= "AND banks LIKE '%".$banks."%'";

                    }

                    if(isset($revitem)){

                    $query .= "AND revitem LIKE '%".$revitem."%'";

                    }

                    if(isset($pmthod)){

                    $query .= "AND pmthod LIKE '%".$pmthod."%'";

                    }

                     

                    Thank you

                    • 7. Re: Advanced Search form using PHP and MySQL
                      osgood_ Level 8

                      I can show you a simplified version of your workflow.

                       

                      I suggest you make a new table in your database for testing purposes. Call it tbl_payment_test

                       

                      Populate that with 5 fields id, pdate, revitem, pmthod, banks

                       

                      Assign type date to the pdate column, the others can be varchar apart from the id which is the primary key

                       

                      Populate the columns fields with some test data. In the test form in the code below you MUST of course enter the date in date format 2018-01-12 (I assume you will have a datepicker in your own option form field)

                       

                      Put the code below in a new DW document, change the conn details in the database connection string to those of your own and start searching.

                       

                       

                      <?php

                      //connect to database

                      $conn = new mysqli('server_name , 'username' , 'password' , 'database_name');?>

                      <?php

                      if(isset($_POST['submit'])){

                      //get data from form fields

                      $sdate = $_POST['sdate'];

                      $edate = $_POST['edate'];

                      $pmthod = $_POST['pmthod'];

                      $banks = $_POST['banks'];

                      $revitem = $_POST['revitem'];

                      // query database table - tbl_payment_test

                      $query = "SELECT * FROM tbl_payment_test WHERE pdate >= '$sdate' AND pdate <= '$edate'";

                      if(isset($pmthod)){

                      $query .= "AND pmthod LIKE '%".$pmthod."%'";

                      }

                      if(isset($banks)){

                      $query .= "AND banks LIKE '%".$banks."%'";

                      }

                      if(isset($revitem)){

                      $query .= "AND revitem LIKE '%".$revitem."%'";

                      }

                      //get number of rows

                      $num_rows = $conn->query($query)->num_rows;

                      //assign results to a variable

                      $listResults = $conn->query($query) or die($conn->error);

                      }

                      ?>

                      <h3>Search Database</h3>

                      <form name="search_database" method="post" action="">

                      <p><label for="sdate">Start Date</label>

                      <input type="text" name="sdate">

                      </p>

                      <p><label for="edate">End Date</label>

                      <input type="text" name="edate">

                      </p>

                      <p><label for="pmthod">Payment Method</label>

                      <input type="text" name="pmthod">

                      </p>

                      <p><label for="banks">Banks</label>

                      <input type="text" name="banks">

                      </p>

                      <p><label for="revitem">Revenue Item</label>

                      <input type="text" name="revitem">

                      </p>

                      <input type="submit" name="submit" value="Search" />

                       

                      </form>

                       

                      <?php if(isset($num_rows) && $num_rows > 0) {

                      echo "<h2>Results ($num_rows)</h2>";

                      }

                      ?>

                      <?php while($row = $listResults->fetch_assoc()) { ?>

                      <?php echo "<p>Revenue Items: ".$row['revitem']."</p>"; ?>

                      <?php echo "<p>Payment Method: ".$row['pmthod']."</p>"; ?>

                      <?php echo "<p>Banks: ".$row['banks']."</p>"; ?>

                      <?php } ?>

                       

                      <?php if(isset($num_rows) && $num_rows == 0) {

                      echo "<h2>Sorry No Results Found</h2>";

                      }

                      2 people found this helpful
                      • 8. Re: Advanced Search form using PHP and MySQL
                        Prince Mike Level 1

                        Thank you so much.

                         

                        I will do all you have suggested ASAP and let you know the outcome.

                         

                        Mike

                        1 person found this helpful
                        • 9. Re: Advanced Search form using PHP and MySQL
                          B i r n o u Adobe Community Professional

                          so try

                           

                          WHERE (revcat LIKE colrevcat AND pdate >= sdate AND pdate <= edate) OR (revcat LIKE colrevcat OR (pdate >= sdate AND pdate <= edate))

                           

                          I have isolate the range date in between parenthese to be chanllenged by the first condition

                          • 10. Re: Advanced Search form using PHP and MySQL
                            Prince Mike Level 1

                            Hi osgood_,

                             

                            Your solution gave me the expected result. Thank you so much.

                             

                            Just an addition sir, please how do I sum the total payments for the selected date period just like the system was able to calculate the total result for the selected date range;

                             

                            Results (81)

                             

                            using the code

                             

                            <?php if(isset($num_rows) && $num_rows > 0) {

                            echo "<h2>Results ($num_rows)</h2>";

                            }

                            ?>

                             

                            I have a field called amount in the table. This field captures the amount paid per transaction. Now for each date period selected in the search, the system should be able to sum the total amount of money paid in within that date range like this

                             

                            Total Amount*: $ 12,029,449.45

                             

                            What code would I use to capture this total please.

                             

                            Thank you

                            • 11. Re: Advanced Search form using PHP and MySQL
                              osgood_ Level 8

                              Add the code marked in red below:

                               

                              <?php if(isset($num_rows) && $num_rows > 0) {

                              echo "<h2>Results ($num_rows)</h2>";

                              }

                              ?>

                              <?php $amount = 0; ?>

                              <?php while($row = $listResults->fetch_assoc()) { ?>

                              <?php echo "<p>Revenue Items: ".$row['revitem']."</p>"; ?>

                              <?php echo "<p>Payment Method: ".$row['pmthod']."</p>"; ?>

                              <?php echo "<p>Banks: ".$row['banks']."</p>"; ?>

                              <?php $amount = $amount + $row['amount']; ?>

                              <?php } ?>

                               

                              <?php echo "<p>Total Amount: $ ".number_format((float)$amount, 2, '.', '')."</p>"; ?>

                               

                               

                               

                              <?php if(isset($num_rows) && $num_rows == 0) {

                              echo "<h2>Sorry No Results Found</h2>";

                              }

                              ?>

                              2 people found this helpful
                              • 12. Re: Advanced Search form using PHP and MySQL
                                Prince Mike Level 1

                                Hi osgood_,

                                 

                                You are too much. Its working well.

                                 

                                However, please how do I place this line of code

                                 

                                <?php echo "<p>Total Amount: $ ".number_format((float)$amount, 2, '.', '')."</p>"; ?>

                                 

                                that shows the Total Amount directly below this line code

                                 

                                <?php if(isset($num_rows) && $num_rows > 0) {

                                echo "<h2>Results ($num_rows)</h2>";

                                }

                                ?>

                                 

                                that shows the Total Result.

                                 

                                So i can have something like this

                                 

                                Results (151)

                                Total Amount: $ 12,029,449.45

                                • 13. Re: Advanced Search form using PHP and MySQL
                                  osgood_ Level 8

                                  Im away from my computer until tomorrow but since you cant move the line of code as it wont do anything until the $listResults php while loop has excecuted you will probably need to write another mysql query to handle that independently:

                                   

                                  $sql ="SELECT amount FROM tbl_payment_test WHERE pdate >= '$sdate' AND  pdate <= '$edate'";

                                  $listAmount = $conn->($sql) or die ($conn->error);

                                   

                                  <?php $amount = 0;  ?>

                                  <?php while($row = $listAmount->fetch_assoc()) { ?>

                                  <?php $amount  = $amount + $row['amount']; ?>

                                  <?php } ?>

                                   

                                  <?php echo "<p>Total Amount: $".number_format((float)$amount,2,'.',")."</p>";

                                   

                                   

                                  See if that works, it should do. Hope the code is clean im typing it on a silly little keypad but you get the idea, make that part of the code requirement transportable.

                                  1 person found this helpful
                                  • 14. Re: Advanced Search form using PHP and MySQL
                                    Prince Mike Level 1

                                    Hi Osgood_

                                     

                                    Once more thank you so much. Your codes are unique. I have now the complete solution.

                                     

                                    To anyone who may have similar issues, please find below the complete code as at this time.

                                     

                                    ----- Please note that I modified some part of the code that is peculiar to my case. However, it should work on all cases. I also modified the dbconnection and the code snippet for Total Amount -----

                                     

                                    <?php

                                    //connect to database

                                    $dbHost = 'localhost';

                                    $dbUsername = 'root';

                                    $dbPassword = '';

                                    $dbName = 'databasename';

                                    //connect with the database

                                    $conn = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);

                                    ?>

                                     

                                    <?php

                                    if(isset($_POST['submit'])){

                                    //get data from form fields

                                    $sdate = $_POST['sdate'];

                                    $edate = $_POST['edate'];

                                    $pmthod = $_POST['pmthod'];

                                    $banks = $_POST['banks'];

                                    $revitem = $_POST['revitem'];

                                     

                                    // query database table - tbl_payment_test

                                    $query = "SELECT * FROM tbl_payment_test WHERE pdate >= '$sdate' AND pdate <= '$edate'";

                                     

                                    if(isset($pmthod)){

                                    $query .= "AND pmthod LIKE '%".$pmthod."%'";

                                    }

                                    if(isset($banks)){

                                    $query .= "AND banks LIKE '%".$banks."%'";

                                    }

                                    if(isset($revitem)){

                                    $query .= "AND revitem LIKE '%".$revitem."%'";

                                    }

                                    //get number of rows

                                    $num_rows = $conn->query($query)->num_rows;

                                    //assign results to a variable

                                    $listResults = $conn->query($query) or die($conn->error);

                                    }

                                    ?>

                                     

                                    <?php

                                    if(isset($_POST['submit'])){

                                    //get data from form fields

                                    $sdate = $_POST['sdate'];

                                    $edate = $_POST['edate'];

                                    $sql ="SELECT amount FROM tbl_payment_test WHERE pdate >= '$sdate' AND  pdate <= '$edate'";

                                    $listAmount = $conn->query($sql) or die ($conn->error);

                                    }

                                    ?>

                                     

                                    <h3>Search Database</h3>

                                    <form name="search_database" method="post" action="">

                                    <p><label for="sdate">Start Date</label>

                                    <input type="text" name="sdate">

                                    </p>

                                    <p><label for="edate">End Date</label>

                                    <input type="text" name="edate">

                                    </p>

                                    <p><label for="pmthod">Payment Method</label>

                                    <input type="text" name="pmthod">

                                    </p>

                                    <p><label for="banks">Banks</label>

                                    <input type="text" name="banks">

                                    </p>

                                    <p><label for="revitem">Revenue Item</label>

                                    <input type="text" name="revitem">

                                    </p>

                                    <input type="submit" name="submit" value="Search" />

                                    </form>

                                     

                                    <?php if(isset($num_rows) && $num_rows > 0) {

                                    echo "<h2>Results ($num_rows)</h2>";

                                    }

                                    ?>

                                     

                                    <?php $amount = 0;  ?>

                                    <?php while($row = $listAmount->fetch_assoc()) { ?>

                                    <?php $amount  = $amount + $row['amount']; ?>

                                    <?php } ?>

                                     

                                    <?php echo "<h2>Total Amount: $ ".number_format($amount, 2)."</h2>"; ?>

                                     

                                    <?php while($row = $listResults->fetch_assoc()) { ?>

                                    <?php echo "<p>Revenue Items: ".$row['revitem']."</p>"; ?>

                                    <?php echo "<p>Payment Method: ".$row['pmthod']."</p>"; ?>

                                    <?php echo "<p>Banks: ".$row['banks']."</p>"; ?>

                                    <?php } ?>

                                     

                                    <?php if(isset($num_rows) && $num_rows == 0) {

                                    echo "<h2>Sorry No Results Found</h2>";

                                    }