site search trouble

[Anon]

in my html form when users select their variables from 5 different drop down menus like:

<select name="variable1">
<option value="">any letter</option>
<option value="a">a/option>
<option value="b">b</option>
</select>

after they hit submit it calls
a php file which basically says:

$query = "select * from $dbtable where (variable1 = '$variable1' AND variable2 ='$variable2' AND variable3 ='$variable3' AND variable4 ='$variable4'AND variable5 ='$variable5')";

now this works if their search matches the variables in the database exactly. if they would change one of those vairables to "any " then it causes an error.

i figured i could get around this if i could call

$query = "select * from $dbtable where (variable1 = $variable1 AND variable2 =$variable2 AND...

when "any" is selected.
but i don't know how to tell it to do so. if anyone has any suggestions it would be appreciated. thanks!!

[Anon]

What you'll have to do is construct you're query. If they select "any" for a variable, then you don't need to have it in your query. So something like this...

if ($variable1 != "any")
{ $criteria[1] = " 'variable1 = '$variable1' "; }
if ($variable2 != "any")
{ $criteria[2] = " 'variable2 = '$variable2' "; }
if ($variable3 != "any")
{ $criteria[3] = " 'variable3 = '$variable3' "; }
if ($variable4 != "any")
{ $criteria[4] = " 'variable4 = '$variable4' "; }
if ($variable5 != "any")
{ $criteria[5] = " 'variable5 = '$variable5' "; }

$final_criteria = implode(" AND ",$criteria);

$result = mysql_query("SELECT * FROM $dbtable WHERE ( $final_criteria )");

You could make the above a loop if you use variable variables or use arrays...but that should give you the idea..

---John Holmes...

[Anon]

john thanks for the help. i tried what you said and now when i select exactly the variables which i know are in the database it doesn't find any results. when i select the any option it gives an implode error.
you can find my form at http://www.50megs.com/jajogega/searchengine.php
my form when you sumbit it looks like
if ($gender != "gender")
{ $criteria[1] = " gender = '$gender' "; }
if ($figure != "figure")
{ $criteria[2] = " figure = '$figure' "; }
if ($eyes != "eyes")
{ $criteria[3] = " eyes = '$eyes' "; }
if ($hair != "hair")
{ $criteria[4] = " hair = '$hair' "; }
if ($marital != "marital")
{ $criteria[5] = " marital = '$marital' "; }
if ($religion != "religion")
{ $criteria[6] = " religion = '$religion' "; }
if ($sexuality != "sexuality")
{ $criteria[7] = " sexuality = '$sexuality' "; }
if ($state != "state")
{ $criteria[8] = " state = '$state5' "; }

$final_criteria = implode(" AND ",$criteria);

$query = mysql_query("SELECT * FROM $dbtable2 WHERE ( $final_criteria )");

i'm new to this so i'm kinda confused why this isn't working. if you could help me in any other way or if you know a great tutorial site on this topic that would be appreciated.!!

[Anon]

one more thing. i'd like to make this similiar to http://jobs.thetaxi.org/search.php3, which i think you created john.

[Anon]

Here is a work around:

<select name="variable1">
<option value="any">any letter</option>
<option value="= 'a'">a/option>
<option value="= 'b'">b</option>
</select>

if ($variable1 = "any")
{ $variable1_parameter = "like '%'"; }
else
{ $variable1_parameter = stripslashes($variable1);}

select * from variable1 $variable1_parameter ....

Kai

[Anon]

Always print out your sql statement while you're debugging things, you might notice a syntax error that you can easily fix.

Let's try this:

if ($gender != "gender")
{ $criteria[0] = " gender = '$gender' "; }
if ($figure != "figure")
{ $criteria[1] = " figure = '$figure' "; }
if ($eyes != "eyes")
{ $criteria[2] = " eyes = '$eyes' "; }
if ($hair != "hair")
{ $criteria[3] = " hair = '$hair' "; }
if ($marital != "marital")
{ $criteria[4] = " marital = '$marital' "; }
if ($religion != "religion")
{ $criteria[5] = " religion = '$religion' "; }
if ($sexuality != "sexuality")
{ $criteria[6] = " sexuality = '$sexuality' "; }
if ($state != "state")
{ $criteria[7] = " state = '$state' "; }

if (count($criteria) > 1)
{
$final_criteria = "WHERE ( " . implode(" AND ",$criteria) . " ) ";
}
elseif (count($criteria) == 1)
{
$final_criteria = "WHERE ( " . $criteria[0] . " ) ";
}
$sql = "SELECT * FROM $dbtable2 $final_criteria";
echo "Query = $sql <BR>\n";

