Basically, your code would look like this.
$entry = array(
'username' => $_POST['username'], //assuming it's comming from the post data or for instance $row['username'] if from previous select statement
'password' => $_POST['password'],
'email' => $_POST['email']
);
$sth = $dbh->prepare('INSERT into data (`username,` `password`, `email`) VALUES (:username, :password, :email)');
$sth->bindValue(':username', $entry['username'], PDO::PARAM_INT);
$sth->bindValue(':password', $entry['password'], PDO::PARAM_STR);
$sth->bindValue(':email', $entry['email'], PDO::PARAM_STR);
$sth->execute();
If you want the bound variables to be dynamically created, then you need to create with a loop the bindValue rows:
$entry = array(
'username' => $_POST['username'], //assuming it's comming from the post data or for instance $row['username'] if from previous select statement
'password' => $_POST['password'],
'email' => $_POST['email']
);
$sth = $dbh->prepare('INSERT into data (`username,` `password`, `email`) VALUES (:username, :password, :email)');
foreach($entry as $key => $value) {
$sth->bindValue(':'.$key, $entry[$key], PDO::PARAM_STR);
}
$sth->execute();
or inside the foreach
$sth->bindValue(':'.$key, $value, PDO::PARAM_STR);
Since your keys are (username, password, email) their keynames will be initiated to $key variable, and their values to the $value variable. in the first case it will produce:
$sth->bindValue(':username', $entry['username'], PDO::PARAM_INT);
$sth->bindValue(':password', $entry['password'], PDO::PARAM_STR);
$sth->bindValue(':email', $entry['email'], PDO::PARAM_STR);
Which will be evaluated to:
$sth->bindValue(':username', $_POST['username'], PDO::PARAM_INT);
$sth->bindValue(':password', $_POST['password'], PDO::PARAM_STR);
$sth->bindValue(':email', $_POST['email'], PDO::PARAM_STR);
In the second case it will be directly evaluated.
Have in mind it's completely unacceptable to dynamically create the column names in the query. And you have to reason to do it. However, not a full query is also hard to be read from the other developers. It's enough for you to dynamically create the bound values. You can make a method do it for you. For instance, if you column names in the query are the same way aliased, as the names of the input fields, you will have nothing more to do, but to execute the query.
Let's say you have that helper method:
Class DBConnect {
private $_driver = "mysql";
private $_dbname = "xxxx";
private $_host = "xxxx";
private $_user = "xxxx";
private $_password = "xxxx";
private $_port = 3306;
private $_dbh;
public function __construct($driver = NULL, $dbname = NULL, $host = NULL, $user = NULL, $pass = NULL, $port = NULL) {
$driver = $driver ?: $this->_driver;
$dbname = $dbname ?: $this->_dbname;
$host = $host ?: $this->_host;
$user = $user ?: $this->_user;
$pass = $pass ?: $this->_password;
$port = $port ?: $this->_port;
try {
$this->_dbh = new PDO("$driver:host=$host;port=$port;dbname=$dbname", $user, $pass);
$this->_dbh->exec("set names utf8");
} catch(PDOException $e) {
echo $e->getMessage();
}
}
public function query($sql) {
$sth = $this->_dbh->prepare($sql);
foreach ($_REQUEST as $key => $value) {
if(is_int($value)) {
$param = PDO::PARAM_INT;
} elseif(is_bool($value)) {
$param = PDO::PARAM_BOOL;
} elseif(is_null($value)) {
$param = PDO::PARAM_NULL;
} elseif(is_string($value)) {
$param = PDO::PARAM_STR;
} else {
$param = FALSE;
}
$sth->bindValue(":$key", $value, $param);
}
$sth->execute();
$result = $sth->fetchAll();
return $result;
}
}
So, lets say in another class you have a lot of queries, separated by methods:
public function getFirstQuery() {
$sql = "SELECT
col1, col2
FROM table1
WHERE col3 = :col3;";
$query = $this->_db->query($sql);
return $query;
}
public function inserSecondquery() {
$sql = "INSERT INTO
`table1`
(col1, col2)
VALUES
((SELECT
id
FROM table2
WHERE col8 = :col8), :post_field_5);";
$query = $this->_db->query($sql);
return $query;
}
Assuming you have called these queries the query() method which also fetches the data, the select one you can foreach to retrieve the data, and the insert one you can just call, to insert data. The only rule here is the post fields should be named same way, for example <input name="post_field_5" />
You can also take a look here: PDO Dynamic Query Building
OK, it seems you need to find library for active record like the ones CodeIgniter uses, or... use CodeIgniter.
From the official documentation:
http://ellislab.com/codeigniter/user-guide/database/helpers.html
$this->db->insert_string();
This function simplifies the process of writing database inserts. It
returns a correctly formatted SQL insert string. Example: $data =
array('name' => $name, 'email' => $email, 'url' => $url);
$str = $this->db->insert_string('table_name', $data);
The first parameter is the table name, the second is an associative
array with the data to be inserted. The above example produces: INSERT
INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com',
'example.com')
So, in your case, you can have something like this:
<form action="" method="post">
<input type="text" name="username" value="testUser123" />
<input type="password" name="password" value="yourPass666" />
<input type="text" name="email" value="email@example.com" />
<input type="submit" value="submit" />
</form>
<?php
//... extending CI
//... opening a method
$table = 'data';
//comming from somewhere, let's dynamically populated array but for testing purpose I will hardcode:
$columns('username', 'password', 'email');
foreach($columns as $column) {
$data[$column] = $_POST[$column]; // this will produce $data=array('username'=>$_POST['username'],password=....);
}
$str = $this->db->insert_string($table, $data);
?>
If you submit the form in the beginning, you will have:
INSERT INTO data (username, password, email) VALUES ('testUser123', 'yourPass666', 'email@example.com');
The whole active record class doc (insert chosen here)
http://ellislab.com/codeigniter/user-guide/database/active_record.html#insert
