SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lack of experience = help please! Creating a database

    I am going to set up a system for users to input data on a weekly basis so that we can generate graphs and track results for our clients.

    Using Flash Graphs and believe that I will set up the following application:
    http://www.hkvstore.com/phpreportmaker/

    This comes with a great desktop feature to generate the code for the graphs.

    HOWEVER, due to lack of experience not sure how to actually create tables in MySQL to use the application to call upon.

    The are going to be a about 20 different items to track, done on a weekly basis and variable field sizes required - some are $$ and others are simple counts.

    Does anybody have any advice on an easy way to set up the database?

    Using phpMyAdmin but everything that I do seems to come up with an error, so missing something obviously.

    ANY advice would be great!
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com

  2. #2
    SitePoint Addict Zarin Denatrose's Avatar
    Join Date
    Jan 2009
    Location
    Surrey BC, Canada
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you elaborate as to the error phpMyAdmin is giving you?

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get the following error:

    Error
    SQL query:

    CREATE TABLE `sryder_dealerdata`.`DealershipData` (

    `dealername ` VARCHAR( 100 ) NOT NULL ,
    `dealercontact` VARCHAR( 100 ) NOT NULL ,
    `CP RO Count` SMALLINT NOT NULL ,
    `WP RO Count` SMALLINT NOT NULL ,
    `IP RO Count` SMALLINT NOT NULL ,
    `Total CP Labor` MEDIUMINT NOT NULL ,
    `Total WP Labor` MEDIUMINT NOT NULL ,
    `Total IP Labor` MEDIUMINT NOT NULL ,
    `Total Labor` MEDIUMINT NOT NULL ,
    `CP Hrs/RO` SMALLINT NOT NULL ,
    `Customer Pay Effective Rate` MEDIUMINT NOT NULL ,
    `Total CP Parts` MEDIUMINT NOT NULL ,
    `Total WP Parts` MEDIUMINT NOT NULL ,
    `Total IP Parts` MEDIUMINT NOT NULL ,
    `Total Parts` MEDIUMINT NOT NULL ,
    `Current CSI` SMALLINT NOT NULL ,
    `3 Month CSI` SMALLINT NOT NULL ,
    `12 Month CSI` SMALLINT NOT NULL ,
    `datesubmitted` DATE NOT NULL ,
    `Notes` VARCHAR( 500 ) NOT NULL
    ) ENGINE = MYISAM
    MySQL said:

    #1166 - Incorrect column name 'dealername '

    Would like to record data similar to the attached image.
    Attached Images Attached Images
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com

  4. #4
    SitePoint Addict Zarin Denatrose's Avatar
    Join Date
    Jan 2009
    Location
    Surrey BC, Canada
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    `dealername ` seems to have a space at the end. Try without it.

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for noticing that for me - my own foolish error.

    Now for my next question... have the table created, just not sure if it is right.

    Can somebody review the attachment and see if makes sense to use?
    Attached Images Attached Images
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com

  6. #6
    SitePoint Addict Zarin Denatrose's Avatar
    Join Date
    Jan 2009
    Location
    Surrey BC, Canada
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your attachments aren't coming through here. Are you able to post the images on photobucket or a similar service so I can take a look?

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here you go!



    Thanks for your help!!
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com

  8. #8
    SitePoint Addict Zarin Denatrose's Avatar
    Join Date
    Jan 2009
    Location
    Surrey BC, Canada
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems alright, with the exception of all your smallint's turning into mediumint's, though I don't see that turning into a serious problem. It'll cost a little space (though not really noticeable).
    You should be good to go, unless you were really hoping for smallints, in which case you should be able to manage that with the edit field buttons in phpMyAdmin.

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Seems OK, but you may want to use UTF-8 collation rather than latin1_swedish_ci, it could save you a lot of headaches later on.

    It's also customary to use just alphanumerics for column names with an underscore as a delimiter.

    wp_ro_count for example, or total_parts.

    Can the 3 and 13 month column values be calculated after the fact? More often than not, the use of such fields can be.

    For example, say, initial_contact_date and 3_month_contact_date. 3_month_contact_date can be calculated from the initial_contact_date, without context though this is hard to determine.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help everybody!

    A couple of follow up questions as well. :-)

    For smallints vs. medints - what is the classifcation of each size? How many characters can it hold?

    Also, I want multiple users to input data as well - not sure how that works yet but how do I tie this table in with users as well? Do they all call upon the same table?

    Total rookie here I guess.... but want to figure it out and thanks for the help!
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Smallint vs medint. That is the subject of MySQL data types.

    http://www.htmlite.com/mysql003.php

    For the users, first step is to design and create a separate table for the users.

  12. #12
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Halifax, Nova Scotia
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much - think that I will redo the table.

    For some fields they are $$ figures as well, so will need decimal points. Guess that smallint won't work - what works best for . fields?
    Shawn Ryder
    Auto University Vice President
    http://www.autouniversity.com
    E-mail: shawn.ryder@autouniversity.com


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
  •