nade93

Hi All basically, I have a select that draws the desired results. Within these results I then need to produce a number for if the case is closed. The field value is 'case_open' so if the case open is set to yes, the number of rows that say yes in that particular field and the same for no can anyone give me any direction for this please?

keith sent you a pm with more details thanks

ah sorry i get you SELECT * FROM jos_users a LEFT JOIN jos_community_users b ON a.id = b.userid LEFT JOIN jos_community_fields_values c ON b.userid = c.user_id WHERE (c.field_id=2 and c.`value`='Male') OR (c.field_id=10 and c.`value`='pleaseselect') OR (c.field_id=18 and c.`value`='5ft-5ft5') OR (c.field_id=19 and c.`value`='Athletic') OR (c.field_id=20 and c.`value`='pleaseselect') OR (c.field_id=21 and c.`value`='pleaseselect') OR (c.field_id=22 and c.`value`='pleaseselect') GROUP BY a.id DESC its showing results but not defining them by the selected criteria

hey reverted back as it was the only thing coming close. the suggestions were not returning any results at all

echo of the query SELECT DISTINCT * , GROUP_CONCAT( DISTINCT value ) FROM jos_users a JOIN jos_community_users b ON a.id = b.userid JOIN jos_community_fields_values c ON b.userid = c.user_id AND ( field_id=2 AND value='Male') OR (field_id=20 AND value='pleaseselect') OR (field_id=18 AND value='pleaseselect') OR (field_id=19 AND value='pleaseselect') OR (field_id=10 AND value='pleaseselect') OR (field_id=22 AND value='pleaseselect') OR (field_id=23 AND value='pleaseselect') OR (field_id=21 AND value='pleaseselect') OR (field_id=17 AND value = '') GROUP BY a.id ORDER BY GROUP_CONCAT( DISTINCT value ) DESC have reverted back to my original as this is the closes i am coming to it, the results are coming up but all the search areas are coming up in most relevant option rather than the users with those specific qualities.

