mysql - create database with pdo in php

Question

When I create database without using bind param, it works perfectly.

$login = 'root';
$password = 'root';
$dsn = "mysql:host=localhost";

$opt = array(
// any occurring errors wil be thrown as PDOException
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// an SQL command to execute when connecting
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

// Making a new PDO conenction.
$conn = new PDO($dsn, $login, $password,$opt);

$db = $conn->prepare( 'CREATE SCHEMA IF NOT EXISTS account');

$db->execute();      
// End Connection and Return to other files.

But after applying the bindParam it is not working properly.

$db = $conn->prepare( 'CREATE SCHEMA IF NOT EXISTS ?');
$db->bindParam(1,`account`);
$db->execute();  //line 18

Showing error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1' in /Applications/MAMP/htdocs/create/index.php:18 Stack trace: #0 /Applications/MAMP/htdocs/create/index.php(18): PDOStatement->execute() #1 {main} thrown in /Applications/MAMP/htdocs/create/index.php on line 18

UPDATE:

<?php
$login = 'root'; // Login username of server host.
$password = 'root'; // Password of server host.
$dsn = "mysql:host=localhost"; // Set up a DSN for connection with Database Frat.
$dbb = 'sale';
$opt = array(
// any occurring errors wil be thrown as PDOException
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// an SQL command to execute when connecting
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

// Making a new PDO conenction.
$conn = new PDO($dsn, $login, $password,$opt);

$db = $conn->prepare( 'CREATE SCHEMA IF NOT EXISTS ?');
$db->bindParam(1,'$dbb'); //line 17
$db->execute();          

?>

it showing error: Cannot pass parameter 2 by reference on line 17

Answer 1

There are two big problems here. The first is minor. These lines of code will never work:

$db->bindParam(1,`account`);
$db->bindParam(1,'$dbb'); //line 17

This is because both of them are attempting to call bindParam as a string. This is impossible. bindParam needs a reference to a variable. This is why you get a "cannot pass parameter 2 by reference" error: you can only pass variables by reference.

Either of these, however, would work:

$db->bindParam(1, $dbb); // call bindParam on a variable
$db->bindValue(1, 'account'); // call bindValue on a string literal

The more fundamental problem, however, is your understanding of prepared statements. The idea of prepared statements is not simple substitution of strings into another string. It is fundamentally about separation of the structure of the query from the data. The name of a table is considered part of the structure of the query, not part of the data. You need to put the table name in the original query. Your first code is the way to do it.

$db = $conn->prepare( 'CREATE SCHEMA IF NOT EXISTS account');