2 updates in 1 query?

[dujmovicv]

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!

[SJH]

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?

[r937]

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

[dujmovicv]

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 :

<?
         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';

?>

[r937]

and the problem with that code is... ?

[dujmovicv]

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)....

[commandos]

It's not a nice way to code but you can do ...

<?
         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';

?>

[dujmovicv]

Not so elegant but it works just fine! thank you 'commandos'!