mysql select formatting query

[bhogaj31]

Hi all,

I am a total newbie to mysql and I have been trying to educate myself.

I created a database and a table within it called "simple", see below.

I loaded data in to "simple" from a tab separated file.

The table simple has the following types -

mysql> describe simple;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| joined | date | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+



If I enter one record in my .txt (tab separated file) and then try and retrieve all records from that table I can see all the data with no problem, see directly below

mysql> select * from simple;
+------------+------+------+------+
| joined | name | age | sex |
+------------+------+------+------+
| 2006-01-01 | bob | 10 | male |
+------------+------+------+------+
1 row in set (0.00 sec)


If I enter more than one record and then try to retrieve all the data, the formatting goes wrong, where I can't see the whole of the first date and the end of the last field in the first record.

mysql> select * from simple;
+------------+------+------+-------+
| joined | name | age | sex |
+------------+------+------+-------+
| 006-01-01 | bob | 10 | male
| 2006-01-02 | tim | 12 | male |
+------------+------+------+-------+

If I add more records, the formatting is worse and the joined column makes no sense all the way down.

I am sure this is something that a mysql pro could answer very quickly.

All help much appreciated.

Regards

Bhogaj31

 

[guelphdad]

well what would help is if you showed us your import statement.

also when you import do you get any warning messages? if so then do:

show warnings

and tell us what they are.

 

[bhogaj31]

Hi quelphdad,

Your suggestion led me back to the tutorial that came with mysql-5.0.21-win32.

Please excuse the fact that I have used a different table to show that I have found the cause of the problem. The one I used in the original query was on a different computer.

I was using the following syntax to load data into the table, which was giving me the odd formatting -

mysql> LOAD DATA LOCAL INFILE 'c:\\customers.txt' into table one;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from one;
+------------+-------+---------+-------+
| doe | fname | sname | house |
+------------+-------+---------+-------+
| 3-02-01 | mat | smith | 11
| 2003-01-01 | jat | bhogal | 12 |
+------------+-------+---------+-------+
2 rows in set (0.00 sec)


After reading the tutorial, the bit about loading data in to the table using a text file e.g -


Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';


I used the following syntax and got a better form of output, which wasn't missing anything.

mysql> LOAD DATA LOCAL INFILE 'c:\\customers.txt' into table one
-> LINES TERMINATED BY '\r\n';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from one;
+------------+-------+---------+-------+
| doe | fname | sname | house |
+------------+-------+---------+-------+
| 2003-02-01 | mat | smith | 11 |
| 2003-01-01 | jat | bhogal | 12 |
+------------+-------+---------+-------+
2 rows in set (0.00 sec)

Thank you for your time and please note that this problem has been solved.

Regards

Bhogaj31