SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Newbie questions with MySql and phpmyadmin

    I'm setting up an MySQL database for the first time and have some questions for anyone who can help. The database holds tournament finishing position statistics for professional golfers and I've set it up like so:-

    Field 1 ID unsigned INT Primary Key
    Field 2 Player Text
    Field 3 Position Text
    Field 4 T_Name Text
    Field 5 T_Date Date
    Field 6 T_ID unsigned smallint

    Table Type: MyIsam


    I want to insert the data from a text file using the "Insert Data from a textfile into table" option in phpmyadmin. However, when I click on that option and enter the location of the textfile (on my local PC), nothing seems to get entered into the table. When I run an sql query, for example:-

    SELECT Player FROM `Golf_Form_USA` WHERE T_ID=1

    I get:

    ID Player Position T_Name T_Date T_ID
    Edit Delete 1 0000-00-00 0
    Edit Delete 2 0000-00-00 0
    Edit Delete 3 0000-00-00 0

    The ID field is incremented but every other field is either empty or just contains zeroes.

    I've tried using comma delimited .csv and tab delimited .txt files but neither works.

    In the "Fields terminated by" box I enter a comma ("," without the quotes) rather than the semicolon and have tried using the default "\" option and "\t" for the "Fields escaped by" option.

    Do you have any idea where Iím going wrong please? I've attached a zip containing the .csv and .txt file versions of the data. I thought I needed to use .csv but can .txt files be used too? Am I also right to think that the .csv/.txt files don't need to be uploaded to the webserver as phpmyadmin gives me the option to browse to the files on my PC?

    Sorry for the novice questions....any advice would be much appreciated.
    Attached Files Attached Files

  2. #2
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The files you have posted are not comma delimited but tab delimited. There is also a problem in the formatting of some of the records and you have at least one stray apostrophe floating around (record 54).

    I've reformatted the data into comma separated and tidied it up a little so give this a go.

    Also, do you have auto_increment setup up on the ID field? If you do, disable it when you are uploading the file and re-enable it when you have finished.

    Let me know how you get on.

    The file will need to be approved by a mod (I think).
    Last edited by elgumbo; Dec 1, 2004 at 04:17.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for going to the trouble of comma delimiting the .txt file.

    I thought Excel was converting the tab delimited file to comma delimited when it generated the .csv file, atleast thats the way I interpreted the conversion wizard. But I guess not and I did wonder if thats where the problem may lie.

    So I can just use the comma delimited .txt file yes...no need for a .csv ?

    Btw, I notice some of the lines have commas at the end and some don't. Does it matter or should I remove those?

    Thanks again for your help

  4. #4
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem. You can just use the .txt file in your phpmyadmin import / upload thingy.

    Hmm, I think the commas where something to do with the format being out when I imported it into excel. I'll upload a new version for you to try
    Attached Files Attached Files

  5. #5
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    The data in the txt file now loads in my table correctly...woohoo

    Out of interest, why does the auto-increment attribute need to be put on after the data is inserted?

    Thanks a bunch elgumbo...you've been great.

  6. #6
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad it worked.

    I think the problem with the auto_increment is because you are trying to import data that already has an ID field. Normally when you enter the data (via a form) you don't specify an ID and the database automatically adds one above the last one (auto_increment).

    The next thing you need to look at is the structure of your database. Ideally, you should not repeat any information in the table. If you do, then you should look at separating that data out into it's own table.

    Example: in your data you repeat the tournament name and tournament date in every record. It would be much better (and easier) in the long run if you put this tournament data into it's own table.

    Also in your example, what will happen in the next tournament? You will have to repeat all the players details and the tournament details again. You should consider moving the players to their own table as well. This will make updating the data a lot easier in the future.

    Your method will work, but there are better, more efficient ways of structuring the data that will pay off in the long run.

  7. #7
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so are you saying I should structure the data something like this:-

    Table 1 (tbl_fp)containing finishing positions
    Table 2 (tbl_golfers) containing golfer names
    Table 3 (tbl_tournaments) containing tournament name and date

    Then I should give each row in each table a unique ID, is that correct?

    Thanks a lot,

  8. #8
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As I say, your current setup will work but you could structure something like:

    tbl_golfers // holding all Golfer details
    ID | GolferName | GolferDetails | GolferDOB | GolferPhotoURL | etc

    tbl_tournaments // holding tournament detail
    ID | TournamentName | TournamentDate | etc

    tbl_fp // ties tournament and golfers together. no need for a unique ID
    // field on this one.
    GolferID | TournamentID | Position


    You could then carry on (depending on how much detail you want to show) with a table for Courses (tied in with the tbl_tournament table).

  9. #9
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks....I'm going to plough on with this over the weekend so I may have some more questions in a day or two if you don't mind. Just want to make sure I'm going the right way about things before I get in too deep.

    Cheers.

  10. #10
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem. I may not be around (my wife's pregnant and the baby was due 2 days ago ) but if you get stuck post a new message on the MySql board and I'm sure somebody will help out - http://www.sitepoint.com/forums/forumdisplay.php?f=182

  11. #11
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Elgumbo...sounds like you have more important things to worry about, hope mother and baby (whenever he/she arrives) are well, cheers.

    If anyone else could help with the following points I'd be most grateful.
    1) I'm now getting the following messages in myphpadmin after creating my first table:-

    The additional Features for working with linked Tables have been deactivated. To find out why click here.

    PMA Database ... not OK[ Documentation ]
    General relation features Disabled

    Various searches suggest the config_inc.php file needs amending but I don't know where to find this. I'm accessing my webhosts phpmyadmin...is this something the webhost needs to do?

    I'm setting up several tables which I'll be running JOIN requests on so I'm not sure I can afford to ignore the message, can anyone shed any light please.


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
  •