CODEWITHSUNDEEP
phpmysql
user1509201
user1509201

Reputation: 99

PHP Update two tables same time

I have two tables one called admin and the other called Permission.

When I edit user i display all the information including permission on same page.

When I click on edit I can update information but because if one table gets update I receive error message even tables have been update successfully.

$sql = " UPDATE admin SET username='test', password='test' WHERE id=1;";
$result = mysql_query($sql, $connection);

if( mysql_affected_rows( $result ) == 1 )
{
   $sql = " UPDATE permission SET permission_name ='add_user', user='admin' WHERE user_id=1;";
   $result = mysql_query($sql, $connection);
   echo 'successfully';
}

else
{
   echo 'some error';
}

when I just wont to change permission of user it does gives me error as I haven't change anything. but if I make changes to admin table everything works fine.

How do I show successful message if one or both have been updated successfully.

Upvotes: 0

Views: 3451

Answers (4)

bobwienholt
bobwienholt

Reputation: 17610

How about a stored proc:

DELIMITER $$
CREATED PROCEDURE DoUpdate(TheID INT, TheUser VARCHAR(255), ThePassword VARCHAR(255), ThePermission VARCHAR(255), ThePermissionUser VARCHAR(255))
BEGIN

    UPDATE admin 
    SET username= TheUser
        , password= ThePassword
    WHERE id = TheID;

    IF ROW_COUNT() = 1 THEN
        UPDATE permission 
        SET permission_name = ThePermission
            , user = ThePermissionUser
        WHERE user_id = TheID;
    END IF;
END $$

Then:

CALL DoUpdate(1, 'test', 'test', 'add_user', 'admin');

Upvotes: 0

Braiba
Braiba

Reputation: 571

Zane Bien's answer is the best option for you in this case, but for cases where you can't just combine the query you should check (!$result) instead of (mysql_affected_rows($result) == 1), because you're trying to check if an error occured (which will cause mysql_query to return false), not whether any rows were actually updated in the table.

Upvotes: 0

Vittorio Delsignore
Vittorio Delsignore

Reputation: 36

<?php

$success = true;

$sql = " UPDATE admin SET username='test', password='test' WHERE id=1;";
$result = mysql_query($sql, $connection);

if(!mysql_affected_rows( $result ))
   $success = false;

$sql = " UPDATE permission SET permission_name ='add_user', user='admin' WHERE user_id=1;";
$result = mysql_query($sql, $connection);

if(!mysql_affected_rows( $result ))
   $success = false;

echo ($success ? 'Success' : 'Error');

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23135

You can join the two tables together to update them simultaneously:

UPDATE admin a
INNER JOIN permission b ON a.id = b.user_id
SET
    a.username = 'test',
    a.password = 'test',
    b.permission_name = 'add_user',
    b.user = 'admin'
WHERE a.id = 1

Upvotes: 5

Related Questions