PHP: Storing arrays in database

PHP: Storing arrays in database

In this example we show how to save anda manage an array inside a DB

by Janeth Kent Date: 18-11-2013 database mysql php array

When working with databases, sometimes it is necessary to store an array in a MySQL field. Unfortunately, there is no way to directly pass in an array as a parameter. Storing these data structures is a bit more complex, but by no means hard or impossible.

To convert any array (or any object) into a string using PHP, call the serialize function:

$array = array( 1, 2, 3 );
$string = serialize( $array );
echo $string;

$string will now hold a string version of the array. The output of the above code is as follows:

a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}

To convert back from the string to the array, use unserialize:

// $array will contain ( 1, 2, 3 )
$array = unserialize( $string );

Now let’s try serializing an array of 200 randomly generated integers from 1 to 1000:

$array = array();
for( $i = 0; $i < 200; $i++ )
	$array[] = mt_rand( 1, 1000 );

$string = serialize( $array );
echo $string;

This outputs something like:

a:200:{i:0;i:465;i:1;i:202;i:2;i:9;i:3;i:448;i:4;i:887;i:5;i:844;i:6;i:230;i:7;i:785;i:8;i:892;i:9;i:949;i:10;i:864;i:11;i:29;i:12;i:239;i:13;i:521;i:14;i:632;i:15;i:115;i:16;i:903;i:17;i:331;i:18;i:732;i:19;i:192;i:20;i:487;i:21;i:297;i:22;i:1000;i:23;i:674;i:24;i:301;i:25;i:208;i:26;i:819;i:27;i:690;i:28;i:906;i:29;i:544;i:30;i:316;i:31;i:932;i:32;i:458;i:33;i:64;i:34;i:268;i:35;i:590;i:36;i:80;i:37;i:375;i:38;i:837;i:39;i:928;i:40;i:209;i:41;i:880;i:42;i:60;i:43;i:98;i:44;i:395;i:45;i:880;i:46;i:336;i:47;i:183;i:48;i:321;i:49;i:167;i:50;i:917;i:51;i:423;i:52;i:882;i:53;i:768;i:54;i:415;i:55;i:728;i:56;i:431;i:57;i:540;i:58;i:72;i:59;i:338;i:60;i:431;i:61;i:669;i:62;i:234;i:63;i:699;i:64;i:983;i:65;i:602;i:66;i:348;i:67;i:995;i:68;i:772;i:69;i:337;i:70;i:113;i:71;i:644;i:72;i:209;i:73;i:587;i:74;i:822;i:75;i:135;i:76;i:269;i:77;i:111;i:78;i:406;i:79;i:364;i:80;i:613;i:81;i:522;i:82;i:621;i:83;i:789;i:84;i:195;i:85;i:15;i:86;i:674;i:87;i:916;i:88;i:186;i:89;i:70;i:90;i:59;i:91;i:911;i:92;i:242;i:93;i:270;i:94;i:903;i:95;i:553;i:96;i:166;i:97;i:201;i:98;i:250;i:99;i:683;i:100;i:801;i:101;i:691;i:102;i:602;i:103;i:862;i:104;i:357;i:105;i:872;i:106;i:105;i:107;i:86;i:108;i:496;i:109;i:208;i:110;i:349;i:111;i:69;i:112;i:938;i:113;i:500;i:114;i:961;i:115;i:437;i:116;i:446;i:117;i:16;i:118;i:782;i:119;i:268;i:120;i:296;i:121;i:341;i:122;i:343;i:123;i:160;i:124;i:247;i:125;i:610;i:126;i:600;i:127;i:962;i:128;i:224;i:129;i:659;i:130;i:951;i:131;i:124;i:132;i:937;i:133;i:819;i:134;i:684;i:135;i:930;i:136;i:104;i:137;i:493;i:138;i:568;i:139;i:290;i:140;i:333;i:141;i:626;i:142;i:160;i:143;i:80;i:144;i:278;i:145;i:840;i:146;i:942;i:147;i:141;i:148;i:28;i:149;i:69;i:150;i:241;i:151;i:724;i:152;i:386;i:153;i:209;i:154;i:933;i:155;i:281;i:156;i:410;i:157;i:397;i:158;i:360;i:159;i:337;i:160;i:29;i:161;i:321;i:162;i:543;i:163;i:642;i:164;i:943;i:165;i:273;i:166;i:505;i:167;i:856;i:168;i:860;i:169;i:67;i:170;i:879;i:171;i:735;i:172;i:964;i:173;i:858;i:174;i:965;i:175;i:984;i:176;i:821;i:177;i:540;i:178;i:857;i:179;i:363;i:180;i:588;i:181;i:707;i:182;i:588;i:183;i:540;i:184;i:380;i:185;i:35;i:186;i:52;i:187;i:926;i:188;i:686;i:189;i:833;i:190;i:941;i:191;i:385;i:192;i:730;i:193;i:743;i:194;i:815;i:195;i:497;i:196;i:567;i:197;i:811;i:198;i:339;i:199;i:144;}

These strings can easily be stored in a database and unserialized when the data is accessed. Often times, base64_encode is used in conjunction with serialize when storing arrays:

$string = base64_encode( serialize( $array ) );

The encrypted string can then be restored to an array by using base64_decode:

$array = unserialize( base64_decode( $string ) );

Unfortunately, these strings can grow to be quite large. To counter the size, you may want to use gzcompress to apply gzip compression and significantly reduce the size:

$smallString = gzcompress( $string );

Note that gzip compression can be undone with gzuncompress.

That’s really all there is to it. Now you can easily store an array of information in a database!

 
by Janeth Kent Date: 18-11-2013 database mysql php array hits : 7186  
 
Janeth Kent

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.

 
 
 

Related Posts

Examine the 10 key PHP functions I use frequently

PHP never ceases to surprise me with its built-in capabilities. These are a few of the functions I find most fascinating.   1. Levenshtein This function uses the Levenshtein algorithm to calculate the…

How to Track Flight Status in real-time using the Flight Tracker API

The Flight Tracker API provides developers with the ability to access real-time flight status, which is extremely useful for integrating historical tracking or live queries of air traffic into your…

What is a JWT token and how does it work?

JWT tokens are a standard used to create application access tokens, enabling user authentication in web applications. Specifically, it follows the RFC 7519 standard. What is a JWT token A JWT token…

How to fix excessive MySQL CPU usage

What happens if we realise that a series of databases that we thought were optimised through the use of indexes, have begun to consume CPU usage time of a server…

PHP - The Singleton Pattern

The Singleton Pattern is one of the GoF (Gang of Four) Patterns. This particular pattern provides a method for limiting the number of instances of an object to just one.…

How to connect to MySQL with Node.js

Let's see how you can connect to a MySQL database using Node.js, the popular JavaScript runtime environment. Before we start, it is important to note that you must have Node.js installed…

How to Send Email from an HTML Contact Form

In today’s article we will write about how to make a working form that upon hitting that submit button will be functional and send the email (to you as a…

htaccess Rules to Help Protect from SQL Injections and XSS

This list of rules by no means is a sure bet to secure your web services, but it will help in preventing script-kiddings from doing some basic browsing around. MySQL injection…

The State of PHP 8: new features and changes

PHP 8.0 has been released last November 26: let's discover together the main innovations that the new version introduces in this language. PHP is one of the most popular programming languages…

HTTP Cookies: how they work and how to use them

Today we are going to write about the way to store data in a browser, why websites use cookies and how they work in detail. Continue reading to find out how…

The most popular Array Sorting Algorithms In PHP

There are many ways to sort an array in PHP, the easiest being to use the sort() function built into PHP. This sort function is quick but has it's limitations,…

MySQL 8.0 is now fully supported in PHP 7.4

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…

Clicky