Вставить массив в базу данных MySQL с помощью PHP

Вы не можете вставить массив непосредственно в mysql, поскольку mysql не понимает типы данных php . Mysql понимает только SQL . Поэтому, чтобы вставить массив в базу данных mysql, вы должны преобразовать его в оператор sql. Это можно сделать вручную или через библиотеку. Результат должен быть INSERT .

Вот стандартный оператор вставки mysql.

 INSERT INTO TABLE1(COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2..) 

Если у вас есть таблица с именем fbdata с столбцами, которые представлены в ключах вашего массива, вы можете вставить этот небольшой фрагмент. Вот как ваш массив преобразуется в этот оператор.

 $columns = implode(", ",array_keys($insData)); $escaped_values = array_map('mysql_real_escape_string', array_values($insData)); $values = implode(", ", $escaped_values); $sql = "INSERT INTO `fbdata`($columns) VALUES ($values)"; 

Обновление для PHP7

Поскольку PHP 5.5 mysql_real_escape_string устарел, а с PHP7 он удален. См. Документацию Php.net о новой процедуре.

Есть несколько разных способов … Я приведу вам пример из одного, используя подготовленные заявления:

 $prep = array(); foreach($insData as $k => $v ) { $prep[':'.$k] = $v; } $sth = $db->prepare("INSERT INTO table ( " . implode(', ',array_keys($insData)) . ") VALUES (" . implode(', ',array_keys($prep)) . ")"); $res = $sth->execute($prep); 

Я обманываю здесь и предполагаю, что ключи в вашем первом массиве – это имена столбцов в таблице SQL. Я также предполагаю, что у вас есть PDO. Подробнее можно узнать по адресу http://php.net/manual/en/book.pdo.php

Вот мое полное решение этого на основе принятого ответа.

Пример использования:

 include("./assets/php/db.php"); $data = array('field1' => 'data1', 'field2'=> 'data2'); insertArr("databaseName.tableName", $data); в include("./assets/php/db.php"); $data = array('field1' => 'data1', 'field2'=> 'data2'); insertArr("databaseName.tableName", $data); 

db.php

 <?PHP /** * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php * * @example * $db = new database(); // Initiate a new database connection * mysql_close($db->get_link()); */ class database{ protected $databaseLink; function __construct(){ include "dbSettings.php"; $this->database = $dbInfo['host']; $this->mysql_user = $dbInfo['user']; $this->mysql_pass = $dbInfo['pass']; $this->openConnection(); return $this->get_link(); } function openConnection(){ $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass); } function get_link(){ return $this->databaseLink; } } /** * Insert an associative array into a MySQL database * * @example * $data = array('field1' => 'data1', 'field2'=> 'data2'); * insertArr("databaseName.tableName", $data); */ function insertArr($tableName, $insData){ $db = new database(); $columns = implode(", ",array_keys($insData)); $escaped_values = array_map('mysql_real_escape_string', array_values($insData)); foreach ($escaped_values as $idx=>$data) $escaped_values[$idx] = "'".$data."'"; $values = implode(", ", $escaped_values); $query = "INSERT INTO $tableName ($columns) VALUES ($values)"; mysql_query($query) or die(mysql_error()); mysql_close($db->get_link()); } ?> 

dbSettings.php

 <?PHP $dbInfo = array( 'host' => "localhost", 'user' => "root", 'pass' => "password" ); ?> 

Сериализуйте массив, и у вас будет текст в столбце базы данных, который решит проблему.

Я делаю это, например, для сохранения объектов, поэтому я могу легко их получить.

Лично я бы json_encode массива (с учетом любого вытекания и т. Д.) И вытащил всю партию в поле text / blob соответствующего размера.

Это позволяет легко хранить «неструктурированные» данные, но реальный PITA для поиска / индексации с любой изюминкой.

Простой json_decode «взорвет» данные обратно в массив для вас.

Вставить данные массива в mysql php

У меня есть данные массива. Я хочу опубликовать данные в базе данных

