MySQL with a text database.

Discussion in 'Mac Programming' started by pknz, May 6, 2005.

  1. macrumors 68020

    pknz

    Joined:
    Mar 22, 2005
    Location:
    NZ
    #1
    Hi, I'm fairly new to MySQL and programming in general, I'm taking a University Programming paper through my high school. (Running PCs).

    I have created a database in notepad and at school on the PCs I would simply type in
    \. H:\football.txt;

    this would load the database, and then I could query it and so forth. But I can't seem to find a way to do this in the terminal. I have looked through the MySQL manual and haven't found a way to load it. The manual has things such as source < database etc. Does the text file need to be in a specific place or is their a different command?

    Thanks
     
  2. macrumors 65816

    wrc fan

    Joined:
    Jan 19, 2003
    Location:
    In a world where LPs are made like pancakes
    #2
    You'll want to use the mysqlimport command. I'm not too sure what you're doing with your other database thing on the PC, but with this you load it once and it stays. So if you don't know how to manipulate the database from the command line, you might want to get a gui program to do it or install phpmyadmin.
     
  3. thread starter macrumors 68020

    pknz

    Joined:
    Mar 22, 2005
    Location:
    NZ
    #3
    I still dont seem to be able to get it. MySQL Import says to type this in
    shell> mysqlimport [options] db_name textfile1 [textfile2 ...]
    so would I type in
    shell> mysqlimport [options] db_name FOOTBALL.TXT;
    as this results in "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 'mysqlimport [options] db_name FOOTBALL.TXT' at line 1"

    This is my text file named FOOTBALL.TXT
    Create database football;
    use football;
    drop table team;
    drop table player;
    drop table venue;
    drop table match1;

    Create table team
    (
    team_id int not null primary key,
    team_name varchar(20),
    team_colour varchar(20),
    team_coach varchar(20),
    team_mngr varchar(20),
    team_chrpsn varchar(20)
    );
    create table player
    (
    player_id int not null primary key,
    player_name varchar(25),
    player_posn varchar(2),
    player_DOB date,
    player_ftdness varchar(1),
    player_salary int,
    player_team_id int references team(team_id)
    );
    create table venue
    (
    venue_id int not null primary key,
    venue_name varchar(20),
    venue_seats int,
    venue_minprice int,
    venue_maxprice int,
    venue_address varchar(40),
    venue_team_id int references team(team_id)
    );
    create table match1
    ( match_id int not null primary key,
    match_venue_id int references venue(venue_id),
    match_home_team_id int references team(team_id),
    match_away_team_id int references team(team_id),
    match_date date,
    match_score_home int,
    match_score_away int
    );
    Insert into team values (111,"Arsenal","Red","Ian Wallace","Arsene Wenger","Frederick Tate");
    Insert into team values (222,"Chelsea","Blue","Sam Jefferson","Jose Murinho","John Paul");
    Insert into team values (333,"Manchester United","Red","Bill Smithers","Sir Alex Ferguson","Samuel Scum");
    insert into team values (444,"Liverpool","Red","Alessandro Pierre","Rafael Benitez","Rick Parry");
    Insert into player values (1,"Steven Gerrard","CM",1980-05-20,"R",40000,444);
    Insert into player values (2,"Xabi Alonso","CM",1982-08-12,"B",37000,444);
    Insert into player values (3,"Fernando Morientes","ST",1981-11-03,"R",27000,444);
    Insert into player values (4,"Jamie Carragher","RB",1978-02-28,"B",32000,444);
    Insert into player values (5,"Luis Garcia","RW",1984-03-17,"L",22000,444);
    Insert into player values (6,"Wayne Rooney","ST",1985-01-11,"R",27000,333);
    Insert into player values (7,"Rio Ferdinand","CB",1980-11-28,"R",37000,333);
    Insert into player values (8,"Ruud van Nistelrooy","ST",1978-04-23,"R",44000,333);
    Insert into player values (9,"Alan Smith","ST",1983-02-08,"R",17000,333);
    Insert into player values (10,"Tim Howard","GK",1979-08-30,"R",33000,333);
    Insert into player values (11,"Didaer Drogba","ST",1977-01-15,"R",38000,222);
    Insert into player values (12,"Joe Cole","LB",1981-09-05,"L",38000,222);
    Insert into player values (13,"John Terry","CB",1976-02-26,"R",44000,222);
    Insert into player values (14,"Arjen Robben","RW",1978-12-18,"R",49000,222);
    Insert into player values (15,"Petr Cech","GK",1981-05-22,"R",37000,222);
    Insert into player values (16,"Sol Campbell","CB",1975-09-17,"R",42000,111);
    Insert into player values (17,"Jens Lehmann","GK",1977-06-03,"R",22000,111);
    Insert into player values (18,"Thierry Henry","ST",1979-10-14,"R",49000,111);
    Insert into player values (19,"Dennis Berkamp","RW",1974-08-22,"R",30000,111);
    Insert into player values (20,"Patrick Viera","CM",1979-03-19,"B",47000,111);
    Insert into venue values (5555,"Ashburton Grove",60000,25,300,"Ashburton Grove London",111);
    Insert into venue values (6666,"Stamford Bridge",55000,20,240,"Stamford Bridge London",222);
    Insert into venue values (7777,"Old Trafford",50000,20,200,"Theatre of Dreams Manchester",333);
    Insert into venue values (8888,"Anfield",44565,15,120,"Ashburton Grove London",444);
    Insert into match1 values (1000,5555,111,222,2005-01-01,1,3);
    Insert into match1 values (1001,7777,333,444,2005-01-01,1,2);
    Insert into match1 values (1002,6666,222,333,2005-01-05,2,0);
    Insert into match1 values (1003,8888,444,111,2005-01-05,2,1);
    Insert into match1 values (1004,7777,333,111,2005-01-06,3,2);
    Insert into match1 values (1005,8888,444,222,2005-01-11,0,2);
    Insert into match1 values (1006,5555,111,444,2005-01-11,1,1);
    Insert into match1 values (1007,7777,333,222,2005-01-15,1,2);
    Insert into match1 values (1008,6666,222,111,2005-01-18,2,2);
    Insert into match1 values (1009,8888,444,333,2005-01-25,1,0);
    Insert into match1 values (1010,5555,111,333,2005-01-29,0,0);
    Insert into match1 values (1011,6666,222,444,2005-02-02,2,2);
     
  4. macrumors 65816

    wrc fan

    Joined:
    Jan 19, 2003
    Location:
    In a world where LPs are made like pancakes
    #4
    That's not a text file, that's a sql file. Or at least not what I thought you meant by a text file.

    Anyway to import that you just need to do is type `mysql -u root < FOOTBALL.txt`
     
  5. macrumors 68030

    superbovine

    Joined:
    Nov 7, 2003
    #5
    http://sourceforge.net/projects/phpmyadmin/
     
  6. thread starter macrumors 68020

    pknz

    Joined:
    Mar 22, 2005
    Location:
    NZ
    #6
    I tried `mysql -u root < FOOTBALL.txt`but again I get an error


    mysql> -u root < FOOTBALL.txt
    -> ;
    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 '-u root < FOOTBALL.txt' at line 1
    mysql> mysql -u root < FOOTBALL.txt
    -> ;
    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 'mysql -u root < FOOTBALL.txt' at line 1
    mysql>
     
  7. macrumors 65816

    wrc fan

    Joined:
    Jan 19, 2003
    Location:
    In a world where LPs are made like pancakes
    #7
    you need to do it from the shell, not from inside of the mysql program.
     
  8. thread starter macrumors 68020

    pknz

    Joined:
    Mar 22, 2005
    Location:
    NZ
    #8
    Ok I tried that from the shell, and got this messaage.

    Admins-Computer:~ hkaniuk$ mysql -u root < FOOTBALL.TXT
    ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
     
  9. macrumors 65816

    wrc fan

    Joined:
    Jan 19, 2003
    Location:
    In a world where LPs are made like pancakes
    #9
    replace the `mysql -u root` part with whatever you normally use to log in to the database. I was just assuming that you hadn't set up any users or passwords.
     
  10. macrumors 6502

    javiercr

    Joined:
    Apr 12, 2005
    Location:
    London
    #10
  11. macrumors regular

    Joined:
    Jun 9, 2004
    #11
    Thanks for introducing me the to \. command in the mysql client. This will save me some time in the coming weeks. Whilst I am working on Windows currently and not at my mac, I cannot confirm if the source command (\.) command is present in the mac mysql client but I would think that it is. Check by typing help <enter> at the mysql> command prompt. The source command for me is located near the bottom of the list. As it states, this is the command for loading an SQL script file. So football.txt should be fine. On you mac check the location of football.txt. If it's in the same directory(folder) that you launched mysql from then typing \. football.txt should work fine, otherwise, put the full path football.txt e.g. \. ~/football.txt or \. /Users/<username>/football.txt

    I don't know either how mysql deals with file endings but usually when I shift notepad files from Windows to unix, I use dos2unix to convert the file ending hidden charaters.
    e.g. from the terminal and in the directory where football.txt exists
    dos2unix football.txt footballux.txt

    this creates a new file called footballux.txt with the correct unix file endings
    so from the mysql command prompt

    mysql> \. footballux.txt

    If you subsequently try and open footballux.txt in notepad, it'll all look very messy. Thankfully unix2dos reverses the process.

    Also try and be careful with upper / lower case, whilst the mac is friendlier than most unix systems, it doesn't hurt to be consistent.

    The approach wrc fan describes will achieve the same thing as the \. it's just that this is applicable within the mysql command prompt and the input redirection < is a Terminal (shell) tool.

    It would be useful to know what errors you originally encountered.
     
  12. macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #12
    Seems you assigned a password to your root user, which means you need to:

    mysql -u root -p < FOOTBALL.TXT

    Then provide the password when prompted.
     
  13. thread starter macrumors 68020

    pknz

    Joined:
    Mar 22, 2005
    Location:
    NZ
    #13
    Still not having any luck with it, here are my latest tries.

    Admins-Computer:~ hkaniuk$ mysql -u root -p < FOOTBALL.TXT
    Enter password:
    ERROR 1007 at line 1: Can't create database 'football'. Database exists

    and

    mysql> \. football.txt
    ERROR 1044: Access denied for user: '@localhost' to database 'football'
    ERROR 1044: Access denied for user: '@localhost' to database 'football'
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected
    ERROR 1046: No Database Selected

    and

    mysql> use football
    ERROR 1044: Access denied for user: '@localhost' to database 'football'

    and

    mysql> \. ft1.txt
    ERROR 1044: Access denied for user: '@localhost' to database 'football'
    ERROR 1046: No Database Selected

    ft1.txt was a simple query on the football.txt database:
    Use football;
    Select team_name
    from team;
     
  14. macrumors 68030

    superbovine

    Joined:
    Nov 7, 2003
    #14
    first, off you don't seem to have any idea of what you are doing. I imagine your lack of understand of mysql is hurting you.

    Check out this website http://www.sitepoint.com/ on the bottom right corner you can download for free the 1st four chapters of a book on php and mysql. the book will walk you through how to setup mysql on a mac and setup root on mysql correctly. it will also cover basic admin function which you don't seem to know. once you have a grasp of these your'll have a better understanding of what is going on.


    have you done a "show databases;"? this will list the databases in mysql. you probably already created it, but you don't have proper access.

    you need to give access to the football database if you want to use it.

    Code:
    GRANT ALL PRIVILIGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
    or

    Code:
    GRANT ALL PRIVILIGES ON <db name>.* TO 'user'@'localhost' IDENTIFIED BY 'password';
    I think my syntax is right, if it isn't check http://www.mysql.com for more help.
     
  15. macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #15
    mysql -u root -p

    Enter password when prompted and then

    drop database football;
    exit;

    mysql -u root -p < FOOTBALL.TXT

    Enter password when prompted.



    ALSO, please read up on using MySQL like superbovine said. As much as we enjoy hand holding :rolleyes: , please make some effort to learn the technology.
     
  16. macrumors 6502a

    Joined:
    Jan 7, 2005
    #16
    As someone already suggested above, just get phpMyAdmin, it's easy to set up and makes interacting directly with MySQL much easier to do. It's also free.
     
  17. macrumors newbie

    Joined:
    Jan 14, 2005
    #17
    You can also use the free mysql administrator program that is available at mysql.com. Just process your text file in Restore. I use this often to sync up between my laptop and home system.
     

Share This Page