Running custom SQL in pre-existing WordPress Plugin

2 Views

OK, so I’m a bit of a novice when it comes to PHP, but here’s what I want to achieve.

I want to change the theme of a WordPress site from one that uses a plugin to rate the posts (WP-Ratings) to a new theme where ratings are built in. The new theme stores the ratings as meta data, and I have managed to write a query which takes the rating from the wp_ratings table and converts it into the necessary meta data. What I would like to do, is store this query in the current ratings plugin, so that any time a post is rated is stores the necessary meta data for when we migrate to the new theme. Like I said, I am a novice to PHP, but can hold my own with SQL, so could someone please advise me how to run the following query against a WordPress database.

Read More

Thanks! 

SET @post_id = 1110;
SET SESSION group_concat_max_len = 1000000;

insert into wp_postmeta (post_id,meta_key,meta_value)
  SELECT
    rating_postid
    ,'like'
    ,CONCAT
      ('a:', COUNT(rating_id), ':{',
        (SELECT CONCAT( GROUP_CONCAT(meta_data_vote SEPARATOR ''), '}') 
         FROM
          (SELECT CONCAT
            ('i:',
             @curRow := @curRow + 1,
             ';a:2:{s:7:"',
             'user_id',
             '";s:1:"0";s:2:"ip";s:16:"',
             '-127-000-000-001',
             '";}'
            ) AS meta_data_vote
           FROM wp_ratings JOIN
             (SELECT @curRow := -1 AS j) r
              WHERE rating_postid = @post_id
                and rating_rating > 0
             )AS meta_data_votes
          )
      ) AS new_ratings_meta_data
  FROM wp_ratings l
  WHERE rating_postid = @post_id
    and rating_rating > 0

Please note that the post ID variable should equal the post ID of the one being rated, which I believe is $post_id in PHP for WordPress.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. You want to use the wpdb. It is a very simple wrapper around PHP’s mysql (yes, mysql) functions. 

    $results = $wpdb->query($your_complicated query); // Congrats on actually knowing how to write SQL :)

    You can feed any valid SQL into that methaod and it should work just like any query but you can’t “stack” queries. mysql and PDO do allow stacked queries (though I am not really a fan of them, being paranoid and all), but, as mentioned wpdb still uses mysql functions.

    You should also replace your hard-coded WordPress table names with the $wpdb equivalents. For example, instead of wp_posts use $wpdb->posts. The reason for this is thatwp_ prefix is changable. It is the default and it very common but can be changed, and changed easily. It is just a simple edit to wp-config.php.