hey thanks for that suggestion but this does not work either, its not returning any results. i am using this form <form action="http://www.site.com/includes/search/searchresults.php" method="post"> <table width="900" border="0" cellspacing="6" cellpadding="0"> <tr> <td><strong>Gender:</strong></td> <td><? $query="select options from jos_community_fields where id=2"; $result=mysql_query($query); // start the select box echo "<select name='gender' style='width:180px;'><option selected value=\"\"></option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\" >$row[options]</option>\n"; } echo "</select>"; ?></td> <td rowspan="7" valign="top"><strong>What they are into:</strong> <table width="100%" border="0" cellspacing="1" cellpadding="0"> <tr> <td>Straight sex</td> <td><? $query="select options from jos_community_fields where id=17"; $result=mysql_query($query); // start the select box while($row=mysql_fetch_row($result)){ echo "<input type=\"checkbox\" value=\"$row[options]\" >$row[options]</option>\n"; } ?></td> <td>ANY</td> <td> </td> </tr> </table></td> </tr> <tr> <td><strong>Height:</strong></td> <td><? $query2="select options from jos_community_fields where id=18"; $result=mysql_query($query2); // start the select box echo "<select name='height' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong>Sexuality:</strong></td> <td><? $query4="select options from jos_community_fields where id=20"; $result=mysql_query($query4); // start the select box echo "<select name='sexuality' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong>Build:</strong></td> <td><? $query6="select options from jos_community_fields where id=19"; $result=mysql_query($query6); // start the select box echo "<select name='build' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong>City:</strong></td> <td><? $query8="select options from jos_community_fields where id=10"; $result=mysql_query($query8); // start the select box echo "<select name='cities' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong>Membership:</strong></td> <td><? $query1="select options from jos_community_fields where id=22"; $result=mysql_query($query1); // start the select box echo "<select name='membertype' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong>Age Group:</strong></td> <td><? $query3="select options from jos_community_fields where id=23"; $result=mysql_query($query3); // start the select box echo "<select name='agegroup' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?></td> </tr> <tr> <td><strong> Looking for:</strong></td> <td><strong> <? $query5="select options from jos_community_fields where id=21"; $result=mysql_query($query5); // start the select box echo "<select name='lookingfor' style='width:180px;'><option selected value=\"pleaseselect\">Please Select</option>\n"; while($row=mysql_fetch_assoc($result)){ echo "<option value=\"$row[options]\">$row[options]</option>\n"; } echo "</select>"; ?> </strong></td> <td><label> <div align="right"> <input type="submit" name="button" id="button" value="Submit"> </div> </label></td> </tr> </table> </form> now using this search results page <? $gender = $_REQUEST['gender']; $height = $_REQUEST['height']; $sexuality = $_REQUEST['sexuality']; $build = $_REQUEST['build']; $cities = $_REQUEST['cities']; $membertype = $_REQUEST['membertype']; $agegroup = $_REQUEST['agegroup']; $lookingfor = $_REQUEST['lookingfor']; $activities = $_REQUEST['activities']; $query = "SELECT * FROM jos_users a LEFT JOIN jos_community_users b ON a.id = b.userid LEFT JOIN jos_community_fields_values c ON b.userid = c.user_id WHERE "; $query .= (empty($gender))?"":sprintf(" (c.field_id=2 and c.`value`='%s') OR",mysql_real_escape_string($gender)); $query .= (empty($cities))?"":sprintf(" (c.field_id=10 and c.`value`='%s') OR",mysql_real_escape_string($cities)); $query .= (empty($height))?"":sprintf(" (c.field_id=18 and c.`value`='%s') OR",mysql_real_escape_string($height)); $query .= (empty($build))?"":sprintf(" (c.field_id=19 and c.`value`='%s') OR",mysql_real_escape_string($build)); $query .= (empty($sexuality))?"":sprintf(" (c.field_id=20 and c.`value`='%s') OR",mysql_real_escape_string($sexuality)); $query .= (empty($lookingfor))?"":sprintf(" (c.field_id=21 and c.`value`='%s') OR",mysql_real_escape_string($lookingfor)); $query .= (empty($membertype))?"":sprintf(" (c.field_id=22 and c.`value`='%s') OR",mysql_real_escape_string($membertype)); $query .= (empty($activities))?"":sprintf(" (c.`value`='%s') OR",mysql_real_escape_string($activities)); $query = substr($SQL,0,-2); $query .= "GROUP BY a.id DESC "; $result = mysql_query($query); if ($row = mysql_fetch_array($result)) { $i = 1; do { // This loop outputs each result. print" <table width=\"80%\" border=\"0\" cellspacing=\"0\" cellpadding=\"6\" style=\"border: thin solid #CCCCCC; font-family:arial, sans-serif;\"> <tr> <td colspan=\"3\" style=\"font-size:10px; color:#CC33CC; border-bottom:thin solid #CCCCCC; background:#eaeaea;\">RESULT NO:{$i}. | USER NO. ".$row['userid']."</td> </tr> <tr> <td width=\"18%\" rowspan=\"3\"><img src=\"http://www.maskerade-swingers.com/".$row['avatar']."\" width=\"100px\" height=\"100px\"></td> <td height=\"15px\" width=\"82%\" colspan=\"2\" style=\"border-bottom:thin solid #CCCCCC; color:#cc33cc; font-size:13px;\">".$row['username']." <font color=\"#CCCCCC\" style=\"font-size:11px;\">".$row['status']."</font></td> </tr> <tr> <td colspan=\"2\"></td> </tr> <tr> <td height=\"15px\" style=\"font-size:10px; color:#CC33CC; border-top:thin solid #CCCCCC; background:#eaeaea;\"><div align=\"left\"> Friends: ".$row['friendcount']." | Matching Details: ".$row['GROUP_CONCAT( DISTINCT value )']."</div></td> <td style=\"font-size:10px; color:#CC33CC; border-top:thin solid #CCCCCC; background:#eaeaea;\"><div align=\"right\"> <a href=\"http://www.maskerade-swingers.com/index.php?option=com_community&view=profile&userid=".$row['userid']."\">View Member </div></td> </tr> </table><br>\n"; // Here you can format the search results and display whatever you want about the result. $i++; } while ($row = mysql_fetch_array($result)); } else { print"<br />Your search returned no results."; }?> the results are just not coming through thanks

thanks keith i really appreciate that but its still not working, its now not returning any results at all x

sorry keith its not working still its the second whereclause. i have tried swithching colons round and still not working.... sorry

thanks keith, looks great but is bringing up and error, trying to look to see where the syntax error is but not sure

would some form of isset work maybe?

Thanks doen both of those and it has not changed anything. thats why im stuck, done a few variations and doesnt seem to have an impact

hi the field_id refers to the jos_community_fields_values. in what way would you plane the on statements in the brackets?

