-1

I'm wondering why the following PHP Code does not insert values into database? I'm trying to insert ten datasets into a table with one execution (=multiple rows in single query), but after execution, the table is still empty. 

<?php
include ("credentials.php");

# Create Data
$fname2 = 'J';
$lname2 = 'M';

$values = array();
for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

# Create SQL
$qry = 'INSERT INTO 02_Experiment (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";
echo "<b>SQL:</b> " . $qry . "<br>"; 
# This ECHO gives:
# INSERT INTO 02_Experiment (FirstName, LastName) VALUES (?,?), (?,?), (?,?), (?,?), (?,?), (?,?), (?,?), (?,?), (?,?), (?,?)


# Database insert
$stmt2 = $mysqli->prepare($qry);

echo "<h1>Examine Array Values:</h1><br>";
echo "<pre>\n"; print_r($values); echo "</pre>\n";
echo "<br>";
echo "<pre>\n"; var_dump($values); echo "</pre>\n";


# Execution
$stmt2->execute($values);

echo "End of PHP<br>";
?>

The database table consists of three rows: id (primary key, int(10), FirstName (varchar(300)), LastName (varchar(300)). The PHP script does not show any errors. I do not find my bug why the database table is still empty after execution.

Peter
  • 1,224
  • 3
  • 16
  • 28
  • You should read some SQL and prepared statement tutorials. That's not the right format for a prepared statement. – clearshot66 Sep 19 '17 at 17:54
  • 2
    MySQL queries fail silently. You'll want to check for [mysqli_errors](http://php.net/manual/en/mysqli.error.php) – aynber Sep 19 '17 at 17:55
  • 1
    `if(!$stmt2->execute($values)){ echo "Error: " . mysqli_error($mysqli); } else { echo "Success!"; }` - What does that show when you replace it with your `$stmt2->execute($values);`? – Funk Forty Niner Sep 19 '17 at 17:59
  • Good catch @chris85 and wondering which API they're using to connect with. So Peter, which one *are* you using to connnect with? – Funk Forty Niner Sep 19 '17 at 18:02
  • Well, the suspense isn't going to kill me. You can @X_Member if you want more direct help. – Funk Forty Niner Sep 19 '17 at 18:07
  • @chris85: I thought, I am using a Prepared Statement and I thought, PDO is the abbreviation for Prepared Statement. The documentation on PHP.net is getting more and more confusing in the last years. To be honest: I do not understand why they don't discribe it clearly. That's why I'm asking questions here. – Peter Sep 19 '17 at 19:26
  • @Fred-ii-: How can I examine this? Do you mean "$mysqli = new mysqli..."??? - If this is the case, yes, I am using $mysqli. I've replaced the line like you requested and I received just the error line *Error:* , but there is no error code behind it. – Peter Sep 19 '17 at 19:26
  • @chris85 : Thank you for the helpful explanation. Then, I have first to read about PDO. – Peter Sep 19 '17 at 21:35
  • 1
    @chris85: after changing my DB Connection to PDO and adjust the PHP, the INSERT works perfectly! – Peter Sep 20 '17 at 09:09
  • @Peter Great, I've removed my comments and put it as an answer. – chris85 Sep 20 '17 at 14:49

1 Answers1

0

Your syntax and usage would be correct if you were using PDO. Mysqli doesn't support binding in the execute function so:

$stmt2->execute($values);

is incorrect. You will need to use, bind_param, or switch to a PDO connection.

chris85
  • 23,846
  • 7
  • 34
  • 51