0

(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
5
  • 2
    Probably this: wordpress.stackexchange.com/q/76072/21376
    – s_ha_dum
    Dec 11, 2013 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? Dec 11, 2013 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); Dec 11, 2013 at 19:17
  • Also no luck removing $wpdb->prepare as per wordpress.stackexchange.com/questions/76072/… Dec 11, 2013 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 Dec 11, 2013 at 21:04

1 Answer 1

0

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'" );
5
  • Thank you for the suggestion. I tried without the periods ('$customtable' instead of '.$customtable.') but php won't load the page without the periods. Dec 11, 2013 at 19:13
  • Sorry, $customtable wont have any surrounded quotes, but others will have as mentioned.
    – Shazzad
    Dec 11, 2013 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? Dec 11, 2013 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, 2013 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. Dec 11, 2013 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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