Select * from [table]; display problem?

I am have a problem with the displaying of data.

[ineedsomex12s]

Hello and thanks in advance for the help. I am a new student to mySQL and I was creating a test database for a class. I have created a database, and also create a table called employee. Now in employee I have multiple columns and rows. The problem comes in is is when I created the rows I used a txt file to import the data. Now when I display the data from the table by using select * from employee in command prompt it does not show all the data in the first column of every row. For example:

Name
John
Jame
Rick
when it shows it to me it is displayed as:
Name
hn
ame
ck

Now if I query it by just select name from employee; it displays the data properly. Is there anything that I can do to fix the display problem when using select everything from the table? And this seems to only affect the first field.

Help please.
I can email a picture of the command prompt if that will help.

[ineedsomex12s]

Here is what the comman prompt shows

mysql> SELECT * FROM PROJECT
-> ;
+-----------------+---------+-----------+------+
¦ PNAME ¦ PNUMBER ¦ PLOCATION ¦ DNUM ¦
+-----------------+---------+-----------+------+
¦ ductX ¦ 1 ¦ Bellaire ¦ 5
¦ ductY ¦ 2 ¦ Sugarland ¦ 5
¦ ductZ ¦ 3 ¦ Houston ¦ 5
¦ puterization ¦ 10 ¦ Stafford ¦ 4
¦ rganization ¦ 20 ¦ Houston ¦ 1
¦ benefits ¦ 30 ¦ Stafford ¦ 4
+-----------------+---------+-----------+------+
6 rows in set (0.00 sec)

mysql> SELECT PNAME FROM PROJECT;
+-----------------+
¦ PNAME ¦
+-----------------+
¦ ProductX ¦
¦ ProductY ¦
¦ ProductZ ¦
¦ Computerization ¦
¦ Reorganization ¦
¦ Newbenefits ¦
+-----------------+
6 rows in set (0.00 sec)

In the first query it cuts off some of the data even though when I just select the Pname column it shows that the data is there. Why? Please Help

[coopster]

Welcome to WebmasterWorld, ineedsomex12s.

Something is certainly wrong here! You may want to either try to repair that table or drop it, create it and populate it with data again.

[ineedsomex12s]

Yes I have dropped it and repaired it, but it seems that when I load in via text file this is the way it displays. Any other thoughts?

[coopster]

Which version of MySQL are you running?

[ineedsomex12s]

MySQL 5.0.45

[coopster]

I have come across this before. You have carriage returns in your data and when you select the data for viewing in a command line interface you are noticing garbled data displayed. If the special meaning strings [dev.mysql.com] are not desired you are going to have to clean them up prior to import or run some cleanup work afterward using the command line interface (or write a script and run it against the table).

[ineedsomex12s]

Ok, I understand. Is this something that it will always do when importing data from a textfile adding that ('x/r') in the command automatically? Is there a better way to import data into tables other than one by one?

[coopster]

I should have noted that you can quickly identify some of the special characters by running the HEX function and comparing a few things. For example, have a look at the following where I show you what a space character and a carriage return look like in HEX and then show you what it looks like in your data ...

mysql> SELECT HEX(' '), HEX('\r'); 
+----------+-----------+ 
¦ HEX(' ') ¦ HEX('\r') ¦ 
+----------+-----------+ 
¦ 20       ¦ 0D        ¦ 
+----------+-----------+

... and now we'll look at the HEX values apart, and then put back together, recognizing the space and the carriage return within ...

mysql> SELECT HEX('Carriage'), HEX('Return'), HEX('Carriage Return'), HEX('Carriage\rReturn')\G

*************************** 1. row *************************** 
        HEX('Carriage'): 4361727269616765 
          HEX('Return'): 52657475726E 
 HEX('Carriage Return'): 43617272696167652052657475726E 
HEX('Carriage\rReturn'): 43617272696167650D52657475726E

Try displaying one on the command line:

mysql> SELECT 'Carriage\rReturn'; 
+-----------------+ 
Return ¦ge 
+-----------------+ 
Return ¦ e 
+-----------------+

Not very pretty. We can run some cleanup though, as I mentioned in the last message.

mysql> SELECT REPLACE('Carriage\rReturn', '\r', ''); 
+---------------------------------------+ 
¦ REPLACE('Carriage\rReturn', '\r', '') ¦ 
+---------------------------------------+ 
¦ CarriageReturn                        ¦ 
+---------------------------------------+

So, you can now see what is likely causing your issue at least.

[coopster]

LOAD DATA INFILE [dev.mysql.com] does not add it, you already have it in your data. It is just leaving it there and pushing it right on into the table columns because you have not specified otherwise. Have a read on that page especially in regards to the FIELDS ESCAPED BY clause.

[ineedsomex12s]

Thanks, that helped tremendously, and put a lot of things in prospective. Appreciate it!