nade93 Posted April 30, 2009 Share Posted April 30, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/ Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Not quite sure what you want. You will get (say) 3 rows returned, one for each match on the value as they are 3 seperate rows. Do you want a list of ids, or a list of the fields and values? If you want a (nasty) way of having a list of the values in one field then investigate the "GROUP CONCAT" function, but personally if you want to use them as default values for check boxes / drop down lists then I would stick to having them on seperate lines. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822523 Share on other sites More sharing options...
nade93 Posted April 30, 2009 Author Share Posted April 30, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822795 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Subselect / group concat way of doing it:- SELECT *, GROUP_CONCAT(value) FROM jos_community_users WHERE userid IN (SELECT user_id FROM jos_community_fields_values WHERE (field_id = 20 AND value = '$height') OR (field_id = 22 AND value = '$build') OR (field_id = 21 AND value = '$eyes')) GROUP BY userid This would give you one record per userid, with one field containing all the attributes for that person seperated by a comma (even ones that were not height, build or eyes). If you wanted to you could explode the resulting string in php to get the individual fields (but then you might has well bring each row back and process it in php). You would probably want to put the field_id as a variable as well if you wanted to make it flexable. An alternative which is probably more efficient but less clean if you need to check more attributes (so if you want to check 5 or 6 attributes occasionally this would be messy to process). However this would bring you back one record per userid with also columns for each individual attribute SELECT * FROM jos_community_users a INNER JOIN jos_community_fields_values b ON a.userid = b.user_id AND b.field_id = 20 AND b.value = '$height' INNER JOIN jos_community_fields_values c ON a.userid = c.user_id AND c.field_id = 22 AND c.value = '$build' INNER JOIN jos_community_fields_values d ON a.userid = d.user_id AND d.field_id = 31 AND d.value = '$eyes' All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822836 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 The latter is better -- and faster -- and doesn't use improper group by logic. Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822956 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 The latter is better -- and faster -- and doesn't use improper group by logic. Its worse than that, there is a massive error in it . SELECT *, GROUP_CONCAT(value) FROM jos_community_users a INNER JOIN jos_community_fields_values b ON a.userid = b.user_id WHERE (field_id = 20 AND value = '$height') OR (field_id = 22 AND value = '$build') OR (field_id = 21 AND value = '$eyes') GROUP BY userid All the best Keit Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822972 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 Still... "*" and GROUP BY don't mix... garbage return values. Only valid column to get back here is "userid". Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822984 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi True, it is a quick demo of the basic idea. Select * is a poor idea anyway. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822988 Share on other sites More sharing options...
nade93 Posted April 30, 2009 Author Share Posted April 30, 2009 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!!!! Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-822994 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 Doesn't work how? Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823015 Share on other sites More sharing options...
nade93 Posted April 30, 2009 Author Share Posted April 30, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823029 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Looks like you intended to use it with LIKE rather than = . As such when you don't specify a value it is trying to find a record where (for example) b.value = '%' whcih is probably not there. Try either (which is possibly doing loads of pointless joins):- $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 LIKE '$gender' JOIN jos_community_fields_values c ON a.userid = c.user_id AND c.field_id =18 AND c.value LIKE '$height' JOIN jos_community_fields_values d ON a.userid = d.user_id AND d.field_id =20 AND d.value LIKE '$sexuality' JOIN jos_community_fields_values e ON a.userid = e.user_id AND e.field_id =19 AND e.value LIKE '$build' JOIN jos_community_fields_values f ON a.userid = f.user_id AND f.field_id =10 AND f.value LIKE '$cities' JOIN jos_community_fields_values g ON a.userid = g.user_id AND g.field_id =22 AND g.value LIKE '$membertype' JOIN jos_community_fields_values h ON a.userid = h.user_id AND h.field_id =23 AND h.value LIKE '$agegroup' JOIN jos_community_fields_values i ON a.userid = i.user_id AND i.field_id =21 AND i.value LIKE '$lookingfor' )"; or something like:- $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']; $query = "(SELECT * FROM jos_community_users a"; if ($gender != "any") {$query .= "JOIN jos_community_fields_values b ON a.userid = b.user_id AND b.field_id =2 AND b.value = '$gender'";} if ($height != "any") {$query .= "JOIN jos_community_fields_values c ON a.userid = c.user_id AND c.field_id =18 AND c.value = '$height'";} if ($sexuality != "any") {$query .= "JOIN jos_community_fields_values d ON a.userid = d.user_id AND d.field_id =20 AND d.value = '$sexuality'";} if ($build != "any") {$query .= "JOIN jos_community_fields_values e ON a.userid = e.user_id AND e.field_id =19 AND e.value = '$build'";} if ($cities!= "any") {$query .= "JOIN jos_community_fields_values f ON a.userid = f.user_id AND f.field_id =10 AND f.value = '$cities'";} if ($membertype != "any") {$query .= "JOIN jos_community_fields_values g ON a.userid = g.user_id AND g.field_id =22 AND g.value = '$membertype'";} if ($agegroup != "any") {$query .= "JOIN jos_community_fields_values h ON a.userid = h.user_id AND h.field_id =23 AND h.value = '$agegroup'";} if ($lookingfor != "any") {$query .= "JOIN jos_community_fields_values i ON a.userid = i.user_id AND i.field_id =21 AND i.value = '$lookingfor'";} if ($activities != "any") {$query .= "JOIN jos_community_fields_values i ON a.userid = i.user_id AND i.field_id =25 AND i.value = '$lookingfor'";} )"; In either case specify the columns you want back (which with the 2nd example where you are only joining for specified values might mean making up the joins and the rest of the select seperatly, and they concatenating them). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823036 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 If you still have issues, then echo the actual sql statement. Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823038 Share on other sites More sharing options...
nade93 Posted April 30, 2009 Author Share Posted April 30, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823070 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi As Fenway says, can you echo out the SQL statement just before it is used so we can see what has been generated. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156233-still-and-issue-with-the-results-from-a-search-form-using-mysql-and-php/#findComment-823129 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.