1: это мои данные массива:

 stdClass Object ( [questions] => Array ( [0] => stdClass Object ( [question_id] => 54 [question] => Which%20of%20the%20following%20is%20a%20rational%20number%20(s)%3F%3Cbr%20%2F%3E%0D%0A%3Cbr%20%2F%3E [option_1] => %3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F010779c34ce28fee25778247e127b82d.png%22%20alt%3D%22%22%20%2F%3E%3Cspan%20class%3D%22Apple-tab-span%22%20style%3D%22white-space%3A%20pre%3B%20%22%3E%09%3C%2Fspan%3E [option_2] => %26nbsp%3B%3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F3af35a16c371ffaaf9ea6891fb732478.png%22%20alt%3D%22%22%20%2F%3E [option_3] => %26nbsp%3B%3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F4a57d5766a79f0ddf659d63c7443982b.png%22%20alt%3D%22%22%20%2F%3E [option_4] => %26nbsp%3BAll%20the%20above%26nbsp%3B [iscorrect] => yes [answerGiven] => D [marksobtain] => 2 [timetaken] => 3 [difficulty_levelval] => 2 ) [1] => stdClass Object ( [question_id] => 58 [question] => %3Cdiv%3EIf%20A%20%26nbsp%3B%3A%20Every%20whole%20number%20is%20a%20natural%20number%20and%3C%2Fdiv%3E%0D%0A%3Cdiv%3E%26nbsp%3B%20%26nbsp%3BR%20%3A%200%20is%20not%20a%20natural%20number%2C%3C%2Fdiv%3E%0D%0A%3Cdiv%3EThen%20which%20of%20the%20following%20statement%20is%20true%3F%3C%2Fdiv%3E [option_1] => %26nbsp%3BA%20is%20False%20and%20R%20is%20true. [option_2] => A%20is%20True%20and%20R%20is%20the%20correct%20explanation%20of%20A [option_3] => %26nbsp%3BA%20is%20True%20and%20R%20is%20false [option_4] => %26nbsp%3BBoth%20A%20and%20R%20are%20True [iscorrect] => no [answerGiven] => D [marksobtain] => 0 [timetaken] => 2 [difficulty_levelval] => 2 ) ) ) 

используемого кода, который я использовал для вставки этих данных:

Код:

 <?php //require_once("config_new2012.php"); require("codelibrary/fb/facebook.php"); include("codelibrary/inc/variables.php"); include_once(INC."functions.php"); include_once(CLASSES."frontuser_class.php"); include_once(CLASSES."testdetails_class.php"); $data = file_get_contents('php://input'); $arr_data = explode("=",$data); $final_data = urldecode($arr_data[1]); $final_data2 = json_decode($final_data); //print_r ($final_data2); if(is_array($final_data2)){ echo 'i am in array '; $sql = "INSERT INTO p_user_test_details(question_id, question, option_1, option_2, option_3, option_4,iscorrect,answerGiven,marksobtain,timetaken,difficulty_levelval) values "; $valuesArr = array(); foreach($final_data2 as $row){ $question_id = (int) $row['question_id']; $question = mysql_real_escape_string( $row['question'] ); $option_1 = mysql_real_escape_string( $row['option_1'] ); $option_2 = mysql_real_escape_string( $row['option_2'] ); $option_3 = mysql_real_escape_string( $row['option_3'] ); $option_4 = mysql_real_escape_string( $row['option_4'] ); $iscorrect = mysql_real_escape_string( $row['iscorrect'] ); $answerGiven = mysql_real_escape_string( $row['answerGiven'] ); $marksobtain = mysql_real_escape_string( $row['marksobtain'] ); $timetaken = mysql_real_escape_string( $row['timetaken'] ); $difficulty_levelval = mysql_real_escape_string( $row['difficulty_levelval'] ); $valuesArr[] = "('$question_id', '$question', '$option_1','$option_2','$option_3','$option_4','$iscorrect','$answerGiven','$marksobtain','$timetaken','$difficulty_levelval')"; } $sql .= implode(',', $valuesArr); mysql_query($sql) or exit(mysql_error()); } else{ echo 'no one is there '; } в <?php //require_once("config_new2012.php"); require("codelibrary/fb/facebook.php"); include("codelibrary/inc/variables.php"); include_once(INC."functions.php"); include_once(CLASSES."frontuser_class.php"); include_once(CLASSES."testdetails_class.php"); $data = file_get_contents('php://input'); $arr_data = explode("=",$data); $final_data = urldecode($arr_data[1]); $final_data2 = json_decode($final_data); //print_r ($final_data2); if(is_array($final_data2)){ echo 'i am in array '; $sql = "INSERT INTO p_user_test_details(question_id, question, option_1, option_2, option_3, option_4,iscorrect,answerGiven,marksobtain,timetaken,difficulty_levelval) values "; $valuesArr = array(); foreach($final_data2 as $row){ $question_id = (int) $row['question_id']; $question = mysql_real_escape_string( $row['question'] ); $option_1 = mysql_real_escape_string( $row['option_1'] ); $option_2 = mysql_real_escape_string( $row['option_2'] ); $option_3 = mysql_real_escape_string( $row['option_3'] ); $option_4 = mysql_real_escape_string( $row['option_4'] ); $iscorrect = mysql_real_escape_string( $row['iscorrect'] ); $answerGiven = mysql_real_escape_string( $row['answerGiven'] ); $marksobtain = mysql_real_escape_string( $row['marksobtain'] ); $timetaken = mysql_real_escape_string( $row['timetaken'] ); $difficulty_levelval = mysql_real_escape_string( $row['difficulty_levelval'] ); $valuesArr[] = "('$question_id', '$question', '$option_1','$option_2','$option_3','$option_4','$iscorrect','$answerGiven','$marksobtain','$timetaken','$difficulty_levelval')"; } $sql .= implode(',', $valuesArr); mysql_query($sql) or exit(mysql_error()); } else{ echo 'no one is there '; } 

