FIND_IN_SET

[APiro]

Hello!
 
I am trying to query a table, and if any of a set of strings is found in a column to return that record
 

SELECT *, 0 as section FROM injuryreport WHERE report='yes' AND FIND_IN_SET(injury,'$commaList')

 
which ends up looking like:

SELECT *, 0 as section FROM injuryreport WHERE report='yes' AND FIND_IN_SET(injury,'shoulder,upper arm, elbow,lower arm')

 
I am getting returns if the column ONLY contains ONE of the words, but I also want to get returns if column injury contains any combination so long as one of them is there.  For example:
 
Head, Shoulder, Upper Arm, Elbow, Lower Arm, Wrist, Hand, Fingers, Ribs
 
Head, Shoulder
 
Upper Arm, Elbow

 

 

Can this be done with FIND_IN_SET or should I be using something else?!

 

Thank you in advance!

[APiro]

To provide more specific information on what is preceeding this which may be helpful.

 

I am querying a table for a list of body areas from a table that looks like this:

 

location | general

head | head

face | head

shoulder | arm

upper arm | arm

elbow | arm

lower arm | arm

wrist | hand

hand | hand

fingers | hand

ribs | body

chest | body

back | body

hip | leg

upper leg | leg

knee | leg

lower leg | leg

ankle | foot

foot | foot

toes | foot

other | other

 

 

 

where the $general variable is passed from a previous selection of 1 of the 7 distinct entries from the above table, I am using:

$query = "SELECT GROUP_CONCAT(location) FROM injurylocations WHERE general='$general'";

      // execute SQL statement
      $list = mysql_db_query($db, $query, $conn);


      // check for errors
      if (!$list) { echo("ERROR: " . mysql_error() . "\n"); }

      
      // check for results
      $querycheck=mysql_num_rows($list);
    
          if ($querycheck < 1) { echo ("I CANT FIND A LIST OF INJURY LOCATION REFERENCES"); }

      
      else {
            
while($row=mysql_fetch_array($list))  
{  
$commaList = $row['GROUP_CONCAT(location)'];
} 

In the original example I posted above $general would have been passed as 'arm' thus producing my arm related comma deliniated list.

 

I can change the way Im querying here for the list and then modify the subsequent query using

 

FIND_IN_SET

, but I can't figure out a clean way to use LIKE and be able to get an OR statement

[Psycho]

You're doing this the wrong way. You don't need to run the two queries. You only need one. A good indicator that there's a problem is the user of GROUP_CONCAT(). You should never be dealing with comma separated lists to compare/search for values.

 

 

$query = "SELECT injuryreport.*, 0 AS section
          FROM injuryreport
          JOIN injurylocations
            ON injuryreport.injury = injurylocations.location
          WHERE report='yes'
            AND injurylocations.general='$general'";
$result = mysql_db_query($db, $query, $conn);
 
// check for errors
if (!$result)
{
    die("ERROR: " . mysql_error());
}
 
// check for results
if (!mysql_num_rows($result))
{
    echo "I CANT FIND A LIST OF INJURY LOCATION REFERENCES";
}
else
{
    //Do something with the results
 
}

[APiro]

Hi Psycho

Thanks for taking some time to look. Your query results in the same - it returns 20 records.

There are actually 26 records if I manually search with:
injury LIKE '%shoulder%' OR
injury LIKE '%upper arm%' OR
injury LIKE '%elbow%' OR
injury LIKE '%lower arm%'

I was trying to keep the question as simple as possible, but with the addition of joins I have to admit that Im actually doing a UNION ALL with another table that has the exact same structure so if I were to manually write it out with LIKE:
 

(SELECT *, 0 as section FROM injuryreport WHERE report='yes' AND injury LIKE '%shoulder%' OR injury LIKE '%upper arm%' OR injury LIKE '%elbow%' OR injury LIKE '%lower arm%')
  UNION ALL
(SELECT *, 1 as section FROM playoffinjuryreport WHERE report='yes' AND injury LIKE '%shoulder%' OR injury LIKE '%upper arm%' OR injury LIKE '%elbow%' OR injury LIKE '%lower arm%')
  ORDER by date ASC, user ASC

I actually just went the completely retarded route of

$commaList = $row['GROUP_CONCAT(location)'];
$newcommaList = str_replace(",", "%' OR injury LIKE '%", $commaList);

and then using the query:

$getreports = "(SELECT *, 0 as section FROM injuryreport WHERE report='yes' AND injury LIKE '%$newcommaList%') UNION ALL (SELECT *, 1 as section FROM playoffinjuryreport WHERE report='yes' AND injury LIKE '%$newcommaList%')  ORDER by date ASC, user ASC";

which works but is insanely and stupidly backwards lol....

[jazzman1]

Psycho's sql logic is correct for me, just add an IN sql operator.

 

Try,

$query = "SELECT injuryreport.*, 0 AS section
          FROM injuryreport
          JOIN injurylocations
            ON injuryreport.injury = injurylocations.location
          WHERE report='yes'
            AND injurylocations.general IN ('$general'");  // list of items

[APiro]

Hi Jazzman

 

It just doesnt pick up any records that have data in that colum that looks like:

Head, Shoulder

Upper Arm, Elbow

Lower Arm, Wrist

Here are the results using that suggestion

 
And here is the results using LIKE which picks them all up