Still and issue with the results from a search form using mysql and php

[nade93]

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

 

[kickstart]

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

[nade93]

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?

[kickstart]

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

[fenway]

The latter is better -- and faster -- and doesn't use improper group by logic.

[kickstart]

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

[fenway]

Still... "*" and GROUP BY don't mix... garbage return values.  Only valid column to get back here is "userid".

[kickstart]

Hi

 

True, it is a quick demo of the basic idea.

 

Select * is a poor idea anyway.

 

All the best

 

Keith

[nade93]

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

[fenway]

Doesn't work how?

[nade93]

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

 

 

[kickstart]

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

[fenway]

If you still have issues, then echo the actual sql statement.

[nade93]

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!

[kickstart]

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