×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

store multi dimensional arrays to mysql

store multi dimensional arrays to mysql

store multi dimensional arrays to mysql

(OP)
Pulling my hair trying to do this ...

I have tried serialized(), base64_encode() and json_encode() and none of them work.

I am getting syntax error - I am trying to store an array to a table. The table is very simple

id, fID, array are the fields/columns

The array is something like:
[code]
$data=array([0]=>array([0]....[23]),[1]=>array([0]....[23]),...)
[code]

Given above array, what must I do to get it stored in a MySQL table?

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: store multi dimensional arrays to mysql

Quote:

The array is something like:

"something like" isn't a particularly useful description to even start formulating a data structure. So I can only suggest storing the array index in a numerical id column and the array elements as a comma separated string in a data column. It is a spectacularly poor schema when searching and filtering are considered but is the best I can come up with.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: store multi dimensional arrays to mysql

(OP)
Chris,

I have resorted to looping through the array and converting it into

CODE

array(28) {
  [0]=>
  string(145) "~Job #~Quote Invoice~Customer Ref #~ORG City~ORG State~ORG Zip~DST City~DST State~DST
 Zip~PU Date~Est Date~DEL Date~Carrier Name~POD~Released~EOF"
  [1]=>
  string(109) "~5605~I~55413-320A~NORTH BERGEN~NJ~07047~RAMSEY~MN~55303~01/21/16~01/25/16~01/25/16~XABC XABC~ABC ABC~~EOF"
  [2]=>
  string(73) "~5606~Q~~NORTH BERGEN~NJ~07047~JACKSONVILLE~NC~28546~~~~XABC XABC~~~EOF"
  [3]=>
  string(88) "~5635~Q~~SANTA FE SPRING~CA~90670~BAKERSFIELD~CA~93309~~~~XABC XABC~~~EOF"
... 
This way it is a single dimensional array and each element is the "imploded" string of the sub array.

It is storing to mysql but all I see is "Array" in the "Text" column where I am storing it. I am gonna "read" it and see if the script reads the array and comes back with something usable or I still have a problem on my hands.

thanks,

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: store multi dimensional arrays to mysql

(OP)
Oh hell, I had to do a quick and dirty work around

(1) converted array to a long string using {^~} as marker to separate the elements
(2) within {^~}, I have the imploded string from the sub-element array

so,

CODE

$x=array(0=>array(0=>'abc',1=>'def',2=>'ghi'),1=>array( .....))

becomes

{^~}abc~def~ghi~{~^}.... 

It is only one more line of code and 100000th of a second any way ... or is it?

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: store multi dimensional arrays to mysql

I think that you are trying to over-complicate [or over-simplify] the whole operation and confusing your self into the bargain. You need to go back to first principles of object orientation and re-evaluate what it is you are trying to do. You have got so focussed on storing the 'array' as is, 'virgo intacto' as it were you have lost sight of the end goal.

Consider something that is essentially a complex multi-dimensional array, such as a book. An object orientated [oriented US] book is an array of pages, a page is an array of strings [lines/sentences], a line is an array of shorter strings [words] and a word is an array of characters. So given that, to store an entire book in a database you only need a list of words, the word's relative position in any given page (document) and maybe some punctuation tokens and their location, but you can also include the punctuation as part of the word and break lines into words on a space character only should you so wish.
The principle is generally known as an "inverted index" [as used by Internet search engines] and it strikes me as that would be the best way to go in your envisaged system, both for reducing data redundancy and allowing for a fast 'full text' search algorithm.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: store multi dimensional arrays to mysql

(OP)
ChrisHirst,

which is why I tried using serialize() to no avail.

Thinking that the array had some character that was causing serialize to fail, I combined it with a number of other methods such as html[*] and I also tried json_encode

I do intend to come back to this and neaten up my code!

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: store multi dimensional arrays to mysql

Hi

Personally I still not get what your problem is. I would do it like this :

CODE --> SouthBeach-save.php

<?php
$data = [
    'integer' => 42,
    'float' => pi(),
    'boolean' => true,
    'null' => null,
    'array' => [1, 1, 2, 3, 5, 8, 13, 21],
    'object' => (object) ['one' => 'first', 'two' => 'second'],
    'date' => new DateTime(),
    'string' => "Hello\nOlá\nDobrý deň\nおはよう",
];

$conn = new PDO('mysql:dbname=master', 'master');
$stat = $conn->prepare('insert into SouthBeach (data) values (:data)');
$stat->execute([':data' => serialize($data)]); 

CODE --> SouthBeach-load.php

<?php
$conn = new PDO('mysql:dbname=master', 'master');
$stat = $conn->query('select * from SouthBeach');
$row = $stat->fetch();
$data = unserialize($row['data']);

var_export($data); 

CODE --> command line

master # mysql -D master <<< 'describe SouthBeach'
Field   Type    Null    Key     Default Extra
data    text    YES             NULL

master # mysql -D master <<< 'select * from SouthBeach'

master # php SouthBeach-save.php 

master # mysql -D master <<< 'select * from SouthBeach'
data
a:8:{s:7:"integer";i:42;s:5:"float";d:3.1415926535897931;s:7:"boolean";b:1;s:4:"null";N;s:5:"array";a:8:{i:0;i:1;i:1;i:1;i:2;i:2;i:3;i:3;i:4;i:5;i:5;i:8;i:6;i:13;i:7;i:21;}s:6:"object";O:8:"stdClass":2:{s:3:"one";s:5:"first";s:3:"two";s:6:"second";}s:4:"date";O:8:"DateTime":3:{s:4:"date";s:26:"2016-02-01 12:44:01.000000";s:13:"timezone_type";i:3;s:8:"timezone";s:15:"Europe/Helsinki";}s:6:"string";s:35:"Hello\nOlá\nDobrý deň\nãŠã¯ã‚ˆã†";}

master # php SouthBeach-load.php 
array (
  'integer' => 42,
  'float' => 3.1415926535897931,
  'boolean' => true,
  'null' => NULL,
  'array' => 
  array (
    0 => 1,
    1 => 1,
    2 => 2,
    3 => 3,
    4 => 5,
    5 => 8,
    6 => 13,
    7 => 21,
  ),
  'object' => 
  stdClass::__set_state(array(
     'one' => 'first',
     'two' => 'second',
  )),
  'date' => 
  DateTime::__set_state(array(
     'date' => '2016-02-01 12:44:01.000000',
     'timezone_type' => 3,
     'timezone' => 'Europe/Helsinki',
  )),
  'string' => 'Hello
Olá
Dobrý deň
おはよう',
) 

Feherke.
feherke.ga

RE: store multi dimensional arrays to mysql

I'm with Feherke on this one, what exactly is the issue with Serialize?. Serialize turns any array into a string regardless of the number of dimensions. Unless there is something fundamentally wrong with the array.

What is the syntax error you are getting?

If you are getting the word array in your database field, then that means you are trying to store a PHP array directly into the field,. Like echoing an array this will only produce the word array. But not actually a useable array.


CODE

$myarray = array(0=>something,1=>somethingelse,...);

echo $myarray;   Output: - "Array" - 

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: store multi dimensional arrays to mysql

" Consider something that is essentially a complex multi-dimensional array, such as a book. An object orientated [oriented US] book is an array of pages, a page is an array of strings [lines/sentences], a line is an array of shorter strings [words] and a word is an array of characters. So given that, to store an entire book in a database you only need a list of words, the word's relative position in any given page (document) and maybe some punctuation tokens and their location, but you can also include the punctuation as part of the word and break lines into words on a space character only should you so wish.
The principle is generally known as an "inverted index" [as used by Internet search engines] and it strikes me as that would be the best way to go in your envisaged system, both for reducing data redundancy and allowing for a fast 'full text' search algorithm. " ~ Chris Hurst


What a Smart Gentleman if ever I had the fortune to encounter one ^_^


Hey Chris, I wanna buy you a beer mate

you rock TEK TIPS \m/

lol

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close