Я ищу о той же проблеме, но мне захотелось сохранить массив в файле, чтобы не добавлять массив как кортеж, поэтому вам может понадобиться функция serialize () и unserialize ().

См. Это http://www.wpfasthelp.com/insert-php-array-into-mysql-database-table-row-field.htm

Возможно, это будет сложный вопрос для этого квеста, но он наверняка сделает эту работу за вас. Я создал два класса для обработки не только вставки массива, но и запроса базы данных, обновления и удаления файлов. Класс «MySqliConnection» используется для создания только одного экземпляра соединения db (чтобы предотвратить дублирование новых объектов).

  <?php /** * * MySQLi database connection: only one connection is allowed */ class MySqliConnection{ public static $_instance; public $_connection; public function __construct($host, $user, $password, $database){ $this->_connection = new MySQLi($host, $user, $password, $database); if (isset($mysqli->connect_error)) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; echo $mysqli->host_info . "\n"; } } /* * Gets instance of connection to database * @return (MySqliConnection) Object */ public static function getInstance($host, $user, $password, $database){ if(!self::$_instance){ self::$_instance = new self($host, $user, $password, $database); //if no instance were created - new one will be initialize } return self::$_instance; //return already exsiting instance of the database connection } /* * Prevent database connection from bing copied while assignig the object to new wariable * @return (MySqliConnection) Object */ public function getConnection(){ return $this->_connection; } /* * Prevent database connection from bing copied/duplicated while assignig the object to new wariable * @return nothing */ function __clone(){ } } /*// CLASS USE EXAMPLE $db = MySqliConnection::getInstance('localhost', 'root', '', 'sandbox'); $mysqli = $db->getConnection(); $sql_query = 'SELECT * FROM users; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); while($row = $result->fetch_array(MYSQLI_ASSOC)){ echo $row['ID']; } */ 

Класс secound «TableManager» более сложный. Он также использует класс MySqliConnection, который я опубликовал выше. Таким образом, вы должны включить оба из них в свой проект. TableManager позволит вам легко делать обновления и деактивации insetrion. Класс имеет отдельный разделитель для чтения и записи.

 <?php /* * DEPENDENCIES: * include 'class.MySqliConnection.inc'; //custom class * */ class TableManager{ private $lastQuery; private $lastInsertId; private $tableName; private $tableIdName; private $columnNames = array(); private $lastResult = array(); private $curentRow = array(); private $newPost = array(); /* * Class constructor * [1] (string) $tableName // name of the table which you want to work with * [2] (string) $tableIdName // name of the ID field which will be used to delete and update records * @return void */ function __construct($tableName, $tableIdName){ $this->tableIdName = $tableIdName; $this->tableName = $tableName; $this->getColumnNames(); $this->curentRow = $this->columnNames; } public function getColumnNames(){ $sql_query = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "'.$this->tableName.'"'; $mysqli = $this->connection(); $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->columnNames[$row['COLUMN_NAME']] = null; } } /* * Used by a Constructor to set native parameters or virtual array curentRow of the class * [1] (array) $v * @return void */ function setRowValues($v){ if(!is_array($v)){ $this->curentRow = $v; return true; } foreach ($v as $a => $b) { $method = 'set'.ucfirst($a); if(is_callable(array($this, $method))){ //if method is callable use setSomeFunction($k, $v) to filter the value $this->$method($b); }else{ $this->curentRow[$a] = $b; } } } /* * Used by a constructor to set native parameters or virtual array curentRow of the class * [0] * @return void */ function __toString(){ var_dump($this); } /* * Query Database for information - Select column in table where column = somevalue * [1] (string) $column_name // name od a column * [2] (string) $quote_pos // searched value in a specified column * @return void */ public function getRow($column_name = false, $quote_post = false){ $mysqli = $this->connection(); $quote_post = $mysqli->real_escape_string($quote_post); $this->tableName = $mysqli->real_escape_string($this->tableName); $column_name = $mysqli->real_escape_string($column_name); if($this->tableName && $column_name && $quote_post){ $sql_query = 'SELECT * FROM '.$this->tableName.' WHERE '.$column_name.' = "'.$quote_post.'"'; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } if($this->tableName && $column_name && !$quote_post){ $sql_query = 'SELECT '.$column_name.' FROM '.$this->tableName.''; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[] = $row; $this->setRowValues($row); } } if($this->tableName && !$column_name && !$quote_post){ $sql_query = 'SELECT * FROM '.$this->tableName.''; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } } /* * Connection class gets instance of db connection or if not exsist creats one * [0] * @return $mysqli */ private function connection(){ $this->lastResult = ""; $db = MySqliConnection::getInstance('localhost', 'root', '', 'sandbox'); $mysqli = $db->getConnection(); return $mysqli; } /* * ... * [1] (string) $getMe * @return void */ function __get($getMe){ if(isset($this->curentRow[$getMe])){ return $this->curentRow[$getMe]; }else{ throw new Exception("Error Processing Request - No such a property in (array) $this->curentRow", 1); } } /* * ... * [2] (string) $setMe, (string) $value * @return void */ function __set($setMe, $value){ $temp = array($setMe=>$value); $this->setRowValues($temp); } /* * Dumps the object * [0] * @return void */ function dump(){ echo "<hr>"; var_dump($this); echo "<hr>"; } /* * Sets Values for $this->newPost array which will be than inserted by insertNewPost() function * [1] (array) $newPost //array of avlue that will be inserted to $this->newPost * @return bolean */ public function setNewRow($arr){ if(!is_array($arr)){ $this->newPost = $arr; return false; } foreach ($arr as $k => $v) { if(array_key_exists($k, $this->columnNames)){ $method = 'set'.ucfirst($k); if(is_callable(array($this, $method))){ if($this->$method($v) == false){ //if something go wrong $this->newPost = array(); //empty the newPost array and return flase throw new Exception("There was a problem in setting up New Post parameters. [Cleaning array]", 1); } }else{ $this->newPost[$k] = $v; } }else{ $this->newPost = array(); //empty the newPost array and return flase throw new Exception("The column does not exsist in this table. [Cleaning array]", 1); } } } /* * Inserts new post held in $this->newPost * [0] * @return bolean */ public function insertNewRow(){ // check if is set, is array and is not null if(isset($this->newPost) && !is_null($this->newPost) && is_array($this->newPost)){ $mysqli = $this->connection(); $count_lenght_of_array = count($this->newPost); // preper insert query $sql_query = 'INSERT INTO '.$this->tableName.' ('; $i = 1; foreach ($this->newPost as $key => $value) { $sql_query .=$key; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } $i = 1; $sql_query .=') VALUES ('; foreach ($this->newPost as $key => $value) { $sql_query .='"'.$value.'"'; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } $sql_query .=')'; var_dump($sql_query); if($mysqli->query($sql_query)){ $this->lastInsertId = $mysqli->insert_id; $this->lastQuery = $sql_query; } $this->getInsertedPost($this->lastInsertId); } } /* * getInsertedPost function query the last inserted id and assigned it to the object. * [1] (integer) $id // last inserted id from insertNewRow fucntion * @return void */ private function getInsertedPost($id){ $mysqli = $this->connection(); $sql_query = 'SELECT * FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = "'.$id.'"'; $result = $mysqli->query($sql_query); while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } /* * getInsertedPost function query the last inserted id and assigned it to the object. * [0] * @return bolean // if deletion was successful return true */ public function deleteLastInsertedPost(){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = '.$this->lastInsertId.''; $result = $mysqli->query($sql_query); if($result){ $this->lastResult[$this->lastInsertId] = "deleted"; return true; }else{ throw new Exception("We could not delete last inserted row by ID [{$mysqli->errno}] {$mysqli->error}"); } var_dump($sql_query); } /* * deleteRow function delete the row with from a table based on a passed id * [1] (integer) $id // id of the table row to be delated * @return bolean // if deletion was successful return true */ public function deleteRow($id){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = '.$id.''; $result = $mysqli->query($sql_query); if($result){ $this->lastResult[$this->lastInsertId] = "deleted"; return true; }else{ return false; } var_dump($sql_query); } /* * deleteAllRows function deletes all rows from a table * [0] * @return bolean // if deletion was successful return true */ public function deleteAllRows(){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.''; $result = $mysqli->query($sql_query); if($result){ return true; }else{ return false; } } /* * updateRow function updates all values to object values in a row with id * [1] (integer) $id * @return bolean // if deletion was successful return true */ public function updateRow($update_where = false){ $id = $this->curentRow[$this->tableIdName]; $mysqli = $this->connection(); $updateMe = $this->curentRow; unset($updateMe[$this->tableIdName]); $count_lenght_of_array = count($updateMe); // preper insert query $sql_query = 'UPDATE '.$this->tableName.' SET '; $i = 1; foreach ($updateMe as $k => $v) { $sql_query .= $k.' = "'.$v.'"'; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } if($update_where == false){ //update row only for this object id $sql_query .=' WHERE '.$this->tableIdName.' = '.$this->curentRow[$this->tableIdName].''; }else{ //add your custom update where query $sql_query .=' WHERE '.$update_where.''; } var_dump($sql_query); if($mysqli->query($sql_query)){ $this->lastQuery = $sql_query; } $result = $mysqli->query($sql_query); if($result){ return true; }else{ return false; } } } /*TO DO 1 insertPost(X, X) write function to isert data and in to database; 2 get better query system and display data from database; 3 write function that displays data of a object not databsae; object should be precise and alocate only one instance of the post at a time. // Updating the Posts to curent object $this->curentRow values ->updatePost(); // Deleting the curent post by ID // Add new row to database */ /* USE EXAMPLE $Post = new TableManager("post_table", "ID"); // New Object // Getting posts from the database based on pased in paramerters $Post->getRow('post_name', 'SOME POST TITLE WHICH IS IN DATABASE' ); $Post->getRow('post_name'); $Post->getRow(); MAGIC GET will read current object $this->curentRow parameter values by refering to its key as in a varible name echo $Post->ID. echo $Post->post_name; echo $Post->post_description; echo $Post->post_author; $Task = new TableManager("table_name", "table_ID_name"); // creating new TableManager object $addTask = array( //just an array [colum_name] => [values] 'task_name' => 'New Post', 'description' => 'New Description Post', 'person' => 'New Author', ); $Task->setNewRow($addTask); //preper new values for insertion to table $Task->getRow('ID', '12'); //load value from table to object $Task->insertNewRow(); //inserts new row $Task->dump(); //diplays object properities $Task->person = "John"; //magic __set() method will look for setPerson(x,y) method firs if non found will assign value as it is. $Task->description = "John Doe is a funny guy"; //magic __set() again $Task->task_name = "John chellange"; //magic __set() again $test = ($Task->updateRow("ID = 5")) ? "WORKS FINE" : "ERROR"; //update cutom row with object values echo $test; $test = ($Task->updateRow()) ? "WORKS FINE" : "ERROR"; //update curent data loaded in object echo $test; */ с <?php /* * DEPENDENCIES: * include 'class.MySqliConnection.inc'; //custom class * */ class TableManager{ private $lastQuery; private $lastInsertId; private $tableName; private $tableIdName; private $columnNames = array(); private $lastResult = array(); private $curentRow = array(); private $newPost = array(); /* * Class constructor * [1] (string) $tableName // name of the table which you want to work with * [2] (string) $tableIdName // name of the ID field which will be used to delete and update records * @return void */ function __construct($tableName, $tableIdName){ $this->tableIdName = $tableIdName; $this->tableName = $tableName; $this->getColumnNames(); $this->curentRow = $this->columnNames; } public function getColumnNames(){ $sql_query = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "'.$this->tableName.'"'; $mysqli = $this->connection(); $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->columnNames[$row['COLUMN_NAME']] = null; } } /* * Used by a Constructor to set native parameters or virtual array curentRow of the class * [1] (array) $v * @return void */ function setRowValues($v){ if(!is_array($v)){ $this->curentRow = $v; return true; } foreach ($v as $a => $b) { $method = 'set'.ucfirst($a); if(is_callable(array($this, $method))){ //if method is callable use setSomeFunction($k, $v) to filter the value $this->$method($b); }else{ $this->curentRow[$a] = $b; } } } /* * Used by a constructor to set native parameters or virtual array curentRow of the class * [0] * @return void */ function __toString(){ var_dump($this); } /* * Query Database for information - Select column in table where column = somevalue * [1] (string) $column_name // name od a column * [2] (string) $quote_pos // searched value in a specified column * @return void */ public function getRow($column_name = false, $quote_post = false){ $mysqli = $this->connection(); $quote_post = $mysqli->real_escape_string($quote_post); $this->tableName = $mysqli->real_escape_string($this->tableName); $column_name = $mysqli->real_escape_string($column_name); if($this->tableName && $column_name && $quote_post){ $sql_query = 'SELECT * FROM '.$this->tableName.' WHERE '.$column_name.' = "'.$quote_post.'"'; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } if($this->tableName && $column_name && !$quote_post){ $sql_query = 'SELECT '.$column_name.' FROM '.$this->tableName.''; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[] = $row; $this->setRowValues($row); } } if($this->tableName && !$column_name && !$quote_post){ $sql_query = 'SELECT * FROM '.$this->tableName.''; $this->lastQuery = $sql_query; $result = $mysqli->query($sql_query); if (!$result) { throw new Exception("Database Error [{$this->database->errno}] {$this->database->error}"); } while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } } /* * Connection class gets instance of db connection or if not exsist creats one * [0] * @return $mysqli */ private function connection(){ $this->lastResult = ""; $db = MySqliConnection::getInstance('localhost', 'root', '', 'sandbox'); $mysqli = $db->getConnection(); return $mysqli; } /* * ... * [1] (string) $getMe * @return void */ function __get($getMe){ if(isset($this->curentRow[$getMe])){ return $this->curentRow[$getMe]; }else{ throw new Exception("Error Processing Request - No such a property in (array) $this->curentRow", 1); } } /* * ... * [2] (string) $setMe, (string) $value * @return void */ function __set($setMe, $value){ $temp = array($setMe=>$value); $this->setRowValues($temp); } /* * Dumps the object * [0] * @return void */ function dump(){ echo "<hr>"; var_dump($this); echo "<hr>"; } /* * Sets Values for $this->newPost array which will be than inserted by insertNewPost() function * [1] (array) $newPost //array of avlue that will be inserted to $this->newPost * @return bolean */ public function setNewRow($arr){ if(!is_array($arr)){ $this->newPost = $arr; return false; } foreach ($arr as $k => $v) { if(array_key_exists($k, $this->columnNames)){ $method = 'set'.ucfirst($k); if(is_callable(array($this, $method))){ if($this->$method($v) == false){ //if something go wrong $this->newPost = array(); //empty the newPost array and return flase throw new Exception("There was a problem in setting up New Post parameters. [Cleaning array]", 1); } }else{ $this->newPost[$k] = $v; } }else{ $this->newPost = array(); //empty the newPost array and return flase throw new Exception("The column does not exsist in this table. [Cleaning array]", 1); } } } /* * Inserts new post held in $this->newPost * [0] * @return bolean */ public function insertNewRow(){ // check if is set, is array and is not null if(isset($this->newPost) && !is_null($this->newPost) && is_array($this->newPost)){ $mysqli = $this->connection(); $count_lenght_of_array = count($this->newPost); // preper insert query $sql_query = 'INSERT INTO '.$this->tableName.' ('; $i = 1; foreach ($this->newPost as $key => $value) { $sql_query .=$key; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } $i = 1; $sql_query .=') VALUES ('; foreach ($this->newPost as $key => $value) { $sql_query .='"'.$value.'"'; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } $sql_query .=')'; var_dump($sql_query); if($mysqli->query($sql_query)){ $this->lastInsertId = $mysqli->insert_id; $this->lastQuery = $sql_query; } $this->getInsertedPost($this->lastInsertId); } } /* * getInsertedPost function query the last inserted id and assigned it to the object. * [1] (integer) $id // last inserted id from insertNewRow fucntion * @return void */ private function getInsertedPost($id){ $mysqli = $this->connection(); $sql_query = 'SELECT * FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = "'.$id.'"'; $result = $mysqli->query($sql_query); while($row = $result->fetch_array(MYSQLI_ASSOC)){ $this->lastResult[$row['ID']] = $row; $this->setRowValues($row); } } /* * getInsertedPost function query the last inserted id and assigned it to the object. * [0] * @return bolean // if deletion was successful return true */ public function deleteLastInsertedPost(){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = '.$this->lastInsertId.''; $result = $mysqli->query($sql_query); if($result){ $this->lastResult[$this->lastInsertId] = "deleted"; return true; }else{ throw new Exception("We could not delete last inserted row by ID [{$mysqli->errno}] {$mysqli->error}"); } var_dump($sql_query); } /* * deleteRow function delete the row with from a table based on a passed id * [1] (integer) $id // id of the table row to be delated * @return bolean // if deletion was successful return true */ public function deleteRow($id){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.' WHERE '.$this->tableIdName.' = '.$id.''; $result = $mysqli->query($sql_query); if($result){ $this->lastResult[$this->lastInsertId] = "deleted"; return true; }else{ return false; } var_dump($sql_query); } /* * deleteAllRows function deletes all rows from a table * [0] * @return bolean // if deletion was successful return true */ public function deleteAllRows(){ $mysqli = $this->connection(); $sql_query = 'DELETE FROM '.$this->tableName.''; $result = $mysqli->query($sql_query); if($result){ return true; }else{ return false; } } /* * updateRow function updates all values to object values in a row with id * [1] (integer) $id * @return bolean // if deletion was successful return true */ public function updateRow($update_where = false){ $id = $this->curentRow[$this->tableIdName]; $mysqli = $this->connection(); $updateMe = $this->curentRow; unset($updateMe[$this->tableIdName]); $count_lenght_of_array = count($updateMe); // preper insert query $sql_query = 'UPDATE '.$this->tableName.' SET '; $i = 1; foreach ($updateMe as $k => $v) { $sql_query .= $k.' = "'.$v.'"'; if ($i < $count_lenght_of_array) { $sql_query .=', '; } $i++; } if($update_where == false){ //update row only for this object id $sql_query .=' WHERE '.$this->tableIdName.' = '.$this->curentRow[$this->tableIdName].''; }else{ //add your custom update where query $sql_query .=' WHERE '.$update_where.''; } var_dump($sql_query); if($mysqli->query($sql_query)){ $this->lastQuery = $sql_query; } $result = $mysqli->query($sql_query); if($result){ return true; }else{ return false; } } } /*TO DO 1 insertPost(X, X) write function to isert data and in to database; 2 get better query system and display data from database; 3 write function that displays data of a object not databsae; object should be precise and alocate only one instance of the post at a time. // Updating the Posts to curent object $this->curentRow values ->updatePost(); // Deleting the curent post by ID // Add new row to database */ /* USE EXAMPLE $Post = new TableManager("post_table", "ID"); // New Object // Getting posts from the database based on pased in paramerters $Post->getRow('post_name', 'SOME POST TITLE WHICH IS IN DATABASE' ); $Post->getRow('post_name'); $Post->getRow(); MAGIC GET will read current object $this->curentRow parameter values by refering to its key as in a varible name echo $Post->ID. echo $Post->post_name; echo $Post->post_description; echo $Post->post_author; $Task = new TableManager("table_name", "table_ID_name"); // creating new TableManager object $addTask = array( //just an array [colum_name] => [values] 'task_name' => 'New Post', 'description' => 'New Description Post', 'person' => 'New Author', ); $Task->setNewRow($addTask); //preper new values for insertion to table $Task->getRow('ID', '12'); //load value from table to object $Task->insertNewRow(); //inserts new row $Task->dump(); //diplays object properities $Task->person = "John"; //magic __set() method will look for setPerson(x,y) method firs if non found will assign value as it is. $Task->description = "John Doe is a funny guy"; //magic __set() again $Task->task_name = "John chellange"; //magic __set() again $test = ($Task->updateRow("ID = 5")) ? "WORKS FINE" : "ERROR"; //update cutom row with object values echo $test; $test = ($Task->updateRow()) ? "WORKS FINE" : "ERROR"; //update curent data loaded in object echo $test; */ 

 <?php function mysqli_insert_array($table, $data, $exclude = array()) { $con= mysqli_connect("localhost", "root","","test"); $fields = $values = array(); if( !is_array($exclude) ) $exclude = array($exclude); foreach( array_keys($data) as $key ) { if( !in_array($key, $exclude) ) { $fields[] = "`$key`"; $values[] = "'" . mysql_real_escape_string($data[$key]) . "'"; } } $fields = implode(",", $fields); $values = implode(",", $values); if( mysqli_query($con,"INSERT INTO `$table` ($fields) VALUES ($values)") ) { return array( "mysql_error" => false, "mysql_insert_id" => mysqli_insert_id($con), "mysql_affected_rows" => mysqli_affected_rows($con), "mysql_info" => mysqli_info($con) ); } else { return array( "mysql_error" => mysqli_error($con) ); } } $a['firstname']="abc"; $a['last name']="xyz"; $a['birthdate']="1993-09-12"; $a['profilepic']="img.jpg"; $a['gender']="male"; $a['email']="abc@hightecit.com"; $a['likechoclate']="Dm"; $a['status']="1"; $result=mysqli_insert_array('registration',$a,'abc'); if( $result['mysql_error'] ) { echo "Query Failed: " . $result['mysql_error']; } else { echo "Query Succeeded! <br />"; echo "<pre>"; print_r($result); echo "</pre>"; } ?> 

У вас есть два способа сделать это:

• Вы можете создать таблицу (или несколько таблиц, связанных друг с другом) с полем для каждого ключа вашего массива и вставить в каждое поле соответствующее значение вашего массива. Это самый распространенный способ
• Вы можете просто иметь таблицу с одним полем и вставлять здесь массив. Я не рекомендую вам делать это, но это полезно, если вам не нужна сложная схема базы данных.

Давайте не будем забывать, что самое главное, чтобы узнать из такого вопроса: SQL Injection .

Используйте PDO и подготовленные операторы .

Нажмите здесь, чтобы получить руководство по PDO.

Недавно я изучаю WordPress, он предоставляет класс WP_QUERY для работы с базой данных. Вы можете вставить массив или объект в базу данных. Я чувствую, что это потрясающе, поэтому я иду посмотреть, как его достичь. Возможно, некоторые идеи вы можете получить от него. Основная функция: apply_filters .

 apply_filters( $tag, $value, $key); 

вы можете игнорировать первый параметр, обрабатывать второй параметр как массив, что делает функция, дезинфицируя массив в строку.

после вставки в базу данных вы можете увидеть это:

 key s:12:"focal_length";s:3:"190";s:3:"iso";s:3:"400";s:13:"shutter_speed";s:6:"0.0004";s:5:"title";s:0:"";}}