Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Posts
    310
    Thanks
    86
    Thanked 3 Times in 3 Posts

    Help with an update script

    Hello,

    I have two sets of tables in mysql database held_stories and new_stories.

    Now, what I want to do is get all the data from held_stories and update new_stories with that information where the story_id's for each table match.

    Can anyone help me please, I think i'm nearly there?

    PHP Code:

    <?php 

    $n
    =1

    // Start query one and get all the details from held stories

    $query_one "SELECT story_id, headline from held_stories"
    $result mysql_query($query_one)  OR die(mysql_error()); 
    while (
    $row mysql_fetch_array($result)) { 
       
    $story_id $row['story_id'];
       
    $headline $row['headline']; 

    //Start the second query to update the new stories with the held stories details

    $query_two "UPDATE new_stories set headline = '$headline' WHERE story_id = $story_id"
    $result mysql_query($query_two)  OR die(mysql_error()); 

    $n++; }

    $number_of_results mysql_num_rows($result); 

    If (
    mysql_num_rows($result) > 0) { 
      echo 
    "We have updated <b>".$number_of_results."</b> stories in the new_stories database"

    else { 
      
    $row mysql_fetch_array$result ); 
      echo 
    "No stories have been updated"


    ?>

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Your UPDATE query inside the SELECT loop is assigning the query return value to $result, but you assigned the query return value of your SELECT query to $result. Your loop is blowing up after $result has been reassigned.

    Though, there is an easy way to do this in one query:

    Code:
    UPDATE new_stories as n JOIN held_stories as h ON n.story_id = h.story_id
    SET n.headline = h.headline

  • Users who have thanked Fumigator for this post:

    stfc_boy (05-04-2009)


  •  

    Posting Permissions

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