SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    london
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    first database design

    OK I've been a member of this site for a while but I've been sort of weary of posting my question cause I've seen how sometimes people make u look stupid when answering your question, anyway I've decided that it doesn't matter anymore, I need to get this on and I've wasted a lot of time by lurking in the dark...so here I go..

    I'm developing my site and I want to have a section for "members of the month", and would like to have a database for it, why, I really don't know but I somehow believe that updating this every month would be easy in the long run right?

    For my members of the month, I'd like to have a picture, (thumbnailed on the home page)a short interview of the member, and obviously the month that they're being featured, so how do I go about designing the table in MYSQL..

    Member name, member picture, date, member interview.

    I have phpmyadmin that I installed on my site so I don't have to run the script thru my browser but if u think I'm better off doing that pls let me know.

    Thank you for your time

  2. #2
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it only gets updated once a month, it is probably equally as plausible doing it with plain HTML. With plain HTML you get to have a lot more control over the layout too so that if you ever want to spice things up you are free to do so.

    But if you like to do it with a DB all the same, then the DB table you have is pretty good. Maybe put in a "id" column as well (so that each row can be uniquely identified?). So you have something like:
    Code:
    id, member_name, member_pic, interview_date, interview_text
    As for the admin section, if you are the only person doing the updates, then phpMyAdmin should have all the functions you need. (Though the "member_picture" field may cause problems. Is this just going to be a file name of the image you have uploaded?).

  3. #3
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    london
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No it's going to have the member's picture...I've laothed around php sites (like a boy lost) and I think I've seen a few posts asking how to put a picture in database..so this is a headache?
    Yeah it's me thats going to be doing the updates..for now anyway.
    Thanks for getting back!

  4. #4
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Binary data (gif/jpeg images for example) can indeed be stored in the DB. I must confess I have not done it before, and I do not recommend this practice. The disadvantages for storing images in the DB:

    1. Each time the page is requested, the DB must be queried to get this image retrieved (adding to load time) - unless you save a temporary copy of it in your web directory until you update the member_of_the_month section. (And that really defeats having the image inside your DB in the first place)
    2. This image is probably not cache-able on the client side.

    Just my opinion though. It'd be much simpler with plain HTML + images. Having said that, I just noticed that phpMyAdmin does allow the use of binary data when inserting DB entries. So it is probably not too hard to implement if you do decide to use it. Cheers~

  5. #5
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Dover, PA
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP != Mysql.

    Quote Originally Posted by Aska
    Binary data (gif/jpeg images for example) can indeed be stored in the DB. I must confess I have not done it before, and I do not recommend this practice. The disadvantages for storing images in the DB:

    1. Each time the page is requested, the DB must be queried to get this image retrieved (adding to load time) - unless you save a temporary copy of it in your web directory until you update the member_of_the_month section. (And that really defeats having the image inside your DB in the first place)
    2. This image is probably not cache-able on the client side.

    Just my opinion though. It'd be much simpler with plain HTML + images. Having said that, I just noticed that phpMyAdmin does allow the use of binary data when inserting DB entries. So it is probably not too hard to implement if you do decide to use it. Cheers~
    The choice is to store the actual images in the database or links to those images in the database. You mentioned some of the downsides to the former method. One might argue an advantage of the latter method would be that you can maintiain referential integrity. But then again, Mysql doesn't care about referential integrity

    I would advise against static HTML just because it's more of a pain to maintain. It would be redundant replacing and inserting different values into an HTML table. While possible, it is a tad more tedious than having a script generate the table given data and based on a template.
    http://www.statgfx.com

  6. #6
    SitePoint Member bolilon's Avatar
    Join Date
    Sep 2003
    Location
    mexico
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Hi i'm trying to implement a helpdeskticket site, but when i create the table in the database i have this error:
    Error: 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(3) DEFAULT '1' NOT NULL, `ticketfecha` DATE (3) DEFAULT '0', `
    This is what i wrote: create table tblticket (
    ticketid int not null auto_increment primary key,
    ticketinfo text,
    ticketusr varchar not null,
    ticketfecha date not null,
    );

  7. #7
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Dover, PA
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bolilon
    Hi i'm trying to implement a helpdeskticket site, but when i create the table in the database i have this error:
    Error: 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(3) DEFAULT '1' NOT NULL, `ticketfecha` DATE (3) DEFAULT '0', `
    This is what i wrote: create table tblticket (
    ticketid int not null auto_increment primary key,
    ticketinfo text,
    ticketusr varchar not null,
    ticketfecha date not null,
    );
    You have an extra comma before the closing parenthesis.
    http://www.statgfx.com

  8. #8
    The knight who said ni! RockyShark's Avatar
    Join Date
    Apr 2003
    Location
    Rockhampton, Australia
    Posts
    699
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Aska
    Binary data (gif/jpeg images for example) can indeed be stored in the DB.
    I was wondering about that. I'm looking at a project where I'll be storing a lot of images, and if I can store the binary data in the database I assume that will save space on the server, and make backups a bit easier. Is that the case? Or is the space taken going to be much the same?

  9. #9
    SitePoint Member bolilon's Avatar
    Join Date
    Sep 2003
    Location
    mexico
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql> CREATE TABLE tblusuarios (
    -> usid INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
    -> uslanuser VARCHAR NOT NULL primary kek,
    -> uslanpassword VARCHAR NOT NULL,
    -> usnombre TINYTEXT NOT NULL,
    -> ustelefono TINYINT UNSIGNED NOT NULL,
    -> usextension TINYTEXT,
    -> usmovil TINYINT UNSIGNED,
    -> usequipo TEXT NOT NULL,
    -> usserial VARCHAR NOT NULL,
    -> usaccesorios TINYTEXT,
    -> usemail VARCHAR NOT NULL, PRIMARY KEY
    -> );
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL s
    erver version for the right syntax to use near 'NOT NULL primary kek,
    uslanpassword VARCHAR NOT NULL,
    usnombre
    mysql>

  10. #10
    SitePoint Member bolilon's Avatar
    Join Date
    Sep 2003
    Location
    mexico
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Thanks, but i still have the error, i apreciate your help.

    mysql> CREATE TABLE tblusuarios (
    -> usid INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
    -> uslanuser VARCHAR NOT NULL primary kek,
    -> uslanpassword VARCHAR NOT NULL,
    -> usnombre TINYTEXT NOT NULL,
    -> ustelefono TINYINT UNSIGNED NOT NULL,
    -> usextension TINYTEXT,
    -> usmovil TINYINT UNSIGNED,
    -> usequipo TEXT NOT NULL,
    -> usserial VARCHAR NOT NULL,
    -> usaccesorios TINYTEXT,
    -> usemail VARCHAR NOT NULL, PRIMARY KEY
    -> );
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL s
    erver version for the right syntax to use near 'NOT NULL primary kek,
    uslanpassword VARCHAR NOT NULL,
    usnombre
    mysql>

  11. #11
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Dover, PA
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by RockyShark
    I was wondering about that. I'm looking at a project where I'll be storing a lot of images, and if I can store the binary data in the database I assume that will save space on the server, and make backups a bit easier. Is that the case? Or is the space taken going to be much the same?
    I don't see how you'd be saving space. The same data is going to be saved on the same drive, just differently.
    http://www.statgfx.com

  12. #12
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Dover, PA
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bolilon
    Thanks, but i still have the error, i apreciate your help.

    mysql> CREATE TABLE tblusuarios (
    -> usid INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
    -> uslanuser VARCHAR NOT NULL primary kek,
    -> uslanpassword VARCHAR NOT NULL,
    -> usnombre TINYTEXT NOT NULL,
    -> ustelefono TINYINT UNSIGNED NOT NULL,
    -> usextension TINYTEXT,
    -> usmovil TINYINT UNSIGNED,
    -> usequipo TEXT NOT NULL,
    -> usserial VARCHAR NOT NULL,
    -> usaccesorios TINYTEXT,
    -> usemail VARCHAR NOT NULL, PRIMARY KEY
    -> );
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL s
    erver version for the right syntax to use near 'NOT NULL primary kek,
    uslanpassword VARCHAR NOT NULL,
    usnombre
    mysql>
    I get the feeling you're pasting these errors without trying to work them out yourself. The error clearly shows "kek" as a mispelling in your query. It's supposed to be "key".
    http://www.statgfx.com

  13. #13
    SitePoint Member bolilon's Avatar
    Join Date
    Sep 2003
    Location
    mexico
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, i donīt see the error, sorry & thanks for your help..

  14. #14
    The knight who said ni! RockyShark's Avatar
    Join Date
    Apr 2003
    Location
    Rockhampton, Australia
    Posts
    699
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dotcomguy
    I don't see how you'd be saving space. The same data is going to be saved on the same drive, just differently.
    Yeah... I thought about that myself last night myself.

    D'OH!


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
  •