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'