CODEWITHSUNDEEP
phpmysql
amosrivera
amosrivera

Reputation: 26514

Arrays vs Tables (PHP/MySQL)

Meet Jimmy. He has made his new life goal to prove that Chocolate is the best ice cream flavor ever. For this he built a simple form with radio buttons and a text field for the name so he can send the link to his friends.

enter image description here

He is using a very common set up, MySQL and PHP to save the form submissions in a table that looks like this:

enter image description here

selection being the id of the flavor. The flavors are stored in a PHP array because he plans to use the favor list in future pages:

$flavors = array(
   1=>"Chocolate",
   2=>"Cherry",
   ....
);

The form was a success and his friends are starting to ask Jimmy to add new options, so Jimmy has decided to take it to the next level and add country, age, email and other things to the form, but this time he is doubtful about whether it is a better idea to put the flavor names, countries, ages and other static data in arrays or save each of them in a database table, he knows how to do joins in queries anyways.

First approach

would mean having a PHP file with many arrays and having access it every time Jimmy needs the flavor name something like:

$query = mysql_query("SELECT name, flavor FROM votes")
while($row = mysql_fetch_assoc($query)){
   echo $row["name"]." - ".$flavors[$row["flavor"]];
}

Second approach

would mean having many tables in the database and having to do a join every time he needs a name like this:

$query = mysql_query("SELECT name, flavor FROM votes LEFT JOIN flavors 
WHERE votes.flavor = flavors.flavor");

while($row = mysql_fetch_assoc($query)){
   echo $row["name"]." - ".$row["flavor"];
}

Although there seems to be little difference this is an important decision for Jimmy as he wants to build many more and bigger forms in the future.

What is the best way for Jimmy to handle static data like flavor names, countries, age groups, etc. that is associated with IDs in the database?

Given environmental details:

  1. The arrays are static and will almost never change
  2. He will be using the data on several pages so hard coding is not convenient
  3. Adding a new array is usually faster

Thanks in advance for helping him out.

Upvotes: 25

Views: 2039

Answers (3)

afuzzyllama
afuzzyllama

Reputation: 6548

I think Jimmy should be thinking about how he wants to lay his data out. Why limit his conquest to flavors.

If Jimmy is looking to store a lot of small bits of data, databases are the way to go. If Jimmy wants to store images of the items, those should be stored in files and he should store their relative location to some root directory in the database

Maybe one table can contain:

VOTE_ITEMS
    ID - PRIMARY KEY
    NAME
    IMAGE
    TAGS - (Maybe an imploded ID array with the IDs pointing to a TAG table)
    ...

Another table can contain:

USERS
    ID - PRIMARY KEY
    ...
    (As much information as your want to collect from your users)
    ...

On to voting:

POLLS
    ID
    VOTE_ITEM_IDS
    ...

USER_VOTES
    POLLS_ID
    VOTE_ITEM
    USER_ID

Since Jimmy seems to know a lot about databases, anytime he wants to add something on he can just add another column (or table) depending on his needs. Also, if we wraps a sweet user system he can reuse it in other projects in the future!

Upvotes: 3

designosis
designosis

Reputation: 5263

I tend to store values like this in db tables, mainly so they can be modified via CMS. Then I retrieve them all at once, only once, near the beginning of my PHP code, in a globals array ... e.g. $glob['flavors'], $glob['cities'], etc. Then it's as simple as ...

    foreach ($person) {
        echo 'Their flavor = '. $glob['flavors'][$person['flavor_id']];
    }

... but you have to remember to include the global in any functions that will use it.

Benefits of this: Only one db lookup, global access. Drawbacks of this: Memory hog if array is huge

Upvotes: 2

Neo
Neo

Reputation: 11557

The second option is of-course more scale-able and almost better in every aspect, the only argument could be performance given that his data is gonna eventually get really big. But even at that point jimmy can easily cache the result from the new flavors table, using a technology like memcache, x-cache, or even write a code that will create the php file with the array of flavors dynamically using the flavores database. I am very confused as why someone with your reputation will ask such a question?!

Upvotes: 4

Related Questions