WordPress.org

Ready to get started?Download WordPress

Forums

Trouble Creating Automatic Table (6 posts)

  1. jac0bean9
    Member
    Posted 2 years ago #

    Hi,

    It's my first time writing a WordPress plugin, so this may be an obvious/basic question, but I'm in charge of a newspaper website, and we are trying to keep track of statistics. As such, I am trying to create a table of team names, once, in the WordPress database.

    What I have so far to create the table is as follows:

    <?php
    register_activation_hook('__FILE__', 'create_big_table');
    
    function create_big_table () {
        global $wpdb;
        $table_name= 'teamlist';
    $sql = "CREATE TABLE $table_name (
    TeamName varchar(255),
    TeamNameAbbrv varchar(9),
    TeamYear varchar(4),
    HeadCoach text,
    AsstCoaches text,
    TeamPhoto file,
    BeatLink text,
    GameDate text,
    WinOrLoss varchar(1),
    Score varchar (7),
    Opponent text
    );";
    
    require_once (ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    ?>

    The issue is that the table does not show up in the WordPress database. I would love to know what I'm doing wrong. If there is a better way of creating the table, too, I'd love to know it as well.

  2. jac0bean9
    Member
    Posted 2 years ago #

    By the way, I did try taking __File__ out of quotes and it did not fix the problem.

  3. bcworkz
    Member
    Posted 2 years ago #

    dbDelta is very picky about syntax and content. I think the problem may be you have not specified a KEY column.

  4. datdesignguy
    Member
    Posted 2 years ago #

    I don't believe file is a valid MySQL column type... that might be the problem?

    Perhaps you could just have the user paste a url to the photo instead of attempting to store the photo in the database? If not, then I think 'binary' is the correct column type for the Team Photo field.

    Also, as bcworkz said, it could also be that you're missing a primary key in your table... try something like this:

    As bcworkz said above, try adding a KEY column. The simplest approach would be to add a primary key column called 'id' to your table like so:

    <?php
    register_activation_hook('__FILE__', 'create_big_table');
    
    function create_big_table () {
        global $wpdb;
        $table_name= 'teamlist';
    $sql = "CREATE TABLE $table_name (
    id int(10) NOT NULL AUTO INCREMENT,
    TeamName varchar(255),
    TeamNameAbbrv varchar(9),
    TeamYear varchar(4),
    HeadCoach text,
    AsstCoaches text,
    TeamPhoto file,
    BeatLink text,
    GameDate text,
    WinOrLoss varchar(1),
    Score varchar (7),
    Opponent text,
    PRIMARY KEY (id)
    );";
    
    require_once (ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    Another piece of advice for you, if you're going to release this code as part of a plugin, it would be a good idea to prefix your table with the appropriate prefix so that if a person is using one database to power multiple installations of WordPress, your plugin won't end up sharing data between installations.

    I've modified your code below to help you out with that as well as adding a check to see if the table already exists:

    <?php
    
    register_activation_hook('__FILE__', 'create_big_table');
    
    function create_big_table () {
        global $wpdb;
        $table_name = $wpdb->prefix . 'teamlist';
    
        // check to see if your table has already been created
        if( $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name) {
      $sql = "CREATE TABLE $table_name (
        id int(10) NOT NULL AUTO INCREMENT,
        TeamName varchar(255),
        TeamNameAbbrv varchar(9),
        TeamYear varchar(4),
        HeadCoach text,
        AsstCoaches text,
        TeamPhoto file,
        BeatLink text,
        GameDate text,
        WinOrLoss varchar(1),
        Score varchar (7),
        Opponent text,
        PRIMARY KEY (id)
      );";
    
    require_once (ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    } // end table check
    }

    I hope this was helpful!

    -datdesignguy

  5. Ben Huson
    Member
    Posted 2 years ago #

    Here's the documentation on dbDelta():
    http://codex.wordpress.org/Creating_Tables_with_Plugins#Creating_or_Updating_the_Table

    It's very picky about things like having 2 spaces after "PRIMARY KEY" etc

  6. jac0bean9
    Member
    Posted 2 years ago #

    Thank you so much for all of the help! It's still hasn't quite worked yet, so I'll have to look over the dbDelta options and, worst comes to worst, assume that it's some problem with another plugin/the theme and just create the table by hand.

    That said, datdesignguy, do you know of any way to convert photos to and from binary for the table photograph storage? Would it have to be a specific file type for the storage to work? (Apologies if this is a side topic -- I can edit this paragraph out if you think it would be better.)

Topic Closed

This topic has been closed to new replies.

About this Topic