background preloader

13 Useful WordPress SQL Queries You Wish You Knew Earlier

13 Useful WordPress SQL Queries You Wish You Knew Earlier
WordPress WordPress is driven by a MySQL database. This is something active WordPress users would know. However, if you only just read about it here from us, here’s what you should know. MySQL is a free relational database management system available in most web hosting services. For example, if you needed to change some information across the board in WordPress, going through each record is very time consuming and prone to human error. Shown below are some SQL queries that can be of great assistance to you when using WordPress. Backup your WordPress Database Before you proceed with any changes, be sure to backup your database. You can download WP-DB-Backup or WP-DBManager plugin to backup your database through your WordPress admin panel. If you decide to backup your WordPress database manually, follow these steps: 13 SQL Queries for WordPress An easy way to run any of the queries below is to use phpMyAdmin. phpMyAdmin is one of the most common database look-up and editing tool. Solution:

Storing form array data to MySQL using PHP Let's say that you've got a form with check boxes sort of like the one below. Here's the code... <form method="post" action="path to script"><input type="checkbox" id="colors[]" value="red" /> Red<input type="checkbox" id="colors[]" value="blue" /> Blue<input type="checkbox" id="colors[]" value="green" /> Green<input type="checkbox" id="colors[]" value="yellow" /> Yellow</form> Notice the [ and ] after "color." Now, let's say someone fills out the form. You want to store the information in that array to your database. $colors=$_POST['colors']; //takes the data from a post operation... But that doesn't work. Instead you'll need to use PHP's serialize() function. As the PHP documentation explains, the serialize function "[g]enerates a storable representation of a value." In other words, it takes arrays (and other data types), and converts the contents into data that can be stored. Let's re-do the above code using serialize(). Funky, ain't it? You should get "red, blue, green, yellow."

mysql date column type Using Date to sort data select e_id, birth_date from employee_per ORDER BY birth_date; +------+------------+ | e_id | birth_date | +------+------------+ | 11 | 1957-11-04 | | 16 | 1964-03-06 | | 21 | 1964-06-13 | | 14 | 1965-04-28 | | 15 | 1966-06-23 | | 7 | 1966-08-20 | | 10 | 1967-07-06 | | 20 | 1968-01-25 | | 12 | 1968-02-15 | | 2 | 1968-04-02 | | 9 | 1968-05-19 | | 13 | 1968-09-03 | | 3 | 1968-09-22 | | 6 | 1969-12-31 | | 17 | 1970-04-18 | | 1 | 1972-03-16 | | 4 | 1972-08-09 | | 19 | 1973-01-20 | | 18 | 1973-10-09 | | 5 | 1974-10-13 | | 8 | 1975-01-12 | +------+------------+ Selecting data using Dates Here is how we can select employees born in March. select e_id, birth_date from employee_per where MONTH(birth_date) = 3; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 16 | 1964-03-06 | +------+------------+ 2 rows in set (0.00 sec) Alternatively, we can use month names instead of numbers.

Related: