0

I've a custom field in Wordpress user meta, which store data in serialized array. I want to retrieve the number of arrays. For e.g.: a:2:{blah blah}


enter image description here Screenshot of my table


I tried the following code but it doesn't works. It should print 2

$number = $wpdb->get_var("
    SELECT count(*)
    FROM wp_usermeta
    WHERE meta_key='bookmark_posts' AND meta_value LIKE '%".get_current_user_id()."%'
");
if ( $number !== '' ) {
    echo 'Total bookmarks (' . $number . ')';
}

I've an error log. (john_newsite is the database name)

WordPress database error Table 'john_newsite.wp_usermeta' doesn't exist for query SELECT count(*) FROM wp_usermeta WHERE meta_key='bookmark_posts'

edit:

I just tried the following code. It works but counts the total bookmarks from all users. How can limit the result by current logged in user?

$meta_key='bookmark_posts';
$number = $wpdb->get_var( $wpdb->prepare( 
    "SELECT sum(meta_value) 
    FROM $wpdb->postmeta 
    WHERE meta_key = %s", 
    $meta_key
) );
echo "{$number}";

2 Answers 2

0

Your Query is wrong beacuse your are searching for the user_id inside the meta_value column instead of the user_id column. In this case i see no point in creating a custom query just for that, So here is how you can do it using the native function get_user_meta

$data = get_user_meta( get_current_user_id(), 'bookmark_posts' );
$number = count($data);
if( $number > 0 ){
    echo 'Total bookmarks ('.$number.')';
}
1
  • You query is counting number of custom "bookmark_posts" (meta_key) associated with current user and in result it is showing (1)
    – wp student
    Jun 16, 2013 at 12:13
0

Well, your SQL is selecting something different than you want, I guess. It's counting rows in your table and you want to select one row and return value of it.

You have to select (with your SQL) the row, that interests you with something like that:

"SELECT count(*) FROM wp_usermeta WHERE meta_key='bookmark_posts' AND meta_value='". get_current_user_id."'

Then you should deserialize this array and check number of it's elements.

PS. I wouldn't use LIKE in this query. It is slower. The other reason is that it will return wrong results (i.e. when current_user_id is 1 your query will return data for user 10, 11, 21 and so on also...)

4
  • Please take a look at $wpdb->prepare()
    – kaiser
    Jun 16, 2013 at 13:54
  • @kaiser Just tried it. Its working but half. Please check the code in question.
    – wp student
    Jun 16, 2013 at 15:32
  • Then you're doing it wrong :) Anyway, you need to use $wpdb->prepare( "SELECT ... %d", get_current_user_id() );. You're missing the brackets in your answer - PHP would assume it's a constant - and you have to use %d for digits of any kind.
    – kaiser
    Jun 16, 2013 at 15:45
  • Do you mean like this? $meta_key='bookmark_posts'; $number = $wpdb->prepare("SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %d ", get_current_user_id() ); echo "{$number}"; This code isn't working either. It echoing the code on page like this: SELECT sum(meta_value) FROM wp_postmeta WHERE meta_key = 1.
    – wp student
    Jun 16, 2013 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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