how to insert and update sql database in different table in the same one button call

Question

I tried to insert the data and also update the data in 2 different table. so I tried this code

    $query = "INSERT INTO mitra (id_user, puas_1, puas_2)
  values (?,?,?);
  UPDATE user
  SET fill = 1
  WHERE id = '".$id_session."';";
  $stmt = $connection->prepare($query);
  $stmt->bind_param("idd", $id_session, $puas1, $puas2);
  $stmt->execute();
  if ($stmt->affected_rows) {
    echo "Uploaded " . $stmt->affected_rows . " rows";
    // header("Location: umum.php");
  } else {
    echo "No rows matched the criteria.";

  }
  $stmt->close();

but it says that Uncaught mysqli_sql_exception: You have an error in your SQL syntax; near update etc. is there anything to make it work please?

you would need to use two distinct queries or have a trigger do the update — Professor Abronsius
– Professor Abronsius, Commented Jul 10, 2019 at 8:01
I dont think PDO supports multiple queries. The nearest way is to use $mysqli->multi_query() which is not recommended. You need to run PDO statements twice — Tushar Gupta
– Tushar Gupta, Commented Jul 10, 2019 at 8:02
Why use prepared statements and then still do this : WHERE id = '".$id_session."';"; — Naruto
– Naruto, Commented Jul 10, 2019 at 8:02
I've tried to run it twice like make $query2 and $stmt2 but still not working @Tushar — Q With Only
– Q With Only, Commented Jul 10, 2019 at 8:07
You need to split it into 2 queries. First insert then update. — Dharman
– Dharman ♦, Commented Jul 10, 2019 at 8:25

Professor Abronsius · Accepted Answer · 2019-07-10 08:38:22Z

1

Were you perhaps intending to use mysqli_multi_query ? That does allow for multiple queries that are separated using a semi-colon.. but that cannot be used with the prepared statement and would potentially open the code to sql injection. To use two prepared statements you could do like this:

$rows = false;
$sql='insert into `mitra` (`id_user`, `puas_1`, `puas_2`) values ( ?, ?, ? )';
$stmt=$connection->prepare( $sql );
if( $stmt ){

    $stmt->bind_param('idd', $id_session, $puas1, $puas2 );
    $res=$stmt->execute();
    $stmt->close();

    if( $res ){

        $sql='update `users` set `fill`=1 where `id`=?';
        $stmt=$connection->prepare( $sql );

        if( $stmt ){
            $stmt->bind_param('i',$id_session );
            $res=$stmt->execute();
            $rows = $stmt->affected_rows;
            $stmt->close();
        }
    }
    if( $res && $rows )exit( header('Location: umum.php') );
}

answered Jul 10, 2019 at 8:38

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dharman Over a year ago

This should probably be done with transactions, and it would also simplify your code.

Chinthaka Fernando · Accepted Answer · 2019-07-10 09:29:33Z

0

You can create a procedure and call it in php.

Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc_insert_and_update` $$
CREATE PROCEDURE `proc_insert_and_update`(
    IN x_user_id int(11),
    IN x_puas_1 VARCHAR(20),
    IN x_puas_2 varchar(20)
    )
BEGIN

    #inserting data
    insert into mitra (id_user, puas_1, puas_2) 
    values ( x_user_id, x_puas_1, x_puas_2 );

    #update data
    update users set fill=1 where id=x_user_id;


END$$
DELIMITER ;

PHP

$stmt = $connection->prepare('CALL proc_insert_and_update(?, ?, ?)');
$stmt->bind_param('iss', $id_session, $puas1, $puas2);

clarify parameter types (i->integer, s->varchar).

answered Jul 10, 2019 at 9:29

Chinthaka Fernando

82411 silver badges13 bronze badges

Collectives™ on Stack Overflow

how to insert and update sql database in different table in the same one button call

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related