YSQL: how do I select rows where a column contains a certain string?

I have a db that has a column that contains categories eg

"Customer Service, Industrial & Driving"

As you can see each seperate category if there are multipler for that row, are seperated by a comma.

at the moment if i do a select from where cat= "customer Service" it will only pick any where that is the only category, where as i need it to look in the field and see if it is in there at all. i.e. first, last or wherever - do you get me?

Any ideas, im sure its simple to crack!

Mike

 

select * from categories where categoryName like "%Industrial & Driving%"

Code (sql):

The % is a wildcard so the will return all fields that contain Industrial & Driving

 

thanks DCT,

that is working great on all but those that have & in them, which is wierd.

this is the code im using to display all categories and the number of entries within that category

<?PHP 
$display .= "<table border=\"0\" cellspacing=\"0\" cellpadding=\"5\">"; 

$sql = "SELECT * FROM job_jobcategories";

$sql_res = mysql_query($sql); 

while ($sql_array = mysql_fetch_array($sql_res)){

$jobcat = $sql_array['jobcategory']; 

$get_num = "SELECT * FROM job_post WHERE jobcategory LIKE '%$jobcat%'";

$get_nem_res = mysql_query($get_num);
$get_rows = mysql_num_rows($get_nem_res); 

$display .= "
<tr>

      <td>$jobcat</td>
      <td>($get_rows)</td>
</tr>"; 

}
$display .= "</table>"; 

echo $display;
?>

Code (markup):

it works fine if i replace "$jobcat" for say "it & internet" which is wierd,

 

Can you add

echo $get_num;

Code (php):

after

$get_num = "SELECT * FROM job_post WHERE jobcategory LIKE '%$jobcat%'";

Code (php):

So we can see what it's trying to execute.

 

ok,

I have added that to the code and the results can be seen http://www.jobsinswindon.com/browse1.php

IT might appear to be working but for example there should be 28 IT + Internet jobs but it is saying there is 0

its odd as the queries seem to be ok

Thanks for the help by the way

 

The SQL is:

SELECT * FROM job_post WHERE jobcategory LIKE '%Accounting & Finance%'

Code (sql):

I'm guessing the & is getting converted to the & some where

 

so I need to use & rather than & in the query? As I am seeing & printed out,

sorry if i have misunderstood

edit - just fyi - the & is stored in the db as a & rather than &

 

Yeah I looked at the source and it was & so if you're comparing & and & they won't match in SQL but would look the same on screen

 

ah yes I see - i was wrong they are & in the db

 

I realise the issue now,

The job post has them stored as & and the jobcategories table has them as & so if i use the string replace to change that we should be tickety boo!