Explode and Query help?

[Mr Chris]

Hi Guys,

 

I’m working with a non-normalized database like so

 

 

and in one column named related  it holds some values it (it could have any amount) which I want to:

 

-  Grab hold of

 

-  Explode (on the comma)

 

-  Then run the query below for each entry in this column

 

So for example it would take the word Preditor Game for story_id=103.  Run the below query, hold the results found and then do the same again for the word alien etc… before eventually outputting the results found

 

Now I know I can’t explode on my query as shown below, but that’s to kind of show what I’m trying to achieve.

 

Is this possible?

 

Thanks

 

 

 

<?php

$query = "SELECT DISTINCT story_id, headline, story, related FROM cfm_stories WHERE (headline LIKE '%(explode(',', $row['related'], -1))%' OR story LIKE '%(explode(',', $row['related'], -1))%') AND story_id != $story_id AND unix_timestamp(published) <= unix_timestamp(NOW()) ORDER BY unix_timestamp(published)asc";  

$result = mysql_query($query) OR die(mysql_error());     

$the_results = mysql_num_rows($result);  

if ($the_results == 0) {  
  echo ("Sorry, there are no related stories");  

} else {  
    while($row = mysql_fetch_assoc($result))   {  
      echo "<a href='story.php?story_id={$row[story_id]}'>'.$row[headline].'</a><br />";              
    }  

}  

?>

 

[dave420]

Have you thought about normalising your database?

[Mr Chris]

Thanks.  I wish I could.  It would make life a lot easier but sadly it's not mine :-(

[sasa]

look mysql function FIND_IN_SET()

[aschk]

You can't do this in a query due to the layout of your database and the information held within.

If the title didn't have the age ratings included (i.e. (15) ). Then we could do a lookup on the title within the related field, perhaps using LOCATE is INSTR, but this is not the case.

 

Otherwise your ONLY option is to parse this with PHP and produce a query from that that does the lookup for you.