Narrowing Mysql search results with PHP

Question

ptara1 0 Junior Poster in Training

13 Years Ago

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.

mysql php

4 Contributors
18 Replies
345 Views
2 Days Discussion Span
Latest Post 13 Years Ago Latest Post by urtrivedi

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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.

vibhaJ 126 Master Poster

13 Years Ago

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;

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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 13 Years Ago by urtrivedi because: n/a

vibhaJ 126 Master Poster

13 Years Ago

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;

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ptara1 0 Junior Poster in Training · Answer 1 · 2011-04-19T07:06:08+00:00

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;

ptara1 0 Junior Poster in Training · Answer 2 · 2011-04-19T11:31:06+00:00

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;

vibhaJ 126 Master Poster · Answer 3 · 2011-04-19T11:39:21+00:00

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?

ptara1 0 Junior Poster in Training · Answer 4 · 2011-04-19T11:39:55+00:00

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"

ptara1 0 Junior Poster in Training · Answer 5 · 2011-04-19T11:43:26+00:00

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 != '')

vibhaJ 126 Master Poster · Answer 6 · 2011-04-19T11:54:44+00:00

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

vibhaJ 126 Master Poster · Answer 7 · 2011-04-19T12:12:25+00:00

vibhaJ 126 Master Poster

13 Years Ago

Did it worked or not????

ptara1 0 Junior Poster in Training · Answer 8 · 2011-04-19T21:01:53+00:00

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

ptara1 0 Junior Poster in Training · Answer 9 · 2011-04-20T08:06:22+00:00

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?

markdh 0 Newbie Poster · Answer 10 · 2011-04-20T08:27:41+00:00

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

ptara1 0 Junior Poster in Training · Answer 11 · 2011-04-20T09:08:09+00:00

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?

urtrivedi 276 Nearly a Posting Virtuoso · Answer 12 · 2011-04-20T10:03:39+00:00

I have added else part

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 13 · 2011-04-20T10:04:03+00:00

I have added else part

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