Scripts
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Web Site Management > Scripts
Reload this Page

php script for mysql import

Discuss php script for mysql import in the Scripts forum on Dev Shed.
php script for mysql import Scripts forum discussing topics including building, optimization, and implementation. Discuss which scripting language is best suited to your needs.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!
  #1  
Old September 1st, 2003, 03:05 AM
tomuksm tomuksm is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Posts: 4 tomuksm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
php script for mysql import
i have a csv file local to my pc, i need to import this comma delimited csv automatically to my mysql database on my web server,

i now think??? i need a php script that i can run as a cron job on my web server, this would automate the process and i could have a refresh button on the website to refresh the page automatically

i thought i could schedule a ftp program to upload the csv file to the web server

but there's a problem,, iam no good at php, can any help me????
Reply With Quote
  #2  
Old September 1st, 2003, 03:59 AM
drgroove's Avatar
drgroove drgroove is offline
pushing envelopes, not pencils
Dev Shed God 2nd Plane (6000 - 6499 posts)
  
Join Date: Feb 2002
Posts: 6,224 drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level)drgroove User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 1 Day 4 h 34 m 57 sec
Reputation Power: 174
Moved to Scripts forum from PHP
__________________
Give a person code, and they'll hack for a day; Teach them how to code, and they'll hack forever.
Analyze twice; hack once.
The world's first existential ITIL question: If a change is released into production without a ticket to track it,
was it actually released?

About DrGroove: ITIL-Certified IT Process Engineer - Enterprise Application Architect -
Freelance IT Journalist - Devshed Moderator - Funk Bassist Extraordinaire
Reply With Quote
  #3  
Old September 1st, 2003, 12:41 PM
Wite_Noiz Wite_Noiz is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: London, England
Posts: 277 Wite_Noiz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 h 42 m 30 sec
Reputation Power: 5
How much help do you require?

Have you got a PHP script that is connecting to your MySQL database, or do you want it all?
Reply With Quote
  #4  
Old September 2nd, 2003, 01:35 AM
tomuksm tomuksm is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Posts: 4 tomuksm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
i have nothing
Reply With Quote
  #5  
Old September 2nd, 2003, 03:24 AM
Wite_Noiz Wite_Noiz is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: London, England
Posts: 277 Wite_Noiz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 h 42 m 30 sec
Reputation Power: 5
Re: php script for mysql import
Okay, here's a starter.

Firstly, I suggest reading the following articals:
PHP 'MySQL Functions': http://uk.php.net/manual/en/ref.mysql.php
MySQL 'Load Data': http://www.mysql.com/doc/en/LOAD_DATA.html

I'll give you a short script so you're started.
Call the file something like 'update.php'.
PHP Code:
<?php
## Connect to a local database server (or die) ##
$dbH = mysql_connect('localhost', 'user', 'pass') or die('Could not connect to MySQL server.<br>' . mysql_error());

## Select the database to insert to ##
mysql_select_db('test') or die('Could not select database.<br>' . mysql_error();

## CSV file to read in ##
$CSVFile = 'data.txt';

mysql_query('LOAD DATA LOCAL INFILE "data.txt" INTO TABLE News FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());

## Close database connection when finished ##
mysql_close($dbH);
?>


This is tested and working on my system. So, hopefully, it'll work on yours
Obviously, you'll have to change the server username and password and the database name for it to work.

Hope it helps
__________________
Wite_Noiz the grey


W3C | PHP | MySQL
W3Schools for HTML, XHTML, CSS and more
Reply With Quote
  #6  
Old November 20th, 2003, 10:24 AM
fluidicity fluidicity is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Nov 2003
Posts: 3 fluidicity User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Hi,

Is there a way of importing a text file that has the likes of...

'WRX\'s,Subaru,Impreza'

as an entry for a field where fields are seperated by , and enclosed by ' but any characters ' are escaped by / as above.

I've tried modifying the original posted here as in...

PHP Code:
 mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "" LINES TERMINATED BY "rn";') or die('Error loading data file.<br>' . mysql_error()); 


..but I keep getting...

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING

I've also tried ....

PHP Code:
 mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "\'" ESCAPED BY "\\" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error()); 


..but I keep get...

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"mms3" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "\"

Is there a way for dealing with the ' in the ENCLOSED BY " " part so that the statement doesn't break up and get parsed wrongly?

I've tried phmyadmin 2.5.4-rc1 but getting " You should select file which you want to insert.", which I think is being caused by the above parsing problem.

Any help is greatly appreciated as I've tried various things but no luck.

Vic.

p.s. Probably should have put this post in the PHP forum!
Last edited by fluidicity : November 20th, 2003 at 10:58 AM.
Reply With Quote
  #7  
Old April 23rd, 2005, 03:45 AM
dev_2005 dev_2005 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2005
Posts: 1 dev_2005 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 22 m 34 sec
Reputation Power: 0
details
Hi!
I'm new in php scripting and i need exatly the same script that u posted. Could u give me some more detail about runing the script? Where do i need to plase the php script? On my webserver? (it is a linux) The csv file is on my windows machine.

Quote:
Originally Posted by Wite_Noiz
Okay, here's a starter.

Firstly, I suggest reading the following articals:
PHP 'MySQL Functions': URL
MySQL 'Load Data': URL

I'll give you a short script so you're started.
Call the file something like 'update.php'.
PHP Code:
<?php
## Connect to a local database server (or die) ##
$dbH = mysql_connect('localhost', 'user', 'pass') or die('Could not connect to MySQL server.<br>' . mysql_error());

## Select the database to insert to ##
mysql_select_db('test') or die('Could not select database.<br>' . mysql_error();

## CSV file to read in ##
$CSVFile = 'data.txt';

mysql_query('LOAD DATA LOCAL INFILE "data.txt" INTO TABLE News FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());

## Close database connection when finished ##
mysql_close($dbH);
?>


This is tested and working on my system. So, hopefully, it'll work on yours
Obviously, you'll have to change the server username and password and the database name for it to work.

Hope it helps
Reply With Quote
  #8  
Old June 17th, 2005, 03:50 PM
cwsteam.com cwsteam.com is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2005
Posts: 6 cwsteam.com User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m
Reputation Power: 0
I was trying something, and your code really helped me, thanks Wite_Noiz !!
Reply With Quote
Reply

Viewing: Dev Shed Forums > Web Site Management > Scripts > php script for mysql import

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway.