WPDB not working, SQL query works in phpmyadmin

Question

Here i have this query, If I replace the value of %f and %d as 1 and 1, it will work in my phpmyadmin panel but I run this query through wpdb get_results with prepare it doesnt work. All the values are being properly passed but still I get null as output.

$f = $wc * (($ul + 100)/100);

$capabilities = $wpdb->prefix."capabilities";

$sql = "
SELECT u.*, up.*, up.pid, ( up.ros - up.soh ) / ( %f ) as qty_suggested
FROM $wpdb->users u
INNER JOIN wp_deals_users_products up
ON u.ID = up.user_id
INNER JOIN $wpdb->usermeta um
ON u.ID = um.user_id            
WHERE up.pid = %d
AND  um.meta_key = '$capabilities' 
AND um.meta_value LIKE '%subscriber%'
";

//echo $sql; This echoes the sql which I tested in phpmyadmin sql query runner

$results = $wpdb->get_results(
    $wpdb->prepare( 
        $sql,
        $f,
        $pid
        )
    );

echo json_encode($results);

If I remove prepare and run query directly or with hardcoded 1 as both values for %d and %f I still get null. Same query will work fine in phpmyadmin sql section

EDIT:

This is the final query that goes in wpdb prepare function:

    SELECT u.*, up.*, up.pid, ( up.ros - up.soh ) / ( %f ) as qty_suggested
        FROM wp_users u
        INNER JOIN wp_deals_users_products up
        ON u.ID = up.user_id
        INNER JOIN wp_usermeta um
        ON u.ID = um.user_id            
        WHERE up.pid = %d
        AND  um.meta_key = 'wp_capabilities' 
        AND um.meta_value LIKE '%subscriber%'

vjy tiwari · Accepted Answer · 2017-07-28 19:56:33Z

2

You used wrong syntax near to the LIKE .

You have to use this way .

$like_str = 'subscriber';
$f = $wc * (($ul + 100)/100);

$capabilities = $wpdb->prefix."capabilities";

    $sql = "
    SELECT u.*, up.*, up.pid, ( up.ros - up.soh ) / ( %f ) as qty_suggested
    FROM $wpdb->users u
    INNER JOIN wp_deals_users_products up
    ON u.ID = up.user_id
    INNER JOIN $wpdb->usermeta um
    ON u.ID = um.user_id            
    WHERE up.pid = %d
    AND  um.meta_key = '$capabilities' 
    AND um.meta_value LIKE %s";




    $query = $wpdb->prepare( $sql,$f,$pid,'%' . $wpdb->esc_like($like_str) . '%');



    $result = $wpdb->get_results($query);
    echo json_encode($result);

answered Jul 28, 2017 at 19:56

vjy tiwari

8611 gold badge5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Waleed Over a year ago

thanks, it worked...I suspected for a very short moment that it could be % in LIKE statement. I get results now.

Amit Joshi · Accepted Answer · 2017-07-28 20:00:12Z

0

Looks like the values of valirables $f and $pid are not coming up correctly. Print out the final query to see if the variables are properly being passed to the '$sql' like this:

$sql = "
SELECT u.*, up.*, up.pid, ( up.ros - up.soh ) / ( %f ) as qty_suggested
FROM $wpdb->users u
INNER JOIN wp_deals_users_products up
ON u.ID = up.user_id
INNER JOIN $wpdb->usermeta um
ON u.ID = um.user_id            
WHERE up.pid = %d
AND  um.meta_key = '$capabilities' 
AND um.meta_value LIKE '%subscriber%'
";

echo $sql;

Debug $sql and try the query that your get in phpmyadmin.

answered Jul 28, 2017 at 20:00

Amit Joshi

1,3521 gold badge8 silver badges11 bronze badges

2 Comments

Waleed Over a year ago

It is an ajax request on a btn click, only I am using this wp installation and when I echo $wpdb->last_query I get this: SELECT option_value FROM wp_options WHERE option_name = 'WPLANG' LIMIT 1 I don't understand why...

Waleed Over a year ago

$wpdb->queries shows 4 queries and my query is not there :D

Collectives™ on Stack Overflow

WPDB not working, SQL query works in phpmyadmin

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related