Skip to content
View in the app

A better way to browse. Learn more.

Web Designer Forum

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

How to Import Excel Data To MySQL Using PHP

Featured Replies

Hi, 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;
                }

            }   
        }
    }

} 

Edited by JohnF

  • Author

i am not geting errors while executing... but the code is going wrong somewhere...


It is not inserting the new records from the same excel file. It
updates the records perfectly. I Want to inser the new records from same
excel file to the database




at the end of your script add this

 

echo mysql_error();

 

then re-run the script this will tell you if there is an SQL error any where

  • Author

Yup... I did it. But there is no error.. there is some logical flaw... i am unable to trace it out

Edited by JohnF

  • Author

When i changed $rollno!=$rows['RollNo'] to 0 != 1 it does enter the
if block it means my rollno and rows['RollNo'] are always the same.
But i am confused that how do i check for the existing roll no and new roll no?

 

 

if existing rollno:- update query

 

if new roll no:- insert query

 

 

HOW?....If anybody can tell.... Please

  • Author

you could try putting all the existing roll's in an array!

So you can make array from existing "rollno"

 

Then you can use this php function if (in_array($rollno, $rollarray)) etc...

http://uk1.php.net/in_array

Thanks a lot...Your idea worked..according to my requirement. :good:

  • 3 weeks later...

You can use mysql on duplicate key update syntax:

 

INSERT INTO StudentData

SET

RollNo = $RollNo

, FirstName=$firstname

, LastName=$lastname

, MobileNo=$mobile

ON DUPLICATE KEY UPDATE

FirstName=$firstname

, LastName=$lastname

, MobileNo=$mobile

  • 3 weeks later...

<?php
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
$host='localhost'; // Host Name.
$db_user= 'root'; //User Name
$db_password= '';
$db= 'product_record'; // Database Name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
mysql_query($sql);
}
fclose($file);
echo 'CSV File has been successfully Imported';
header('Location: index.php');
}
else
echo 'Invalid File:Please Upload CSV File';
}
?>

Create an account or sign in to comment

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.