Jump to content

age/year dropdown


adamlacombe

Recommended Posts

  • Replies 66
  • Created
  • Last Reply

Top Posters In This Topic

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites


<?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'); ?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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'
}

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.