Using Key Value pairs with MySQL

jsandahl

So you’re stumped on how to deal with PHP and MySQL when it comes to key value pairs.

First off, key-value pairs are not always good database architecture. Use them only when you don’t want to limit the kinds of things stored in the database. Things such as user settings make a lot of sense, so you don’t have to store hundreds of rows of settings on each user–mostly with blank data.

So let’s suppose you have a user settings table in which you need to find the group of settings with a user’s id and get their email subscription preference. Your query could be something like this:

SELECT * FROM `users` WHERE `key` =  'email_preference'

Now let’s suppose that you want to filter email preferences and only return a result if there is a value for email preference, or a certain value, such as “wants email.” This can be confusing, since `key` and `value` are both columns and if MySQL is new to you, you would instinctively look for the value to relate to the key, when in fact it relates to the row. Once you have that differentiation down, your problem is solved.

SELECT * FROM `users` WHERE `key` = 'email_preference' AND `value` = 'wants email'

5 thoughts on "Using Key Value pairs with MySQL"

  1. that will be the end of this article. Right here youll locate some sites that we consider you will enjoy, just click the links over

  2. Young Money says:

    below youll obtain the link to some sites that we feel you must visit

  3. always a large fan of linking to bloggers that I like but dont get quite a bit of link love from

  4. World says:

    usually posts some very exciting stuff like this. If youre new to this site

  5. usually posts some incredibly intriguing stuff like this. If youre new to this site

What Are Your Thoughts?

Your email address will not be published. Required fields are marked *