MySQL

Distinction on meta value on pre_get_posts

DISCLAIMER : Not a WordPress Expert, Just an old MySQL DBA Here is your original generated SQL SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type=”post” AND (wp_posts.post_status=”publish” OR wp_posts.post_status=”private”) AND ( ( wp_postmeta.meta_key = ‘_position’ AND CAST(wp_postmeta.meta_value AS CHAR) > ‘0’ ) ) GROUP BY wp_posts.ID, wp_postmeta.meta_value … Read more

SQL database export-import

backup only all tables you need from localhost so your .sql file contain only the tables you need & after this go to your live server keep your databse as it the use the following cmd from unix/linux SSH to keep the old tables & only restore the new tables mysql -u username -ppassword databasename … Read more

Why doesn’t this code add a table to my database?

A key must be defined for a table by using a single column, or multiple. So on your code, you need to a line to the sql KEY id (id) – $sql = “CREATE TABLE $table_name ( id int NOT NULL AUTO_INCREMENT, feedurl text NOT NULL, category text NOT NULL, KEY id (id) );”;

Changing Table Prefix for an Existing WordPresss Site

This is pretty straightforward. You can use phpMyAdmin or MySQL Workbench to change the prefix on all the tables at once, or you can do it one-at-a-time with a tool like SequelPro. If you need to run the SQL by hand, the syntax is… RENAME TABLE `old_name` TO `new_name`; Once all the table names are … Read more

writing inner join in wpdb

thanks for your time and thanks to @czerspalace I started the query with double quotation and then separate it with a single quotation and after looking at the echo output I found that there’s no spaces between From and table name this is the correct way $pending_reservations = $wpdb->get_results(‘ SELECT booking_calendars.cal_name FROM ‘.$wpdb->prefix.’booking_calendars AS booking_calendars … Read more

MySQL crashed because of the large number of requests

MySQL shouldn’t crash because of queries like those, but it could feel like it does because your site won’t work. I’d first run SELECT option_name, length(option_value) FROM wp_options WHERE autoload = ‘yes’ ORDER BY length(option_value) DESC; to see if some option has accumulated a large value (I’ve seen wp-cron entries grow very large in one … Read more

Join new table with SQL query

Always escape/prepare your data Don’t leave it open to injections: global $wpdb; $all_posts = $wpdb->get_results( $wpdb->prepare( ” SELECT %s.* FROM %s LEFT JOIN %s ON(%s = %s) WHERE %s=”soundcloud” AND %s=”%s” ” ,$wpdb->posts ,$wpdb->posts ,”{$wpdb->prefix}soundcloud” ,$wpdb->posts.ID ,”{$wpdb->prefix}soundcloud.idpost” ,$wpdb->posts.post_type ,$wpdb->posts.post_name ,$name ), OBJECT ); echo ‘<pre>’.var_export( $all_posts, true ).'</pre>’; Use the prefix $wpdb; offers the $wpdb->prefix, … Read more

+ More