Insert array into MySQL database with PHP

Question

I have the following array I want to store in my database      insData   array       uid    gt   fbme  id         first name    gt   fbme  first name         last name    gt   fbme  last name         email    gt  isset  fbme  email       fbme  email              link    gt   fbme  link         affiliations    gt   networks       birthday    gt   info 0   birthday date         current location    gt  isset  fbme  location    name       fbme  location    name              education history    gt   education       work    gt   workInfo       hometown location    gt  isset  fbme  hometown    name       fbme  hometown    name              interests    gt   info 0   interests         locale    gt   info 0   locale         movies    gt   movies       music    gt   music       political    gt   info 0   political         relationship status    gt   info 0   relationship status         sex    gt   isset  fbme  gender       fbme  gender              tv    gt   television       status    gt   0        created    gt   now       updated    gt   now       I ve tried searching google on how to do this and all I can find is information stating my array needs to be split  before inserting into the table  Is this correct  Sorry for the naivity  very new to php

User · Answer

function insertQuery  tableName  cols  values  connection      numberOfColsAndValues   count  cols      query    INSERT INTO    tableName      getColNames  cols  numberOfColsAndValues     VALUES    getColValues  values  numberOfColsAndValues          if mysqli query  connection   query         return true     else        echo  Error       query     lt br gt     mysqli error  connection         return false          function getColNames  cols  numberOfColsAndValues      result         foreach  cols as  key   gt   val        result    result  val               return substr  result 0 strlen  result -2       function getColValues  values  numberOfColsAndValues      result         foreach  values as  key   gt   val        val      val         result    result  val               return substr  result 0 strlen  result -2

User · Answer

lt  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    gt  false                         mysql insert id    gt  mysqli insert id  con                          mysql affected rows    gt  mysqli affected rows  con                          mysql info    gt  mysqli info  con                               else           return array   mysql error    gt  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   lt br   gt        echo   lt pre gt        print r  result       echo   lt  pre gt        gt

User · Answer

You can not insert an array directly to MySQL as MySQL doesn t understand PHP data types  MySQL only understands SQL  So to insert an array into a MySQL database you have to convert it to a SQL statement  This can be done manually or by a library  The output should be an INSERT statement   Update for PHP7 Since PHP 5 5 mysql real escape string has been deprecated and as of PHP7 it has been removed  See  php net s documentation on the new procedure    Original answer  Here is a standard MySQL insert statement  INSERT INTO TABLE1 COLUMN1  COLUMN2        VALUES  VALUE1  VALUE2     If you have a table with name fbdata with the columns which are presented in the keys of your array you can insert with this small snippet  Here is how your array is converted to this statement   columns   implode  quot    quot  array keys  insData     escaped values   array map  mysql real escape string   array values  insData     values    implode  quot    quot    escaped values    sql    quot INSERT INTO  fbdata   columns  VALUES   values  quot

User · Answer

query   INSERT INTO table       implode      array keys  insData        VALUES      implode      array values  insData            Only need to write this line to insert an array into a database   implode      array keys  insData      Gives you all keys as string format  implode      array values  insData     Gives you all values as string format

User · Answer

Personally I d json encode the array  taking into account any escaping etc needed  and bung the entire lot into an appropriately sized text blob field   It makes it very easy to store  unstructured  data but a real PITA to search index on with any grace   A simple json decode will  explode  the data back into an array for you

User · Answer

You have 2 ways of doing it    You can create a table  or multiple tables linked together  with a field for each key of your array  and insert into each field the corresponding value of your array  This is the most common way You can just have a table with one field and put in here your array serialized  I do not recommend you do do that  but it is useful if you don t want a complex database schema

User · Answer

Insert array data into mysql php  I Have array data   I want post that data in database  1  this is my array data   stdClass Object        questions    gt  Array                        0    gt  stdClass Object                                        question id    gt  54                      question    gt  Which 20of 20the 20following 20is 20a 20rational 20number 20 s  3F 3Cbr 20 2F 3E 0D 0A 3Cbr 20 2F 3E                      option 1    gt   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    gt   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    gt   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    gt   26nbsp 3BAll 20the 20above 26nbsp 3B                      iscorrect    gt  yes                      answerGiven    gt  D                      marksobtain    gt  2                      timetaken    gt  3                      difficulty levelval    gt  2                         1    gt  stdClass Object                                        question id    gt  58                      question    gt   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    gt   26nbsp 3BA 20is 20False 20and 20R 20is 20true                       option 2    gt  A 20is 20True 20and 20R 20is 20the 20correct 20explanation 20of 20A                      option 3    gt   26nbsp 3BA 20is 20True 20and 20R 20is 20false                      option 4    gt   26nbsp 3BBoth 20A 20and 20R 20are 20True                      iscorrect    gt  no                      answerGiven    gt  D                      marksobtain    gt  0                      timetaken    gt  2                      difficulty levelval    gt  2                                   code used i used to insert that data   Code      lt  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

User · Answer

I search about the same problem  but I wanted to store the array in a filed not to add the array as a tuple  so you may need the function serialize   and unserialize     See this http   www wpfasthelp com insert-php-array-into-mysql-database-table-row-field htm

User · Answer

Let s not forget the most important thing to learn out of a question like this  SQL Injection   Use PDO and prepared statements   Click here for a tutorial on PDO

User · Answer

Recently I m learning Wordpress it provide a class WP QUERY to operate the database  You can insert array or object to the database I feel it is amazing so I go to see how to achieve it Maybe some ideas you can get from it The main function is  apply filters   apply filters   tag   value   key     you can ignore the first parameter  treat the second parameter as array what the  function do is sanitizing the array into string   after inserting into the database you can see this    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

User · Answer

most easiest way   for   i 0   i  lt  count  tableData    i                 cost       tableData  i   cost             quantity   tableData  i   quantity             price      tableData  i   price             p id       tableData  i   p id             mysqli query  conn  INSERT INTO bill details  bill id  price  bill date  p id  quantity  cost  VALUES   bill id bill id   price  date  p id  quantity  cost

User · Answer