Hi All I am struggling to get the results i need from a search form i am using. my form consists of both drop down lists and checkboxes. the results are diplaying all the users rather than displaying just the users with those matching criteria this is the select method i am using $gender = $_REQUEST['gender']; $height = $_REQUEST['height']; $sexuality = $_REQUEST['sexuality']; $build = $_REQUEST['build']; $cities = $_REQUEST['cities']; $membertype = $_REQUEST['membertype']; $agegroup = $_REQUEST['agegroup']; $lookingfor = $_REQUEST['lookingfor']; $activities = $_REQUEST['activities']; SELECT DISTINCT * , GROUP_CONCAT( DISTINCT value ) FROM jos_users a JOIN jos_community_users b ON a.id = b.userid JOIN jos_community_fields_values c ON b.userid = c.user_id AND ( field_id=2 and value='$gender') OR (field_id=20 and value='$sexuality') OR (field_id=18 and value='$height') OR (field_id=19 and value='$build') OR (field_id=10 and value='$cities') OR (field_id=22 and value='$membertype') OR (field_id=21 and value='$lookingfor') OR (value = '$activities') GROUP BY a.id DESC Is there a way of just singling out the users that only have those values? thanks in advance!!!

they are in seperate files with the ISSET stuff, would that just check the once? where would i need to put that in the code?

thats all the code im using unfortunately. its just those two little bits until i can get it right then going to add it to the site

Hi All I am getting duplicated rows placed when uploading from site to database. the first row is blank and the second row contains the data uploaded. I know it will be a tiny mistake or a comma missing but have traweld for hours and cannot seem to find it my form codes is <form enctype="multipart/form-data" action="upload_file.php" method="POST"><br> <table width="750" cellspacing="4" cellpadding="3" align="center" style="font-size:12px;"><tr><td width="83" bgcolor="eaeaea"> <div align="left"><strong> <!-- MAX_FILE_SIZE must precede the file input field --> <input type="hidden" name="MAX_FILE_SIZE" value="30000" /> <!-- Name of input element determines name in $_FILES array --> <span style="font-size:12px;"> Upload Photo</span></strong></div></td><td width="641"> <div align="left"> <input name="userfile" type="file" size="70" /> </div></td></tr><tr><td bgcolor="eaeaea"><div align="left"><strong><span style="font-size:12px;">Caption</span></strong></div></td> <td> <div align="left"> <input name="caption" type="text" id="caption" size="70"> </div></td></tr><tr><td bgcolor="eaeaea"><div align="left"><strong><span style="font-size:12px;">description</span></strong></div></td> <td> <div align="left"> <input name="desc" type="text" id="desc" size="70"> </div></td></tr> <tr><td colspan="2" style="padding-right:15px;"><div align="right"> <input type="submit" value="Send File" /> </div></td></tr></table> </form> and my upload form is <?php // In PHP versions earlier than 4.1.0, $HTTP_POST_FILES should be used instead // of $_FILES. mysql_connect("", "", "") or die("Could not connect: " . mysql_error()); mysql_select_db(""); $caption = $_REQUEST['caption']; $description = $_REQUEST['desc']; $uploaddir = '../../swf/gallery/asset/3dobject/'; $uploadfile = $uploaddir . basename($_FILES['userfile']['name']); $file = '/swf/gallery/asset/3dobject/'. basename($_FILES['userfile']['name']); if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) { echo "File is valid, and was successfully uploaded.\n"; } else { echo "ARGH! the file did not upload!!!!<br>There may be one or two reasons for this:<br><li>File size is too large</li><li>Are you sure its either a jpeg gif or png file?</li>\n"; } echo 'The file uploaded. <a href="http://www.tdhcouture.com/admin/templates/photos_upload.php">Click here to return to the List of Photos</a>'; $res = mysql_query("INSERT INTO photos SET image='$file', caption='$caption', desciption='$description'"); ?> any ideas please??

this is not working either just producing all the results. Used the second option from keiths last post and it doesnt select any particlar data just all the results tried a if "any"then and else .... didnt seem to do anything at all sorry but totally out of ideas!

always says that the search has returned no results even though the database side of things works when i use the select script. If i search for just "female" and the rest of the dropdowns are at "please select as returning no results. Any ideas? I would post a direct link for the search form but its an adult content site and do not want to offend anyone

