$wpdb->get_results() not working

1
votes

I'm creating my own favorite posts solution, because the plugin did not work. So I created a new table in my WP database called wp_favorites with 2 cols user ID and post ID. I've already done the insertion and it works. I'm having a problem getting the data from the table. Here's the code:

function is_favorite($pid, $uid) 
{
    global $wpdb;

    $q = $wpdb->prepare("SELECT * FROM wp_favorites WHERE uid=$uid AND pid=$pid;");
    echo $q;
    $res = $wpdb->get_results($q);
    var_dump($res);
    return(count($res)>0);
}

When I copy the output ($q) and insert it in PHPmyAdmin, it works perfectly. However, the output of $res is always an empty array (array(0) { } ) I've tried without prepare, and a couple of other variations, but I can't get it to work.

Same thing happens when I try to display the fav posts.

    <?php
/*
Template Name: Favorites
*/
?>

<?php 

get_header();
get_sidebar();

global $wpdb;
$q = "
    SELECT * FROM wp_favorites
    LEFT JOIN wp_posts ON wp_favorites.pID = wp_posts.ID
    WHERE wp_favorites.uID = 1      
";

 $pageposts = $wpdb->get_results($q, OBJECT);

 var_dump($pageposts);

?>

    <div id='content_and_floater'>

        <?php get_template_part('social_floater'); ?>
        <div id='content'>
            <?php get_template_part('loop'); ?>
        </div>

    </div>

<?php
get_footer();
?>

The result is, again, an empty array. Any ideas ?

4
phpwordpress

4 Answers

1
votes

The problem is sql statement $q = $wpdb->prepare("SELECT * FROM wp_favorites WHERE uid=$uid AND pid=$pid;");

When you use prepare you need to use placeholders . I have corrected code here:

$uid='3';// you can assign correct digits
$pid='5';$q = $wpdb->prepare("SELECT * FROM wp_favorites WHERE uid=%d AND pid=%d", $uid, $pid);
1
votes

I was sitting with this problem too. I run my query through Mysql Workbench and returns a result. then run through the program and returns no result.

My problem was that I had changed a value in workbench and had not commit my change.

0
votes

Sample Database Table

| uid | pid |
|-----|-----|
| 1   | 10  |
| 1   | 5   |

PHP Function & Test Case

function is_favorite($pid, $uid)
{
  global $wpdb;
  $results = $wpdb->get_results( $wpdb->prepare("SELECT * FROM wp_favorites WHERE uid=$uid AND pid=$pid"), ARRAY_A );
  print_r($results);
  return (count($results) > 0);
}

echo 'Test 1';
$test1 = $this->is_favorite(10, 1);
var_dump($test1);

echo 'Test 2';
$test2 = $this->is_favorite(100, 1);
var_dump($test1);

Test Results/Printout

Test 1
Array
(
    [0] => Array
        (
            [uid] => 1
            [pid] => 10
        )

)
bool(true)

Test 2
Array
(
)
bool(true)

Tested on WordPress 3.4.1

-1
votes

I believe it should be get_result() not get_results().