[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

From csv file insert into MySQL

i have a code that lets user uplaod a csv & then insert the values from csv into 2 tables in the MySql DB. it works fine if the student in the csv table are unique & doesn't already exist in the DB.
BUT i want it to work in the following way:
1. if there are student code with the same number (eg 2000), then get only the unique ones from csv before storing into the student_info table
2. if the student code (eg 2000) specified in the csv file already exists in the DB, then be able to match it by retrieving student codes with the unique ones from csv file, & if matched then skip insertion into student_info table & only insert in sitting_data table. But if doesn't match then code will also be inserted in sitting_data table
Is it possible? Can someone please help?
0
ARC_UM
Asked:
ARC_UM
  • 7
  • 4
  • 2
1 Solution
 
mstrelanCommented:
First do an sql select query of student codes and store them in array.

Then foreach record in the csv call php's in_array function to see if the code exists in the array. if yes insert in sitting_data if no insert in student_info and sitting_data
0
 
shobinsunCommented:
HI,

Use this idea:

Hope this will help you.

Regards
		$handle = fopen($csvfile, "r");
 
		while (($data = fgetcsv($handle, 1000)) !== false)
 
		{
 
			$code = $data[0];
 
			
 
			$query = 'SELECT student_code FROM student_info';
 
			$result = mysql_query($query);
 
			while($row = mysql_fetch_array($result))
 
			{
 
				if ($row['student_code']==$code)
 
				{
 
					$query = "INSERT INTO sitting_data_table () VALUES('')";
 
					mysql_query($query);
 
				}
 
				else
 
				{
 
					$query = "INSERT INTO student_info () VALUES('')";
 
					mysql_query($query);
					$query1 = "INSERT INTO sitting_data_table () VALUES('')";
 
					mysql_query($query1);
 
				}
 
			}
 
			mysql_free_result($result);
 
		}
 
		fclose($handle);

Open in new window

0
 
mstrelanCommented:
shobinsun's solution is a good start but i wouldn't recommend putting $query = 'SELECT student_code FROM student_info'; inside the while loop. Do it first like my in_array suggestion OR change it to 'SELECT student_code FROM student_info WHERE student_code = $studentCode'.

The in_array method means  for every record you have to scan through the array, the other method means you need to perform lots of sql queries. decide which one is more efficient.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ARC_UMAuthor Commented:
I didn't had a chance to see your suggestion since i was away. will try & let you know how it goes. Thanks
0
 
ARC_UMAuthor Commented:
how to read using in_array method? I had also read from file by the way shobinsun has shown:
$code = $data[0];
i have read &  assigned all my column fields in this way
0
 
ARC_UMAuthor Commented:
hi shobinsun,
how can enter uniques student codes in the table if there are student code with the same number (eg 2000), beacause student table can have only uniques student id's? See the attached csv template.
template.xls
0
 
ARC_UMAuthor Commented:
don't think it is checking
 if ($row['student_code']==$code)
correctly with the ones entered with that of in the existing table. any suggestions?
0
 
shobinsunCommented:
Hi,

Use this Idea:

                fclose($handle);
 
 
                $handle = fopen($csvfile, "r");
 
                while (($data = fgetcsv($handle, 1000)) !== false)
 
                {
 
                        $code = $data[0];
 
                        
 
                        $query = "SELECT * FROM student_info where student_code='$code'";
 
                        $result = mysql_query($query);
 
			$count=mysql_num_rows($result);
 
                                if($count!=0)
 
                                {
 
                                        $query = "INSERT INTO sitting_data_table () VALUES('')";
 
                                        mysql_query($query);
 
                                }
 
                                else
 
                                {
 
                                        $query = "INSERT INTO student_info () VALUES('')";
 
                                        mysql_query($query);
                                        $query1 = "INSERT INTO sitting_data_table () VALUES('')";
 
                                        mysql_query($query1);
 
                                }
 
 
 
                }
 
                fclose($handle);

Open in new window

0
 
ARC_UMAuthor Commented:
why is it not being able to compare $row['student_code']==$code ? is there a syntax error?
0
 
ARC_UMAuthor Commented:
doesn't enter the record in student table that doesn't exist. only enters all records in sitting table
0
 
shobinsunCommented:
Hi,

Check the data with this:

 echo "CODE:",$code;
$query = "SELECT name FROM pdf1 where code='$code'";
 $result = mysql_query($query);
 $row = mysql_fetch_array($result);
echo "<br>:", $row['name'];
0
 
ARC_UMAuthor Commented:
my select query gets only the 1st record that is 2001 student id form the DB. is there anything wrong i am doing wrong? have a look at my code

echo "CODE:",$col2;
 
               	$query = "SELECT `STUDENT_CODE` FROM `$_SESSION[student_info]`";			 
                $results = mysql_query($query);
                		
		$row = mysql_fetch_array($results); 
		echo "<br>:Table", $row['STUDENT_CODE']; 
while($row)
{
if($row['STUDENT_CODE']==$col2)
{
$sql = "INSERT INTO sitting_table................

Open in new window

0
 
shobinsunCommented:
Hi,

No problem with the following code. use it :


                $handle = fopen($csvfile, "r");
 
                while (($data = fgetcsv($handle, 1000)) !== false)
 
                {
 
                        $code = $data[0];
 
                        
 
                        $query = "SELECT * FROM student_info";
 
                        $result = mysql_query($query) or die(mysql_error());
 
 			while($row = mysql_fetch_array($result)){
			
 
                                if($row['student_code']==$code)
 
                                {
 
                                        $query = "INSERT INTO sitting_data_table () VALUES('')";
 
                                        mysql_query($query);
 
                                }
 
                                else
 
                                {
 
                                        $query = "INSERT INTO student_info () VALUES('')";
 
                                        mysql_query($query);
                                        $query1 = "INSERT INTO sitting_data_table () VALUES('')";
 
                                        mysql_query($query1);
 
                                }
 
 
 
                }
 
                fclose($handle);

Open in new window

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now