0

I have a html form where a user can narrow down his or her's search. For example they want to find a person based off of a number of characteristics, say hair color, eye color, body type. These are selected by a drop down menu and there are additional radio buttons to return a yes answer. For example smokes cigarettes and has pets are the radio buttons they click on if they smoke, so a yes.

So I have these five variables. Now I want to return results from a mysql database table that are narrowed down by each of the users choices on the form.

What is the best way to structure the php processing script? I'm relatively new to php and this has been driving me crazy and I can't seem to find an answer anywhere.

Is this a situation where I should nest if statements? I'm just not sure how I should tackle this problem. Any help is appreciated.

4
Contributors
18
Replies
23
Views
6 Years
Discussion Span
Last Post by urtrivedi
0

Somebody will only able to help you, if you do not post your code or sample here.
No body can visualize by just reading the statement.

0

post your code.
I use below searching logic:
1. Create join query which will have all five parameters as a result.
2. now create where condition by concatenation.
e.g.

if(isset($_POST['color']))
  $where.= " AND table1.color=".$_POST['color'];
if()
  ....

3. So your final query will be.
$sql = " select... WHERE 1=1 ".$where;

0

I didn't try it that way. I tried it as an array. The code isn't working though.

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

post your code.
I use below searching logic:
1. Create join query which will have all five parameters as a result.
2. now create where condition by concatenation.
e.g.

if(isset($_POST['color']))
  $where.= " AND table1.color=".$_POST['color'];
if()
  ....

3. So your final query will be.
$sql = " select... WHERE 1=1 ".$where;

0

first

$arrQueryParts[$key] = " " . ucwords($key) . "='" . mysql_real_escape_string($$strVarName) . "'";

have you intentionally placed $$strVarName


second

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

about code does not look valid

it should be like following

echo $strQuery;//copy the printed query from browser and run in phpmyadmin and see whether it gives expected result or not.

$result=mysql_query($strQuery));

while($row=mysql_fetch_assoc($result))
{
.
.
.

Edited by urtrivedi: n/a

0

Replace this much code. and then check it.

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

This is the query that is returned:

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 the problem is that No Preference is supposed to be like a select all in that category because that doesn't narrow the search field down at all.

How do I make it so that when it is No Preference is selected it doesn't restrict the query?

Replace this much code. and then check it.

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;
0
$strVarName = $key . "F";
echo $strVarName; //check what is the value of $strVarName, I am sure it is not "no preference"
0

Either do
1)

if($value != 'No Preference')

OR 2)
In dropdown in option value

<option value="">No Preference</option>

and in coding use,

if($value != '')

This is the query that is returned:

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 the problem is that No Preference is supposed to be like a select all in that category because that doesn't narrow the search field down at all.

How do I make it so that when it is No Preference is selected it doesn't restrict the query?

0

Nothing is displayed by that when I put that into my code.

$strVarName = $key . "F";
echo $strVarName; //check what is the value of $strVarName, I am sure it is not "no preference"
0

Where should I put these into the code?

Either do
1)

if($value != 'No Preference')

OR 2)
In dropdown in option value

<option value="">No Preference</option>

and in coding use,

if($value != '')
0

THE complete 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)
{ 
    $strVarName = $key . "F";
    if($value != 'No Preference')
    {   
       $arrQueryParts[$key] = " AND ".ucwords($key)."='".$value."'";
    }   
}

//your query
$strAddQuery = $arrQueryParts['Age'] . $arrQueryParts['Sex'] . $arrQueryParts['Hair'] . $arrQueryParts['Dress'];
$strQuery = "select * from people " . (trim($strAddQuery) != ""?" where 1=1 ":"") . $strAddQuery . " order by Name";
$rs = mysql_query($strQuery);
while($row = mysql_fetch_assoc($rs))
{

$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>"; }
?>
0

When I tried implementing the changes this morning I got this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

going to keep working on it. Any idea why that error occured?

Did it worked or not????

0

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 tried implementing the changes this morning I got this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

going to keep working on it. Any idea why that error occured?

0

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

If I make a selection then it shows as Hair='Red'Sex='Male'No PreferenceNoPreference etc.

What is the SQL query that's generated by your code?

0

I have added else part

if($value != 'No Preference')    
{          
        $arrQueryParts[$key] = " AND ".ucwords($key)."='".$value."'";    
}
else
{          
        $arrQueryParts[$key] = "";    
}

Edited by urtrivedi: n/a

0

I have added else part

if($value != 'No Preference')    
{          
        $arrQueryParts[$key] = " AND ".ucwords($key)."='".$value."'";    
}
else
{          
        $arrQueryParts[$key] = "";    
}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.