Saving Array to MySQL Table

I thought that I would start by making a few posts about some common task, especially in php. One thing that comes up often, id how to dynamically munge and save an array, for instance from $_POST, to the database?

The answer is easier than you might think, first, let’s create a munging function, which you can extend for error checking and data sanitization:

Munge is an old computer term, sometimes called a backronym: Modify Until Not Guessed Easily, but is usually defined as: iterating over data and altering a piece of data.

Now we need to collect and prepare the field names. Now you could just use array_keys, however, It’s better and less bug prone to write your own prep function for the field names. We’ll also implement a whitelist of fields that are allowed to be set into the database. There are many reasons to do this, functional and security wise. I.E. you don’t really know what’s been posted, and it could case an error.

Now that we’ve done that, we need to write something to collect up the fields and values, and format them into a mysql string. Note that I am using a functional approach, but this could be easily moved over into the OOP arena, I’ll certainly talk about that sometime when I get to discussing how to do your own ActiveRecord like ORM implementation in PHP.

Now, let’s test it out with a bit of code:

You should get back out some sql code, like so:

Remember, before you use mysql_real_escape_string(); you need to be connected to a database with mysql_connect();

Well, that about wraps it up for this lesson, thanks for reading, and check back soon for more gravy goodness.

/Jason

About Jason

I am a 31 year old programmer living in the south of France. I currently work actively in the fields of Ruby/PHP/Javascript and server/website administration.
This entry was posted in MySQL, PHP Tutorials. Bookmark the permalink.