MySQL variable in query

admin管理员组

文章数量:1287802

I've got tenant portal when I need to show running balance for each transaction. Here's query I've written which does what I need it to do.

Problem I've got is that it doesn't work in Wordpress. Here's my code:

if (is_user_logged_in()) {
$user = wp_get_current_user();
$balance = $wpdb->query( 
$wpdb->prepare( 
    set @csum := "SELECT current_balance FROM exp_ten WHERE tenant_number =  . (int) $user->ID";
    select tenant_number, transaction_amount, (@csum := @csum + transaction_amount) as narrative
    from exp_tran
    order by tenant_number;
    )

);

and here is the error I get:

Parse error: syntax error, unexpected '@', expecting ',' or ')' in /home/path-to-the-file.php on line 165

Could anyone please help how to change query so it would be accepted by WordPress. If I'm right problem is that WordPress doesn't accept mysql variable @csum.

Thanks in advance fro your help.

I've got tenant portal when I need to show running balance for each transaction. Here's query I've written which does what I need it to do.

Problem I've got is that it doesn't work in Wordpress. Here's my code:

if (is_user_logged_in()) {
$user = wp_get_current_user();
$balance = $wpdb->query( 
$wpdb->prepare( 
    set @csum := "SELECT current_balance FROM exp_ten WHERE tenant_number =  . (int) $user->ID";
    select tenant_number, transaction_amount, (@csum := @csum + transaction_amount) as narrative
    from exp_tran
    order by tenant_number;
    )

);

and here is the error I get:

Parse error: syntax error, unexpected '@', expecting ',' or ')' in /home/path-to-the-file.php on line 165

Could anyone please help how to change query so it would be accepted by WordPress. If I'm right problem is that WordPress doesn't accept mysql variable @csum.

Thanks in advance fro your help.

• query
• mysql
• variables

Share Improve this question asked Aug 9, 2017 at 10:43 webhappywebhappy 1133 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

See the documentation for $wpdb->prepare(). You need to pass a String, but you're typing set @csum directly in the parameter. Your code should look like this:

if ( is_user_logged_in() ) {
    $user = wp_get_current_user();

    $balance = $wpdb->query( 
        $wpdb->prepare(
            "SET @csum := (SELECT current_balance FROM exp_ten WHERE tenant_number=%d);
            SELECT tenant_number, transaction_amount, (@csum := @csum + transaction_amount) as narrative
            FROM exp_tran
            ORDER BY tenant_number",
            $user->ID
        )
    );
}

I don't have any way to test, since I don't have your database, but that should work.

Also note how I've used the second argument of $wpdb->prepare() to pass the user ID. There's no point using prepare if you're not going to pass in variables that way.

本文标签： MySQL variable in query