March 10, 201412 yr Hi, Can anyone tell me: I have to upload the excel file and put all its data in database, but the condition is thatif 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 . Itworks fine with the existing data i.e It Updates all the existing . Butit is not inserting the new data. Please check the code from :elseif($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 March 10, 201412 yr by JohnF
March 10, 201412 yr 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. Itupdates the records perfectly. I Want to inser the new records from sameexcel file to the database
March 10, 201412 yr 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
March 10, 201412 yr Author Yup... I did it. But there is no error.. there is some logical flaw... i am unable to trace it out Edited March 10, 201412 yr by JohnF
March 11, 201412 yr Author When i changed $rollno!=$rows['RollNo'] to 0 != 1 it does enter theif 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
March 12, 201412 yr 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
March 13, 201412 yr 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.
April 4, 201412 yr 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
April 22, 201412 yr <?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');}elseecho 'Invalid File:Please Upload CSV File';}?>
Create an account or sign in to comment