 Importing .csv Into Mysql Database, NEED HELP! |
|
|
|
|
  |
 Jan 27 2007, 05:31 AM
Post
#1
|
|
|
Member [Level 1]  Group: Members Posts: 57 Joined: 23-January 07 Member No.: 37,683  |
I need help importing a .csv contents into a Mysql database.
I have this. But its not working. CODE <?php connection = mysql_connect("******", "**********", "********") or die ("Unable to connect to server"); $db = mysql_select_db("b9_259782_CC", $connection) or die ("Unable to select database"); fopen ('csvranks.csv', 'r'); mysql_query("INSERT INTO test_table (id, name, guild, level, exp) ?> What am I doing wrong? The mysql table is all set up.. Any help is GREATLY appreciated. If you need more info just ask. Thanks! EDIT: The CSV file looks like this one http://movoda.net/api/csvranks.html This post has been edited by BuffaloHELP: Jan 27 2007, 09:10 PM |
 |
 
|
|
Jan 27 2007, 05:40 AM
Post
#2
|
|
|
To Cool for Cache  Group: [MODERATOR] Posts: 1,091 Joined: 16-June 05 From: Some Place. Member No.: 8,317  |
Would you mind showing us the error page? It could be an incorrect db nname/password
|
|
|
|
|
Jan 27 2007, 06:24 AM
Post
#3
|
|
|
I'm back... well, sort of. Group: [HOSTED] Posts: 697 Joined: 26-December 05 From: somewhere in the middle of nowhere Member No.: 16,226  |
i told you, you haven't looked enough through the search engines
 you opened the file, but you didn't use a handle for it. try this one CODE $file_handle = fopen ("filename.csv"); while (($row = fgetcsv($file_handle)) !== false) { $data = explode (",",$row); $insertrecord = "INSER INTO `$tablename` VALUES ('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."')"; mysql_query($insterrecord); } fclose (file_handle); i've adjusted the code to fit the csv file you have indicated. of course that is assumed that all of the values in each row are string. your database could be indicating some of the attributes as of integer type. convert data[3] and data[4] to integer values (i'm assuming that you're keeping the leading zeros in the values of data[0]). i think the function is intval() taking the string as an argument. so the line CODE $insertrecord = "INSER INTO `$tablename` VALUES ('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."')"; will become CODE $insertrecord = "INSER INTO `$tablename` VALUES ('".$data[0]."','".$data[1]."','".$data[2]."','".intval($data[3])."','".intval($data[4])."')"; also, change the '$tablename' to the name of the table where these values are to be inserted. hope that helps (and i hope that works). This post has been edited by master_bacarra: Jan 27 2007, 06:27 AM |
|
|
|
|
Jan 27 2007, 02:43 PM
Post
#4
|
|
|
Member [Level 1] Group: Members Posts: 57 Joined: 23-January 07 Member No.: 37,683 |
Ok. First, KuBi. I didn't get any error.. It connected to the database, and selected the database. It was just a blank page...
Anyway, master_bacarra thanks, I will try this. I made need more help though. I'm not very good at PHP and Mysql.. |
|
|
|
|
Jan 27 2007, 02:58 PM
Post
#5
|
|
|
Member [Level 1] Group: Members Posts: 57 Joined: 23-January 07 Member No.: 37,683 |
I guess this is because my lack of knowledge and slow learning but i'm having trouble..
I will show what I have now. The Mysql table: CODE ALTER TABLE `test_table` CHANGE `id` `id` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `name` `name` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `guild` `guild` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `level` `level` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `exp` `exp` INT( 100 ) NOT NULL DEFAULT '0' The PHP: CODE <?php connection = mysql_connect("sql1.byethost9.com", "***", "***") or die ("Unable to connect to server"); $db = mysql_select_db("b9_259782_CC", $connection) or die ("Unable to select database"); $file_handle = fopen ("csvranks.csv"); while (($row = fgetcsv($file_handle)) !== false) { $data = explode (",",$row); $insertrecord = "INSER INTO `tst_table` VALUES ('".$data[0]."','".$data[1]."','".$data[2]."','".intval($data[3])."','".intval($data[4])."')"; mysql_query($insterrecord); } fclose (file_handle); ?> http://chesspieces.byethost9.com/test.php thats the page its saved to. I go to it and it does nothing. I check the database table and its empty.. Sorry for the bother.. |
|
|
|
|
Jan 27 2007, 09:49 PM
Post
#6
|
|
|
I'm back... well, sort of. Group: [HOSTED] Posts: 697 Joined: 26-December 05 From: somewhere in the middle of nowhere Member No.: 16,226 |
QUOTE(Mystixs @ Jan 27 2007, 10:58 PM)  The Mysql table: CODE ALTER TABLE `test_table` CHANGE `id` `id` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `name` `name` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `guild` `guild` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `level` `level` INT( 100 ) NOT NULL DEFAULT '0', CHANGE `exp` `exp` INT( 100 ) NOT NULL DEFAULT '0' i don't get it. you've given me the database entries, but two attributes don't say that it is of INT data type. the id attribute can be of INT, same goes with level and exp. but i don't get it why you're putting name and guild attributes to INT. it doesn't match your table entries indicated in the csv file. here's one sample data: 0000000073,moc1,CoM,26,594169 assuming that i'm thinking that each entry is under the correct attribute, then 0000000073 is the id, moc1 is the name, CoM is the guild, 26 is the level, and 594169 is the exp. you can't put name and guild as INT because it won't match moc1 and CoM. it won't match the data types. like what KuBi has said, i think you should post the error here. QUOTE(Mystixs @ Jan 27 2007, 10:58 PM) The PHP: CODE <?php connection = mysql_connect("sql1.byethost9.com", "***", "***") or die ("Unable to connect to server"); $db = mysql_select_db("b9_259782_CC", $connection) or die ("Unable to select database"); $file_handle = fopen ("csvranks.csv"); while (($row = fgetcsv($file_handle)) !== false) { $data = explode (",",$row); $insertrecord = "INSER INTO `tst_table` VALUES ('".$data[0]."','".$data[1]."','".$data[2]."','".intval($data[3])."','".intval($data[4])."')"; mysql_query($insterrecord); } fclose (file_handle); ?> don't forget to put $ before the php variables you're using. put $ before connection and file_handle (the one inside fclose()). query should be INSERT not INSER. |
|
|
|
|
Jan 27 2007, 10:06 PM
Post
#7
|
|
|
Member [Level 1] Group: Members Posts: 57 Joined: 23-January 07 Member No.: 37,683 |
Thanks. Problems resolved
|
|
|
|
|
Similar Topics