storing array in a sql database

Question

I started getting into arrays and don't quite get it to work well. I'm used to work with explode/implode functions but I though arrays would make my life easier in this part of the code. Here is the function called:

function save_event($event_items = NULL) {
include 'connect.php';

$now = date("Y-m-d H:i:s");
$sqla = "
INSERT INTO `event`(`event_items`, `event_entered`)
    VALUES ('$event_items','$now')";

    $resulta = mysqli_query($link, $sqla);
    if(!$resulta)
        {
        echo '<br/>An error occurred while inserting your data. Please try again later.<br/>';
        }
    else
        { echo 'this is the variable to be stored:<br/>';
          print_r($event_items);

          $sql = "SELECT * FROM `event` WHERE event_entered = '".$now."'";
          $result = mysqli_query($link, $sql);

          if($result)
            { while($row = mysqli_fetch_assoc($result))
                  { echo '<br/></br>This is the value of the database:<br/>';
                    print_r ($row['event_items']);
                  }
            }
          }
}

This function prints:

this is the variable to be stored:
Array( [0] => Array ( [item] => Powered Speaker [note] => [quantity] => 2 [price] => 200.00 [category] => Audio ) [1] => Array ( [item] => Wireless Microphone [note] => Lavalier [quantity] => 3 [price] => 175.00 [category] => Audio ))

This is the value of the database:
Array

In phpMyAdmin, all I see in the column event_items is the word Array.

Additional info: I have a table called Groups, each group will have one or multiple orders (another table called Order) and each order will have also one or multiple events (another table). Lastly, each event will have one or multiple items (each item with its corresponding price, quantity, note and category), which are stored in one (or many) columns in the Event table.

You can't just insert an array and expect it to be stored the way you do here. You can go back to using explode and implode or normalize the schema and store one value per row. — mzedeler
– mzedeler, Commented Jun 20, 2013 at 20:59
array means that the variable to be stored was array not a string, as it should be. So the problem is in your INSERT query. Consider using serialize() and unserialize(). — Voitcus
– Voitcus, Commented Jun 20, 2013 at 21:00

Jessica · Accepted Answer · 2013-06-20 20:59:40Z

9

Don't try to store an array in one field. You should store each item in the array as it's own row in a related table.

answered Jun 20, 2013 at 20:59

Jessica

6,99630 silver badges39 bronze badges

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

6 Comments

Claudio Over a year ago

Before trying to make arrays, I was using implode/explode and have columns named item, note, price, etc. Is that the way to go?

Jessica Over a year ago

You should have one table that holds the parent item. (An order?) And a table that holds each product (columns would be item, note, price, etc). It should also have the order_id. Google "data normalization" if you need more.

Claudio Over a year ago

Wow, I think there are things that I though were not worth to tell but I see the need to now: I have a table called Groups, each group will have one or multiple orders (another table called Order) and each order will have also one or multiple events (another table). Lastly, each event will have one or multiple items (each item with its corresponding price, quantity, note and category), which are stored in one (or many) columns in the Event table.

Jessica Over a year ago

Sounds good. Categories should be it's own table, and you store categoryId.

Jessica Over a year ago

Never store arrays. Or the result of imploding them, serializing them, etc.

|

Dany Caissy · Accepted Answer · 2013-06-20 21:04:26Z

3

You are trying to insert multiple values in a single database record, this is not impossible but it's also not recommended in general.

The main reason someone would do this would be for optimization, which is not at all something you should worry about for now.

What you really want to do is review your database schema, if you wish to store an array of value, you need to create a new row (record) for each of those. This might necessitate the creation of another table, depending on what you want to do.

answered Jun 20, 2013 at 21:04

Dany Caissy

3,21617 silver badges21 bronze badges

3 Comments

Claudio Over a year ago

I'm actually coming from that approach! I just thought an array would be easier to work with. Now, serialize/unserialize are looking pretty good to learn :-)

Dany Caissy Over a year ago

Serializing is certainly a possible approach that will work, but it's better practice to create a new table for the records. Even while working on sites with over 500000 unique visitors a month, we very rarely need to do something like that for optimization.

Jessica Over a year ago

serialize is for using an array - not the right way to go here.

Simone · Accepted Answer · 2013-06-20 21:01:42Z

2

You could serialize your array with the serialize() function.

Example:

serialize($event_items);

Generates a storable representation of a value.

This is useful for storing or passing PHP values around without losing their type and structure.

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

answered Jun 20, 2013 at 21:01

Simone

21.6k15 gold badges84 silver badges112 bronze badges

Collectives™ on Stack Overflow

storing array in a sql database

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related