[SOLVED] Searching a MySQL column that has comma separated values and retrieving results

[datb]

Hi there,

 

I seem to be having a problem, as my search isn't working. So, this is what I am trying to achieve:

 

There is a column in a MySQL table that is called "sister_markets" and the user inputs data like this:

BLAH, BLAH1, BLAH2

 

Now, I want to grab this data and search each word individually (as a string) and find the result from another column in the same table (a partial match, only needs to be matching the start of the word). But for some reason, the search isn't working. It comes up with the results of the first string only, no more.

 

The example data is : WCLD, WCLD-F, WKDJ-F, WMJW, WAID

 

This is the PHP code:

<?php

$search=$_REQUEST["sm"];
$pieces = explode(",", $search);

foreach ($pieces as $data) 
{
$result = mysql_query("SELECT * FROM websiteadmin_radio WHERE call_letters LIKE  '$data%'");
while($r=mysql_fetch_array($result))
{	
  
  
   $title=$r["call_letters"];

   echo "$title <br />";
}
}
?>

 

But this only searches WCLD and none of the other strings. I have attempted doing this

 

<?php
$search=$_REQUEST["sm"];
$pieces = explode(",", $search);


$result = mysql_query("SELECT * FROM websiteadmin_radio WHERE call_letters LIKE  ('$pieces')");

while($r=mysql_fetch_array($result))
{	

  
   $title=$r["call_letters"];


   echo "$title <br />";

}
?>

 

But this yields no results.

 

Any help would be highly appreciated.

[datb]

I've even tried with REGEXP

<?php
//connect to mysql
//change user and password to your mySQL name and password
$con = mysql_connect("localhost","root","fre1599");
mysql_select_db("jobstreet", $con);

$search=$_POST["sister_markets"];
$pieces = explode(",", $search);

foreach ($pieces as $data) 
{
$sql = "SELECT * FROM websiteadmin_radio 
WHERE call_letters REGEXP '(^|, *)".mysql_real_escape_string($data)."'"; 
$result = mysql_query($sql);

//grab all the content
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $title=$r["call_letters"];

   
   //display the row
   echo "$title <br />";
}
}
?>

 

But nothing seems to work. Could it be because there are spaces between the data that is being taken from column on the table?

 

I honestly can't think of a way around this.... And yes, I do realise that this is dirty and that it's not "normalised" like it should be done, but right this minute it makes no difference to me. I am quite certain this can be done, I just don't know why it isn't working.

[Bjom]

strip the spaces?

[sasa]

try

SELECT * FROM `websiteadmin_radio` WHERE find_in_set('$data', replace(call_letters,', ',','))

[datb]

Thanks for all the replies guys, I fixed it by added a trim() around $data.

 

Everything works perfectly now.