adamlacombe Posted March 21, 2011 Author Share Posted March 21, 2011 condition in the WHERE clause Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 21, 2011 Share Posted 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 . . . ? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 21, 2011 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 21, 2011 Author Share Posted March 21, 2011 Yesum it does. Thank you so very much for the help. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 21, 2011 Share Posted March 21, 2011 You're welcome . . . Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 21, 2011 Author Share Posted 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?  Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted March 22, 2011 is it too much in that query? if there is a way to shorten it should and how do i? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted March 22, 2011 OK. I'll look it over after I make coffee . . . Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted March 22, 2011 okay thanks for the help again, I highly appreciate it Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted March 22, 2011 Can you post the current code for the search form and processing script? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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'); ?> Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted 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 . . . Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 22, 2011 Share Posted 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' } Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted March 22, 2011 hmm well would it be a bad thing to keep it the way I have it? Quote Link to comment Share on other sites More sharing options...
adamlacombe Posted March 22, 2011 Author Share Posted March 22, 2011 like would it slow the server down much if I keep it the way I got it? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.