import csv to mysql. No clue how to do it.

[lorddemos90]

I want to populate a mysql table from data from a .csv file.

I want to have it so that the script can just be automatically run and it will populate the table with the data without any user input.

Also I want to have it set so that it will not "add" the data to previously existing rows but instead erase all the table rows and then insert all the new information. So in other words I'd like the mysql table to always only reflect what is contained in the .csv file.

I have no idea how to do this and so far all the scripts that I have found aren't quite what I want.

[drifter]

well you are going to need to start writing something and then ask if you have questions. Or go to the freelance section...

Start by reading the file...

use a file()
then a foreach()
and use the php csv funstions to read the data...

once you get that  far, and you have data, you can worry about putting itin the DB.

[thepip3r]

Most often on this site, people make an attempt at coming up with a solution as most of us have enough projects of our own.  If you are new to PHP and MySQL, you're going to have quite a few problems actually accomplishing this simple task.  You'll need to look into PHP's [url=http://www.php.net/file]file[/url] functions, probably some of the [url=http://www.php.net/manual/en/language.operators.string.php]string operators[/url], and become familiar with the [url=http://www.php.net/manual/en/ref.mysql.php]MySQL Functions[/url].  I'm sorry I could not be of more help, maybe one of the others forum-goers.

[HuggieBear]

Shouldn't be too difficult...

[code]<?php
// connect to database
include('connect.php');

// clear the table down
$sql = "TRUNCATE TABLE table_name";
mysql_query($sql);

// open the csv file
$data = file('/path/to/your/file/goes_here.csv');

// create an insert statement for each row in the csv file
foreach ($data as $row){
  $cols = explode(",", $row); // put each piece of data seperated by a comma, into an array called $cols
  $sql = "INSERT INTO table_name (column_1, column_2, column_3) VALUES ('$cols[0]', '$cols[1]', '$cols[2]')";
  mysql_query($sql);
}
?>[/code]

This has no error checking on it, but it should work OK,

Regards
Huggie

[lorddemos90]

here's what I've got...

<?php
// connect to database
$connection = mysql_connect('localhost', 'login', 'pass');

mysql_select_db('seasons');

// clear the table down
$sql = "TRUNCATE TABLE 'tabletwo'";

mysql_query($sql);

$txtfile = "book1.csv";
$table = "tabletwo";

$sql = 'LOAD DATA LOCAL INFILE "'.$txtfile.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" IGNORE 1 LINES';

?>

It's not generating any errors, but it doesn't seem to be affecting the table at all. 

[HuggieBear]

I don't know anything about loading csv files into MySQL, hence the other approach I suggested.  I can't help if you go down this route, sorry.

Huggie

[emehrkay]

[quote author=HuggieBear link=topic=119162.msg488227#msg488227 date=1166542033]
I don't know anything about loading csv files into MySQL, hence the other approach I suggested.  I can't help if you go down this route, sorry.

Huggie
[/quote]

i just done this at work, and i basically did what you purposed, but with error checking.