wpdb - Retriving array size from serialized data

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}

---

^{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}";

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}

---

^{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}";

• wpdb

Share Improve this question edited Jun 16, 2013 at 15:34 wp student asked Jun 16, 2013 at 11:56 wp studentwp student 37111 gold badge66 silver badges1818 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.')';
}

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