SQL COUNT becomes boolean when pushed to page

[aikorei]

I created a function to count a number of appointments in my database:

 

function bp_core_get_user_appointments( $user_id ) {
	global $wpdb;
	
	// Finds the total number of appointments a user has completed.
	if ( !$app_count_cached = wp_cache_get( '$app_count_cached_' . $user_id, 'bp') ) {
		$app_table = $wpdb->prefix . 'app_appointments';
		$sql = $wpdb->prepare("SELECT COUNT(worker) FROM $app_table WHERE worker = $user_id AND status = 'completed' ");
		$app_count = $wpdb->query($sql);
		$app_count_cached = wp_cache_set( '$app_count_cached_' . $user_id, $app_count, 'bp', 86400);
		return $app_count;
	} else {
		$app_count = wp_cache_get( '$app_count_cached_' . $user_id, 'bp');
		return $app_count;
	}
}

 

 

I then call this within another function thusly:

 

$app_count = bp_core_get_user_appointments( $user_id );
if ( $app_count ) {
	$fullname .= ' [' . $app_count . ']';
}

 

When I run the above SQL query in phpMyAdmin (modified slightly so it grabs the right table and everything), it produces the correct count. When it populates on my pages, however, it simply displays as:

 

"[1]"

 

This is regardless of how many actual appointments there are, so I'm guessing it's simply being pulled through somewhere as a boolean, i.e. "True - there are appointments here to count". But I'm not sure why.

 

Where did I go wrong here?

[Jessica]

what does $wpdb->query() return?

[aikorei]

When I enter 

 

$test = $wpdb->query();
echo(var_dump($test));

 

 

the output is

 

bool(false) bool(false)

[Jessica]

Not quite what I meant. Go find the code for query() and see what it returns;

Post that function here.

[aikorei]

Sorry.
 

The query() code is buried somewhere in WordPress, and I'm not finding the actual code online yet. What I did find, however, is this snippet from WordPress.org:

 

 

query  (string) The SQL query you wish to execute.

The function returns an integer corresponding to the number of rows affected/selected. If there is a MySQL error, the function will return FALSE.

 

 

I didn't realize it was used in this way (true/false). It looks like I may need to find another way to pull this value through.

[Jessica]

There should be some equivalent of fetch_row or fetch_assoc to get the rows you selected.

 

I'm moving this to Third Party since you need help with WP functions.

[aikorei]

Thanks for the help, Jessica. 

[Jessica]

I'm not seeing anything related to it on Wordpress docs, what version?

[aikorei]

3.5.1. I'm still not finding much either, and unfortunately all the sites I've found for running any standard SQL query in WP do so using wpdb->query(). 

[Jessica]

What type of object is $wpdb? Where does it get instantiated?

[Jessica]

http://codex.wordpress.org/Class_Reference/wpdb

 

Using the $wpdb Object

Methods in the wpdb() class should not be called directly.

 

WordPress provides a global variable, $wpdb, which is an instantiation of the class already set up to talk to the WordPress database. Always use the global $wpdb variable. (Remember to globalize $wpdb before using it in any custom functions.)

 

The $wpdb object can be used to read data from any table in the WordPress database (such as custom plugin tables), not just the standard tables that WordPress creates. For example to SELECT some information from a custom table called "mytable", you can do the following.

 

 

$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

[Jessica]

You'll want get_var() I think. Give your count an alias.

[aikorei]

I wish I knew. All I know is that it's a global variable and that whenever I need to interact with a table I need to use the syntax I've used above. Unfortunately, I'm only going on about 3 or 4 weeks of actually diving into code to significantly modify my site.

 

As an alternative approach, I tried to use get_results(), as follows:

 

$sql = $wpdb->get_results("SELECT COUNT(worker) FROM $app_table WHERE worker = $user_id AND status = 'completed' ");

 

 

This resulted in:

 

int(0) int(0)

 

from var_dump().

 

I found this site, which has been somewhat helpful in syntax, but doesn't explain the deeper details of what's happening with $wpdb or the other WP functions:  http://wp.smashingmagazine.com/2011/09/21/interacting-with-the-wordpress-database/

[aikorei]

I just realized there was a syntax error on my part. get_var works, I just can't use it with query() [duh]. This syntax worked:

 

$app_count = $wpdb->get_var("SELECT COUNT(worker) FROM $app_table WHERE worker = $user_id AND status = 'completed' ");

 

 

Thanks very much for the help. I really do appreciate your time. =)