PHP Select Statement

Discussion in 'PHP' started by smallmerchant, Jun 17, 2010.

0
  1. #1
    Hello, if someone could help me out I would appreciate it.

    Currently I use this statement to select the 25 records from a database with the most hits.

    $sql = "SELECT * FROM sh_urls ORDER BY vi DESC LIMIT 25";
    PHP:
    I would like to select from the field "sh_urls" all records that contact a specific word in that field.

    How do I do this?

    Thanks so much in advance.
     
    smallmerchant, Jun 17, 2010 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    The field contains urls, right?

    The slow way is 
    $sql = "SELECT * 
FROM `sh_urls` 
WHERE lower(`sh_urls`) like '%{$str}%'
ORDER BY vi DESC LIMIT 25";
    PHP:
    so if you are searching for "king" then www.andrewking.co.nz will be returned

    You can put some regex in there to be smarter about the search.
     
    sarahk, Jun 17, 2010 IP
  3. s_ruben

    s_ruben Active Member

    Messages:
    735
    Likes Received:
    26
    Best Answers:
    1
    Trophy Points:
    78
    #3
    You can do like this:

    
$sql = "SELECT * FROM sh_urls WHERE vi LIKE '%specword%' ORDER BY vi DESC";
    Code (markup):
     
    s_ruben, Jun 17, 2010 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #4
    apart from the field name, how is that different?
     
    sarahk, Jun 17, 2010 IP
  5. dropcatchsell

    dropcatchsell Active Member

    Messages:
    431
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    60
    #5
    It's different because it dumps less data into the array. The wildcard * dumps all the fields, whereas calling a single field is going to result in faster code because it isn't iterating a bunch of stuff you don't need.
     
    dropcatchsell, Jun 18, 2010 IP
  6. OnurSQL

    OnurSQL Guest

    Messages:
    67
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    The other poster used a wildcard too :)
     
    OnurSQL, Jun 18, 2010 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #7
    We both used * and the issue isn't the fields that are returned but the filter to get the right data.
     
    sarahk, Jun 18, 2010 IP
  8. dropcatchsell

    dropcatchsell Active Member

    Messages:
    431
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    60
    #8
    Sorry, I was looking at the wrong SQL statement from a different post.
     
    dropcatchsell, Jun 18, 2010 IP