Questions tagged [mysql]

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

Filter by
Sorted by
Tagged with
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
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 * ...
user avatar
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/...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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'...
user avatar
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?
user avatar
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( '...
user avatar
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` ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
  • 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 ...
user avatar
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....
user avatar
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: ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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->...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 1,359

1
2 3 4 5
31