SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Command check and then add to a field

    Hello all,

    Could anyone help me generate a sql command to do the following, if it's even possible that is.

    I have a database with a table called: content
    the fields are: keywords & text

    How do I get the query to search the text fields only for say the word "dog" if it finds results then the next part of the query would add the keyword "dog" at the end of the keyword field for all the records? There are keywords currently in place, is it possible to add to a field? ie. modify?

    I have over 10,000 pages on my site and obviously looking to aid in the SEO aspect.

    Can anyone tell me if this is possible?

    Thank you once again in advance for any help or advice.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you have a big problem right here: "add the keyword "dog" at the end of the keyword field"

    don't do that

    use a separate content_keywords table instead, with one row per keyword for each content
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Thank you kindly for your reply.

    Do I have a big problem because what I'm after doing is impossible?

    I can't really alter or change the structure of the database as it's being run by a CMS.

    Any ideas?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jasper1106 View Post
    Do I have a big problem because what I'm after doing is impossible?
    impossible? no

    ill advised, maybe

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas on the sql syntax I need to use to get these keywords into these CMS fields as per original post? I can't do much about the pre-determined structure I'm afraid so whether it's ill advised or not I'm kind of stuck with it :S

    The keyword field is text type and with a value like this:
    dog, cat, bird, horse

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    UPDATE content
       SET keywords = CONCAT(COALESCE(CONCAT(keywords,', '),''),'dog')
     WHERE `text` LIKE '%dog%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ^ life saver thank you so much R937!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •