PDA

View Full Version : MySQL with a text database.




pknz
May 7, 2005, 12:17 AM
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



wrc fan
May 7, 2005, 12:32 AM
You'll want to use the mysqlimport (http://dev.mysql.com/doc/mysql/en/mysqlimport.html) 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.

pknz
May 7, 2005, 05:21 PM
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);

wrc fan
May 7, 2005, 06:00 PM
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`

superbovine
May 7, 2005, 06:17 PM
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"

[/I]

http://sourceforge.net/projects/phpmyadmin/

pknz
May 7, 2005, 08:13 PM
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>

wrc fan
May 7, 2005, 08:44 PM
you need to do it from the shell, not from inside of the mysql program.

pknz
May 9, 2005, 03:48 AM
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)

wrc fan
May 9, 2005, 05:06 AM
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.

javiercr
May 9, 2005, 05:52 AM
http://sourceforge.net/projects/phpmyadmin/

you can copy and paste it in the sql query screen of phpmyadmin which easy to use and not too hard to set up

petej
May 9, 2005, 06:19 AM
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.

jeremy.king
May 9, 2005, 02:09 PM
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)

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.

pknz
May 10, 2005, 03:59 AM
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;

superbovine
May 10, 2005, 04:22 AM
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.


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

or


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.

jeremy.king
May 10, 2005, 09:17 AM
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.

MontyZ
May 10, 2005, 04:33 PM
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.

white1827
May 14, 2005, 08:16 AM
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.