Upload .CSV file into MySql DB using PHP

i need the script that will reae the contents of the csv file  & store it into the 2 tables in MySql DB

Give it a try:http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/

This should get you pretty close:

<?php
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
 
if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}
 
$handle = @fopen("inputfile.csv", "r");
 
if ($handle) {
    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        $values = explode(",", $buffer);
		if(count($values == 2)) { // make sure the line is valid
			$col1 = trim($values[0]);
			$col2 = trim($values[1]);
			if(
				($col1 != '') &&
				(is_int($col1)) &&
				($col2 != '') &&
				(is_string($col2))
			) { // sample validation routine
				$sql = "INSERT INTO table_name
					(column_name1, column_name2)
					VALUES
					($col1, $col2);";
	
				$result = mysql_query($sql);
 
				if (!$result) {
					echo "Could not successfully run query ($sql) from DB: " . mysql_error();
					exit; // comment this out if you'd like to continue after errors
				}
			}
			else {
				echo "Data is invalid";
			}
		}
		else {
			echo "Incorrect number of values on line";
		}
    }
    fclose($handle);
}
 
mysql_free_result($result);
?>
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:

Of course, change line 20 to match the number of values you expect on each line of your csv and duplicate the code in lines 29-39 for the second table.

hi dazweeja, thanks for the suggestion. i have the csv file uploaded on the server -- will your code be able to read the file from there. what is 4096 in line 18?

It will be able to read files on the same server - just give it the correct file path. It will also be able to read files on other servers, ie. using a URL as the path, but only if allow_url_fopen is On for that server (the default). See my comments here:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_24334585.html?cid=1066#a24183619

4096 is the number of bytes to reads into memory for each line. This value should handle most situations - it's at least 2048 characters per line - so is commonly used. You can make this value smaller if your lines are short. I think you'd only bother if memory/performance of your website was critical.

it doesn't save the records in DB table

there is also this error i forgot to mention earlier:
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home1/uploader.php on line 89

My mistake, that line doesn't need to be there so get rid of it. It only applies if you are using SQL SELECT statements and it's in the wrong place anyway. I accidentally copied from another piece of code I had.

Are you getting any other error messages?

Are you sure it is opening your csv file correctly? If you change '@fopen' to 'fopen', you should see an error message on the screen or in your PHP error log if it's unable to open the CSV file. The '@' symbol is there to suppress error messages but at the coding stage, you probably want to see them. When the site is live, you would probably want to change it back to '@fopen' and put this at line 51:

else {
   echo "Problem opening csv file";
}

If the file is opening correctly, have you changed the SQL to match your table structure? As a debugging step, insert this line at line 33 to make sure your SQL command is what you expect:

echo $sql;

In the development stage, you might want to put echo statements (or error_log statements) all through your code to see exactly where the problem is or use a PHP debugger like XDebug or ZendDebugger.

To clarify, I meant delete the line (should be line 89 in your code):

mysql_free_result($result);

i am receiving the following error:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, PROJECT_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, gipps, 200, 2002);Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, PROJECT_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, gipps, 200, 2002);) from DB: Unknown column 'gipps' in 'field list'

i dont need to add values in sequential order as in the DB table since i am mentioning which columns the values go under? any idea how to solve the problem above?

in my csv if I have row headers, how do i omit while inserting in DB

ERROR MSG:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)) from DB: Duplicate entry '2002' for key 2

looks like it is reading the first line over again

disregard the prev message

i get this error but data is also inserted in the DB:
Resource id #13INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2002)1Data is invalidINSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (4, 200, 2003)1Data is invalidINSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (, , )Could not successfully run query (INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES (, , )) from DB: 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 ' , )' at line 1

how can i solve this bit?

If you are inserting a value into a column and the datatype of that column in your database is not numeric, eg. char, varchar, text, etc., you have to put quotation marks around your values in your SQL. You can also put quotation marks around numeric data as well but it's optional.

For example:

$sql = "INSERT INTO `gipps_student_info` (SUBMITTER_ID, SCHOOL_NAME, STUDENT_CODE) VALUES ('" . $submitter_id . "','" . $school_name . "','" . $student_code . "');";
 
echo $sql;

                                              

1:
2:
3:

Also, your last message seems to suggest that an INSERT is being attempted even when the data is empty or not valid. Have a look at your validation code and put in checks like ($school_name != "") if necessary.

i've uploaded the code as well as a sample csv template i want to insert. i couldn't upload .csv file so you may need to save it as .csv file. users may not have to insert all fields, some may be kept blank.
when reading i want the insertion to exclude/skip the heading row in the csv file

$handle = @fopen($target_path, "r");
 
if ($handle) {
    while (!feof($handle)) 
    {
        $buffer = fgets($handle, 4096);
        $values = explode(",", $buffer);
                if(count($values == 10)) 
                { 
                        $col1 = trim($values[0]);
                        $col2 = trim($values[1]);
                        $col3 = trim($values[2]);
                        $col4 = trim($values[3]);
                        $col5 = trim($values[4]);
                        $col6 = trim($values[5]);
                        $col7 = trim($values[6]);
                        $col8 = trim($values[7]);
                        $col9 = trim($values[8]);
                        $col10 = trim($values[9]);                 
						
			$sql = "INSERT INTO `$_SESSION[student_info]` (SCHOOL_NAME, STUDENT_CODE, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, YEAR_LEVEL, CLASS, GENDER, TEACHER_TITLE, TEACHER_FIRST_NAME, TEACHER_LAST_NAME, PROJECT_ID, SUBMITTER_ID) 
				VALUES ($col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, '$_SESSION[project_id]','$_SESSION[teachersid]');";
			$result = mysql_query($sql);
			
                        if (!$result) {
                		echo "Could not successfully run query ($sql) from DB: " . mysql_error();
                       		exit; 
                                }
                        
                        else {
                                echo "Data is invalid";
                        }
                }
                else {
                	echo "Incorrect number of values on line";
                }
    }
    fclose($handle);
}
?>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

the code inserts the data in teh DB but has the following error:
Data is invalidData is invalidData is invalidData is invalidData is invalid

it inserts an extra blank record for student.
How can i skip reading teh header in the csv file?

I won't be able to test this until I get home but start by putting quotation marks around all the values in line 22:

VALUES ('$col1', '$col2', '$col3',...

It won't hurt if the variables are blank.

If you want to skip the first line, you could do:

    $header_line = true;
    while (!feof($handle)) 
    {
        $buffer = fgets($handle, 4096);
        if($header_line) {
           $header_line = false;
           continue;
        }
        $values = explode(",", $buffer);
                if(count($values == 10))

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

sorry i put the wrong code in. it inserts properly & there is no error, BUT INSERTS A BLANK RECORD AFTER FINISHING INSERTING FROM THE CSV FILE.
I'LL TRY YOUR CODE TO SKIP THE FIRST LINE

it's not reading header, but inserting a blank record