Jump to content

SQL COUNT becomes boolean when pushed to page


aikorei
Go to solution Solved by Jessica,

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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" );

Link to comment
Share on other sites

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/

Link to comment
Share on other sites

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. =)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.