SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

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

            return 
    $wpdb->get_results($queryARRAY_A);
        } 
    I then have another method which creates a table like so:

    PHP Code:
    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:

    PHP Code:
    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

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,260
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    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...ger-types.html
    *the maximum value for an unsigned TINYINT is ........ can you guess ..... 255 !

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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...ger-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!

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,260
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •