Save PHP array to MySQL

Question

What is a good way to save an array of data to a single mysql field     Also once I query for that array in the mysql table  what is a good way to get it back into array form   Is serialize and unserialize the answer

User · Answer

Serialize and unserialize are pretty common for that  You could also use JSON via json encode and json decode for a less PHP-specific format

User · Answer

You can save your array as a json  there is documentation for json data type  https   dev mysql com doc refman 5 7 en json html I think this is the best solution  and will help you maintain your code more readable by avoiding crazy functions  I expect this is helpful for you

User · Answer

There is no good way to store an array into a single field   You need to examine your relational data and make the appropriate changes to your schema  See example below for a reference to this approach   If you must save the array into a single field then the serialize   and unserialize   functions will do the trick  But you cannot perform queries on the actual content    As an alternative to the serialization function there is also  json encode   and json decode     Consider the following array   a   array      1   gt  array           a    gt  1           b    gt  2           c    gt  3            2   gt  array           a    gt  1           b    gt  2           c    gt  3             To save it in the database you need to create a table like this   c   mysql connect  server   username   password   mysql select db  test     r   mysql query       DROP TABLE IF EXISTS test     r   mysql query       CREATE TABLE test         id INTEGER UNSIGNED NOT NULL        a INTEGER UNSIGNED NOT NULL        b INTEGER UNSIGNED NOT NULL        c INTEGER UNSIGNED NOT NULL        PRIMARY KEY  id             To work with the records you can perform queries such as these  and yes this is an example  beware    function getTest          ret   array         c   connect         query    SELECT   FROM test        r   mysql query  query  c       while   o   mysql fetch array  r MYSQL ASSOC              ret array shift  o      o            mysql close  c       return  ret    function putTest  t         c   connect        foreach   t as  k   gt   v             query    INSERT INTO test  id                    implode     array keys  v                       VALUES   k                    implode      v                             r   mysql query  query  c             mysql close  c      putTest  a    b   getTest      The connect   function returns a mysql connection resource  function connect          c   mysql connect  server   username   password       mysql select db  test        return  c

User · Answer

Generally  yes  serialize and unserialize are the way to go   If your data is something simple  though  saving as a comma-delimited string would probably be better for storage space  If you know that your array will just be a list of numbers  for example  then you should use implode explode  It s the difference between 1 2 3 and a 3  i 0 i 1 i 1 i 2 i 2 i 3     If not  then serialize and unserialize work for all cases

User · Answer

you can insert serialized object   array   to mysql   example serialize  object  and you can unserize object example unserialize  object

User · Answer

As mentioned before - If you do not need to search for data within the array  you can use serialize - but this is  php only   So I would recommend to use json decode   json encode - not only for performance but also for readability and portability  other languages such as javascript can handle json encoded data

User · Answer

Instead of saving it to the database  save it to a file and then call it later    What many php apps do  like sugarcrm  is to just use var export to echo all the data of the array to a file   This is what I use to save my configurations data    private function saveConfig         file put contents  this- gt  data  pathtocompileddata     lt  php    PHP EOL     acs confdata       var export  this- gt  data true                      I think this is a better way to save your data

User · Answer

Just use the serialize PHP function    lt  php  myArray   array  1    2     seralizedArray   serialize  myArray     gt    However  if you are using simple arrays like that you might as well use implode and explode Use a blank array instead of new

User · Answer

I would suggest using implode explode with a character that you know will not be contained in any of the individual array items  Then store it in SQL as a string

User · Answer

Uhh  I don t know why everyone suggests serializing the array   I say  the best way is to actually fit it into your database schema  I have no idea  and you gave no clues  about the actual semantic meaning of the data in your array  but there are generally two ways of storing sequences like that  create table mydata     id int not null auto increment primary key    field1 int not null    field2 int not null          fieldN int not null     This way you are storing your array in a single row   create table mydata       id int not null auto increment primary key             create table myotherdata       id int not null auto increment primary key      mydata id int not null      sequence int not null      data int not null     The disadvantage of the first method is  obviously  that if you have many items in your array  working with that table will not be the most elegant thing  It is also impractical  possible  but quite inelegant as well - just make the columns nullable  to work with sequences of variable length   For the second method  you can have sequences of any length  but of only one type  You can  of course  make that one type varchar or something and serialize the items of your array  Not the best thing to do  but certainly better  than serializing the whole array  right   Either way  any of this methods gets a clear advantage of being able to access an arbitrary element of the sequence and you don t have to worry about serializing arrays and ugly things like that   As for getting it back  Well  get the appropriate row sequence of rows with a query and  well  use a loop   right

User · Answer

check out the implode function  since the values are in an array  you want to put the values of the array into a mysql query that inserts the values into a table    query    INSERT INto hardware  specifications  VALUES    implode      specifications         If the values in the array are text values  you will need to add quotes   query    INSERT INto hardware  specifications  VALUES     implode        specifications         mysql query  query     Also  if you don t want duplicate values  switch the  INto  to  IGNORE  and only unique values will be inserted into the table

User · Answer

The best way  that I found to myself is save array as data string with separator characters   array   array  value1    value2    value3           valuen     array data   implode  array separator    array     query    INSERT INTO my tbl name  id  array data  VALUES NULL       array data            You can then search data  stored in your array with simple query   query    SELECT   FROM my tbl name WHERE array data LIKE   value3       use explode   function to convert  array data  string to array   array   explode  array separator    array data     note that this is not working with multidimensional arrays and make sure that your   array separator  is unique and had not exist in array values    Be careful     if you just will take a form data and put in database  you will be in trap  becous the form data isn t SQL-safe   you must handle your form value  with mysql real escape string or if you use MySQLi mysqli  real escape string or if value are integer or boolean cast  int   boolean  on them    number    int   POST  number     checked    boolean    POST  checked      name   mysql real escape string  db pt    POST  name      email   mysqli obj- gt real escape string   POST  email

User · Answer

Serialize Unserialize array for storage in a DB  Visit http   php net manual en function serialize php  From the PHP Manual   Look under  Return  on the page  Returns a string containing a byte-stream representation of value that can be stored anywhere   Note that this is a binary string which may include null bytes  and needs to be stored and handled as such  For example  serialize   output should generally be stored in a BLOB field in a database  rather than a CHAR or TEXT field   Note  If you want to store html into a blob  be sure to base64 encode it or it could break the serialize function    Example encoding     YourSerializedData   base64 encode serialize  theHTML       YourSerializedData is now ready to be stored in blob   After getting data from blob you need to base64 decode then unserialize Example decoding    theHTML   unserialize base64 decode  YourSerializedData

User · Answer

Yup  serialize unserialize is what I ve seen the most in many open source projects

[php] Save PHP array to MySQL?

Examples related to php

Examples related to mysql

Examples related to arrays