the latter select worked great but now it does not work with the search form sample of the search is <select name='gender' style='width:180px;'> <option selected value="any">Please Select</option> <option value="Male">Male</option> <option value="Female">Female</option> </select> and the results page is /* connection information */ $hostname = "localhost"; $username = "site"; $password = "password"; $dbName = "site"; /* make connection to database */ MYSQL_CONNECT($hostname, $username, $password) OR DIE( "Unable to connect to database"); @mysql_select_db("$dbName") or die( "Unable to select database"); $gender = $_REQUEST['gender']; $height = $_REQUEST['height']; $sexuality = $_REQUEST['sexuality']; $build = $_REQUEST['build']; $cities = $_REQUEST['cities']; $membertype = $_REQUEST['membertype']; $agegroup = $_REQUEST['agegroup']; $lookingfor = $_REQUEST['lookingfor']; $activities = $_REQUEST['activities']; if ($gender == "any") {$gender = '%';} if ($height == "any") {$height = '%';} if ($sexuality == "any") {$sexuality = '%';} if ($build == "any") {$build == '%';} if ($cities== "any") {$cities == '%';} if ($membertype == "any") {$membertype == '%';} if ($agegroup == "any") {$agegroup == '%';} if ($lookingfor == "any") {$lookingfor == '%';} if ($activities == "any") {$activities == '%';} $query = "(SELECT * FROM jos_community_users a JOIN jos_community_fields_values b ON a.userid = b.user_id AND b.field_id =2 AND b.value = '$gender' JOIN jos_community_fields_values c ON a.userid = c.user_id AND c.field_id =18 AND c.value = '$height' JOIN jos_community_fields_values d ON a.userid = d.user_id AND d.field_id =20 AND d.value = '$sexuality' JOIN jos_community_fields_values e ON a.userid = e.user_id AND e.field_id =19 AND e.value = '$build' JOIN jos_community_fields_values f ON a.userid = f.user_id AND f.field_id =10 AND f.value = '$cities' JOIN jos_community_fields_values g ON a.userid = g.user_id AND g.field_id =22 AND g.value = '$membertype' JOIN jos_community_fields_values h ON a.userid = h.user_id AND h.field_id =23 AND h.value = '$agegroup' JOIN jos_community_fields_values i ON a.userid = i.user_id AND i.field_id =21 AND i.value = '$lookingfor' )"; Argh help!!!!

hey thanks for the reply basically, say i searched for someone who was tall with blue eyes these crteria would not come up in the search results as these values reside in the value field and are linked by a field value (field_id) and set out as in the table above. i have tried the WHERE (value= ..... and value LIKE but none work, as well as sing AND and OR to get the values to all interlink to produce the user with the matching criteria does that make sense?

Hi all and thanks in advance for any help! I have a real problem with a search form i am trying to create. Basically I think the form is set up right, but the site is using an existing social networking package that has set the data i want to capture in the DB as such CREATE TABLE IF NOT EXISTS `jos_community_fields_values` ( `id` int(10) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `field_id` int(10) NOT NULL, `value` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `search` (`value`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=805 ; The "value" field holds all the data for a specific person and just matches against their id for example id user id field id value 1 62 20 tall 2 62 22 slim 3 62 31 blue eyes etc.... so basically, i have a list of drop down boxes and checkboxes that all work fine on a normally set up database. I also have to join these with other parts of the database to get a more rounded result the select is as follows: SELECT DISTINCT * FROM jos_community_fields_values a JOIN jos_community_users b ON a.user_id = b.userid JOIN jos_users c ON b.userid = c.id WHERE ( value='$height' OR value='$build' OR value='$eyes') THE PROBLEM - because the values all exist in the same field the result for one user often duplicates or cancels out owing to another field being selected. For example, if i were to search for someone who was tall with blue eyes the search would produce the same user twice. Bascially, i need a php code to work around this issue, or some coding that will allow the information to automatically be put into a seperate table on the database. Any ideas would be great!!! thanks

Hi All Fenway provided me a solution to my last problem which worked great, the only issue is, i am using a search form and one of the fields uses a multiple select field. this produces duplicated results though I am wanting to limit this for 1 per id, put unsure how to prepare this properly. My select statement is as follows: SELECT * FROM jos_community_fields_values a JOIN jos_community_users b ON a.user_id = b.userid JOIN jos_users c ON b.userid = c.id WHERE ( value='$gender' OR value='$sexuality' OR value='$height' OR value='$build' OR value='$cities' OR value='$membertype' OR value='$agegroup' OR value='$lookingfor' OR value LIKE '%$activities% the $activities refers to the multiple select field. is there a way i can limit this to just result displayed per id please? thanks

ahhhh sorry fenway my bad, had a week of people been off hand in forums. sorry mate! good advice and works like a charm

thanks for the "ugh" it wasnt in the least bit condescending. but that has solved the issue so thanks for you help

ha ha tell me about it, got a client using jom social for joomla and wanting a dynamic search so the tables are predefined unfortunately, just working ways around it. so what would the multiple entail please?