Inserting into two table simultaneously sharing same id

Question

Please i have a little problem here. the below code i wrote was meant to insert into two tables simultaneously but it those not work. but if i remove the second INSERT the first INSERT will work dont know whats wrong. ITs meant insert in the first table and also collect the last Insert Id of the First table to the Second table. What did i do wrong

<?php
  $english_name = $_POST['EnglishName'];
  $tel_number = $_POST['TelNumber'];
  $email_address = $_POST['EmailAddress'];
  $gender = $_POST['Gender'];
  $age = $_POST['Age'];
  $region = $_POST['Region'];

  mysql_connect("localhost", "root", "") or die ('Error: ' . mysql_error());
  mysql_select_db("fruitmarket");

  $query="INSERT INTO data (english_name, tel_number, email_address, gender,         age, region) VALUES (";
  $query.="'".$english_name."', ";
  $query.="'".$tel_number."', ";
  $query.="'".$email_address."', ";
  $query.="'".$gender."', ";
  $query.="'".$age."', ";
  $query.="'".$region."')";

  $query .= "INSERT INTO data_category (id, english_name)
VALUES (LAST_INSERT_ID(), '$english_name');";


  mysql_query($query) or die ('Error updating database');

  echo "Record is inserted."; 
?>

It will not work because it became an invalid query after you concatenated another query in `$query`, read more from [here](https://stackoverflow.com/questions/1988921/how-can-i-put-two-queries-in-one-mysql-query). And consider changing to mysqli or PDO, as mysql is already deprecated — Carl Binalla, Oct 13 '17 at 08:36
Then can you help me change it to Mysqli i dont really understand PDO — Edward Walker, Oct 13 '17 at 08:38
Sorry but you need to learn it of your own, SO is not a code-writing service — Carl Binalla, Oct 13 '17 at 08:39
Try reading [this](http://php.net/manual/en/mysqli.multi-query.php) — hungrykoala, Oct 13 '17 at 08:40
Can you share why you want to run those two insert statements simultaneously. — Siddharth Chaudhery, Oct 13 '17 at 08:40
Sorry but tot stackoverflow.com is here to help people in any way possible as long as its possible ? or are you indirectly telling me my understanding of stackoverflow.com is wrong or that you dont know it ? — Edward Walker, Oct 13 '17 at 08:41
because my android app market fetchs value from the two tables to display category. So Data table id must correspond to data-category table — Edward Walker, Oct 13 '17 at 08:46
Am receiving this error Notice: Undefined variable: mysqli in C:\xampp\htdocs\markeet\olx\test\update2.php on line 35 Notice: Trying to get property of non-object in C:\xampp\htdocs\markeet\olx\test\update2.php on line 35 Errormessage: — Edward Walker, Oct 13 '17 at 08:58

Masivuye Cokile · Answer 1 · 2017-10-13T08:55:59.530

its almost 2018, so please stop using depreciated and removed mysql_* functions use PDO/mysqli with prepared statements.

I have re-written your code with prepared statements, please follow these links :

Why shouldn't I use mysql_* functions in PHP?

How can I prevent SQL injection in PHP?

Prepared statements

<?php
$servername = "localhost";
$username   = "username";
$password   = "";
$dbname     = "fruitmarket";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$stmt = "INSERT INTO data (english_name,tel_number,email_address,gender,age,region) VALUES(?,?,?,?,?,?)";

$sql = $conn->prepare($stmt);
$sql->bind_param("ssssis", $english_name, $tel_number, $email_address, $gender, $age, $region);

if ($sql->execute()) {

    $id = $sql->insert_id;

    $insert = $conn->prepare("INSERT INTO data_category (id, english_name) VALUES(?,?)");
    $insert->bind_param("is", $id, $english_name);

    if ($insert->execute()) {

        echo "data inserted successfully";
    } else {

        printf("Errormessage: %s\n", $mysqli->error);
    }
} else {

    printf("Errormessage: %s\n", $mysqli->error);
}

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

Prepared statements basically work like this:

Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO myTabvle VALUES(?, ?, ?)
The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values Compared to executing SQL statements directly, prepared statements have three main advantages:
Prepared statements reduces parsing time as the preparation on the query is done only once (although the statement is executed multiple times)
Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query
Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

Am getting error Notice: Undefined variable: mysqli in C:\xampp\htdocs\markeet\olx\test\update2.php on line 35 Notice: Trying to get property of non-object in C:\xampp\htdocs\markeet\olx\test\update2.php on line 35 Errormessage: — Edward Walker, Oct 13 '17 at 08:58
you need to use the code I gave u all of it @EdwardWalker delete ur code and use this one — Masivuye Cokile, Oct 13 '17 at 09:49
i just copied it and passed it and changed the database user. the only mistake i made is not to include that its used by form i mean registration form pass value to it so it will upload it to database — Edward Walker, Oct 13 '17 at 13:52

score 1 · Answer 2 · answered Oct 13 '17 at 16:31

I tested the above code and noticed you just need just to add and change some code see my below example

<?php
  $english_name = $_POST['EnglishName'];
  $tel_number = $_POST['TelNumber'];
  $email_address = $_POST['EmailAddress'];
  $gender = $_POST['Gender'];
  $age = $_POST['Age'];
  $region = $_POST['Region'];

  mysql_connect("localhost", "root", "") or die ('Error: ' . mysql_error());
  mysql_select_db("fruitmarket");

  $query="INSERT INTO data (english_name, tel_number, email_address, gender, age, region) VALUES (";
  $query.="'".$english_name."', ";
  $query.="'".$tel_number."', ";
  $query.="'".$email_address."', ";
  $query.="'".$gender."', ";
  $query.="'".$age."', ";
  $query.="'".$region."')";

  mysql_query($query) or die ('Error updating database');

  echo "Record is inserted."; 

  $query= "INSERT INTO data_category (id, english_name)
VALUES (LAST_INSERT_ID(), '$english_name');";

mysql_query($query) or die ('Error updating database');

  echo "Record is inserted.";
?>

test it to check if it will work

Inserting into two table simultaneously sharing same id

2 Answers2