Wordpress insert using $wpdb inserting wrong data

Ok, this one has me stumped.

I’ve been searching Google for hours and I’m not getting anywhere.

I am writing a plugin which adds a widget allowing users to vote on their favourite book.

When I activate the plugin I have a method which gets data like this:

public function book_list()
    {
        global $wpdb;
        $query = "SELECT ID, post_title FROM wp_posts WHERE post_type = \\"book\\";";

        return $wpdb->get_results($query, ARRAY_A);
    }

I then have another method which creates a table like so:

public function create_book_vote_table()
    {
        if (!current_user_can('activate_plugins'))
        {
            return;
        }
        global $wpdb;
        $table_name = $wpdb->prefix . 'cc_book_votes';
        $sql = "CREATE TABLE {$table_name} (
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        book_id TINYINT UNSIGNED NOT NULL,
        book_title VARCHAR(150) NOT NULL,
        votes INTEGER UNSIGNED NOT NULL DEFAULT '0',
        PRIMARY KEY  (id)
        );";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

I then have a third method which adds data to the created table above:

public function insert_book_vote_data()
    {
        global $wpdb;
        $wpdb->show_errors();
        $table_name = $wpdb->prefix . 'cc_book_votes';
        $titles = $this->book_list();
        if ($titles)
        {
            foreach ($titles as $title)
            {
                $title_id = $title['ID'];
                $title_post_title = $title['post_title'];
                $wpdb->query($wpdb->prepare("INSERT INTO {$table_name} ( book_id, book_title, votes ) VALUES ( %d, %s, %d ) ", array(
                            $title_id,
                            $title_post_title,
                            0
                        )));
            }
        }
    }

However, the book_id columns has the wrong ID’s stored. For some reason, when I get the ID’s from the book_list method the ID’s which are returned are not stored in the database when the method insert_book_vote_data is called. The returned ID’s are 316, 317, 318, 319, 7, 320, 506 (these are the post ID’s). But the stored book_id’s are 255,255,255,255,7,255,255. I have no idea why. Only one of the post ID’s is getting stored correctly as a book_id. The 255 is the ID of an attachment which is child of the post with ID of 7.

It is probably something simple but I’ve been looking at it so long I’m not spotting the obvious. Any help much appreciated.

Thanks,
Jon

Two things.

You may want to add an “if not exists” to your CREATE

It is a good idea to save on database size where possible, but in this case TINYINT won’t be sufficient for your needs.
https://dev.mysql.com/doc/refman/5.1/en/integer-types.html
*the maximum value for an unsigned TINYINT is … can you guess … 255 !

Mittineague, you are a life saver! I didn’t notice it was set to TINYINT. As I said, I have been looking at the website I’m building too long to notice things. I feel like an idiot for not noticing something so obvious. Thank you very much!

Don’t feel bad, lessons learned this way tend to “stick”.
The only reason I spotted it was because I had a similar problem years ago :shifty: