Wordpress - 更新数据库 - $ wpdb-> prepare

Question

I do have a weird problem inside my custom wordpress script. I want to update my database and I used this line of code to do it:

$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_avg_rate SET avg='$raty' WHERE user_id= '$user->ID'"));

Problem was now, that my page loaded about 20 seconds so I started debbuging and I found out that $wpdb->prepare needs a second parameter. I tried this code and right now it is working:

$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_avg_rate SET avg='$raty' WHERE user_id= %d'", $user->ID));

Problem is now, that in my opinion there is a ' (at the end by %d) closed but never opened so I tried these codes:

$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_avg_rate SET avg='$raty' WHERE user_id= '%d'", $user->ID));

$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_avg_rate SET avg='$raty' WHERE user_id= %d", $user->ID));

As soon as I am using those codes the site load is again more than 20 seconds. Can someone help me and tell me what the correct syntax for the database update is?

Answer 1

The correct syntax for this SQL query is:

$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_avg_rate SET avg='$raty' WHERE user_id= %d", $user->ID));

prepare will replace the %d with a sanitised integer of $user->ID

The 20 second delay you are seeing must be a problem with your database, database server, or your connection to it, which we can't troubleshoot given the information provided.

Here are things you can try

• First and foremost make sure that the SQL is getting generated properly. Check out Debug Bar, and the SAVE_QUERIES option for wp-config https://codex.wordpress.org/Debugging_in_WordPress
• Next Check that you can perform these queries quickly using a MySQL Client. I would recommend using the mysql command line tool if possible but PHPMyAdmin or MySQL Workbench will do the job too
• Finally if the query is correct and fast when executed in a client, then you might want to check your DNS settings. I have encountered similar problems caused by the Database Server not being able to resolve the WordPress server's hostname

Please let me know how that goes!