How to Import Excel Data To MySQL Using PHP

Question

Can anyone tell me:

I have to upload the excel file and put all its data in database, but the condition is that if any of the records are existing already in the database i have to fire an update query. else fire an insert query for new records.

I am comparing the Roll No from database with Roll No from excel . It works fine with the existing data i.e It Updates all the existing . But it is not inserting the new data. Please check the code from :else if($num_rows>0)

Please Help Me.

Below is the code:

if($_FILES['excelFile']['name']!="")
{
    $fileName=uploadFile($_FILES['excelFile'],array(".xls",".xlsx"),"excel_file");
    $data = new Spreadsheet_Excel_Reader();
    $data->read('excel_file/'.$fileName);
    $ans=mysql_query("SELECT * FROM StudentData");
    $num_rows = mysql_num_rows($ans);
    for($i=1;$i<=$data->sheets[0]['numRows'];$i++)
    {
        $rollno=$data->sheets[0]['cells'][$i][1];
        $firstname=$data->sheets[0]['cells'][$i][2];
        $lastname=$data->sheets[0]['cells'][$i][3];
        $mobile=$data->sheets[0]['cells'][$i][4];
        $city=$data->sheets[0]['cells'][$i][5];

        if($num_rows<=0)
        {
            echo('Inserting : '.$rollno);
            $query="INSERT INTO StudentData(RollNo,FirstName,LastName,MobileNo,City)VALUES('".$rollno."','".$firstname."','".$lastname."','".$mobile."','".$city."')";
            mysql_query($query);
        }
        else if($num_rows>0)
        {
            while($rows=mysql_fetch_array($ans))
            {
                if($rollno!=$rows['RollNo'])
                {
                    echo('<p style="color:green">Inserting : '.$rollno.'</p>');
                    $query="INSERT INTO StudentData(RollNo,FirstName,LastName,MobileNo,City)VALUES('".$rollno."','".$firstname."','".$lastname."','".$mobile."','".$city."')";
                    mysql_query($query);
                    mysql_error();
                    break;
                }
                else
                {
                    echo('<p style="color:red">Updating Roll:'.$rollno.'and DBR:'.$rows['RollNo'].'</p>');
                    $query="UPDATE StudentData SET FirstName='".$firstname."',LastName='".$lastname."',MobileNo='".$mobile."',City='".$city."' WHERE RollNo='".$rollno."'";
                    mysql_query($query);
                    break;
                }

            }   
        }
    }

}

What's the issue ? You can also use INSERT ... ON DUPLICATE KEY — Abdul Manaf
– Abdul Manaf, Commented Mar 10, 2014 at 9:49
I am comparing the Roll No from database with Roll No from excel . It works fine with the existing data i.e It Updates all the existing . But it is not inserting the new data. Please check the code from :else if($num_rows>0) — Darshan
– Darshan, Commented Mar 10, 2014 at 9:56

Darshan · Accepted Answer · 2014-03-13 12:01:53Z

1

I tried putting all the existing roll's in an array!

So I made an array from existing rollno

while($rows=mysql_fetch_array($ans))
{
 $existing_rollno[]=$rows['RollNo'];
}

Then I used this php function:-

if (in_array($rollno, $existing_rollno))

and then it worked as i wanted.

answered Mar 13, 2014 at 12:01

Darshan

391 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Murad · Accepted Answer · 2014-03-10 10:57:04Z

0

Does the insert statement work when num_ros <=0 ? If it doesnt then your insert statement isn't working. Plus, I am not sure but don't you need a space before VALUES.

answered Mar 10, 2014 at 10:57

Murad

3831 silver badge7 bronze badges

1 Comment

Darshan Over a year ago

INSERT STATEMENT WORKS When num_rows<=0...BUT doesnt work in the else if block below that.

Murad · Accepted Answer · 2014-03-10 20:29:11Z

0

Ok. So your insert statement works no problem. Now check this. You have got echo after the statement if ($rollno!=$rows['RollNo']) Does it print anything at all. If it doesnt then your if statement is wrong. Try !== instead of !=

answered Mar 10, 2014 at 20:29

Murad

3831 silver badge7 bronze badges

8 Comments

Darshan Over a year ago

I tried using !== also. It is not entering the if($rollno!==$rows['RollNO']) block. It goes in the else block everytime.

Murad Over a year ago

$rollno!=$rows['RollNo'] delete this bit and put 0 != 1 instead and see if it enters the block this time. And check what it echos. If it does enter it means your rollno and rows['RollNo'] are always the same.

Darshan Over a year ago

Yes it is as you said... But i am confused that how do i check for the existing roll no and new roll no? existing rollno: update new roll no:insert HOW?....If you can tell e Please

Murad Over a year ago

intval( $rollno) != intval( $rows['RollNo']) try this and lets see

Murad Over a year ago

Try printing rollno and rows[`RollNo'] do they match to the values in the spreadsheet and the database. If i was you i wd create a small spreadsheet and a small database lets say upto row 4-5. And make the first rows different.

|

Collectives™ on Stack Overflow

How to Import Excel Data To MySQL Using PHP

3 Answers 3

Comments

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related