Importing data into MySQL database??

[printman55]

In my hosting account I have set up a database named george. There is one table named "news". There are three fields "id" (primary key), "date", and "text". I am keeping this simple for illustration. What is the best/easiest/fastest way to import the data or records to the table via mysqladmin import text file??? Thanks

[pritaeas]

It depends on your source. Importing an SQL file is probably the fastest as it requires no parsing. You can also import from CSV. If your source is a table, you can just copy it in with a query (provided you can connect to both).

[smantscheff]

@priteas: As far as I know you're wrong. CSV import is always the fastest with MySQL. If there are foreign keys, foreign key checking should be disabled during import for speed.

[pritaeas]

Really, even though sql file is ready to go and the csv has to parsed and validated ? Impressive if that is true. Do you have any resources on this ?

[printman55]

Would someone please provide basic sql query code to use to upload data.
The db name is news; the table name is george; the fields are:
id (primary key int(10));
news_date (varchar(15));
news_text (char(100))
Thanks

[pritaeas]

What data ? A CSV file ?

[printman55]

Yes, the data is a csv file.

[smantscheff]

The code from the mysql prompt goes like

load data infile "george.csv" into table george fields terminated by "\t";

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

@priteas: No, I only remember dimly to have read it on the mysql website itself that LOAD DATA is the fastest upload operation.

[printman55]

Thanks,This is the code that I finally got to work in mysqladmin when I up loaded this CSV data:
NULL,2011-01-31,"January 31, 2011 new text Egypt"
NULL,2011-01-28,"January 28, 2011 text about market meltdown"
NULL,2011-01-15,"January 15, 2011 text about fourth quarter report"

SQL query: LOAD DATA LOCAL INFILE '/var/php_sessions/uploads/phpHo3X8g'  INTO TABLE `news` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'(
`ID` , `news_date` , `news_text`
)# Affected rows: 3

I am now up to record #50. If I edit the ID field primary key field back to 1,2,3 and then upload again with the same data the record number starts at #51 rather that #4. Is there a way to clean out the history of the auto-increment primary key?

[smantscheff]

Recreate the table before each run with a complete "CREATE TABLE" statement.
Or tell mysql:

alter table news auto_increment = 0;

Have a look at the output of "SHOW CREATE TABLE news" which will make things clearer.

[printman55]

I ran the

ALTER TABLE news AUTO_INCREMENT = 0;

and then uploaded another set of three records. The new one's had record numbers in the 50's.
I tried the

SHOW CREATE TABLE news

but it didn't do anything that I could see??

[smantscheff]

Are you using the mysql command line interface? If not, you should.

[printman55]

Yes this comes from mysqladmin SQL command pane:

SQL query: ALTER TABLE news AUTO_INCREMENT =0;

[smantscheff]

I'm referring to the command line tool mysql, not to mysqladmin or any GUI. Command line mysql seems to be the only tool around which does not filter any messages you might want to see - in this case the output of "SHOW CREATE TABLE"

[printman55]

I think I found the answer: http://www.liewcf.com/mysql-reset-auto-increament-number-115/ What I did was delete the high record rows and then ran
ALTER TABLE news AUTO_INCREMENT =0; this time changing "0" to "1" and it seemed to work. Now I have records 1 thru 6.

The next step is one that I also need help on.
I am going to set up a form to enter info into the database and then use that data to build a list on a webpage. I entering data into a database the last data entered is at the bottom. So when the page is loaded the last record entered will be at the bottom of the list. Is there a SQL command to reverse the order so the last record entered is at the top?

[smantscheff]

SQL does not support the concept of "first" and "last" records or a natural record order. All ordering and sorting has to be done explicitly.
Since you use an auto_increment field you can sort your query result by this field for the desired result:

select * from news order by id desc;

But the fact that you don't know this leads me to recommend that you first take a basic mysql primer course before venturing on real database work. Could save you a lot of time.

[printman55]

I appreciate the suggestion and that is in fact what I am doing. I'm studying "PHP and MySQL Web Development". I have done a fair amount of ASP work and want to get into mysql. Let me see if I understand the code: order by ID desc. Is desc decending and could you go the other way say ascending?

[smantscheff]

Yes, this way you can order results by any scalar values (even float and double). You can also combine ASC and DESC orders for different result fields.

[printman55]

Thanks,
Would the PHP syntax be the same?

[smantscheff]

PHP has an interface which submits SQL queries to MySQL exactly as you would enter them in the mysql command line interface or any other interface I know of - because that is the language MySQL is understanding.
The PHP interface has no database abstraction but is a (nearly) 1:1 translation of the MySQL API.
Put your query in a PHP text string or a variable and submit it with the mysql() or the mysql_query() function.

[printman55]

Thanks so much for all your help. I may have a few more questions today or tomorrow after which I will mark as solved.

[drjohn]

I use tab delimited files, not csv, so much easier, as I too have a comma in the middle of some fields. I also use some software (SQL Manager Lite for MySQL, from EMS or HeidiSQL) for remote connection, so I don't even have to upload the file in advance.

load data local infile "C:/Documents and Settings/dr john/My Documents/logpile/AccountDataExport_2011-01-29.txt" into table accounts;

the keyword local tells it to take the file from my PC's drive.

csv is overrated, tab delimited is so much easier to work with. Spreadsheets can save as tab delimited if that's where your data comes from, mine comes from an extraction app running on a desktop's database.

[printman55]

The mysql database I set up used date format for the field. Consequently dates have to be entered as 0000-00-00. So when I retrieve the field name news_date from the database it will print as 2011-02-02. I would like to display it as February 2, 2011. Could you possible help me with the PHP code so I can print it in the web page as February 2, 2011. Thanks.

[mwasif]

Use MySQL's DATE_FORMAT() function. The query will be

SELECT DATE_FORMAT(news_date, '%M %e, %Y') FROM table;

[printman55]

Here is the code I have been testing. It displays the date field as 2011-02-03 what I need is to display the date as February 3, 2011:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="CCS-Styles.css" rel="stylesheet" type="text/css" />
</head>

<body><div align="center" >
<table width="100%" border="0" cellspacing="3" cellpadding="8">
<?php 
$conn = mysql_connect('database', 'username', 'password'); 
if (!$conn) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully'; 
mysql_select_db(ndb);
$query  = "SELECT news_date, news_text FROM news ORDER BY ID desc";
$result = mysql_query($query);

while(list($news_date,$news_text)= mysql_fetch_row($result))
{
 Print "<tr><td class='ParaText' align='left' valign='top'>Date</td><td class='ParaText'>".$news_date ."</td><td class='ParaText' align='left' valign='top'>" .$news_text ."</td></tr>";    
}
mysql_close($conn); 
?>
</div>
</body>
</html>

Thanks

[smantscheff]

The solution is in the last post of mwasif.