$query = mysql_query($sql);

Run that and if it doesn't work, then post what it echo'd out for the $sql variable. It seems like this should work. It's the same method i use for jobs.thetaxi.org.

Also, one thing to look at, you're searching for exact matches. Make sure that the values of your select statements match the values you put in the database. Like for marital <option value="Dating">Dating</option>, you will be searching your database for marital = 'Dating' so make sure that's exactly what is in your fields.

---John Holmes...

[Anon]

Yeah...this is the same method I used there. How did you come across my page?

---John Holmes...

[Anon]

thank you so much for your help. i tried what you said and it didn't work exactly. i plugged in the sql statement that it echoed into my sql console and it was correct, it pulled out the user who met the variables. so i am sure the problem is in the rest of my php.
after what you said i have:

$value_row = mysql_num_rows($query );

if($value_row == 0)

{

include("top.inc");?>
			no match

<?include("bottom.inc");
exit;

}

$userid = (mysql_result($result,0,"id"));
{

$query = "select * from $dbtable4 where id ='$userid'";
$result = mysql_db_query("$dbName", $query);
$user = (mysql_result($result,0,"username"));
include("top.inc");?>match:&nbsp;<?echo $user;

}

include("bottom.inc");
exit;?>

it's telling me "Warning: Supplied argument is not a valid MySQL result resource in E:\Inetpub\wwwroot\johnnyhookup\searchresults.php on line 49", which is the first line that i just posted here. i don't see anything that i am doing wrong here maybe i'm just overlooking something.??
oh yea.. and i heard about your site from one of the other forum questions in here. i originally saw the same question mirrored on another site on the net but i forget exactly where. it seems like you know your stuff though. 🙂

[Anon]

Okay...this is different from what you've got, but after I saw what you were trying to do, give this a try...just add in your table names.

You can cut your sql query down to one by joining them together. Give it a go and let me know if it works. I'll explain anything if you need me to, also. This will also print out all of the matches, in case there are multiple. I assumed that would be possible.

---

<body>
<?php

//enter your table names here.
$dbtable2 = ??;
$dbtable4 = ??;

//create our criteria
if ($gender != "gender")
{ $criteria[0] = " db2.gender = '$gender' "; }
if ($figure != "figure")
{ $criteria[1] = " db2.figure = '$figure' "; }
if ($eyes != "eyes")
{ $criteria[2] = " db2.eyes = '$eyes' "; }
if ($hair != "hair")
{ $criteria[3] = " db2.hair = '$hair' "; }
if ($marital != "marital")
{ $criteria[4] = " db2.marital = '$marital' "; }
if ($religion != "religion")
{ $criteria[5] = " db2.religion = '$religion' "; }
if ($sexuality != "sexuality")
{ $criteria[6] = " db2.sexuality = '$sexuality' "; }
if ($state != "state")
{ $criteria[7] = " db2.state = '$state' "; }

//join together criteria with "and" if there are more than
//one of them.
if (count($criteria) > 1)
{
$final_criteria = " AND ( " . implode(" AND ",$criteria) . " ) ";
}
elseif (count($criteria) == 1)
{
$final_criteria = " AND ( " . $criteria[0] . " ) ";
}

//create sql command and echo to browser.
$sql = "SELECT db4.username FROM $dbtable2 as db2, $dbtable4 as db4 WHERE db2.id = db4.id $final_criteria";
echo "Query = $sql <BR>\n";

//issue query to mysql
$query = mysql_query($sql);

//include top header
include("top.inc");

//check and see if a row was returned...
if ($row = mysql_fetch_array($query))
{
//if row was returned, loop through the matches
do
{
echo "Match: " . $row["username"] . "<BR>\n";
} while ($row = mysql_fetch_array($query))
}
//else if no row was returned, echo message.
else
{
echo "No Match<BR>\n";
}

//include bottom part of page.
include("bottom.inc");

?>

</body>

---

---John Holmes...

[Anon]

So did you get this thing to work?

[Anon]

yea i did thanks to you! i picked the script apart and for the most part i understand what is going on with it. i would have never though of doing it the way you did, i've learn a lot. my site is coming along well although i think my code is a bit jumbled and thick but i'm sure i'll be able to clean that up as i become better with the languages. i'm trying to work on making a rating or voting system right now so you might see some more posts from me in the near future! 🙂 thanks again.

[Anon]

Let me know if you have any questions about the code I wrote.

Looking forward to answering your next post...

---John Holmes...