Questions tagged [mysql]
MySQL is an open-source, relational database management system.
1,540
questions
105
votes
6
answers
156k
views
Using wpdb to connect to a separate database
I want to connect wpdb to another database. How do I create the instance and pass it the database name/username/password?
Thanks
- 2,798
61
votes
5
answers
74k
views
MySQL Database User: Which Privileges are needed?
The short installation instruction for WordPress ("5 Minutes") state that:
Create a database for WordPress on your web server, as well as a MySQL user who has all privileges for accessing and ...
- 12.8k
53
votes
3
answers
54k
views
How do you properly prepare a %LIKE% SQL statement?
I'd like to use a LIKE %text% statement while still using the WordPress $wpdb class to sanitize and prepare input.
SELECT column_1 from `prefix_my_table` WHERE column_2 LIKE '%something%';
I've ...
- 1,731
48
votes
5
answers
38k
views
Why is my database import losing text widget data?
I've created a site in WordPress on our development machine. In the theme we're using there are numerous widget zones to display text in (sidebar and front page). I've used simple Text widgets in all ...
- 1,551
35
votes
3
answers
49k
views
Should I use wpdb prepare?
I'm new to SQL and am wondering if I need to use wpdb->prepare for the following query to a table I've created
global $wpdb;
$tablename = $wpdb->prefix . "my_custom_table";
$sql = "SELECT * ...
- 631
28
votes
7
answers
165k
views
What SQL Query to do a simple find and replace
Whenever I create a new website I first create a staging site on a subdomain like "stage.domain-name.com".
After everything works correctly I export the database, open it in notepad++ and do a find/...
- 4,356
24
votes
5
answers
34k
views
the_date() not working
I am using wordpress 3.2 and I did a query post like this:
<?php query_posts("posts_per_page=1post=type&page=post_parent=10");?>
Then I try to echo out the date of this post I queried like ...
- 343
24
votes
2
answers
109k
views
How to return number of found rows from SELECT query
I wrote a function which is supposed to return the number of rows found in a SELECT query but it always either seems to return 0 or an array. I have been messing around with this for about an hour now ...
- 1,274
17
votes
7
answers
30k
views
Checking if Database Table exists
I read the wordpress codex and professional wordpress. It seems both use something like
if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
to determine if the table exists. Is ...
- 2,243
17
votes
6
answers
72k
views
Will it break my site if I delete all transient records in wp_options table?
My site currently has an outrageous 500k+ transient records in the wp_options table.
This causes the table to be crashed frequently and so be my site.
I thought transient records will all expired ...
- 410
15
votes
10
answers
36k
views
How to replace the domain name in a Wordpress database?
I have a Wordpress database which was installed in a development environment... thus, all references to the site itself have a fixed IP address (say 192.168.16.2). Now, I have to migrate that database ...
- 253
14
votes
2
answers
13k
views
WordPress (MyISAM) database is slow, should I switch to InnoDB?
I have a WordPress site with well over 10k posts, and things are starting to get very slow whenever I am adding and editing posts. Pages load nice and fast for users, along with the admin lists of ...
- 251
14
votes
1
answer
12k
views
Please explain how Wordpress works with MySQL character set and collation at a low level
As the question title suggests, I'm looking to understand how Wordpress works with MySQL character sets and collation options. As I will show below, things don't make much sense to me...
I installed ...
- 243
14
votes
1
answer
13k
views
How to run WordPress across 2 VMs for high availability
Microsoft Azure requires that applications utilize two instances across multiple data centers in order to achieve their "high availability" SLA and ensure your sites don't go down for routine ...
- 360
13
votes
2
answers
22k
views
WordPress mysqli and PDO
Does WordPress code base use mysqli or PDO?
I know PDO is superior to mysqli but mysqli is not bad neither. Plus from one of the features of what makes PDO is superior to mysqli ( that is being ...
- 1,839
13
votes
4
answers
6k
views
Return only Count from a wp_query request?
Is it possible with the standard build-in tools in Wordpress to get wp_query to only return the count of an query?
Right now, I’ve a query which has several meta_queries but what the only thing I’m ...
- 770
13
votes
3
answers
16k
views
Why are simple updates to wp_postmeta's "_edit_lock" so slow?
In our MySQL slow query log, the cumulatively slowest query is a simple update to wp_postmeta. Here's an example:
UPDATE `wp_postmeta`
SET `meta_value` = '1392835505:386'
WHERE `post_id` = 94705 ...
- 1,391
12
votes
1
answer
52k
views
Where is the content of widgets stored in mysql table
I have the cPanel access to a website but no wordpress login info. So I wants to change a widget content in the website.
How can I change it through phpmyadmin. Where are that widgets sidebar data ...
- 276
12
votes
2
answers
5k
views
The MySQL alternatives: Do Percona Server and MariaDB work well with WordPress, and do they make WordPress go better?
Lately I've been reading about MariaDB and Percona Server, two strong alternatives to MySQL that have gained popularity since Oracle bought Sun.
As far as I know, both of them could be used with ...
- 495
11
votes
5
answers
32k
views
Wordpress Database lost auto increment
After rolling back a Wordpress database to a backup version it seems that all the tables lost their auto_increment on the primary key columns. I read on another post this could be to do with InnoDB ...
- 111
11
votes
4
answers
43k
views
Mysqldump add drop table?
I notice in the Codex that the --add-drop-table option is displayed for backing up a database. Before I screw anything up, does this just mean that when the backup is eventually imported, the tables ...
- 1,381
11
votes
3
answers
83k
views
How to fetch Data in WordPress using MySQLi or $wpdb
I have custom table like this:
useraw
1. id (Primary*)
2. user_ip
3. post_id
4. time
I am inserting data in the table using
$wpdb->insert($table_name , array('user_ip' => $user_ip, 'post_id'...
- 603
11
votes
5
answers
18k
views
What are the recommended database permissions for WordPress?
I just got WP running on my own server. I am not trying to lock things down more. What permissions should the db user have to my WP db?
- 487
11
votes
2
answers
12k
views
Use REGEXP in WP_Query meta_query key
I know I can use REGEXP in WP_Query like this:
$query = new WP_Query(array(
'posts_per_page' => -1,
'post_status' => 'publish',
'meta_query' => array(
array(
'...
- 367
10
votes
1
answer
4k
views
dbDelta support for FOREIGN KEY
On PHP 5.3.13/MySQL 5.5.21 the following code doesn't work:
if($check_custom_fields_form!=1){
$sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (
`form_name` ...
- 564
9
votes
4
answers
29k
views
wpdb->insert multiple record at once
So after I have created the table 'settings-table' i want to add a couple of records to it.
This is how I want to do it, not the best way, so I need a better way to do this because I will add more ...
- 396
9
votes
2
answers
110k
views
Connect to database using wordpress wp-config file
How can I connect to the database using the wp-config.php file ?
I'm trying to make a script more Wordpress friendly, and I need to connect to the database, but without installing the script as a ...
- 1,320
9
votes
3
answers
2k
views
What's a good strategy for planning WordPress DB growth?
Hoping for some feedback on the topic of optimizing and staying on top of performance as a WordPress database grows... to paint a picture.... let's say you have a WordPress / Buddypress MU site that ...
- 407
9
votes
5
answers
3k
views
Is there a way to list all used/unused WP templates?
I'm working to optimize a site that I've recently taken management of, and it appears to me that there are a fair few unused templates. I want to remove all the unused and redundant template files so ...
- 91
8
votes
6
answers
23k
views
Retrieve featured image
We are building an Android app based on a Wordpress mysql database. We were able to get most of the information from the datase to display on to the app. Now all we have left is to retrieve the posts ...
- 143
8
votes
2
answers
27k
views
Get Featured Image via direct sql query
I know you can use the wordpress methods for getting a featured image, however with this particular project I need to get the featured image via mysql query. Can anyone point me in the right direction....
- 357
8
votes
3
answers
32k
views
Get url of product's images (woocommerce)
What happen when a post (product) didnt have a guid,because it has the same image with another product ?
For example:
Query1:
select guid from wp_posts where post_type='attachment' and ...
- 81
8
votes
3
answers
13k
views
Get Previous & Next posts by Post ID
How can I go about getting the previous and next posts from outside the loop? Everything I try to do aside from the custom SELECT query (using get_posts or WP_Query) breaks stuff further down the page....
- 809
8
votes
1
answer
9k
views
Transaction when using WP functions rather than vanilla SQL?
Does WordPress provide a way of rolling back when using, for example, update_post_meta or wp_insert_post?
E.g. I'm inserting a new post and giving it some post meta inside one function:
...
- 806
8
votes
3
answers
4k
views
Complex meta query with 3 keys
I think the problem essentially relates to sql query structure and I am not an expert....
I need to search for posts (custom post type) by 2 parameters:
pd_city
pd_country
Please note that ...
- 197
8
votes
1
answer
233
views
Reset Post IDs to less than 64bit integer
I have been running Wordpress for many years and have about 26000 posts.
At some point my post ids have exceeded a 32bit integer and I am now locked into using Wordpress on a 64 bit system.
I am ...
- 81
8
votes
1
answer
2k
views
Automatically delete inactive users after 2 months
I am trying to modify this function:
// automatically delete users after 7 days in wordpress
function wcs_auto_delete_users() {
global $wpdb;
$query = $wpdb->prepare( "SELECT ID FROM $wpdb->...
- 445
8
votes
1
answer
241
views
How do I optimize a custom post type admin page with 25,000 posts?
I'm getting the occasional excessive resource limit error to due to loading so many posts at once. We had set the PHP limit to 128 but just loading the list of posts for a couple of the post types is ...
- 477
7
votes
3
answers
225k
views
Order by DESC, ASC in custom WP_Query
I need to do multi-level ordering in a query. The issue is with ordering one value DESC and the other ASC as in SQL. The following SQL seems to give me what I want when I run it in terminal:
SELECT ...
- 187
7
votes
4
answers
13k
views
Delete all subscribers from wp_users and wp_usermeta a few thousand at a time
I have a site with 70K spam subscribers, and I don't need any of them. So I need to delete all subscribers from wp_users and each user's associated meta from wp_usermeta using a query in adminer and/...
- 8,488
7
votes
3
answers
19k
views
How To Export/Import WordPress (MySQL) Database Properly Via Command-Line?
Normally, a MySQL database can be exported and imported using these simple SSH commands:
Export:
mysqldump -u USERNAME -p DATABASE_NAME > filename.sql
Import:
mysql -u USERNAME -p DATABASE_NAME ...
- 4,461
7
votes
2
answers
6k
views
How to prevent execution of default query, while preserving ability to use WP_Query in template?
PROBLEM: I'am trying to prevent execution of default WordPress query for custom category template. I have found a possible solution, but looks like it preventing execution of all post queries:
...
7
votes
2
answers
3k
views
Can i use php sql functions instead of $wpdb?
I'm new to WordPress and was wondering why in WordPress API do they tell you to use functions such as prepare(), insert(), get_col(), get_row(), query(), etc. when technically I can use the built-in ...
- 197
7
votes
1
answer
4k
views
Restoring Wordpress posts from database only
I paid 1and1 my payment, but even after showing payment proof they deleted my entire account, domain and all. I only have my Database left (long ago I took a database backup using phpMyAdmin).
Can I ...
- 71
7
votes
3
answers
6k
views
Set page template for all pages?
I know the page template is set in the wpdb as meta_key = _wp_page_template and meta_value = template.php.
How can I write a SQL script to set each post where post_type = 'page' to a specific ...
- 1,381
7
votes
4
answers
11k
views
How can I find the source of slow queries in Wordpress?
I have a WordPress site with more than 8000 posts and everytime I add a new one the site becomes unresponsive. I checked the MySQL slow queries log and found out that it is performing a select that ...
- 221
7
votes
1
answer
5k
views
Creating 20,000 Posts or Pages using a .csv file?
Here's my challenge: I have a .csv file which contains 20,000 rows of data.
Each row contains 5 columns of data on an particular individual.
I need each row (each person) to be populated into a ...
- 985
6
votes
3
answers
39k
views
How to properly insert data into custom table via plugin
I have a plugin that reads data from an uploaded file and runs queries on the wp database. These inserts are into a table created by my plugin, so they aren't the post type.
This is how I got it ...
- 163
6
votes
1
answer
19k
views
$wpdb->delete column values IN ARRAY()?
I am wondering if this is possible?
$wpdb->delete(
'table_name',
array('id' => array(1, 2, 3)),
array('%d')
);
So, in this situation, it should remove 3 rows at once, and call the ...
- 274
6
votes
2
answers
528
views
WordPress database scalability from the code perspective
I have read through multiple questions here on StackExchange that concern WordPress scaling and the common opinion is that WordPress is indeed highly scalable provided you have the infrastructure to ...
- 1,359