Advanced Search form using PHP and MySQL

[Prince Mike]

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

[B i r n o u]

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)

[Prince Mike]

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)

[B i r n o u]

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))

[Prince Mike]

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

[B i r n o u]

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

[osgood_]

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."%'";

}

[Prince Mike]

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

[osgood_]

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>";

}

[Prince Mike]

Thank you so much.

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

Mike

[Prince Mike]

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

[osgood_]

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>";

}

?>

[Prince Mike]

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

[osgood_]

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.

[Prince Mike · Accepted solution]

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>";

}