Maybe it will be to complex for this question but it surely do the job for you  I have created two classes to handle not only array insertion but also querying the database  updating and deleting files   MySqliConnection  class is used to create only one instance of db connection  to prevent duplication of new objects          lt  php                       MySQLi database connection  only one connection is allowed             class MySqliConnection          public static   instance          public   connection           public function   construct  host   user   password   database                this- gt  connection   new MySQLi  host   user   password   database               if  isset  mysqli- gt connect error                     echo  Failed to connect to MySQL        mysqli- gt connect errno           mysqli- gt connect error                  echo  mysqli- gt 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- gt  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- gt getConnection             sql query    SELECT   FROM users               this- gt lastQuery    sql query               result    mysqli- gt query  sql query           while  row    result- gt fetch array MYSQLI ASSOC                echo  row  ID                        The second  TableManager  class is little bit more complex  It also make use of the MySqliConnection class which I posted above  So you would have to include both of them in your project  TableManager will allow you to easy make insertion updates and deletions  Class have separate placeholder for read and write     lt  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- gt tableIdName    tableIdName           this- gt tableName    tableName           this- gt getColumnNames             this- gt curentRow    this- gt columnNames         public function getColumnNames         sql query    SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE table name       this- gt tableName           mysqli    this- gt connection         this- gt lastQuery    sql query       result    mysqli- gt query  sql query       if    result            throw new Exception  Database Error    this- gt database- gt errno     this- gt database- gt error               while  row    result- gt fetch array MYSQLI ASSOC                        this- gt 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- gt curentRow    v              return true                        foreach   v as  a   gt   b                 method    set  ucfirst  a               if is callable array  this   method                       if method is callable use setSomeFunction  k   v  to filter the value                  this- gt  method  b                else                   this- gt 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- gt connection             quote post    mysqli- gt real escape string  quote post            this- gt tableName    mysqli- gt real escape string  this- gt tableName            column name    mysqli- gt real escape string  column name           if  this- gt tableName  amp  amp   column name  amp  amp   quote post                sql query    SELECT   FROM    this- gt tableName   WHERE    column name         quote post                   this- gt lastQuery    sql query               result    mysqli- gt query  sql query               if    result                    throw new Exception  Database Error    this- gt database- gt errno     this- gt database- gt error                               while  row    result- gt fetch array MYSQLI ASSOC                     this- gt lastResult  row  ID       row                   this- gt setRowValues  row                                   if  this- gt tableName  amp  amp   column name  amp  amp    quote post                sql query    SELECT    column name   FROM    this- gt tableName                  this- gt lastQuery    sql query               result    mysqli- gt query  sql query               if    result                    throw new Exception  Database Error    this- gt database- gt errno     this- gt database- gt error                               while  row    result- gt fetch array MYSQLI ASSOC                                this- gt lastResult      row                   this- gt setRowValues  row                                          if  this- gt tableName  amp  amp    column name  amp  amp    quote post                sql query    SELECT   FROM    this- gt tableName                  this- gt lastQuery    sql query               result    mysqli- gt query  sql query               if    result                    throw new Exception  Database Error    this- gt database- gt errno     this- gt database- gt error                               while  row    result- gt fetch array MYSQLI ASSOC                                this- gt lastResult  row  ID       row                   this- gt setRowValues  row                                                       Connection class gets instance of db connection or if not exsist creats one        0         return  mysqli            private function connection             this- gt lastResult                db   MySqliConnection  getInstance  localhost    root        sandbox             mysqli    db- gt getConnection            return  mysqli                                 1   string   getMe        return void            function   get  getMe           if isset  this- gt curentRow  getMe                 return  this- gt curentRow  getMe            else              throw new Exception  Error Processing Request - No such a property in  array   this- gt curentRow   1                                             2   string   setMe   string   value        return void            function   set  setMe   value            temp   array  setMe  gt  value            this- gt setRowValues  temp                       Dumps the object        0          return void            function dump            echo   lt hr gt            var dump  this           echo   lt hr gt                        Sets Values for  this- gt newPost array which will be than inserted by insertNewPost   function        1   array   newPost   array of avlue that will be inserted to  this- gt newPost        return bolean            public function setNewRow  arr            if  is array  arr                 this- gt newPost    arr              return false                       foreach   arr as  k   gt   v                if array key exists  k   this- gt columnNames                     method    set  ucfirst  k                   if is callable array  this   method                         if  this- gt  method  v     false     if something go wrong                          this- gt 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- gt newPost  k     v                                 else                   this- gt 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- gt newPost        0         return bolean            public function insertNewRow               check if is set  is array and is not null         if isset  this- gt newPost   amp  amp   is null  this- gt newPost   amp  amp  is array  this- gt newPost                 mysqli    this- gt connection                 count lenght of array   count  this- gt newPost                   preper insert query              sql query    INSERT INTO    this- gt tableName                    i   1              foreach   this- gt newPost as  key   gt   value                     sql query    key                  if   i  lt   count lenght of array                         sql query                                             i                                  i   1                 sql query      VALUES                 foreach   this- gt newPost as  key   gt   value                     sql query        value                      if   i  lt   count lenght of array                         sql query                                             i                                   sql query                    var dump  sql query               if  mysqli- gt query  sql query                     this- gt lastInsertId    mysqli- gt insert id                   this- gt lastQuery    sql query                              this- gt getInsertedPost  this- gt 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- gt connection             sql query    SELECT   FROM    this- gt tableName   WHERE    this- gt tableIdName         id               result    mysqli- gt query  sql query           while  row    result- gt fetch array MYSQLI ASSOC                 this- gt lastResult  row  ID       row               this- gt 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- gt connection             sql query    DELETE FROM    this- gt tableName   WHERE    this- gt tableIdName        this- gt lastInsertId              result    mysqli- gt query  sql query           if  result                this- gt lastResult  this- gt lastInsertId     deleted               return true           else              throw new Exception  We could not delete last inserted row by ID    mysqli- gt errno     mysqli- gt 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- gt connection             sql query    DELETE FROM    this- gt tableName   WHERE    this- gt tableIdName        id              result    mysqli- gt query  sql query           if  result                this- gt lastResult  this- gt 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- gt connection             sql query    DELETE FROM    this- gt tableName              result    mysqli- gt 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- gt curentRow  this- gt tableIdName            mysqli    this- gt connection             updateMe    this- gt curentRow          unset  updateMe  this- gt tableIdName             count lenght of array   count  updateMe              preper insert query          sql query    UPDATE    this- gt tableName   SET             i   1          foreach   updateMe as  k   gt   v                 sql query     k         v                  if   i  lt   count lenght of array                     sql query                                     i                       if  update where    false                  update row only for this object id              sql query     WHERE    this- gt tableIdName        this- gt curentRow  this- gt tableIdName               else                add your custom update where query              sql query     WHERE    update where                        var dump  sql query           if  mysqli- gt query  sql query                 this- gt lastQuery    sql query                     result    mysqli- gt 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- gt curentRow values - gt 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- gt getRow  post name    SOME POST TITLE WHICH IS IN DATABASE      Post- gt getRow  post name     Post- gt getRow       MAGIC GET will read current object   this- gt curentRow parameter values by refering to its key as in a varible name echo  Post- gt ID  echo  Post- gt post name  echo  Post- gt post description  echo  Post- gt post author     Task   new TableManager  table name    table ID name       creating new TableManager object   addTask   array    just an array  colum name    gt   values       task name    gt   New Post        description    gt   New Description Post        person    gt   New Author           Task- gt setNewRow  addTask     preper new values for insertion to table  Task- gt getRow  ID    12      load value from table to object   Task- gt insertNewRow      inserts new row  Task- gt dump      diplays object properities   Task- gt person    John     magic   set   method will look for setPerson x y  method firs if non found will assign value as it is    Task- gt description    John Doe is a funny guy     magic   set   again  Task- gt task name    John chellange     magic   set   again   test     Task- gt updateRow  ID   5       WORKS FINE     ERROR     update cutom row with object values echo  test   test     Task- gt updateRow       WORKS FINE     ERROR     update curent data loaded in object echo  test

