How to insert data in two different table simultaneously?

[saeedcs98]

Hey Everyone!

Is it possible to insert data in two different table simultaneously if yes then how? In Mysql

Basically, I want to insert data in two different tables simultaneously with just a single query

[m_hutley]

well you cant STRICTLY with one query. you can send multiple query statements in a single string, though.

[r937]

it’s possible if the tables are somehow related, and you have a trigger defined on the one you’re updating to update the other

[m_hutley]

@r937

you’re updating to update the other

@saeedcs98

I want to insert data

two different operations there…

[r937]

@m_hutley

two different operations there…

point

restated – “a trigger defined on the table you’re inserting into, to also insert into the other”

[saeedcs98]

Well I tried different Idea!
@m_hutley @r937 kindly look my code and tell what’s wrong in here basically now I send two queries instead one is that right way to send two or multiple queries like this?
thank you

else {
		$sql = "INSERT INTO sys_task(task,start_date,due_date,description) VALUES (?, ?, ?, ?)";
		$stmt = mysqli_stmt_init($conn);
			if (!mysqli_stmt_prepare($stmt, $sql)) {
				header("Location: ../admin/add-new-task.php?error=sqlError", true);
				exit();
			}
			else {
			mysqli_stmt_bind_param($stmt, 'ssss',$task_title,$startDate,$dueDate,$descript);
			mysqli_stmt_execute($stmt);
			mysqli_stmt_store_result($stmt);
				mysqli_stmt_close($stmt);
				mysqli_close($conn);
					$sql2 = "INSERT INTO sys_task_employee(task_id,emp_id) VALUES (?, ?)";
					$stmt2 = mysqli_stmt_init($conn);
					if (!mysqli_stmt_prepare($stmt2, $sql2)) {
						header("Location: ../admin/add-new-task.php?error=sql2Error", true);
						exit();
					}
				else{
						foreach ($_POST['assign-to'] as $assignTo)
						 {
						mysqli_stmt_bind_param($stmt2, 'ii',$task_id,$assignTo);
						mysqli_stmt_execute($stmt2);
						mysqli_stmt_store_result($stmt2);
						}

			header("Location: ../admin/add-new-task.php?result=Task_is_successfully_added&start_date=".$startDate."&end_date=".$dueDate."&assignto=".$assignTo."&task_title".$task_title, true);
				exit();
				}
			}
	}

[saeedcs98]

@saeedcs98

sys_task

@saeedcs98

sys_task_employee

I want to insert data inside both tables that are mention above and the form of that code is given below:

[r937]

@saeedcs98

basically now I send two queries instead one is that right way to send two or multiple queries like this?

what happened when you tested it? ™

[mabismad]

The posted code should be producing several php errors. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php would help you by reporting and displaying all the errors it detects?

Just two of the many problems are -

1. There’s no assignment statement for the $task_id variable (it would be from the last insert id from the 1st query.)
2. The copy/pasted together code is closing the database connection after the 1st query, so nothing past that point will ‘work’.

If you switch to the much simpler PDO extension and use exceptions for errors, over half of this code will disappear. You should also put the form and the form processing code on the same page. This will eliminate the doubling of logic, first setting up cryptic error values, then testing and displaying meaningful errors on a different page. This will also let you re-populate the form fields with the previously submitted values so that the user doesn’t need to keep re-entering the same data over and over.

[mabismad]

The following is what your code would look like (untested) if using the PDO extension and exceptions for errors (which are in most cases fatal for a database dependent page) and putting the form and the form processing code on the same page -

else {
	// insert the task record
	$sql = "INSERT INTO sys_task(task,start_date,due_date,description) VALUES (?, ?, ?, ?)";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$task_title,$startDate,$dueDate,$descript]);

	// get the last insert id (task id)
	$task_id = $pdo->lastInsertId();

	// insert the task employee records
	$sql = "INSERT INTO sys_task_employee(task_id,emp_id) VALUES (?, ?)";
	$stmt = $pdo->prepare($sql);
	foreach ($_POST['assign-to'] as $assignTo)
	{
		$stmt->execute([$task_id,$assignTo]);
	}
}

[m_hutley]

Except you asserted that the user probably doesnt have error reporting on, and then assumed that they’d get informative information from a database exception

Try/Catch the executions, and output it manually.

[saeedcs98]

@saeedcs98

if (!mysqli_stmt_prepare($stmt2, $sql2)) { header(“Location: …/admin/add-new-task.php?error=sql2Error”, true); exit(); }

when I test this it will this error
@r937

[saeedcs98]

@mabismad Thank you let check and tell all of you what’s going on!

[saeedcs98]

@mabismad

$stmt = $pdo->prepare($sql);

where the variable $pdo is defined?

And is lastInsertId() pre-defined function?

@mabismad

lastInsertId()

[saeedcs98]

Now I’m getting this error

[mabismad]

@saeedcs98

when I test this it will this error

Due to redirecting all over on your site, any non-fatal php run-time error messages that are being output are being discarded. Another reason to put the form and form processing code on the same page.

The error at the 2nd query code is due to this -

@mabismad

The copy/pasted together code is closing the database connection after the 1st query, so nothing past that point will ‘work’.

@saeedcs98

where the variable $pdo is defined?

As stated, the posted code is what you would end up with if you switch to use the PDO database extension. The variable would be set in the connection code. The following is an example of typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match your database table's character set

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc

@saeedcs98

And is lastInsertId() pre-defined function?

I see that you are not familiar with the php.net documentation - https://www.php.net/manual/en/pdo.lastinsertid.php

[saeedcs98]

Yes thank you so much actually I’m new in web development that’s why I get tired and confused but once again thanks for your help. I think I need more practice in PDO

[saeedcs98]

I think your’re professional PHP developer am I right?

[saeedcs98]

but I think if I switch to PDO I screwed up and stuck in many different different problem
Is there any other solution instead of PDO so I can complete my HRMS Project ?

[mabismad]

Using the PDO extension, using prepared queries when supplying external/unknown data to the query, and using exceptions for errors (letting php catch and handle the exception in most cases, the exception to which is for ‘recoverable’ application errors), results in the simplest and most consistent code. Won’t simpler code take less time to write, test, and debug? You should also note that the prepared query ? place-holders are the same between the mysqli and PDO extensions, so you don’t have make any changes to the actual sql queries. An added advantage of learning the PDO extension is you can use the same php statements with about a dozen different database types (the actual sql query syntax may need database specific changes.)