SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    2 updates in 1 query?

    Hi All,

    I'm trying to update fields in 2 different tables with 1 query..... I was looking for a solution on the net, having thought that's not a big deal.... I haven't found the answer. Is it possible guys????

    Thank you in advance!

    Full time ADMIN - art community
    Part time coder - dsign

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For security reasons PHP's mysql_query function only allows one query to be executed at a time. As for updating multiple tables with a single UPDATE query, I'm not sure if that's possible. I'm sure a resident SQL guru will show his/her face and confirm this at some point.

    Why do you need to do it with one query anyway?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i ~think~ you can do it, provided that the rows you want to update in both tables can be joined

    please show your tables and describe what you want to update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, there is a content management system i'm working on. there's a form for changing entries in 2 mysql tables that are holding the site content. Then i have a page that will select the appropriate table and update the fields there. something like :
    PHP Code:
    <?
             
    include '../library/config.php';
             include 
    '../library/opendb.php';

    $id $_GET['id'];
    $page $_GET['page'];

    $title $_POST['title'];
    $content_1 $_POST['content_1'];
    $title_right $_POST['title_right'];
    $content_right_1 $_POST['content_right_1'];
    $image_url $_POST['image_url_1'];
    $content_rows $_POST['content_rows_1'];
    $label $_POST['label'];
    $link $_POST['link'];

    switch (
    $page) {

        case 
    "home":
        
    $query "UPDATE table_home SET
                                         title = '
    $title', 
                                        content_1 = '
    $content_1',
                                        title_right = '
    $title_right',
                                        content_right_1 = '
    $content_right_1'
                                        WHERE id = '
    $id' ";
        break;
        
        case 
    "book":
        
    $query "UPDATE table_rows SET
                                        content_1 = '
    $content_rows',
                                        image_url_1 = '
    $image_url'
                                        WHERE page = 'book' "
    ;
    // I have to update another table if the $page==book
        
    break;
        
        }

    mysql_query($query) or die('Error, query failed. ' mysql_error());

    echo 
    "<h2>Data modifiied!!!!!</h2>";
    ?>
    <a href="add_content.php" target="_parent">Add new content</a>
    <a href="admin.php?page=<?=$page?>" target="_parent">View all the content</a>
    <?                                    
             
    include '../library/closedb.php';

    ?>

    Full time ADMIN - art community
    Part time coder - dsign

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    and the problem with that code is... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there's no problem with that code! I just want to update another table in the same query (I have commented the line at the code)....

    Full time ADMIN - art community
    Part time coder - dsign

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not a nice way to code but you can do ...


    PHP Code:
    <?
             
    include '../library/config.php';
             include 
    '../library/opendb.php';

    $id $_GET['id'];
    $page $_GET['page'];

    $title $_POST['title'];
    $content_1 $_POST['content_1'];
    $title_right $_POST['title_right'];
    $content_right_1 $_POST['content_right_1'];
    $image_url $_POST['image_url_1'];
    $content_rows $_POST['content_rows_1'];
    $label $_POST['label'];
    $link $_POST['link'];

    switch (
    $page) {

        case 
    "home":
        
    $query "UPDATE table_home SET
                                         title = '
    $title', 
                                        content_1 = '
    $content_1',
                                        title_right = '
    $title_right',
                                        content_right_1 = '
    $content_right_1'
                                        WHERE id = '
    $id' ";

          
    mysql_query($query) or die('Error, query failed. ' mysql_error());

        break;
        
        case 
    "book":
        
    $query "UPDATE table_rows SET
                                        content_1 = '
    $content_rows',
                                        image_url_1 = '
    $image_url'
                                        WHERE page = 'book' "
    ;
    // I have to update another table if the $page==book

         
    $query2 "UPDATE another_table SET ...";

    mysql_query($query) or die('Error, query failed. ' mysql_error());
    mysql_query($query2) or die('Error, query failed. ' mysql_error());

        break;
        
        }



    echo 
    "<h2>Data modifiied!!!!!</h2>";
    ?>
    <a href="add_content.php" target="_parent">Add new content</a>
    <a href="admin.php?page=<?=$page?>" target="_parent">View all the content</a>
    <?                                    
             
    include '../library/closedb.php';

    ?>
    Thai Boxing
    E-Pix
    Chaaban
    Tekfirm Want to have a web site ?

  8. #8
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not so elegant but it works just fine! thank you 'commandos'!

    Full time ADMIN - art community
    Part time coder - dsign


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
  •