User · Answer

There are a number of different ways    I will give you an example of one using prepared statements    prep   array    foreach  insData as  k   gt   v          prep      k     v     sth    db- gt prepare  INSERT INTO table       implode      array keys  insData        VALUES      implode      array keys  prep            res    sth- gt execute  prep     I m cheating here and assuming the keys in your first array are the column names in the SQL table  I m also assuming you have PDO available  More can be found at http   php net manual en book pdo php

User · Answer

Serialize the array and you ll have a text on your database column  that will solve the problem   I do that  for instance to save objects  that way I can retrieve them easily

User · Answer

I think the simple method would be using  ob start      Start output buffer print r  var    var   ob get contents      Grab output ob end clean      Discard output buffer   and your array output will be recorded as a simple variable

User · Answer

columns   implode      array keys  data     escaped values   array map array  con   real escape string   array values  data     values    implode          escaped values   return  sql    INSERT INTO  reservations   columns  VALUES    values       This is improvement to the solution given by Shiplu Mokaddim

User · Answer

The simplest method to escape and insert    global  connection   columns   implode      array keys  array data     func   function  value        global  connection      return mysqli real escape string  connection   value       escaped values   array map  func  array values  array data     values    implode        escaped values    result   mysqli query  connection   INSERT INTO  table name   columns  VALUES   values

User · Answer

Here is my full solution to this based on the accepted answer Usage example   include    assets php db php     data   array  field1    gt   data1    field2   gt   data2    insertArr  databaseName tableName    data     db php   lt  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- gt get link         class database      protected  databaseLink      function   construct            include  dbSettings php            this- gt database    dbInfo  host             this- gt mysql user    dbInfo  user             this- gt mysql pass    dbInfo  pass             this- gt openConnection            return  this- gt get link              function openConnection         this- gt databaseLink   mysql connect  this- gt database   this- gt mysql user   this- gt mysql pass              function get link        return  this- gt databaseLink                  Insert an associative array into a MySQL database        example        data   array  field1    gt   data1    field2   gt   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  gt  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- gt get link         gt    dbSettings php   lt  PHP  dbInfo   array       host         gt   localhost        user         gt   root        pass         gt   password       gt

[php] Insert array into MySQL database with PHP

Examples related to php

Examples related to mysql

Examples related to arrays