1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'Databases' started by billybrag, Jul 11, 2006.

  1. #1
    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
     
    billybrag, Jul 11, 2006 IP
  2. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #2
    
    select * from categories where categoryName like "%Industrial & Driving%"
    
    Code (sql):
    The % is a wildcard so the will return all fields that contain Industrial & Driving
     
    dct, Jul 11, 2006 IP
  3. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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,
     
    billybrag, Jul 11, 2006 IP
  4. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #4
    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.
     
    dct, Jul 11, 2006 IP
    billybrag likes this.
  5. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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 :)
     
    billybrag, Jul 11, 2006 IP
  6. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #6
    The SQL is:
    
    SELECT * FROM job_post WHERE jobcategory LIKE '%Accounting &amp; Finance%'
    
    Code (sql):
    I'm guessing the & is getting converted to the &amp; some where
     
    dct, Jul 11, 2006 IP
  7. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #7
    so I need to use &amp; 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 &amp;
     
    billybrag, Jul 11, 2006 IP
  8. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #8
    Yeah I looked at the source and it was &amp; so if you're comparing &amp; and & they won't match in SQL but would look the same on screen
     
    dct, Jul 11, 2006 IP
  9. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #9
    ah yes I see - i was wrong they are &amp; in the db :)
     
    billybrag, Jul 11, 2006 IP
  10. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #10
    I realise the issue now,

    The job post has them stored as & and the jobcategories table has them as &amp; so if i use the string replace to change that we should be tickety boo!
     
    billybrag, Jul 11, 2006 IP