Store a PHP array in a single SQL cell

Question

I wish to store the ingredients if an item as an array or similar data type in my SQL Database and cannot able to find satisfactory information on the subject

In PHP, the information will get stored as ingredient["$id"]=true or false, where $id represents an ingredient

So for plain bread (please note this is mostly still pseudo code as the data entry side has not yet been started)

//code that creates array this bit is still theory and will be manually emulated in the db for the time being
    $id=1;
    while (isset ($_POST["ingredient part of form".$ID])){
        if ($_POST["ingredient".$id]==checked){
            $p["ingredient"][$id]=true;
        }
        else{
            $p["ingredient"][$id]=false
        }
    $id++;
    }

//the code that gets the values back for the ingredient name we will use a separate array ingredient_n[$id]
echo p[$prod_id]["item"].': '
$id=1
while (isset ($ingredient[$id])){
    if ($p[$prod_id]["ingredient"][$id]==true)
        if ($id!=1){
            echo ', ';
        }
        echo $ingredient_n[$id];
    }
}
echo '.';

This should produce something like

'PLAIN BREAD: WHEAT FLOUR, WATER, SALT, YEAST.'

I looked in to ENUM and SET but that would make adding new ingredients harder

Eric Herlitz · Accepted Answer · 2012-07-20 08:47:06Z

16

Judging by the topic (Store a PHP array in a single SQL cell) you should serialize this. There are standardized methods for that

$array["a"] = "Hello";
$array["b"] = "World";
$array["c"] = "!";

$str = serialize($array);

// The contents of $str
// a:3:{s:1:"a";s:5:"Hello";s:1:"b";s:5:"World";s:1:"c";s:1:"!";}

To reverse use unserialize

$unserializedString = unserialize($str);

You can use this for both arrays and objects.

http://php.net/manual/en/function.serialize.php

edited Jul 20, 2012 at 8:47

answered Jul 20, 2012 at 8:40

Eric Herlitz

26.4k28 gold badges117 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Berry Langerak · Accepted Answer · 2012-07-20 08:45:41Z

5

I wish to store the ingredients if an item as an array or similar data type in my SQL db an cannot find satisfactory information on the subject

If serialising data is the answer to a database-oriented question, chances are that you're asking the wrong question. Normalise your database, and you'll probably insert the ingredients into a separate table, using a JOIN to retrieve them. This makes your database easier to use, more searchable and more maintainable.

That said; there are cases where storing a serialised string is actually the most viable solution, but this one doesn't seem to be that case.

answered Jul 20, 2012 at 8:45

Berry Langerak

18.9k5 gold badges49 silver badges62 bronze badges

5 Comments

Eric Herlitz Over a year ago

Agree with this, but the topic divides this question a bit.

Alex Barnes Over a year ago

Aye what Berry says :) also makes the database smaller, there could also be an argument that this makes the data more implementation independant.

Hector James Haddow Over a year ago

How would i use a join where there are multiple values in the primary table ie PLAIN BREAD has 4 ingredients (and that's the smallest list) would I not need have to have one column for each potential ingredient?

Berry Langerak Over a year ago

@HectorJamesHaddow Read up on normalisation, for your own benefit. No, you wouldn't need four columns for four ingredients, you need four rows in another table. Yes, this results in multiple rows, but if you ever want to know which products contain the ingredient "yeast", you'll be happy you've normalised your database.

Hector James Haddow Over a year ago

there is another table with id and ingredient that is what makes up while(row=...) ingredient_n[row[id]]=row[ingredient] (which is called form with ingredient_n[$id]) so say ingredient_n[4]=yeast then if ($ingredient[$4]==true) contains yeast; else contains no yeast;`

Collectives™ on Stack Overflow

Store a PHP array in a single SQL cell

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related