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( 
    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.


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( 
            "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",

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.