PHP/MySQL…Inserting 3 values from 3 tables into one table with one query?

[sasabonsam]

Hi everyone!

Thank you in advance for reading this and any help you are able to provide. This has been a bit of a long road but I'm learning along the way. Before I begin I am well aware of the dangers of SQL injection and understand that using prepared statements would decrease injection attacks for the following code. This is a PHP/MySQL test code to see if it works before actual implementation on a live site. With that said here we go:

I have a database and it contains four tables (for the sake of security I gave them disney character names) named huey, dewey, lewey and uncledonald.

I would like to have the values from the columns deweysays in the table dewey, hueysays from the table huey and leweysays from the table lewey to show up in thier corresponding deweysays, hueysays and leweysays columns in the table uncledonald. See attached pic to see visually what I mean.

I've tried the following code and get the result I want (values added to all tables) but only once. After that I get data in the dewey, huey and lewey tables but nothing else in the uncledonald table.

Here is the PHP:

<?php

//Let's see whether the form is submitted
if (isset ($_POST['submit'])) {


$con=mysqli_connect("localhost","root","root","provingground");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "INSERT INTO dewey (lot_id, deweysays) VALUES (0, '{$_POST['deweyspeak']}');";
$sql .= "INSERT INTO huey (cust_id, hueysays) VALUES (0, '{$_POST['hueyspeak']}');";
$sql .= "INSERT INTO lewey (personal_id, leweysays) VALUES (0, '{$_POST['leweyspeak']}');";
$sql .= "INSERT INTO uncledonald (deweysays) SELECT deweysays FROM dewey ";
$sql .= "INSERT INTO uncledonald (hueysays) SELECT hueysays FROM huey ";
$sql .= "INSERT INTO uncledonald (leweysays) SELECT leweysays FROM lewey ";

// Execute multi query
if (mysqli_multi_query($con,$sql)){

print '<p> The Ducks Have Spoken.</p>'; 

} else { 

die ('<p>Could not add entry because:<b>' . mysqli_error() . '</b>.</p><p>The query being run was: ' . $sql . '</p>'); 
}


}
mysqli_close($con);

?>

Is there something missing in my $sql query to uncledonald? Is the script completely off? Lot of questions…Help please!

[Ingolme]

I think this might be a result of doing all the queries at the same time.

There's another problem with that query: If it worked as it's supposed to, every time you run it a new copy of every single row of dewey, huey and lewey will be made on uncledonald.
Assuming you're passing letters of the alphabet, the first time you run the code the deweysays column would have these values:
A
The second time it would look like this:
A, A, B.
The third time would be:
A, A, B, A, B, C.
and then:
A, A, B, A, B, C, A, B, C, D

If what you actually want is the values you just inserted then you should be passing the POST data straight to the uncledonald queries. For efficiency, you probably should be doing it all in the same query:

INSERT INTO uncledonald (deweysays, hueysays, leweysays) VALUES ('{$_POST['deweyspeak']}', '{$_POST['hueyspeak']}', '{$_POST['leweyspeak']}'

If you actually need to copy the values from the tables because the POST variables are no longer available you need to have the ID of the row you want to copy the values from.

INSERT INTO uncledonald (deweysays) SELECT deweysays FROM dewey WHERE lot_id = 500

Usually this ID can be obtained using the mysqli_insert_id() function, but if you can get the ID from that function then the original POST values should still be available.

[sasabonsam]

@Ingolme - That was one of the most thorough and well thought answers I've received for this question. I've asked around and can't get anyone to answer the question but you did and I truly appreciate your knowledge.

 

Best yet, I tried it and it worked and then I had to think clearly about what I was actually trying to do…

 

I initially thought that it was possible to transfer the values of the huey, dewey and lewey tables into the uncledonald table while in the process of simultaneously executing the query. Then after your advice, I found that my logic was a bit off since in order for those values to be there for uncledonald they would have to be entered first then called or entered directly into the uncledonald table as part of the multi query. I wish I had thought of that before slamming my head against the wall on this for a week…Ah well you live an learn I guess!

 

So I have a related question… So instead of requesting the deweysays, hueysays and leweysays to be displayed into the uncledonald table, what if I wanted the auto incremented IDs of lot_id for the dewey table, cust_id for the huey table and personal_id for the lewey table to be entered into the uncledonald table under columns of the same name. For that to work would I have to run a query for each entry, obtain the auto-incremented ID with a mysqli_last_insert_id(); and then insert it into the uncledonald table with an INSERT INTO?

 

I'm attaching a picture as reference to what I'm talking about...Thank you in advance for any further advice you can provide.