Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

(There are a lot of related questions to this but none I can find with answers that have worked.)

get_var returns empty when I query a custom table from a custom plugin for multisite. What am I missing? (I have also tried get_row and get_results which also return empty.)

This is for a publish/unpublish button in the subsite admin using jquery/ajax. The publish function works, creates a new post in the subsite user's blog, and inserts the post_id, blog_id and uniquecode in the custom table. The unpublish function also works in so far as I am able to delete the correct row from the custom table.

What's not working is, within the unpublish function, before deleting the row from the custom table, I need to retrieve the post_id and delete the post from the subsite user's blog, but the get_var query for the post_id returns empty.

Here is the relevant portion of the unpublish function, showing the get_var query and delete correct row, plus print_r checks at bottom:

global $wpdb; 
$customtable = $wpdb->$table_prefix.'customtable'; // works for $wpdb->delete but not $wpdb->get_var
$user_blog_id = get_current_blog_id(); // works 
$uniquecode = $_POST[uniquecode]; // works via jquery-ajax, value obtained from external database
$post_id = $wpdb->get_var($wpdb->prepare('SELECT post_id FROM '.$customtable.' WHERE uniquecode = '.$uniquecode.' AND blog_id = '.$user_blog_id.'')); // not working, returns empty
wp_delete_post ($post_id, true); // $post_id = $wpdb->get_var is empty, preventing wp_delete_post from working 

$wpdb->delete( $customtable, array // works - deletes row from $customtable 
    (
    'blog_id'=>$user_blog_id,  
    'uniquecode'=>$uniquecode 
    ),  
    array('%d','%s')    
);      
echo 'Post ID#';
print_r($post_id); // returns empty
?><br><?
    echo 'Unique code #';
print_r($uniquecode); // returns correct value
?><br><?
echo 'Blog ID#';
print_r($user_blog_id); // returns correct value
share|improve this question
2  
Probably this: wordpress.stackexchange.com/q/76072/21376 –  s_ha_dum Dec 11 '13 at 16:56
    
Prepare should still run and pass the unaltered string back though right @s_ha_dum? Have you tried echoing out the sql that is returned from prepare() and seeing if that's valid? –  Andrew Bartel Dec 11 '13 at 18:11
    
I'm not sure if I'm doing this right, but the following returns empty: $query = $wpdb->query($wpdb->prepare('SELECT post_id FROM '.$customtable.' WHERE uniquecode = '.$uniquecode.' AND blog_id = '.$user_blog_id.'')); echo 'Query says: '; print_r($query); –  user2981243 Dec 11 '13 at 19:17
    
Also no luck removing $wpdb->prepare as per wordpress.stackexchange.com/questions/76072/… –  user2981243 Dec 11 '13 at 19:28
    
Print_r of the SQL query (without using $wpdb->prepare) yields: SELECT post_id FROM customtable WHERE uniquecode = 1234567890 AND blog_id = 123 –  user2981243 Dec 11 '13 at 21:04
add comment

1 Answer 1

up vote 0 down vote accepted

There's error in your query statement. Change to this

$post_id = $wpdb->get_var( "SELECT post_id FROM $customtable WHERE uniquecode = '$uniquecode' AND blog_id = '$user_blog_id'" );
share|improve this answer
    
Thank you for the suggestion. I tried without the periods ('$customtable' instead of '.$customtable.') but php won't load the page without the periods. –  user2981243 Dec 11 '13 at 19:13
    
Sorry, $customtable wont have any surrounded quotes, but others will have as mentioned. –  Shazzad Dec 11 '13 at 20:44
    
Thank you! - I've been trying for days to get this. How did you know quotes were required around '$uniquecode' and '$user_blog_id' but not around $customtable? And how did you know that double quotes are required around the ends of the SELECT statement but single quotes are needed around '$uniquecode' and '$user_blog_id'? Also, I'm using Notepad++ and it usually colors the code according to whether it is php, html or sql, but in this case the php variables end up colored as sql. Is there another software that would be more intuitive? –  user2981243 Dec 11 '13 at 21:27
    
Try using Adobe Dreamweaver to edit your codes. And - - Quotes not required around table name - Within Double quote, you can use variable directly. - Comparing strings value should be within a quote a="v" or a='v' both is same. - and that's also same thing - 'SELECT * FROM '. $tablename and "SELECT * FROM $tablename" –  Shazzad Dec 11 '13 at 21:33
    
Thanks. I tried changing single quotes for double on the strings but the page wouldn't load. Also tried single quotes around the SELECT statement but page also wouldn't load. It seems the combination of the table name variable not needing quotes while the string variables do need quotes makes it necessary to use the particular combination of double and single quotes in this instance. –  user2981243 Dec 11 '13 at 21:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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