sql - Getting rid of unwanted nonSQL syntax characters when debugging a query

admin管理员组

文章数量:1287246

I am trying to debug a SQL query, but I am getting a lot of @r and other unwanted characters right before I run the query like so:

print_r($query); die;
$rows = $wpdb->get_results($query);

I get:

SELECT @r:= CASE WHEN value < 60000 THEN 10000 WHEN value >= 60000 AND value < 100000 THEN 20000 WHEN value >= 100000 THEN 100000 END r, (floor((wpp.value) / @r) * @r) minvalue, ((floor((wpp.value + @r) / @r) * @r) - 1) maxvalue, COUNT(*) `count` FROM wp_posts INNER JOIN wp_product wpp ON wpp.post_id = wp_posts.ID AND ( wpp.location IN ('15652') AND (deleted = 0 OR ( deleted = 1 AND ( wpp.value >= 85000 AND wpp.deleteddate >= (DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) ) ) ) ) AND (0 OR (wpp.srp = 1) ) AND ( wpp.isbudget IS NULL OR wpp.isbudget <> 1 ) AND ( wpp.deleted IS NULL OR wpp.deleted <> 1 ) WHERE wp_posts.post_type = 'used-product' AND wpp.value > 0 AND wp_posts.post_status = 'publish' GROUP BY r, minvalue, maxvalue

I want it to output a SQL query that can be run manually. Right now, it can't because of @r:= and @r.

The query is created by appending strings, so the @r= and @r are inside the strings. However, I want to output the SQL query ran by Wordpress.

I am trying to debug a SQL query, but I am getting a lot of @r and other unwanted characters right before I run the query like so:

print_r($query); die;
$rows = $wpdb->get_results($query);

I get:

SELECT @r:= CASE WHEN value < 60000 THEN 10000 WHEN value >= 60000 AND value < 100000 THEN 20000 WHEN value >= 100000 THEN 100000 END r, (floor((wpp.value) / @r) * @r) minvalue, ((floor((wpp.value + @r) / @r) * @r) - 1) maxvalue, COUNT(*) `count` FROM wp_posts INNER JOIN wp_product wpp ON wpp.post_id = wp_posts.ID AND ( wpp.location IN ('15652') AND (deleted = 0 OR ( deleted = 1 AND ( wpp.value >= 85000 AND wpp.deleteddate >= (DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) ) ) ) ) AND (0 OR (wpp.srp = 1) ) AND ( wpp.isbudget IS NULL OR wpp.isbudget <> 1 ) AND ( wpp.deleted IS NULL OR wpp.deleted <> 1 ) WHERE wp_posts.post_type = 'used-product' AND wpp.value > 0 AND wp_posts.post_status = 'publish' GROUP BY r, minvalue, maxvalue

I want it to output a SQL query that can be run manually. Right now, it can't because of @r:= and @r.

The query is created by appending strings, so the @r= and @r are inside the strings. However, I want to output the SQL query ran by Wordpress.

• wp-query
• sql

Share Improve this question edited Oct 25, 2021 at 16:24 asked Oct 25, 2021 at 15:29 user214276user214276 2

• 2 Can you add current output and desired output to your question? How are you creating $query? – kero Commented Oct 25, 2021 at 15:31
• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Bot Commented Oct 27, 2021 at 8:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

what you are getting is a sql statement not a sql query result.

actually die is a function so should be die(); when using

print_r($query); die();
$rows = $wpdb->get_results($query);

the second line will just not be used, so no results will be display from the query, this said, $query is a php variable that will be the sql query, but could have any other name like for example $myQuery. this said, the output you are getting is the query(sql statement) that will be used in the second line when you comment the first line. so unless in your database you have all that @r, the query to your database will not get any result, will return an error of sintaxe. this means, and like I am understanding the @r are not the intended rows names of your table. the issue comes from behind, from where your variable $query is being created. here's an example that will demonstrate what I am saying. put this code before those lines you have share with us

$myQuery = 'show tables';
$rows = $wpdb->get_results($myQuery);
echo '<pre>';
print_r($rows);
echo '</pre>';
die();

this will return all tables names, if you use only your code without the first line, you will receive possibly an sintaxe error. if this happens, the issue is on your variable $query, on how the variable is being created.

本文标签： sqlGetting rid of unwanted nonSQL syntax characters when debugging a query