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. 🙂

    I might go with a regular expression instead of trying to use LIKE.

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

    However, whatever we come up here that works is probably sub-optimal compared to normalizing the database so that you are not storing multiple things in a single field. This could be done by using a separate table that would have two columns: the primary key from the main table and a single station's call letters. Then a query with a JOIN could get all the matches for a given call letter, and do so much more efficiently since the call letter column could be indexed and would not require a complete table scan as it will with the REGEXP or LIKE solutions.

      Thanks for the reply NogDog,

      Yeah, I know about normalising and all that, but I just need a dirty way to manage it now. I have no problem in the values being stored as they are now.

      Your regular expression seems to do the exact same as my code. It still only processes the first value and stops. Could this be because the values
      WCLD, WCLD-F, WKDJ-F, WMJW, WAID

      Have spaces between them?
      I can either POST or GET the data but both still don't work. I mean, the search DOES work, it just doesn't process more than one of the strings and I don't understand why....

        Maybe you need to trim() $data before using it in the query, in case there is a space after the comma?

          Write a Reply...