age/year dropdown

adamlacombe · March 21, 2011

condition in the WHERE clause

Pikachu2000 · March 21, 2011

What is the structure of that table, and what are you using for values in that field? Is it a yes/no, 0/1, true/false, or . . . ?

adamlacombe · March 21, 2011

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `religious` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `orientation` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `smoke` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `drink` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `ethnicity` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `body_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `children` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `education` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `music` text COLLATE utf8_unicode_ci NOT NULL,
  `movies` text COLLATE utf8_unicode_ci NOT NULL,
  `books` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

all fields use text, so smoke and drink are both yes/no

Pikachu2000 · March 21, 2011

That table structure looks pretty inefficient to me. A lot of the fields probably shouldn't be VARCHAR, especially at the lengths some are set. 100 for a field that will only contain a yes or no is excessive, IMHO. You might consider using TINYINT() or maybe even ENUM() for those.

But anyhow, to answer the question, here's one way to do that. You'd add this to the where clause

AND id IN( SELECT userid FROM questions WHERE smoke = '$choice' )

You'll need to adjust the query string dynamically to use this effectively. Store it in a variable, and concatenate it to the query if the search utilizes it. I.e. if the search doesn't specify whether smokers should be included or not ("Doesn't Matter"), you could leave it off entirely. If the search should include only smokers or only non-smokers, set the $choice variable to 'yes' or 'no', and add the string to the WHERE clause.

pseudo-code

if( $_POST['smokes'] != 'doesn't matter' ) {
     $choice = $_POST['smokes'];
     $query_string .= " AND id IN( SELECT userid FROM questions WHERE smokes = '$choice'";
}

Does that make sense to you?

adamlacombe · March 21, 2011

Yesum it does. Thank you so very much for the help. :happy-03:

Pikachu2000 · March 21, 2011

You're welcome . . .

adamlacombe · March 21, 2011

ehhh Something is going on..

<?php
$title="Search";
$metakeywords="search, find";
$metadescription="Search for your love!";
include ('header.php');


echo '
<table width="100%"><tr><td valign="top" class="content">
            <form action="index.php?action=search" method="post">

                  <b>Seeking Gender</b>
                  <select name="gender">
                    <option value="">Either</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                  </select>
<hr>
                  <b>Smoke?</b>
                  <select name="smoke">
                    <option value="">Doesn\'t matter</option>
                    <option value="Yes">Yes</option>
                    <option value="No">No</option>
                  </select>
<hr>

                  <b>Drink?</b>
                  <select name="drink">
                    <option value="">Doesn\'t matter</option>
                    <option value="Yes">Yes</option>
                    <option value="No">No</option>
                  </select>
</td><td valign="top" class="content">

                  <b>First Name</b>
	<input type="text" name="first" size="17"><br> <b>Last Name</b><input type="text" name="last" size="17">

<hr>


	<b>Sexual orientation</b>
               <select class="orientation" name="orirntation">
               <option value="">Any</option>
<option value="Bi">Bi</option>
<option value="Gay/Lesbian">Gay/Lesbian</option>
<option value="Straight">Straight</option>
                  </select>

<hr>

                  <b>Age Range</b>
                  <select class="date" name="age_from">';


$years = range( 16, 100 );

foreach( $years as $v ) {
     echo "<option value=\"$v\">$v</option>\n";
}
echo '
                  </select>
to 
                  <select class="date" name="age_to">';

$years = range( 16, 100 );

foreach( $years as $v ) {
     echo "<option value=\"$v\">$v</option>\n";
}
echo '</select></td><td valign="top" class="content">

	<b>Religion</b>
               <select name="religious">
               <option value="">Any</option>
<option value="Agnostic">Agnostic</option>
<option value="Atheist">Atheist</option>
<option value="Buddhist">Buddhist</option>
<option value="Catholic">Catholic</option>
<option value="Christian">Christian</option>
<option value="Hindu">Hindu</option>
<option value="Jewish">Jewish</option>
<option value="Mormon">Mormon</option>
<option value="Muslim">Muslim</option>
<option value="Other">Other</option>
<option value="Protestant">Protestant</option>
<option value="Satanist">Satanist</option>
<option value="Scientologist">Scientologist</option>
<option value="Taoist">Taoist</option>
<option value="Wiccan">Wiccan</option>
                  </select>

<hr>



	<b>Ethnicity</b>
               <select name="ethnicity">
               <option value="">Any</option>
<option value="Asian">Asian</option>
<option value="Black / African descent">Black / African descent</option>
<option value="East Indian">East Indian</option>
<option value="Latino / Hispanic">Latino / Hispanic</option>
<option value="Middle Eastern">Middle Eastern</option>
<option value="Native American">Native American</option>
<option value="Pacific Islander">Pacific Islander</option>
<option value="White / Caucasian">White / Caucasian</option>
<option value="Other">Other</option>
                  </select>

<hr>

                  <br><input type="submit" name="submit" value="Search" /></form>
</td></tr></table>';

if($_POST['submit']){
$first = clean_up($_POST['first']);
$last = clean_up($_POST['last']);
$smoke = clean_up($_POST['smoke']);
$drink = clean_up($_POST['drink']);
$gender = clean_up($_POST['gender']);
$age_from = clean_up($_POST['age_from']);
$age_to = clean_up($_POST['age_to']);
$orientation = clean_up($_POST['orientation']);
$religious = clean_up($_POST['religious']);
$ethnicity = clean_up($_POST['ethnicity']);

if($gender){ $sql="AND `gender`='$gender'"; }
if($first){ $sql2="AND `first` LIKE '%$first%'"; }
if($last){ $sql3="AND `last` LIKE '%$last%'"; }
if($smoke){ $sql4="AND id IN( SELECT userid FROM questions WHERE `smoke` = '$smoke' )"; }
if($drink){ $sql5="AND id IN( SELECT userid FROM questions WHERE `drink` = '$drink' )"; }
if($orientation){ $sql6="AND id IN( SELECT userid FROM questions WHERE `orientation` = '$orientation' )"; }
if($religious){ $sql7="AND id IN( SELECT userid FROM questions WHERE `religious` = '$religious' )"; }
if($ethnicity){ $sql8="AND id IN( SELECT userid FROM questions WHERE `ethnicity` = '$ethnicity' )"; }

$r3 = mysql_query("SELECT `id`, `first`, `last`, `avatar`, `gender`, 
( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) AS age 

FROM `users` 

WHERE 

( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) )  

BETWEEN $age_from AND $age_to $sql $sql2 $sql3 $sql4 $sql5 $sql6 $sql7 $sql8") or die(mysql_error());  

echo "<table width='100%' align='center'>
<div class='content'>
Search users results for: $keyword
<br />";

if(mysql_num_rows($r3) == "0"){ echo "sorry there are no results"; }
while($rr3=mysql_fetch_array($r3)){
$user=clean_up($rr3[id]);
$first=clean_up($rr3[first]);
$last=clean_up($rr3[last]);
$avatar=clean_up($rr3['avatar']);

echo "<a href='index.php?action=profile&id=$user'><img src='avatars/$avatar' width='41' height='41' /> $first $last</a><hr>";
}
echo "</div></table>";


echo "</div>";

}

include ('footer.php'); ?>

It was working fine then I added a few more ANDs. I keep getting this error "Operand should contain 1 column(s)" what does that mean?

adamlacombe · March 22, 2011

is it too much in that query? if there is a way to shorten it should and how do i?

adamlacombe · March 22, 2011

oh and aslo when I search for $orientation it works but it spits out wrong information. like it says someone is Straight when really there isn't even anything in that field

Pikachu2000 · March 22, 2011

When you search, are you trying to search on more then one of the criteria, like with multiple checkboxes or radio buttons?

adamlacombe · March 22, 2011

no, when I search for "Religion" or "Ethnicity" it spits out: "Operand should contain 1 column(s)"

When I try to search for someone who is straight ($orientation) it spits out results of people who don't even have anything in that orientation field.

Pikachu2000 · March 22, 2011

OK. I'll look it over after I make coffee . . .

adamlacombe · March 22, 2011

okay thanks for the help again, I highly appreciate it

Pikachu2000 · March 22, 2011

Can you post the current code for the search form and processing script?

adamlacombe · March 22, 2011


<?php
$title="Search";
$metakeywords="search, find";
$metadescription="Search for your love!";
include ('header.php');


echo '
<table width="100%"><tr><td valign="top" class="content">
            <form action="index.php?action=search" method="post">

                  <b>Seeking Gender</b>
                  <select name="gender">
                    <option value="">Either</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                  </select>
<hr>
                  <b>Smoke?</b>
                  <select name="smoke">
                    <option value="">Doesn\'t matter</option>
                    <option value="Yes">Yes</option>
                    <option value="No">No</option>
                  </select>
<hr>

                  <b>Drink?</b>
                  <select name="drink">
                    <option value="">Doesn\'t matter</option>
                    <option value="Yes">Yes</option>
                    <option value="No">No</option>
                  </select>
</td><td valign="top" class="content">

                  <b>First Name</b>
	<input type="text" name="first" size="17"><br> <b>Last Name</b><input type="text" name="last" size="17">

<hr>


	<b>Sexual orientation</b>
               <select class="orientation" name="orirntation">
               <option value="">Any</option>
<option value="Bi">Bi</option>
<option value="Gay/Lesbian">Gay/Lesbian</option>
<option value="Straight">Straight</option>
                  </select>

<hr>

                  <b>Age Range</b>
                  <select class="date" name="age_from">';


$years = range( 16, 100 );

foreach( $years as $v ) {
     echo "<option value=\"$v\">$v</option>\n";
}
echo '
                  </select>
to 
                  <select class="date" name="age_to">';

$years = range( 16, 100 );

foreach( $years as $v ) {
     echo "<option value=\"$v\">$v</option>\n";
}
echo '</select></td><td valign="top" class="content">

	<b>Religion</b>
               <select name="religious">
               <option value="">Any</option>
<option value="Agnostic">Agnostic</option>
<option value="Atheist">Atheist</option>
<option value="Buddhist">Buddhist</option>
<option value="Catholic">Catholic</option>
<option value="Christian">Christian</option>
<option value="Hindu">Hindu</option>
<option value="Jewish">Jewish</option>
<option value="Mormon">Mormon</option>
<option value="Muslim">Muslim</option>
<option value="Other">Other</option>
<option value="Protestant">Protestant</option>
<option value="Satanist">Satanist</option>
<option value="Scientologist">Scientologist</option>
<option value="Taoist">Taoist</option>
<option value="Wiccan">Wiccan</option>
                  </select>

<hr>



	<b>Ethnicity</b>
               <select name="ethnicity">
               <option value="">Any</option>
<option value="Asian">Asian</option>
<option value="Black / African descent">Black / African descent</option>
<option value="East Indian">East Indian</option>
<option value="Latino / Hispanic">Latino / Hispanic</option>
<option value="Middle Eastern">Middle Eastern</option>
<option value="Native American">Native American</option>
<option value="Pacific Islander">Pacific Islander</option>
<option value="White / Caucasian">White / Caucasian</option>
<option value="Other">Other</option>
                  </select>

<hr>

                  <br><input type="submit" name="submit" value="Search" /></form>
</td></tr></table>';

if($_POST['submit']){
$first = clean_up($_POST['first']);
$last = clean_up($_POST['last']);
$smoke = clean_up($_POST['smoke']);
$drink = clean_up($_POST['drink']);
$gender = clean_up($_POST['gender']);
$age_from = clean_up($_POST['age_from']);
$age_to = clean_up($_POST['age_to']);
$orientation = clean_up($_POST['orientation']);
$religious = clean_up($_POST['religious']);
$ethnicity = clean_up($_POST['ethnicity']);

if($gender){ $sql="AND `gender`='$gender'"; }
if($first){ $sql2="AND `first` LIKE '%$first%'"; }
if($last){ $sql3="AND `last` LIKE '%$last%'"; }
if($smoke){ $sql4="AND id IN( SELECT userid FROM questions WHERE `smoke` = '$smoke' )"; }
if($drink){ $sql5="AND id IN( SELECT userid FROM questions WHERE `drink` = '$drink' )"; }
if($orientation){ $sql6="AND id IN( SELECT userid FROM questions WHERE `orientation` = '$orientation' )"; }
if($religious){ $sql7="AND id IN( SELECT userid FROM questions WHERE `religious` = '$religious' )"; }
if($ethnicity){ $sql8="AND id IN( SELECT userid FROM questions WHERE `ethnicity` = '$ethnicity' )"; }

$r3 = mysql_query("SELECT `id`, `first`, `last`, `avatar`, `gender`, 
( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) ) AS age 

FROM `users` 

WHERE 

( (DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(`bdate`, '%Y') ) - ( DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT( `bdate`, '00-%m-%d')) )  

BETWEEN $age_from AND $age_to $sql $sql2 $sql3 $sql4 $sql5 $sql6 $sql7 $sql8") or die(mysql_error());  

echo "<table width='100%' align='center'>
<div class='content'>
Search users results for: $keyword
<br />";

if(mysql_num_rows($r3) == "0"){ echo "sorry there are no results"; }
while($rr3=mysql_fetch_array($r3)){
$user=clean_up($rr3[id]);
$first=clean_up($rr3[first]);
$last=clean_up($rr3[last]);
$avatar=clean_up($rr3['avatar']);

echo "<a href='index.php?action=profile&id=$user'><img src='avatars/$avatar' width='41' height='41' /> $first $last</a><hr>";
}
echo "</div></table>";


echo "</div>";

}

include ('footer.php'); ?>

Pikachu2000 · March 22, 2011

There's a typo here: <select class="orientation" name="orirntation">. See if that fixes any of the issues, and let me know if any of them remain.

While developing, it's helpful to have "error_reporting = -1" and "display_errors= On" in your php.ini file. Problems like that would be reported as 'undefined index' or 'undefined variable' warnings.

adamlacombe · March 22, 2011

okay that fixed the orientation problem but now every option there besides the age is giving me "Operand should contain 1 column(s)" when I submit the search

Pikachu2000 · March 22, 2011

OK. I didn't really think that would fix all the issues, but it would have been nice . . . I'll look at it more closely and see what's up.

adamlacombe · March 22, 2011

Okay never mind. Problem fixed. Is that query alright though? Like it seems like it should be or could be shorter. I dont want it to slow things down ya know

Pikachu2000 · March 22, 2011

Yeah, it could be more efficient. Instead of using a subquery for each condition, you could check to see if any of the conditions have been changed from the form's default, and only then add a subquery. Since all of the 'secondary' conditions are coming from the same table, if more than one secondary condition has been selected, add that to the WHERE clause of the subquery instead of adding an additional subquery. So the query sould end up more like this:

SELECT user FROM users WHERE id = $id AND id IN( SELECT userid FROM questions WHERE gender = $gender AND orientation = $orientation AND smoke = $smoke )

If you need help organizing it, just let me know . . .

adamlacombe · March 22, 2011

but what if someone didn't choose a specific gender? then it would search `gender`=' '

so it would pull all accounts that didn't answer the question when really all we want it to do is not enter it in the query at all.

Pikachu2000 · March 22, 2011

That's right. So you need to check to see if the value has been selected, and use a conditional so the value isn't used if it hasn't been selected. If the form's default value is an empty string: value="", then

if( $_POST['criteria'] !== '' ) {
     $query .= AND field = '$criteria'
}

adamlacombe · March 22, 2011

but what if its the first field that gets searched is $query it would look like:

SELECT user FROM users WHERE id = $id AND id IN( SELECT userid FROM questions WHERE AND field = '$criteria'

I need it to say

SELECT user FROM users WHERE id = $id AND id IN( SELECT userid FROM questions WHERE field = '$criteria' AND field2 = '$criteria2'

Do you understand what im saying? sorry if im a little confusing

There can't be an AND after WHERE

adamlacombe · March 22, 2011

hmm well would it be a bad thing to keep it the way I have it?

adamlacombe · March 22, 2011

like would it slow the server down much if I keep it the way I got it?

Sign In

age/year dropdown

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information