store multi dimensional arrays to mysql

[southbeach]

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:

$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
[URL unfurl="true"]http://www.fp2php.com[/URL]
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[ChrisHirst]

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

 

[southbeach]

Chris,

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

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.

 

[southbeach]

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,

$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.

 

[ChrisHirst]

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

 

[southbeach]

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.

 

[feherke]

Hi

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

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

[navy]$conn[/navy] [teal]=[/teal] [b]new[/b] [COLOR=orange]PDO[/color][teal]([/teal][i][green]'mysql:dbname=master'[/green][/i][teal],[/teal] [i][green]'master'[/green][/i][teal]);[/teal]
[navy]$stat[/navy] [teal]=[/teal] [navy]$conn[/navy][teal]->[/teal][COLOR=orange]prepare[/color][teal]([/teal][i][green]'insert into SouthBeach (data) values (:data)'[/green][/i][teal]);[/teal]
[navy]$stat[/navy][teal]->[/teal][COLOR=orange]execute[/color][teal]([[/teal][i][green]':data'[/green][/i] [teal]=>[/teal] [COLOR=orange]serialize[/color][teal]([/teal][navy]$data[/navy][teal])]);[/teal]

[teal]<?php[/teal]
[navy]$conn[/navy] [teal]=[/teal] [b]new[/b] [COLOR=orange]PDO[/color][teal]([/teal][i][green]'mysql:dbname=master'[/green][/i][teal],[/teal] [i][green]'master'[/green][/i][teal]);[/teal]
[navy]$stat[/navy] [teal]=[/teal] [navy]$conn[/navy][teal]->[/teal][COLOR=orange]query[/color][teal]([/teal][i][green]'select * from SouthBeach'[/green][/i][teal]);[/teal]
[navy]$row[/navy] [teal]=[/teal] [navy]$stat[/navy][teal]->[/teal][COLOR=orange]fetch[/color][teal]();[/teal]
[navy]$data[/navy] [teal]=[/teal] [COLOR=orange]unserialize[/color][teal]([/teal][navy]$row[/navy][teal][[/teal][i][green]'data'[/green][/i][teal]]);[/teal]

[COLOR=orange]var_export[/color][teal]([/teal][navy]$data[/navy][teal]);[/teal]

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

[blue]master #[/blue] mysql -D master <<< 'select * from SouthBeach'

[blue]master #[/blue] php SouthBeach-save.php 

[blue]master #[/blue] 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ãŠã¯ã‚ˆã†";}

[blue]master #[/blue] 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

 

[vacunita]

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.

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

echo $myarray;   Output: - "[b][COLOR=#4E9A06]Array[/color][/b]" -

----------------------------------
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

 

[c0deM0nK424]

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