CODEWITHSUNDEEP
phpmysql
inthebluesky
inthebluesky

Reputation: 45

PHP: How to combine INSERT with UPDATE

My project is a simple attendance record for my small school. I am submitting entry and exit logs through an online form, and writing them to a database with this query:

$sql = "INSERT INTO table_one (first_name, last_name, location)
VALUES ('$first_name', '$last_name', '$location')";

It works fine - so far so good.

At the same moment I would like to write some of this submitted information to another table in the same database. This query works fine by itself when standing alone:

$sql = "UPDATE another_table SET location='$location' WHERE first_name='$first_name'";

However my problem is how to make them both happen, in sequence. Just listing them successively doesn't work:

$sql = "INSERT INTO table_one (first_name, last_name, location) VALUES 
('$first_name', '$last_name', '$location')";

$sql = "UPDATE personnel_table SET location='$location' WHERE 
first_name='$first_name'";

What is the most effective (and safest) way to combine both commands so that they execute together?

Upvotes: 2

Views: 2950

Answers (4)

RahulD
RahulD

Reputation: 757

You can create a trigger like below:

delimiter # 
create trigger after_ins_trig after insert on first_table
 for each row begin
UPDATE second_table 
SET new.location=old.location 
WHERE new.first_name=old.first_name end#
delimiter ;

You can check id in where clause.

Upvotes: 1

Neutral
Neutral

Reputation: 74

Why not this: 

Table: teraz
Create Table: CREATE TABLE `teraz` (
  `col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

// 

<?php 
$last_name = 77;
$conn = new mysqli('localhost','root','','shopping');
$sql = "INSERT INTO teraz VALUES ('{$last_name}')";
$sql2 = "SELECT * FROM teraz";
$conn->query($sql);
$result = $conn->query($sql2);
$x = $result->fetch_assoc() ;
echo $x['col'];
?>

?

Upvotes: 0

invisal
invisal

Reputation: 11171

You need to use transaction so that if one query fail, both should fail. Only if both query success that it will add/update the database.

$db= new PDO('mysql:host=localhost; dbname=test', $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $db->beginTransaction();

    $sh = $db->prepare("INSERT INTO table_one (first_name, last_name, location) VALUES (?, ?, ?)");
    $sh->execute([$first_name, $last_name, $location]);

    $sh = $db->prepare("UPDATE personnel_table SET location=? WHERE first_name=?");
    $sh->execute([$location, $first_name]);

    $db->commit();
} catch ( Exception $e ) {
    $db->rollBack();
}

Upvotes: 3

hamid_reza hobab
hamid_reza hobab

Reputation: 929

for this problem you must use trigger option in database (forEx mysql).

trigger is like an event. when insert in on table automate update second table. forEx:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

this trigger that is a object for account table. update @sum variable and then use for update second table

Upvotes: 1

Related Questions