Questions tagged [mysql]

MySQL is an open-source, relational database management system.

Filter by
Sorted by
Tagged with
100
votes
6answers
138k 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
58
votes
5answers
66k 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 ...
48
votes
5answers
36k 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 ...
41
votes
3answers
45k 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 ...
31
votes
3answers
37k 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 * ...
25
votes
7answers
147k 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/...
22
votes
5answers
29k 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 ...
18
votes
2answers
89k 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 ...
15
votes
6answers
21k 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 ...
14
votes
10answers
30k 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 ...
14
votes
6answers
60k 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 ...
14
votes
2answers
12k views

Wordpress Database 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 ...
13
votes
1answer
11k 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 ...
12
votes
2answers
20k 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 ...
12
votes
1answer
10k 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 ...
12
votes
2answers
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 ...
12
votes
3answers
13k 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 ...
11
votes
2answers
44k 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 ...
11
votes
2answers
9k 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( '...
10
votes
3answers
39k 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 ...
10
votes
2answers
5k 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 ...
10
votes
5answers
17k 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?
9
votes
3answers
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 ...
9
votes
5answers
2k 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 ...
9
votes
1answer
3k 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` ...
8
votes
6answers
18k 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 ...
8
votes
2answers
25k 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....
8
votes
3answers
11k 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....
8
votes
2answers
103k 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 ...
8
votes
3answers
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 ...
8
votes
1answer
180 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 ...
8
votes
1answer
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->...
8
votes
1answer
231 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 ...
7
votes
2answers
179k 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 ...
7
votes
2answers
5k 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
3answers
16k 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 ...
7
votes
2answers
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 ...
7
votes
1answer
3k 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 ...
7
votes
3answers
61k 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'...
7
votes
4answers
10k 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 ...
6
votes
5answers
23k 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 ...
6
votes
2answers
515 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 ...
6
votes
2answers
1k views

Lack of composite indexes for meta tables

Why wp_commentmeta table does not have a composite index (comment_id, meta_key) by default? I believe it's the only useful index for that table, am I wrong? Instead, it has a strange set of indexes: ...
6
votes
3answers
4k views

Resetting comment count

Recently, I had to move to another domain and had to recreate all my MySQL tables on the enw server. I noticed that for some posts, the number of comments is doubled. Example - http://inversekarma.in/...
6
votes
2answers
4k views

Optimize Wordpress Query that take 5 seconds to execute

I have a large wordpress database: rows in key tables: 730K wp_posts 404K wp_terms 752K wp_term_relationships 27K wp_term_taxonomy 1.8 Million wp_postmeta The issue is that I have a query ...
6
votes
3answers
12k views

is it possible to run some php code within a wordpress page?

I'd like to access a DB table and render it in some data grid format using php on a dedicated wordpress page, accessible to members of a certain group. Is this at all possible with wordpress or is ...
6
votes
3answers
4k views

How to log mysql errors from wordpress core?

I am developing a wordpress site and I would like to be able to log and handle mysql errors from wordpress core. My site has a mix of wordpress pages and posts + a few php pages that run under the ...
6
votes
3answers
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 ...
6
votes
1answer
3k 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 ...
6
votes
1answer
853 views

Would switching to InnoDB from MyISAM improve performance of comments table?

A site I've built gets lots of comments in a short space of time - so perhaps 100 comments might be left in 5 or 10 minutes. This seems to cause a pretty heavy load on the database, especially when ...

1
2 3 4 5
30