Importing .csv Into Mysql Database

Mystixs

Jan 27 2007, 05:31 AM

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

Topic modified

Reply

Kubi

Jan 27 2007, 05:40 AM

Would you mind showing us the error page? It could be an incorrect db nname/password

Reply

master_bacarra

Jan 27 2007, 06:24 AM

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).

Reply

Mystixs

Jan 27 2007, 02:43 PM

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..

Reply

Mystixs

Jan 27 2007, 02:58 PM

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..

Reply

master_bacarra

Jan 27 2007, 09:49 PM

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.

Reply

Mystixs

Jan 27 2007, 10:06 PM

Thanks. Problems resolved

Reply

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE

Importing .csv Into Mysql Database - NEED HELP!

Importing .csv Into Mysql Database - NEED HELP!