How to use prepared mysqli commands in PHP?

Question

I am trying to prepare prepared statements, but the code below does not work. I get an error message:

Fatal error: calling the execute () member function on a non-object in /var/www/prepared.php on line 12

<?php $mysqli = new mysqli("localhost", "root", "root", "test"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: " . $mysqli->connect_error; } $stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)"); // insert one row $stmt->execute(array('one',1)); // insert another row with different values $stmt->execute(array('two',1)); ?> 

Also, do I need to use mysqli for prepared statements? Can someone point me to a complete example of prepared statements from connecting to an insert to select with error handling?

Answer 1

fdsa From mysqli::prepare docs :

The parameter marker must be bound to application variables using mysqli_stmt_bind_param () and / or mysqli_stmt_bind_result () before executing an instruction or fetching strings.

bind_param docs .

i.e:.

 $stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)"); // bind parameters. I'm guessing 'string' & 'integer', but read documentation. $stmt->bind_param('si','one',1); // *now* we can execute $stmt->execute(); 

Answer 2

I also need to use mysqli for a prepared statement. Can someone point me to a complete example of a prepared statement from connecting to an insert to an error handling selection

You can also use PDO, which I prefer. Actually, it looks like you are confusing PDO and Mysqli in your sample code.

 $db = new PDO($dsn, $user, $pass); $stmt = $db->prepare("INSERT INTO users (name, age) VALUES (?,?)"); $stmt->execute(array($name1, $age1)); $stmt->execute(array($name2, $age2)); 

Unlike mysqli, you do not need to call a separate binding function, although this function is available if you prefer / want / should use it.

Another fun thing about PDO is called placeholders, which can be much less confusing in complex queries:

 $db = new PDO($dsn, $user, $pass); $stmt = $db->prepare("INSERT INTO users (name, age) VALUES (:name,:age)"); $stmt->execute(array(':name' => $name1, ':age' => $age1)); $stmt->execute(array(':name' => $name2, ':age' => $age2)); 

Answer 3

Try this, clear the incoming data. And don't forget the php tags.

 function clean($data) { $data = trim(strip_tags(htmlspecialchars($data))); return $data; } $field1 = isset($_POST['field1']) ? clean($_POST['field1']): NULL; $field2 = isset($_POST['field2']) ? clean($_POST['field2']): NULL; $field3 = isset($_POST['field3']) ? clean($_POST['field3']): NULL; $field4 = isset($_POST['field4']) ? clean($_POST['field4']): NULL; $field5 = isset($_POST['field5']) ? clean($_POST['field5']): NULL; $field6 = isset($_POST['field6']) ? clean($_POST['field6']): NULL; $field7 = isset($_POST['field7']) ? clean($_POST['field7']): NULL; $database = new mysqli("localhost", "username", "password", "database"); if ($database->errno) die("Error opening database: " . $database->error()); $query = 'INSERT INTO `tablename` (`field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`) VALUES (?, ?, ?, ?, ?, ?, ?)'; $result = $database->prepare($query); $result->bind_param('sssssss', $field1, $field2, $field3, $field4, $field5, $field6, $field7); $result->execute(); $database->close(); { header("Location: http://www.somewebsite.com"); }