Insert data into multiple table using PHP

Question

I am trying to add data into 2 tables using PHP

My PHP code: insert.php

<?php
session_start();
$db['host'] = "dbhost";
$db['user'] = "user";
$db['pass'] = "pass";
$db['name'] = "dbname";

//making an array with the data recieved
$data = array('f_name' => $_POST['txt_f_name'],
              'l_name' => $_POST['txt_l_name'],
              'VNum' => $_POST['txtVisaNo']);

try {
    // preparing database handle $dbh
    $dbh = new PDO("mysql:host=".$db['host']."; dbname=".$db['name']."", $db['user'], $db['pass']);
    // set the PDO error mode to exception
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$insertall = "BEGIN; "
        . "INSERT INTO students (f_name, l_name) "
        . "VALUES (:f_name, :l_name); "
        . "INSERT INTO visa (students_id, VNum) "
        . "VALUES (:LAST_INSERT_ID(), :VNum); "
        . "$addStdInf->execute($data); "
        . "COMMIT;";
$addStdInf = $dbh->prepare($insertall);

echo 'Success!';
}
catch(PDOException $e){
    echo $sql,'<br />', $e->getMessage();

}
$dbh = null;
?>

Notice is "Success!" but it inserted nothing into database, please guide me the ERROR.Thank you.

You `prepare` the statement but never `execute` the query. http://php.net/manual/en/pdostatement.execute.php — , May 07 '15 at 11:08

score 4 · Answer 1 · answered May 07 '15 at 11:06

4

You are only preparing the statement - you never execute it. After the prepare call you receive a ready to execute statement, if you execute it with some parameters, it will be inserted in the database: http://php.net/manual/en/pdostatement.execute.php

answered May 07 '15 at 11:06

vakata

3,726
1
17
31

You also need to check the return value from execute() before declaring success - and be aware that there are some problems with issuing multiple SQL statements in a single API call - see also http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – symcbean May 07 '15 at 11:19

score 1 · Accepted Answer · answered May 07 '15 at 12:33

You are forget to execute your pdo statements

<?php
session_start();
$db['host'] = "dbhost";
$db['user'] = "user";
$db['pass'] = "pass";
$db['name'] = "dbname";

//making an array with the data recieved
$data = array('f_name' => $_POST['txt_f_name'],
              'l_name' => $_POST['txt_l_name'],
              'VNum' => $_POST['txtVisaNo']);

try {
    // preparing database handle $dbh
    $dbh = new PDO("mysql:host=".$db['host']."; dbname=".$db['name']."", $db['user'], $db['pass']);
    // set the PDO error mode to exception
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$insertall = "BEGIN; "
        . "INSERT INTO students (f_name, l_name) "
        . "VALUES (:f_name, :l_name); "
        . "INSERT INTO visa (students_id, VNum) "
        . "VALUES (:LAST_INSERT_ID(), :VNum); "
        . "$addStdInf->execute($data); "
        . "COMMIT;";
$addStdInf = $dbh->prepare($insertall);
$result = $addStdInf->execute();
if ($result) {
    echo 'Success!'; 
} else {
    echo 'please check';
}
}
catch(PDOException $e){
    echo $sql,'<br />', $e->getMessage();

}
$dbh = null;
?>

Insert data into multiple table using PHP

2 Answers2