SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a query

    I'm running a Mambo CMS on a client site. I have setup a load of content pages, but at the moment only a few of them have content in. I need to copy the content from these pages to the others.

    For example:
    Code:
    Category 1
    - Software
    - Training
    - Support
    
    Category 2
    - Software
    - Training
    - Support
    
    Category 3
    - Software 
    - Training
    - Support
    where Software, Training etc. are the "title" field (in the db). All of this content is stored in one table called "content".

    In total there's 4 catgories and in each 7 of those pages. Currently category has the content pages filled in. I simply need to copy the content to the other categories. I would do some UPDATE queries, but that'll take forever to do, especially as in each row, there are about 15 cells, and I only want to copy certain cells over (i.e. I need to preserve various IDs and so on).

    A query I might run would be:

    PHP Code:
    UPDATE content SET text='blah blah blah blah' WHERE title='Software' 
    having copied the text from my original Software into the text='blah'. But that'd mean I'd need to do it say 10 times for each of the 7 content pages. Actually I suppose that would only be 7 queries.

    Could I do something like:

    PHP Code:
    UPDATE content SET text={content title id},images={content images id},..., WHERE title='Software' 
    if I found the ID of the row that holds the content of the row I want to copy over? I could stick the titles and ID into an array and just loop through it easy enough.

    I haven't played with MySQL for a while and I'm a little rusty... Muchos thanks
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    INSERT INTO Content (columns)
    SELECT columns FROM Content WHERE id = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't want to do an INSERT because the rows are already there (and linked to the menu system which I don't want to redo). Mambo actually has a copy option I've just found.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    INSERT/SELECT copies from one set of rows to another, which is exactly what you asked for

    i was going to suggest looking for a "copy" function in the cms but that would've been too simple, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I won't be using the copy function because I'd still have to do each one manually and then reconfigure the menu system, I was just pointing out it is a handy little feature if you haven't already spent hours setting up the content pages and menu system.

    And what I meant was copy the CONTENT only to rows that already exist. Sorry that wasn't clear.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, yes, "to rows that already exist" makes a difference

    what version of mysql is this on?

    there is multi-table update syntax but it requires 4.0+

    it's in the manual under UPDATE

    if you need help interpreting it to your situation, let me know the table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    4.0.22-standard

    I'll check out the manual, ta.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky


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
  •