Articles of sql

Why my query does not run with prepare

Hi Below is my query and it works with prepare keyword on the localhost but whenever I use prepare keyword with my queries on live server the data does not show up and as soon I remove prepare from query on live server the query works. So I did this to save my site from […]

DB_HOST – is LOCALHOST speedy than domain name?

I am interested, on some hosting(although they are paid hostings, and even the domain.com DNS nameservers are pointed to that hosting), you have to insert: mysql.domain.com (instead of: localhost) , and i was afraid, that connecting to mysql.domain.com will be much slower… Am I right? p.s.

wpdb->get_var always returning 0

I am trying to run this statement: global $wpdb; $amt = $wpdb->get_var($wpdb->prepare(“SELECT SUM(amount) as amt FROM wp_pay_table WHERE postid = %i”, $pid)); …but $amt always comes out as 0. I have checked that $pid is the correct value. I have also run the sql statement in my db and it returns the correct value (should […]

Clean up very big and very dirty database

I’m trying to clean up a database in order to move a website from a server to another. Database is more than 1gb, mostly posts revisions and spam comments. Is it safe for instance to do something like this? DELETE FROM wp_2_posts WHERE post_name LIKE ‘%revision%’ Thanks.

What does the $posts_join filter join to?

I am trying to use the posts_join filter to join 2 tables: wp_postmeta to wp_posts. I am a novice at SQL, so I’m not sure if I am confused because I don’t understand how WordPress is implementing posts_join, or if I just don’t understand the SQL syntax. My questions are: Why is only one table […]

WP User Query get all authors with last name starting with specific letter

I need to program a page to which I pass an initial through the url and it will show all author users whose last name starts with that letter. I would like to do this via WP User Query so that I can easily loop over the results Right now I have this: <?php $letter […]

A faster way to query custom post types with multiple conditions?

I am using a lot of custom post types (CPTs) with custom meta and so far I have been using queries like the below, to get for example all events in CountryX that happened in 2014: <?php global $paged; $curpage = $paged ? $paged : 1; $args = array( ‘numberposts’ => -1, ‘post_type’ => ‘event’, […]

Use variable in SQL statement

I have this: <?php $jerseys = $wpdb->get_results(“SELECT order_item_id, order_id FROM wp_woocommerce_order_items WHERE order_item_name = ‘Mystery Jersey'”); echo “<p>Jerseys</p>”; if ( $jerseys ) { foreach ( $jerseys as $post ) { $orderID = $post->order_id; $size = $wpdb->get_results(“SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE meta_key = ‘_wc_checkout_add_on_label’ AND order_item_id = ‘” . echo $orderID . “‘”); echo ‘<p>’ ; […]

Posts modified in the last 48 hours

I am using the code below to generate posts created in the last 48 hours. function filter_where($where = ”) { $where .= ” AND post_date > ‘” . date(‘Y-m-d’, strtotime(‘-2 days’)) . “‘”; return $where; } add_filter(‘posts_where’, ‘filter_where’); But I also want to get the posts modified in the last 48 hours. Even if their […]

prepare function sql safe method

I have a curious question about sql injection attack using $wpdb->prepare functions usages. https://codex.wordpress.org/Class_Reference/wpdb#Placeholders this link say prepare method is better to protect sql injection. So I use below code with prepare. $query = $new_wpdb->prepare( “INSERT INTO sym_data ( user_id, country, dob, height, weight, bmi ) VALUES ( %d, %s, %s, %d, %d, %f)”, $user_id, […]