Skip to main content
P
Prince Mike
Inspiring
January 12, 2018
Answered

Advanced Search form using PHP and MySQL

  • January 12, 2018
  • 2 replies
  • 8584 views

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

This topic has been closed for replies.
Correct answer Prince Mike

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.


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

}

2 replies

O
osgood_
Braniac
January 12, 2018

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

}

P
Prince MikeAuthor
Inspiring
January 12, 2018

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

P
Prince MikeAuthorCorrect answer
Inspiring
January 13, 2018

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.


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

}

B i r n o u
B i r n o u
Braniac
January 12, 2018

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)

P
Prince MikeAuthor
Inspiring
January 12, 2018

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
B i r n o u
Braniac
January 12, 2018

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))
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices