PHP for HTML Form in order to filter mysql table search results

[ptara1]

I'm creating a html form that allows the user to search for people in a database. I want the form to work with drop down selects and radio buttons. Currently I'm using an if { } elseif {} else if{} format to my script.

However this causes a problem because I have to set up individual if statements for a multiple of variables. For example: Age is a drop down, so is sex, so is height. The radio buttons, for example, would be yes - smoker, yes-drinks etc.

I end up with a script sort of like this expert (minus the radio buttons) but with more variables and more requests from the database:

if (($ageF == 'No Preference') && ($sexF == 'No Preference') && ($hairF == 'No Preference') && ($DressF == 'No Preference'))

 {
       $result=mysql_query("SELECT * FROM People ORDER by Name") or die(mysql_error());
        }
	else if (( $ageF != 'No Preference') && ($sexF == 'No Preference') && ($hairF == 'No Preference') && ($DressF == 'No Preferencce')) 
{
       $result=mysql_query("SELECT * FROM People WHERE Age='".$ageF."'") or die(mysql_error());



    }

The variables are correct and its working so far. I just know there must be an easier way and would like to learn the correct way to do it.

Any suggestions on how I should set up the php script that handles the html form?

[R0bb0b]

I would do something like this. It's not yet debugged.

<?php
$arrQueryParts = array();
$arrQueryParts["age"] = ""; 
$arrQueryParts["sex"] = ""; 
$arrQueryParts["hair"] = ""; 
$arrQueryParts["Dress"] = ""; 

foreach($arrQueryParts as $key=>&$value)
{
    $strVarName = $key . "F";
    if(strtolower($$strVarName) != 'no preference')
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'";
    }   
}

//your query
$strAddQuery = $arrQueryParts["age"] . $arrQueryParts["sex"] . $arrQueryParts["hair"] . $arrQueryParts["Dress"];
$strQuery = "select * from People " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";
?>

[ptara1]

Can you explain what the parts of the script are doing? This is above my coding level but I would like to understand it.
Also if the age is not no preference, or if sex in not no preference, then the rows would be filtered by what the selected age is and what the selected sex is. The radio buttons act as either a yes or no and if yes then all filtered results would be filtered by the radio buttons.

I would do something like this. It's not yet debugged.

<?php
$arrQueryParts = array();
$arrQueryParts["age"] = ""; 
$arrQueryParts["sex"] = ""; 
$arrQueryParts["hair"] = ""; 
$arrQueryParts["Dress"] = ""; 

foreach($arrQueryParts as $key=>&$value)
{
    $strVarName = $key . "F";
    if(strtolower($$strVarName) != 'no preference')
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'";
    }   
}

//your query
$strAddQuery = $arrQueryParts["age"] . $arrQueryParts["sex"] . $arrQueryParts["hair"] . $arrQueryParts["Dress"];
$strQuery = "select * from People " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";
?>

[R0bb0b]

<?php
//start with an array of possible query portions that will initially be blank associated with the fields in question.  On a side note, in order to use a system like this you need to plan in advance by making sure that your variables are all named accordingly and stick with all upper case or all lower case or you can start with upper or whatever, just make sure that you follow the same pattern for all variables.
$arrQueryParts = array();
$arrQueryParts["age"] = ""; 
$arrQueryParts["sex"] = ""; 
$arrQueryParts["hair"] = ""; 
$arrQueryParts["Dress"] = ""; //the reason that this starts with an upper case letter is because I noticed that you have an upper case letter for this variable. 

//we are going to loop through the array using the key name in as many places that we possible can.  This is where it helps to be uniform.
foreach($arrQueryParts as $key=>&$value)
{
    $strVarName = $key . "F"; //your variables are name $key . "F" so we add an F at the end of the variable name.  This is a string now but we will be using for a variable variable.  for more info on variable variables search google for "php variable variables".
    if(strtolower($$strVarName) != 'no preference') //here we check each of your variables to see if the lower case version of the value is not "no preference".  If it is not "no preference" the we run this code block.
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'"; //each value in our array will hold it's portion of the query that we will be constructing on if the value of its associated variable is not "no preference", other wise it will be a blank string.
    }   
}

//add all of our variable query portions together to inject into our main query
$strAddQuery = $arrQueryParts["age"] . $arrQueryParts["sex"] . $arrQueryParts["hair"] . $arrQueryParts["Dress"];
$strQuery = "select * from People " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";  // here you will notice a ?: statement in the middle of the query, it says that if $strAddQuery is not blank then also include the "where" clause other wise do not.
?>

[ptara1]

I've been trying to get the code to work. Nothing displays but I don't think that has been requested. What would be the best way to display the results of the StrQuery?

<?php
//start with an array of possible query portions that will initially be blank associated with the fields in question.  On a side note, in order to use a system like this you need to plan in advance by making sure that your variables are all named accordingly and stick with all upper case or all lower case or you can start with upper or whatever, just make sure that you follow the same pattern for all variables.
$arrQueryParts = array();
$arrQueryParts["age"] = ""; 
$arrQueryParts["sex"] = ""; 
$arrQueryParts["hair"] = ""; 
$arrQueryParts["Dress"] = ""; //the reason that this starts with an upper case letter is because I noticed that you have an upper case letter for this variable. 

//we are going to loop through the array using the key name in as many places that we possible can.  This is where it helps to be uniform.
foreach($arrQueryParts as $key=>&$value)
{
    $strVarName = $key . "F"; //your variables are name $key . "F" so we add an F at the end of the variable name.  This is a string now but we will be using for a variable variable.  for more info on variable variables search google for "php variable variables".
    if(strtolower($$strVarName) != 'no preference') //here we check each of your variables to see if the lower case version of the value is not "no preference".  If it is not "no preference" the we run this code block.
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'"; //each value in our array will hold it's portion of the query that we will be constructing on if the value of its associated variable is not "no preference", other wise it will be a blank string.
    }   
}

//add all of our variable query portions together to inject into our main query
$strAddQuery = $arrQueryParts["age"] . $arrQueryParts["sex"] . $arrQueryParts["hair"] . $arrQueryParts["Dress"];
$strQuery = "select * from People " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";  // here you will notice a ?: statement in the middle of the query, it says that if $strAddQuery is not blank then also include the "where" clause other wise do not.
?>

[ptara1]

This is my code that doesn't work

<?php

include 'connect.php';

$arrQueryParts = array();
$arrQueryParts['Age'] = $_POST['AgeF']; 
$arrQueryParts['Sex'] = $_POST['SexF']; 
$arrQueryParts['Hair'] = $_POST['HairF']; 
$arrQueryParts['Dress'] = $_POST['DressF']; 
 
foreach($arrQueryParts as $key=>&$value)
{
    $strVarName = $key . "F";
    if(strtolower($strVarName) != 'no preference')
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'";
    }   
}
 
//your query
$strAddQuery = $arrQueryParts['Age'] . $arrQueryParts['Sex'] . $arrQueryParts['Hair'] . $arrQueryParts['Dress'];
$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))
{

$ID = $row['ID'];

$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Age = $row['Age'];

$Sex = $row['Sex'];

$Hair = $row['Hair'];



echo "<table>"; 



echo "<tr><td>";

echo "<a href='www.example.com'>".$Name."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Phone_Number."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Age."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Sex."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Hair."</a></td></tr>";

echo "</table>"; }
?>

I've been trying to get the code to work. Nothing displays but I don't think that has been requested. What would be the best way to display the results of the StrQuery?

[R0bb0b]

first off, echo $strQuery and make sure that it is pieced together correctly. If it looks ok, run it against your database manager like phpmyadmin or whatever you are using to manage your database. Once you get that far and if you haven't figured out what the problem is, post the results of $strQuery so that we can take a look at it.

[ptara1]

Here is what is returned:

select * from people where Age='' Sex='' Hair='' Dress='' order by Name

So I assume the postings from the form are not being inserted into the array..Any idea how I should fix that?

first off, echo $strQuery and make sure that it is pieced together correctly. If it looks ok, run it against your database manager like phpmyadmin or whatever you are using to manage your database. Once you get that far and if you haven't figured out what the problem is, post the results of $strQuery so that we can take a look at it.

[R0bb0b]

put this at the top of the script to display your post array like so:

echo "<pre>" . print_r($_POST, true) . "</pre>";

this will tell us what your post looks like and then we can code accordingly.

[ptara1]

The result shows what was selected for the variables.

Array
(
[AgeF] => BBQ
[SexF] => No Preference
[HairF] => No Preference
[DressF] => No Preference
)

So that means the information is being passed to the script correctly so the error has to be with running the query?

put this at the top of the script to display your post array like so:

echo "<pre>" . print_r($_POST, true) . "</pre>";

this will tell us what your post looks like and then we can code accordingly.

[ptara1]

Update on code: figured out how to get the restriction to show. By making the code this:

foreach($arrQueryParts as $key=>$value)
{ 
    $strVarName = $key . "F";
    if(strtolower($strVarName) != 'no preference')
    {   
       $arrQueryParts[$key] = " AND ".ucwords($key)."='".$value."'";
    }   
}
 
//your query
$strAddQuery = $arrQueryParts['Age'] . $arrQueryParts['Sex'] . $arrQueryParts['Hair'] . $arrQueryParts['Dress'];
echo $strQuery = "select * from people " . (trim($strAddQuery) != ""?"where 1=1 ":"") . $strAddQuery . " order by Name";
 exit;

I get this result:

select * from people where 1=1 AND Age='19' AND Sex='No Preference' AND Hair='No Preference' AND Dress='No Preference' order by Name

Now I want to make it so that when No Preference is selected there is no restriction passed down to the query...

The result shows what was selected for the variables.

Array
(
[AgeF] => BBQ
[SexF] => No Preference
[HairF] => No Preference
[DressF] => No Preference
)

So that means the information is being passed to the script correctly so the error has to be with running the query?

[R0bb0b]

ok, I found a bug but this should fix it. Try this code:

<?php

include 'connect.php';

$arrQueryParts = array();
$arrQueryParts['Age'] = $_POST['AgeF']; 
$arrQueryParts['Sex'] = $_POST['SexF']; 
$arrQueryParts['Hair'] = $_POST['HairF']; 
$arrQueryParts['Dress'] = $_POST['DressF']; 
 
foreach($arrQueryParts as $key=>&$value)
{
    if(strtolower(trim($value)) != 'no preference')  //this now checks the value of the key=>value pair
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($value) . "'"; //this now assigns the value of the key=>value pair
    }   
}
 
//your query
$strAddQuery = $arrQueryParts['Age'] . $arrQueryParts['Sex'] . $arrQueryParts['Hair'] . $arrQueryParts['Dress'];
$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))
{

$ID = $row['ID'];

$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Age = $row['Age'];

$Sex = $row['Sex'];

$Hair = $row['Hair'];



echo "<table>"; 



echo "<tr><td>";

echo "<a href='www.example.com'>".$Name."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Phone_Number."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Age."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Sex."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Hair."</a></td></tr>";

echo "</table>"; }
?>

[ptara1]

When I use this code nothing is displayed. I think I need to add something here:

$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))

But I'll keep working on it and see if I can get it to work.

ok, I found a bug but this should fix it. Try this code:

<?php

include 'connect.php';

$arrQueryParts = array();
$arrQueryParts['Age'] = $_POST['AgeF']; 
$arrQueryParts['Sex'] = $_POST['SexF']; 
$arrQueryParts['Hair'] = $_POST['HairF']; 
$arrQueryParts['Dress'] = $_POST['DressF']; 
 
foreach($arrQueryParts as $key=>&$value)
{
    if(strtolower(trim($value)) != 'no preference')  //this now checks the value of the key=>value pair
    {   
        $arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($value) . "'"; //this now assigns the value of the key=>value pair
    }   
}
 
//your query
$strAddQuery = $arrQueryParts['Age'] . $arrQueryParts['Sex'] . $arrQueryParts['Hair'] . $arrQueryParts['Dress'];
$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))
{

$ID = $row['ID'];

$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Age = $row['Age'];

$Sex = $row['Sex'];

$Hair = $row['Hair'];



echo "<table>"; 



echo "<tr><td>";

echo "<a href='www.example.com'>".$Name."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Phone_Number."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Age."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Sex."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Hair."</a></td></tr>";

echo "</table>"; }
?>

[ptara1]

The error is in this section of code:

$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";

The problem is getting the "wheres" correct. They are all appearing as one:

SELECT * FROM people WHERE 'No PreferenceNo PreferenceNo PreferenceNo Preference order by Name

I need to get && to go between the sections of the array. Will keep looking but if anyone knows and could say that'd be great.

When I use this code nothing is displayed. I think I need to add something here:

$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))

But I'll keep working on it and see if I can get it to work.

[markdh]

What's your table structure look like and could you give us a sample record as well?

[R0bb0b]

Sometimes it is a little difficult when you are not actually testing the code to see where your logical errors are but I see what it is doing. Hopefully, one more time, try this:

<?php

include 'connect.php';

$arrQueryParts = array();
$arrQueryParts['Age'] = $_POST['AgeF']; 
$arrQueryParts['Sex'] = $_POST['SexF']; 
$arrQueryParts['Hair'] = $_POST['HairF']; 
$arrQueryParts['Dress'] = $_POST['DressF']; 
 
$strAddQuery = ""; 
foreach($arrQueryParts as $key=>&$value)
{
    if(strtolower(trim($value)) != 'no preference')  //this now checks the value of the key=>value pair
    {   
        $strAddQuery .= " " . ucwords($key) . "='" . mysql_real_escape_string($value) . "'"; //this now assigns the value of the key=>value pair
    }   
}
 
$strQuery = "select * from people " . (trim($strAddQuery) != ""?"where ":"") . $strAddQuery . " order by Name";


while($row = mysql_query($strQuery))
{

$ID = $row['ID'];

$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Age = $row['Age'];

$Sex = $row['Sex'];

$Hair = $row['Hair'];



echo "<table>"; 



echo "<tr><td>";

echo "<a href='www.example.com'>".$Name."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Phone_Number."</a></td>";

echo "<td>";
echo "<a href='www.example.com'>".$Age."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Sex."</a></td>";

echo "<td align='center'>";

echo "<a href='www.example.com'>".$Hair."</a></td></tr>";

echo "</table>"; }
?>