sql - Read For Learn

get woocommerce product image url and price from database [closed]

The only way I see is to use nested WHERE clause: SELECT p.*, pm2.meta_value as image_file FROM ( SELECT p.ID, p.post_title, `post_content`, `post_excerpt`, t.name AS product_category, t.term_id AS product_id, t.slug AS product_slug, tt.term_taxonomy_id AS tt_term_taxonomia, tr.term_taxonomy_id AS tr_term_taxonomia, MAX(CASE WHEN pm1.meta_key = ‘_price’ then pm1.meta_value ELSE NULL END) as price, MAX(CASE WHEN pm1.meta_key = ‘_sku’ … Read more

How can I add a new row in a separate database when someone registers via WordPress?

(As you said, this is not specifically a WordPress question except for how to hook into WordPress functionality with users. Here are some thoughts) I think there are some design questions you need to have clear in order to make it easy figuring out the order to build things: Users signing up and signing in … Read more

CREATE TABLE with dbDelta does not create table

Found a couple of things and am including what I believe will work to correct your issue. (As an aside, you should try and simplify your initial attempts so you can isolate what works and what doesn’t. This is really complex for an initial attempt.) One thing, you’ll struggle to have a field named timestamp, … Read more

Correct PHP to output a single result of a Function with SQL Query

Solved it! Amazing what a good nights sleep does! Function: function get_all_rating_criteria_travel6(){ global $wpdb; $rating6 = $wpdb->get_var( ‘SELECT RatingCriteriaText from ratingcriteria WHERE NicheID=1 AND StarRatingID=5;’); echo $rating6; } Code to display: <?php get_all_rating_criteria_travel6(); ?>

How to Update post_modified of all wordpress post

You can use a generalized update query. As always, when dealing with databases take a backup first. With that out of the way, your query should look something like: BEGIN; — Start a transaction UPDATE wp_posts SET post_modified = <Your new date> AND post_modified_gmt = <Your new date in GMT>; SELECT * FROM wp_posts LIMIT … Read more

Connect to Ms SQL Server

You have probably figured it out by now, but just in case. I was also asked to pull data from an MS SQL server and present the data on a WordPress site. In my plugin, I stored the connection values (encrypted) as options. Here are the basics. Connection Function: public function rimsdb() { global $rimsdb; … Read more

Use $wpdb or other PHP script method to find/replace in WP database

wpdb doesn’t have a built in regex replace function. The easiest solution would be to loop through all of their posts, check the contents for the incorrectly formatted shortcode, and perform the regex replace on the post contents. It might not be as fast as a pure-SQL solution, but it would be less prone to … Read more

Using AND and bracket grouping in SQL not working

You need 2 table joins since there are effectively 2 meta keys in your WHERE clause: SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta AS um1 ON (wp_users.ID = um1.user_id) INNER JOIN wp_usermeta AS um2 ON (wp_users.ID = um2.user_id) WHERE (wp_users.user_login LIKE ‘%little%’ OR wp_users.user_email LIKE ‘%little%’ OR wp_users.user_url LIKE ‘%little%’ OR wp_users.display_name LIKE … Read more

SQL Bulk update all WordPress user’s nicknames to firstname + lastname format

You are updating the wrong column. user_nicename is used for the permalink of authors (so you really should undo your change if already done so in the actual database). If you want to change how usernames are displayed, update display_name instead. UPDATE wp_users SET display_name = CONCAT( (SELECT meta_value FROM wp_usermeta WHERE meta_key = ‘first_name’ … Read more

wpdb->get_row is selecting the variable as a column name

The error in question happened because you did not properly build your SQL query/statement: The column name in your query is hashcode, and you are trying to get the row where the column value matches the value of the $file_hash variable, i.e. WHERE {$file_hash} = `hashcode`. But because the value is a string/text (and not … Read more

+ More