the problem is my function insert inserts my record in two rows.
this is my code to connect to database in a file named :
connect.php
<?php
try{
$db = new PDO("mysql:host=localhost;dbname=NPD" , "root" , "");
echo "connected";
}
catch(Exception $e){
echo $e->getMessage();
}
this is my database class in a file
database.php
<?php
require 'connect.php';
class DB {
public function insertInto($tableName , $info){
global $db;
foreach ($info as $coloumnName => $coloumnValue) {
$stmt = $db->prepare("INSERT INTO $tableName ($coloumnName) VALUES ('$coloumnValue') ");
$stmt->execute();
}
}
}
$da = new DB;
$da->insertInto('tableOne',array('name' => 'lolo' , 'deg' => '100'));
the result in the database is : tableOne
how can to make the insert function inserts my record in one row.
note : i want to insert any number of columns and values.
try to do something like this:
$arr = array('name' => 'lolo' , 'deg' => '100');
$columns=array_keys($arr);
$values=array_values($arr);
$str="INSERT INTO $tableName (".implode(',',$columns).") VALUES ('" . implode("', '", $values) . "' )";
echo $str;//your sql
// $stmt = $db->prepare($str);
// $stmt->execute();//uncomment to execute
This Is how i coded my own insert function
public function insertRecord($table,$records){
//A variable to store all the placeholders for my PDO INSERT values.
$placeholder = '';
for ($i = 0; $i < sizeof($records); $i++){
$placeholder[$i] = '?';
}
//A FOR-LOOP to loop through the records in the $record array
$placeholder = implode(',', $placeholder);
//Imploding ',' in between the placeholders
$sql = "INSERT INTO ".$table." VALUES ("{$placeholder}")";
$query = $this->dbh->prepare($sql);
$query->execute($records);
}
It Might not be the best..worked for me though.
Like this but there are some concerns ( also I haven't tested this )
class DB {
protected $_conn;
public function __construct( $user, $pass, $database='NPD', $host='localhost' ){
try{
$this->_conn = new PDO("mysql:host={$host};dbname={$database}" , $user , $pass);
echo "connected";
}catch(Exception $e){
echo $e->getMessage();
}
}
public function insertInto($tableName , $info){
$sql = 'INSERT INTO $tableName (';
$sql .= '`'implode('`,`', array_keys($info[0])).'`';
$sql .= ')VALUES';
foreach ($info as $index => $row) {
$sql .= '(';
foreach( $row as $column => $value){
$sql .= ':'.$column.$index.',';
$params[':'.$column.$index] = $value;
}
$sql = rtrim($sql, ',');
$sql .= '),';
}
$sql = rtrim($sql, ',');
$stmt = $this->_conn->prepare($sql);
$stmt->execute($params);
}
}
}
$da = new DB('root', '');
$da->insertInto('tableOne',array( array('name' => 'lolo' , 'deg' => '100') ) );
First of all you loose any sql injection protection on the column names. If you can manage the placeholders on the values, then that is ok, but without using them there you loose protection on that as well. This can be solved by using the db schema itself, via Show columns but that gets a wee bit complex.
https://dev.mysql.com/doc/refman/5.7/en/show-columns.html
Second, your input array structure is all wrong, it needs to be array(0=>array(...), 1=>array(...)) instead of just array(...)
Third I would make this class a "Singleton" but that's just me
Forth, if you just want to do a single row at a time you can change this method
public function insertInto($tableName , $info){
$sql = 'INSERT INTO $tableName (';
$sql .= '`'implode('`,`', array_keys($info)).'`';
$sql .= ')VALUES(';
$params = array();
foreach( $info as $column => $value){
$sql .= ':'.$column.$index.',';
$params[':'.$column.$index] = $value;
}
$sql = rtrim($sql, ',');
$sql .= ')';
$stmt = $this->_conn->prepare($sql);
$stmt->execute($params);
}
And use the current input array structure you have.
As some other answers/comments have stated, there are quite a few critiques one could make about this overall process. However, in the interests of simply answering the question, you may want to just build the statement by looping through the columns, then looping through the values, then executing the finished statement (code below is just an example and hasn't been tested):
require 'connect.php';
class DB {
public function insertInto($tableName , $info){
global $db;
$query = "INSERT INTO $tableName (";
$columns = array_keys($info);
// build the columns in the statement
$length = count($columns);
foreach($columns as $index => $column) {
$query .= "$column";
if ($index+1 < $length) {
$query .= ','
}
}
$query .= ") VALUES ("
// build the values in the statement
$i = 1;
$length = count($info);
foreach($info as $value) {
$query .= "'$value'"
if ($i < $length) {
$query .= ","
}
$i++;
}
$query .= ")"
$stmt = $db->prepare($query);
$stmt->execute();
}
}
$da = new DB;
$da->insertInto('tableOne',array('name' => 'lolo' , 'deg' => '100'));