Page 1 of 1
Replacing Table Data
Posted: Fri Jul 10, 2009 4:39 pm
by DAVID87
Hi Everyone.
I have set up my database and one of the table that I use needs to be updated regularly. This means totally wiping the table data and replacing it with new. Now I know that the code for wiping all of the data is:
Can anybody help me with a code to import all of the data form a csv file?
Curently the only way to do this is to log into the database directly and then truncate it and import the data. however I want to do this from my website and not "manually" as such.
Can anyone help? Is this possible?
Thanking you in advance.
Re: Replacing Table Data
Posted: Fri Jul 10, 2009 4:49 pm
by andyhoneycutt
There are many ways to do this and you don't necessarily need PHP to accomplish this task. That said, here's a solution in PHP.
Code: Select all
$dblink = mysql_connect("host","user","pass") or die("Oops.");
mysql_select_db("catalog_name",$dblink) or die("Hrm.");
$data_file = "/path/to/csv";
$table_name = "some_table";
$delimiter = ",";
$enclosure = "\"";
$eol = '\n';
$query = "
LOAD DATA INFILE '$data_file'
INTO TABLE $table_name
FIELDS TERMINATED BY '$delimiter' ENCLOSED BY '$enclosure'
LINES TERMINATED BY '$eol'
";
mysql_query($query,$dblink) or die("Damn it.");
Now just plug a call to "php /path/to/this/script.php" in your crontab.
Reference
mysql load data infile
Reference
crontab usage
Re: Replacing Table Data
Posted: Sat Jul 11, 2009 3:14 am
by DAVID87
Thanks for the fast response.
I have modified the code to fit in to my website.
I have set up 2 pages under a test area. The 2 pages I have set up are called 1.php and 2.php.
2.php contains a form that basically has a file input field and a submit button. On the click of the submit button the form is posted to 1.php. here is the code in the 1.php file:
Code: Select all
<?php
include 'dbc.php';
$data_file = $_POST['ascpbs'];
$table_name = "ascpbs_test";
$delimiter = ",";
$enclosure = "\"";
$eol = '\n';
$query = "
LOAD DATA INFILE '$data_file'
INTO TABLE $table_name
FIELDS TERMINATED BY '$delimiter' ENCLOSED BY '$enclosure'
LINES TERMINATED BY '$eol'
";
mysql_query($query) or die(mysql_error());
header("Location: 2.php?msg=Imported!");
exit();
?>
I have included my dbc.php file which contains all of the database information as shown below. I have used this method because I have alot of files that look to the same database for information and if I need to change the password or anything it makes it alot easier.
dbc.php file:
Code: Select all
<?php
$dbname = 'database_name';
$link = mysql_connect("host","User_name","password") or die("Couldn't make connection.");
$db = mysql_select_db($dbname, $link) or die("Couldn't select database");
?>
Now when I try the import code the error I get is an access denied error.
Now can anyone explain why this might be as the dbc.php file works fine with everything else.
Re: Replacing Table Data
Posted: Sun Jul 12, 2009 12:52 pm
by andyhoneycutt
First, I'd make sure this script isn't accessible to the outside world if you plan on calling it via http. Second, could you please post the exact error message you're receiving?
Thanks much,
Andy
Re: Replacing Table Data
Posted: Sun Jul 12, 2009 2:06 pm
by DAVID87
the error message I am getting is:
Access denied for user 'user_name'@'%' (using password: YES)
Re: Replacing Table Data
Posted: Mon Jul 13, 2009 12:38 pm
by andyhoneycutt
Access denied for user 'user_name'@'%' (using password: YES)
No php error associated with this, like a script filename and line number? Either way, I would suggest a couple things:
1 - Flush privileges, ensure that the user you are connecting with does, indeed, have access.
2 - Specify a host for the user as opposed to connecting via wildcard (%).
3 - Ensure that you have passed the correct credentials to mysql_connect()
-Andy