Grow your CSS skills. Land your dream job.

How to setup a database table?

  • # June 30, 2013 at 5:13 pm

    URL: http://chrisburton.me/recently

    First, I’m trying to store the `url` and `title` into a database from my “Read” section. I’ve created a database, database user and set a table with the name `read` with 2 columns (one for url and the other for link). This is where I’m stuck. I’m not exactly sure what I should put inside some of the options shown below.

    Or should my PHP be setting this information from the array?

    View larger image.

    Also, I hope I’m not exposing any sensitive information here. Please let me know if I am.

    # June 30, 2013 at 5:30 pm

    Well, traq is the expert, but I can say something about it.

    You only need **Type** (if it’s VARCHAR, which I guess you want here, you need **Length** too). Length is the maximum number of characters you expect on that field. something like 255 should be sufficient.

    Other attributes (which you don’t need):

    **Default** is exactly what you’d guess it is, in this case, I don’t think you want that.

    **Collation** is the character encoding, you can set it globally for your database so you don’t need to add it again and again:

    ALTER DATABASE databasename
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_unicode_ci;

    **Null** if null is allowed for that column.

    **Index** this is for indexing. That’s for read performance (look-up by index is very fast) and constraints (for example a foreign index is a constraint so that a value of that column has to reference a row by index in another table). You can use this if you want read performance (you probably do).

    Regarding indexes you might want a 3rd column (or first usually) named ‘id’ or something, with an auto-incrementing primary key of type int. That’s common practice.

    Hope @traq will correct me if I’m wrong or fill in where I missed anything :)

    EDIT: InnoDB engine should be okay, I’m always using InnoDB.

    # June 30, 2013 at 5:43 pm

    Damn it. I thought it would be easier to understand than all of what you just said.

    # June 30, 2013 at 5:50 pm

    Maybe I suck at explaining, it was all easy to follow in my head.

    # June 30, 2013 at 5:57 pm

    @CrocoDillion I have many questions.

    But first, in PHPMyAdmin under localhost->db-name, why is my database collation set at `latin1_swedish_ci`? I should probably change that, yes?

    # June 30, 2013 at 5:59 pm

    you should have:

    id – tinyint(8), primary index, auto increment

    url – varchar(250)

    title – varchar(250)

    # June 30, 2013 at 6:10 pm

    How is this table related to other tables? do you have users creating url’s and title’s? you might want to add user_id as well so you can relate(join) two tables if you ever need to display more complex data. `user_id` would be the id in the users table for example.

    # June 30, 2013 at 6:27 pm

    @AlenAbdula

    I’m not sure why I should set an ID? What’s supposed to go there?

    No, the only “user” would be me. I add what I read to my readability account and pull that data from the RSS feed.

    I’m just trying to store that data in case I decide to do something with it later. Something like show what all I’ve read throughout the year. As for now, I’m just going to be outputting my latest article that I’ve read.

    # June 30, 2013 at 6:34 pm

    ID is your table index, every table should have it.

    This number keeps track how many different unique entries you have. It starts at 1 and with every entry it auto increments. In the example you have how would you display ascending or descending data? You would only be able to sort it by url or title.

    # June 30, 2013 at 6:52 pm

    In your use case you would only have one user id = 1 and the table read would have all user_id fields with value 1. The id’s once you set them up they auto increment, so there’s no need to pass data to it. You would only pass user_id value of 1, then title and url.

    # June 30, 2013 at 7:02 pm

    @AlenAdbula What is confusing me is the way you were explaining it. The image helped a lot.

    I won’t be needing a `user_id` since there will only be one user. Do I still need to set one for myself? Seems kind of unnecessary.

    # June 30, 2013 at 7:10 pm

    I would, so if you wanted to add extra info about the user down the road that data will be abstracted. Since you are the only user just pass 1 to user_id. or if you want to keep it real simple just add id (index, auto increment) to the read table.

    So

    CREATE TABLE IF NOT EXISTS `read` (
    `id` tinyint(8) NOT NULL AUTO_INCREMENT,
    `title` varchar(250) NOT NULL,
    `url` varchar(250) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    # June 30, 2013 at 7:12 pm

    your table should have a primary auto incrementing integer id two varchar for url and title. just set a length for those and you shouldnt have any issues.

    no need for user id if you are the only user

    # June 30, 2013 at 7:19 pm

    Now I’m confused again. Should I be using `latin1_swedish_ci` or `utf8_unicode_ci`?

    # June 30, 2013 at 7:26 pm

    @pixelgrid @AlenAbdula So my table should look something like this, correct?

    http://cloud.chrisburton.me/image/1t0L0e3W1412

Viewing 15 posts - 1 through 15 (of 60 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".