problem with sql query in wordpress plugin

Question

I've got this and I dont know why its not working. It's not returning the ID from the first query so I can use it in the 2nd query. Nevermind the REFERER, its an external link it will refer from.

require("/home/username/public_html/wp-load.php");

global $wpdb;

$sql = $wpdb->prepare( "SELECT link_id FROM $wpdb->links WHERE link_url LIKE '%%s'%", $_SERVER['HTTP_REFERER'] );
$row = $wpdb->get_row($sql);
$lid = $row->link_id;

$wpdb->query("UPDATE $wpdb->wptstats SET impressions = impressions+1 WHERE link_id = ".$lid);

Can anyone please help me in the right directions ?

error log file

[03-Mar-2015 17:13:26 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET impressions = impressions+1 WHERE link_id =' at line 1 for query UPDATE SET impressions = impressions+1 WHERE link_id =

Answer 1

I think the problem may be with your wildcards. Because you're trying to use Wildcards in the prepare, which takes %s, the WPDP doesn't know the difference between the wildcard % and the placeholder %. Using this answer as reference: How to use wildcards in $wpdb queries using $wpdb->get_results & $wpdb->prepare? You'll have to double-escape the wildcards:

$sql = $wpdb->prepare( "SELECT link_id FROM $wpdb->links WHERE link_url LIKE '%%%s%%'", $_SERVER['HTTP_REFERER'] );

There was also an issue with a wildcard being outside the single quote '. Hopefully this will work for you.

As for the error, try this, I was able to update link_rating using the same method though I am not familiar with wpstats:

$wpdb->query( "UPDATE $wpdb->wptstats SET impressions = impressions+1 WHERE link_id = $lid" );

Using update function instead of query:

$wpdb->update(
    $wpdb->wptstats ,
    array( 'impressions' => 'impressions' + 1 ),
    array( 'link_id' => $lid ),
    array( '%d' )
);