Constructing mysql field search from form posting where some values may be empty.

[afletcher187]

Hello to all at PHPbuilder, I am a newbie to PHP 😕 but hope someone can help with this problem.

I have a database that stores packhouse product movements. i.e

Tables are:
Deliveries, packing process and Storage and finally Dispatch

One of the query pages I have put together needs to have a search capability to analyse data:

User is offer: Date, Start, Finish, Customer, Source, Location, PackDate, Checkedby.

Now the user may only choose to enter a Date and a customer
(Just wants to know whether a customer order was dispatched on the given date perhaps)

How can I build the $query = string that looks for existance of values and then only includes those in the $Query string......?

Hope someone can help me as I have been pondering this for sometime with any real success.
Thanks Fletch

[CoderDan]

assuming basic mysql - may be different with mysqli:

$wheres = array();
if ( isset( $_POST['int_filter'] ) && !empty( $_POST['int_filter'] ) // assuming no 0 values allowed
{
  $wheres[] = "filter1 = ".intval( $_POST['int_filter'] );
}
if ( isset( $_POST['string_filter'] ) && !empty( $_POST['string_filter'] )
{
  $wheres[] = "filter1 = '".mysql_real_escape_string( $_POST['int_filter'] )."'";
}
// etc
$query = "SELECT * FROM table ";
if ( count( $wheres ) )
{
  $query .= "WHERE ".join( ' AND ', $wheres );
}
$query .= "ORDER BY whatever ";

[afletcher187]

Hi CoderDan, many thanks for your help[ I will give this a whirl over the next couple of days and let you know how it goes. Much appreciate the time taken to provide me with help.

Best Regards
Fletch

[afletcher187]

Hi CoderDan, I have been looking at your kindly offer code solution to my original question but am having difficulty getting it clear in my somewhat dim brain :o

Could I be a complete pain and ask for a little explanation / comments on how the construct is happening... ?

My understanding is this:

$wheres = array(); <---- this sets the Variable as an array so we can "add values or "conditions" for my query ?

Is the "int_filter" in your line " If ( isset($POST['int_filter'] ) && !empty( $POST['int_filter] )
where I have ['Submit'] ? and do I just replace that bit
or
Is this intended to be a Field name posted to PHP ?

Interms of getting all the conditions into the $wheres variable I guess this is being done with:
if ( Count( $wheres ) )
{
$Query .="WHERE ".join( ' AND ', $wheres );

How am I doing ? Miles away I bet 😕

Thanks for your help
Fletch

[johanafm]

Yes, int_filter is the value of some element (input, select) attribute for a form control, and also indicating that it is supposed to contain a number. Hence the intval(), to ensure that it really will be.

The same line of reasoning goes for the element with a name attribute of 'string_filter', which is supposed to be a string, and hence needs to be escaped (using mysql_real_escape_string()) so it does not contain nasty stuff.

I'm unclear on what you mean with "where I have Submit", but I take it as a question regarding wether this is sent when the user clicks submit, and the answer is yes. This code should go wherever you handle the post request for the form.

And you are correct againt. count($wheres) checks if there are any elements (conditions) at all. If not, count returns 0 which evaluates as false (non-zero means true).
The join() gives you: WHERE somecondition = first_element_from_wheres AND someothercodition = second_element_from_wheres

So you seem to be doing fine 🙂
And when in doubt, use echo, print_r or var_dump to see what your variables contains in different places. It usually gives you a very clear picture of exactly what's happening and when.

[afletcher187]

Hi Johanafm, many thanks for your comments they are much appreciate. I guess I just suffer from a lack of confidence really and it is really nice to be able to come to this forum and just double check my understanding from time to time.

Thanks for the advise on echoing the $where value to check on what is being put in it thats a good Idea ands one I have often done in other languages but for some reason I've never done with web based scripting languages ;-)

I will let you all know how this pans out shortly......

And thanks once again for your help....
Fletch

[afletcher187]

Okay I have put together the following script but I keep getting a T_echo error or other error
after the second line... Any idea's what I'm doing wrong there ? I appreciate I have not secured my code with relevant filter yet but just want to get the logic correct thus far ;-)

<?php
include("connect.php");
if (isset($POST['submit']) && !empty( $POST['Submit'] )
{
echo " Formulating query " ;
}
if (isset($POST['Date']) && !empty( $POST['Date'])
{
$Wheres[] = "Filter1 = ".($POST['Date'] );
}
if (isset($POST['Customer']) && !empty( $POST['Customer'])
{
$Wheres[] = "Filter2 = ".($POST['Customer'] );
}
if (isset($POST['Variety']) && !empty( $POST['Variety'])
{
$Wheres[] = "Filter3 = ".($POST['Variety'] );
}
if (isset($POST['PalletNo']) && !empty( $POST['PalletNo'])
{
$Wheres[] = "Filter4 = ".($POST['PalletNo'] );
}

$query = "SELECT * FROM Packing";
if ( count( $Wheres ))
{
$query .= "WHERE ".join( ' AND ', $Wheres );
$query .= "ORDER BY ID ";
}
$Result = mysql_query($query) or die(Mysql_error("Data Error"));

[johanafm]

You're missing a closing parentheses on all your if statements.

[afletcher187]

Many thanks johanafm.. I got that now .... is the rest of the code basically correct ?

Kind Regards
Fletch

[afletcher187]

Okay so we have no Syntax issues with that part of my code but boo hoo it did not get me any results so What have I done wrong with the next piece of the puzzle ??

Code is:
$query = "SELECT * FROM Packing";
if ( count( $Wheres ))
{
$query .= "WHERE ".join( ' AND ', $Wheres );
$query .= "ORDER BY ID ";
}
$Result = mysql_query($query) or die(Mysql_error("Data Error"));

echo '<FONT SIZE="2">';
echo "<table width=1105 border='1' cellspacing='0' cellpadding='0' bgcolor='#C1DDC4' >";
echo "<tr> <th>ID</th> <th>Date</th> <th>Pickdate</th> <th>Start</th> <th>Finish</th> <th>Pallet No</th> <th>Source</th> <th>Field ID</th> <th>Variety</th> <th>Customer</th> <th>Class</th> <th>Pack Size</th> <th>Total Packs</th> <th>Total Kgs</th> <th>Checked By</th> </tr>";
echo '</FONT>';
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $Result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['ID'];
echo "</td><td>";
echo $row['Date'];
echo "</td><td>";
echo $row['Pickdate'];
echo "</td><td>";
echo $row['Start'];
echo "</td><td>";
echo $row['Finish'];
echo "</td><td>";
echo $row['PalletNo'];
echo "</td><td>";
echo $row['Source'];
echo "</td><td>";
echo $row['Field_ID'];
echo "</td><td>";
echo $row['Variety'];
echo "</td><td>";
echo $row['Customer'];
echo "</td><td>";
echo $row['Class'];
echo "</td><td>";
echo $row['Pack_Size'];
echo "</td><td>";
echo $row['Total_Packs'];
echo "</td><td>";
echo $row['Total_Kgs'];
echo "</td><td>";
echo $row['Checked'];
echo "</td></tr>";
}
echo "</table>";
?>

[johanafm]

Well, I'm assuming you want to check the user supplied dates against the values stored in a field among Date, Start, Finish, Customer, Source, Location, PackDate and Checkedby respectively.

So the following

if (isset($_POST['Customer']) && !empty( $_POST['Customer'])
{
$Wheres[] = "Filter2 = ".($_POST['Customer'] );
}

Should be

if (isset($_POST['Customer']) && !empty( $_POST['Customer']))
{
$Wheres[] = "Customer = ".($_POST['Customer'] );
}

and so on...

[Weedpacket]

Incidentally, if it's not set then it is empty. Conversely, if it's not empty then it is set. So

 if (isset($_POST['Customer']) && !empty( $_POST['Customer']))

could just be

if(!empty($_POST['Customer']))

[afletcher187]

Now I thought that I should be replacing "Filter" in your example with the names of the fields but for some reason that now I can't even remember, I decided to put in the Filter1, filter2.....
What a plonker I am... Thanks guys for your help and apologies for late reply as I was away all weekend.....

Fletch

[afletcher187]

Okay guys just a final bit of the jigsaw here, The query builds nicely but I am left with conditions that don't have the necessary '' wrapper that Mysql needs to execute a query.

Mysql eg = Select* From Packing Where Customer ='Marks' AND Variety = 'Elsanta' ;

our query does not wrap Marks or Elsanta

I've tried to play about with this but no joy.... How do I achieve this.....

The code again is:
<?php
include("connect.php");
$Wheres = array();
if (isset($POST['submit']) && !empty( $POST['Submit'] ))
{
echo " Formulating query " ;
}
if (isset($POST['Date']) && !empty( $POST['Date']))
{
$Wheres[] = "Date = ".($POST['Date'] );
}
if (isset($POST['Customer']) && !empty( $POST['Customer']))
{
$Wheres[] = "Customer = ".($POST['Customer'] );
}
if (isset($POST['Variety']) && !empty( $POST['Variety']))
{
$Wheres[] = "Variety = ".($POST['Variety'] );
}
if (isset($POST['PalletNo']) && !empty( $POST['PalletNo']))
{
$Wheres[] = "PalletNo = ".($POST['PalletNo'] );
}

$query = "SELECT * FROM Packing";
if ( count( $Wheres ))
{
$query .= " WHERE ".join( ' AND ', $Wheres );
$query .= " ORDER BY ID; ";
echo $query;
$Result = mysql_query($query) or die(Mysql_error("Data Error"));

[johanafm]

This is why CoderDan called his keys int_filter (for integers) and string_filters (for strings, dates). Notice the single quote before and after the string_filter handling which is not there for the int.

CoderDan;10924183 wrote:

if ( isset( $POST['int_filter'] ) && !empty( $POST['int_filter'] ) // assuming no 0 values allowed
{
$wheres[] = "filter1 = ".intval( $POST['int_filter'] );
}
if ( isset( $POST['string_filter'] ) && !empty( $POST['string_filter'] )
{
$wheres[] = "filter1 = '".mysql_real_escape_string( $POST['string_filter'] )."'";

[afletcher187]

Ohhhh Dam & Blast !!!! trying massively to hide my embarassment guys but thanks so much that it ... It all works perfectly....

Code blindness staring at this stuff just completely missed the quotes shown above.

Hope I can eventually be half the help you guys have been for some other newbie one day ;-)

Fletch 🙂