What am I missing with php command to INSERT INTO SELECT FROM?

Question

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 data 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 columns in the table uncledonald. See attached pic to see visually what I mean.

4 tables

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

    <?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? Please help!

I don't know if changing to those names was a good idea. I couldn't even make sense of your second paragraph. xD — FirstOne, Mar 04 '16 at 19:33
If you need to change your table names to Disney characters for security purposes, you're doing something wrong somewhere else in your overall security :p — Markinson, Mar 04 '16 at 19:35
how are your tables defined? I wasnt sure if it failed to insert because of the same lot_id being applied? — Fallenreaper, Mar 04 '16 at 19:38
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jay Blanchard, Mar 04 '16 at 19:41
@FirstOne...The names are just placeholders. The second paragraph means that I rans this script in a form and I received results (input data) for the tables dewey, huey and lewey and uncledonald. But when I tried to input another record once again dewey, huey and lewey received data but not uncledonald...which is confusing that it would work only once but not over and over. — AKOFA Creative, Mar 04 '16 at 19:54
Nice @Derp...But this is just an experiment with false placeholder names before real dev happens. :) — AKOFA Creative, Mar 04 '16 at 19:55
@Fallenreaper - Same lot_id not being applied but I wouldn't think that would matter right? — AKOFA Creative, Mar 04 '16 at 19:57
@JayBlanchard - Oh yeah I know that SQL injection will be an issue but this is a test to see what is possible before the full development and optimization. Thank you for the link I may need to contact you about those prepared statements — AKOFA Creative, Mar 04 '16 at 19:59
@Chris - I'm curious about wrapping the subqueries in parenthesis...How would that look in code? — AKOFA Creative, Mar 04 '16 at 20:17
@AKOFACreative See some examples here http://dev.mysql.com/doc/refman/5.7/en/subqueries.html — Chris, Mar 04 '16 at 20:19
@Chris-Thank you for the link! So would I be wrapping the INSERT INTO or the SELECT-FROM subquery? — AKOFA Creative, Mar 04 '16 at 20:23
@AKOFACreative Most of the SQL injection bugs in the wild started out as "just a test" and then that code went into production. Don't create dangerous code to start with. If you use prepared statements properly you'll have fewer bugs to deal with, you won't have escaping problems, and your code will look a lot cleaner. — tadman, Mar 04 '16 at 20:24
Duly noted @tadman...I understand the dangers of SQL injection and the benefits of starting out on the right foot programming wise but my initial question hasn't really been answered. I would not want to come off as terse but my priority is getting the query to work then I can focus on security measures. Would you have an idea as to how to make the above query work so I can then focus on preventingSQL injection bugs? — AKOFA Creative, Mar 04 '16 at 20:49
@Anant - Sorry dude no dice...the script you provided does not add anything to the uncledonald table...Values still flow into dewey, huey and lewey tables but nothing in uncledonald? Any more ideas...I'm open to your suggestions! — AKOFA Creative, Mar 04 '16 at 20:58

What am I missing with php command to INSERT INTO SELECT FROM?

0 Answers0