How to save data to Two or More MySQL Table with a Single Query

PHP
1

Hello, I went through all the links available in Google but didn’t found any correct solution. Please help the noob here.

I have created two tables so that I can do RDBMS (Data Normalization and Joining).

Here is my code:

<?php
require("conn.php");
 
// Escape user inputs for security
$name = mysqli_real_escape_string($conn, $_POST['name']);
$desc = mysqli_real_escape_string($conn, $_POST['desc']);
$cars = mysqli_real_escape_string($conn, $_POST['cars']);
$gender = mysqli_real_escape_string($conn, $_POST['gender']);
$bike = mysqli_real_escape_string($conn, $_POST['bike']);
$cycle = mysqli_real_escape_string($conn, $_POST['cycle']);
 
// attempt insert query execution
$sql = "INSERT INTO tbl_project2 (Name, Description, Cars, Gender, Own) VALUES ('$name', '$desc', '$cars', '$gender', '$bike' )";
$sql = "INSERT INTO tbl_project2b (Cycle) VALUES ('$cycle' )";

if(mysqli_query($conn, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
 
// close connection
mysqli_close($conn);
?>

Whatever I try nothing works. I tried mysqli multi_query but its also not working.

My code only saves data to the 2nd table and not in the 1st table.

Thank you.

2

That is to be expected.

The variable is this.
No wait, the variable is this instead.
OK, now do something with the variable.

3

Yes I understand thats what is happening in my current code…

But then how do you experienced guys do this stuff?

Or am I totally thinking it the wrong way?

4

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query().

‘not working’ is an inappropriate description of your actual state. provide code, working examples, error messages. at least you should stop overwriting $sql and start concatenating like the examples in the manuals do.

5

I would use prepared statements and not write less secure code.

And I would use different names for the variables that were meant to have different values.

If it were only one or two queries I would probably write separate blocks of code for each.
But if there were more and they all had a lot in common I would wrap the code inside a function and pass what ever was different to the function as parameters. eg.

function run_my_query($input_array, $query) {
 $query_string = $query;
.....
 foreach($input_array as $key => $value) {
.....
run_my_query($data1, $query1);
run_my_query($data2, $query2);
6

Try this two suggestions:

1st.

$sql = "INSERT INTO tbl_project2 (Name, Description, Cars, Gender, Own) VALUES ('$name', '$desc', '$cars', '$gender', '$bike' ), ";
$sql = "INSERT INTO tbl_project2b (Cycle) VALUES ('$cycle'), ";

2nd.

mysqli_query($conn,"INSERT INTO tbl_project2 (Name, Description, Cars, Gender, Own) VALUES ('$name', '$desc', '$cars', '$gender', '$bike' );
INSERT INTO tbl_project2b (Cycle) VALUES ('$cycle' ) ");
7

##DO NOT save data to Two or More MySQL Table with a Single Query.

There is not a single reason to do so. Whatever ideas you have behind this whim are wrong. Run separate queries using separate API calls.

8

@liontas76, both your suggestions are essentially wrong.

9

And that’s what I wanted to know. I wanted to know that whether I am thinking right or wrong.

I am a newbie. So please can you tell how to do that or send some tutorial links to read?

10

It’s just your regular function to run a query is meant here. Call this function as many times as many queries you have to run.

Speaking of best practices, you definitely have to use prepared statements. In this regard I would suggest PDO over mysqli. Just because PDO is more consistent when working with prepared statements and has some nice features that make your code way more clean.

1 Like
11

As @Mittineague said above, the issue is that you create the first query, but then you don’t execute it. You then create a second query, but because you’ve used the same variable name $sql, you’ve overwritten the first query now. So when you finally come to actually execute the query, it’s only the second one.

If this is related to the other thread you had (sorry, I can never remember poster names) where we talked about splitting the checkboxes out into a separate table {

you have missed out a bit. There seems to be no way to link your row in tbl_project2 to the row in tbl_project2b. You also seem to be writing the value of one of the checkboxes into the first table, and the value of the second into the second, which isn’t what I meant.

What I meant was, store the user details (name, description, gender etc.) in a single users table, and have a unique id for each user. Once you’ve inserted that row into the users table, retrieve the unique user id. Then, if the form-filler ticked either bike or cycle, insert an individual row for each of those values into the second table, along with the user id, to link them together.

}

1 Like
12

Yippie, I can now add data to both the tables.

Got the solution from here

Now I will try to create a Foreign key and link the tables together OR the ID’s together.

13

Out of interest, which specific part of that post provided the answer? I didn’t immediately spot anything on that page that isn’t mentioned above, other than the OPs code in that question used the old-style database calls.

Great to hear that it’s working now, though.

14

I think I was not using the mysqli_multi_query properly.

Here is one solution but not gonna use it.

$sql = "INSERT INTO tbl_project2 (Name, Description, Cars, Gender) VALUES ('$name', '$desc', '$cars', '$gender' )";
$sql2 = "INSERT INTO tbl_project2b (Bike, Cycle) VALUES ('$bike', '$cycle' )";

if(mysqli_query($conn, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}

if(mysqli_query($conn, $sql2)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}

Here is the other recommended method to use:

$sql = "INSERT INTO tbl_project2 (Name, Description, Cars, Gender) VALUES ('$name', '$desc', '$cars', '$gender' ); 
INSERT INTO tbl_project2b (Bike, Cycle) VALUES ('$bike', '$cycle' );
";

if(mysqli_multi_query($conn, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}

Thank you to all of you :slight_smile:

15

You are still not using mysqli_multi_query properly. This function’s purpose is different and it shouldn’t be used by a whim. So you will have problems in the future.

Like you have been told, to run two queries, you need two separate calls, exactly like it is listed in the solution which you “aren’t gonna using”. It is always a pleasure to see a learner who is doing everything their own way, despite what they are being told, ending up in a trouble. Good luck.

16

Owww … OK…

but when I use two separate calls it gives me the message “Data added successfully” twice. Any idea how to fix that.

17

If you configure mysqli to report errors by itself, you will need no manual error checking. So if you will add the following line before mysqli_connect,

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

then you can write your code as simple as this:

$sql = "INSERT INTO tbl_project2 (Name, Description, Cars, Gender) VALUES ('$name', '$desc', '$cars', '$gender' )";
$sql2 = "INSERT INTO tbl_project2b (Bike, Cycle) VALUES ('$bike', '$cycle' )";

mysqli_query($conn, $sql));
mysqli_query($conn, $sql2))

echo "Records added successfully.";

it will echo a success message if both queries executed properly, or throw an error and halt the script execution in case of error.

1 Like
18

That’s because you echo twice. What I often do is use a boolean. eg.

$first_successful = false;
$second_successful = false;
 ⋮
if($db_stuff_successful) {
$first_successful = true;
}
 ⋮
if($first_successful && $second_successful) {
  echo "are you sure you really want to show me here?";
1 Like
19

I see… OK Rapnel and Mitti many many thanks for your help. I will look in to them and will ask if I face any more problem :slight_smile:

20

I agree that error handling is better handled in a way that does not show errors to users.

And unless you want to show some type of “thanks” message to users, messages like “the code worked” are best left for development.

In addition to using booleans I also often you Strings. eg;

$worked = "";
 ⋮
if($db_stuff_successful) {
$worked .= "first worked. ";
}
 ⋮
if($db_stuff_successful) {
$worked .= "second worked. ";
}
 ⋮
if( !empty($worked) ) {
  echo $worked;