Mysql tabel field is not updating in PHP

PHP
1

Hi I’m using below code to update my table in DB.

Field is excelid datatype Varchar.

I’m getting the successful message, but actually its not update anything. Whats went wrong here?

  $myarray = array (1,2,3,4,5,6,7,8,9,10,11,12);
   foreach( $myarray as $value)
       {
    $values[] = '('.$value.')';
           }
		 	$string = implode( ',', $values );  
		$query="UPDATE freddyshipment SET excelid='$string' WHERE excelid='NULL'";
            $result=mysqli_query($conn,$query);
			   if($result){
					echo " Updated Successfully, Clik to go in <a href='index.php'>System</a><br />";										
						}    
							else
							{
					echo "Sorry,Record not updated" . mysqli_error($conn);
					}
2

For one thing, MySQL doesn’t do “= null”

https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

To test for NULL, use the IS NULL and IS NOT NULL operators
…
You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

2 Likes
3

Thanks dear, its work with IS NOT NULL.

But I got another issue, in the field data is updating as below.
in all row
(1),(2),(3
(1),(2),(3
(1),(2),(3

But I need as below,
1
2
3
4
.
.
.Whats is the problem?

echo $string; result is.
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

4

I’ll hazard the guess you’re not telling it to do what you’re wanting it to do.

Currently it’s something like
“for every row that has a NULL excelid field, SET that field value to the String (1),(2),(3),(4), …”

I’m guessing that you really want to UPDATE one row at a time sequentially SETting the excelid field to one of the array values.

Are the array values you’re using to populate the excelid field meaningful or arbitrary?

5

I want to update 12 row at a time , I think my code is not in the correct way. Is it possible to update same field(excelid)multiple row from the array value as mentioned above? If yes, please give me some hints.

6

It can be done, but I still don’t understand what you’re you’re wanting to do.

You want to assign the values 1 to 12 to the excelid field of 12 rows where that field is null.

But it doesn’t matter which row gets which excelid value other than it was a null?

It doesn’t make sense to me to assign values (for the most part) randomly and I can’t help but to feel that you are going about it wrong. But if that’s what you want to do, you could prepare the query, then in a loop bind the array values and execute the statement.

7

This is what I need to do, please show an query to do this. it will help me a lot.

8

FINALLY, I DID IT.

$start=1;
$end=13;		
  function array_chunk_greedy($arr, $count){
    $arr = array_chunk($arr, $count);
    if(($k = count($arr)-1) > 0){    
        if(count($arr[$k]) < $count){
            $arr[$k-1] = array_merge($arr[$k-1], $arr[$k]);
            unset($arr[$k]);
        }            
    }
    return $arr;    
}
$arr = range($start, $end);
$arr = array_chunk_greedy($arr, 1);
print_r($arr);
echo "</br>";
echo $totalarray = sizeof($arr);
for($i=0;$i<$totalarray;$i++) {

    $InsertArrayno = $arr[$i];
  $str=implode(",",$InsertArrayno);
  // $InsertFname = $fname[$i];
    //$InsertLname = $lname[$i];
$query="INSERT INTO freddyshipment (excelid) VALUES ('$str')";
$resultex = mysqli_query($conn,$query);
}
  if($resultex){
 echo " Row Record ";
			}    
			else
		{
		echo "Sorry,Record not insert" . mysqli_error($conn);
		}
1 Like
9

Dear Please see below code, while i run insert query of this script,then its working nicely insert data both 101 & 102 in my table as expecting.

But when I active update query, it only insert 102 in the table field, but it should update not null field with 101 & 102. What is the problem with insert query or in the coding?

Also its updating all the row, whether it is not null or having any data already.

$formattedNumbers = [];
for($i = 101; $i <= 102; $i++) {
   $formattedNumbers[] = sprintf('%03d', $i);
}

$array = $formattedNumbers;
$desiredLength = 24;
$newArray = array();

while(count($newArray) <= $desiredLength){
    $newArray = array_merge($newArray, $array);
}

$array = array_slice($newArray, 0, $desiredLength);

  sort($array);
	$arrlength = count($array);
	for($x = 0; $x < $arrlength; $x++) {
		print_r ($array[$x]);
	}
	echo "<br>";
echo $totalarray = sizeof($array);

for($i=0;$i<$totalarray;$i++) {

    $InsertArrayno = $array[$i];

//$query="INSERT INTO freddyshipment (excelid) VALUES ('$InsertArrayno')";

$query="UPDATE freddyshipment SET excelid='$InsertArrayno' WHERE excelid IS NOT NULL";
$resultex = mysqli_query($conn,$query);
}
  if($resultex){
 echo " Row Record ";
10

Dear Here below my full code,
It has 3 part-
a. insert data in DB table
b.update null value of field excelid (with dynamic id)
c. delete the empty row of field “qty”

how i process the code.
01.It will import data from excel file.
02.It will check excel file, is in order or not?
03.It will insert data row by row and some column as row. (most complicated part for me). in the quantity field.
04.I’m generating dynamic id by php array for field excelid in DB. It will help me to get the output for some other use.
05.it will update the field “excelid” in the table with the dynamic id.
06.Finally it will delete the empty row under field qty. there will be some row empty most of the time.

Below code is working nicely.

Just having one issue with deleting part, its always giving me success message, whether there is empty row or not? Even some time there is no empty row & not delete anything, but showing the message “Empty Row Record Deleted Successfully, Clik to go in System”.
What is wrong here? Please advise.

My last question, is it okay to insert data, update data & delete data with the one script, or have to do 3 process separately. This daily job & with many file, so I don’t want to do the three job separately for so many file. Please advise your opinion and comments.

<?php
		include 'db_connection.php';
		include 'reader.php';	

		if(isset($_POST["Import"])){ //01-import
        echo $filename=$_FILES["file"]["tmp_name"];		
	    
    		$file="$filename";
			$sheet = (isset($_POST['sheet'])) ? $_POST['sheet'] : '';
			$connection=new Spreadsheet_Excel_Reader();
			$connection->read($file);	

		echo"<table>";
			$x=1;
			while($x<=$connection->sheets[$sheet]['numRows']) {
      echo "\t<tr>\n";
			$y=1;
      while($y<=$connection->sheets[$sheet]['numCols']) {
        $cell = isset($connection->sheets[$sheet]['cells'][$x][$y]) ? $connection->sheets[$sheet]['cells'][$x][$y] : '';
        echo "\t\t<td>$cell</td>\n";  
        $y++;
			}  
      echo "\t</tr>\n";
      $x++;
		}
		echo "</table>";  
	$xx=$x-1;
	$excelrow=$xx-1;
	echo "Excel rows: ".$xx;
	echo "</br>";
	echo "Excel column: ".$y;
	echo "</br>";	
	echo "Excel data Row: ".$excelrow;
	echo "</br>";	
	echo "Total Size: 12";
	echo "</br>";
	echo "Total Record:".$excelrow*12;//12ttl-in-size
	echo "</br>";
		
		echo "</br>";		
		
			$startrow=1;
			$endrow=2;
	
			for($i=$startrow;$i<$endrow;$i++){ //02-excel title row/1st row
			$style1= $connection->sheets[$sheet]["cells"][1][1]."";		
			$order2= $connection->sheets[$sheet]["cells"][1][2]."";		
			$col3= $connection->sheets[$sheet]["cells"][1][3]."";					
			$size44= $connection->sheets[$sheet]["cells"][1][4]."";		
			$size56= $connection->sheets[$sheet]["cells"][1][5]."";		
			$size68= $connection->sheets[$sheet]["cells"][1][6]."";	
			$size710= $connection->sheets[$sheet]["cells"][1][7]."";	
			$size812= $connection->sheets[$sheet]["cells"][1][8]."";	
			$size914= $connection->sheets[$sheet]["cells"][1][9]."";	
			$size10xs= $connection->sheets[$sheet]["cells"][1][10]."";	
			$size11s= $connection->sheets[$sheet]["cells"][1][11]."";	
			$size12m= $connection->sheets[$sheet]["cells"][1][12]."";	
			$size13l= $connection->sheets[$sheet]["cells"][1][13]."";	
			$size14xl= $connection->sheets[$sheet]["cells"][1][14]."";	
			$size15xxl= $connection->sheets[$sheet]["cells"][1][15]."";	
			$ctnqty16= $connection->sheets[$sheet]["cells"][1][16]."";	
			$invoice17= $connection->sheets[$sheet]["cells"][1][17]."";	
			$kcgmt18= $connection->sheets[$sheet]["cells"][1][18]."";	
			$season19= $connection->sheets[$sheet]["cells"][1][19]."";	
			$buyer20= $connection->sheets[$sheet]["cells"][1][20]."";	
			$factory21= $connection->sheets[$sheet]["cells"][1][21]."";	
			
			echo ($style1);
			echo ($order2);
			echo ($col3);
			echo ($size44);
			echo ($size56);
			echo ($size68);
			echo ($size710);
			echo ($size812);
			echo ($size914);
			echo ($size10xs);
			echo ($size11s);
			echo ($size12m);
			echo ($size13l);
			echo ($size14xl);
			echo ($size15xxl);
			echo ($ctnqty16);
			echo ($invoice17);
			echo ($kcgmt18);
			echo ($season19);
			echo ($buyer20);
			echo ($factory21);
			echo "<br /> ";
			}//02-excel title row
			if ($size44 !== "4" 
				|| $size56 !=="6"
				|| $size68 !=="8"
				|| $size710 !=="10"
				|| $size812 !=="12"
				|| $size914 !=="14"
				|| $size10xs !=="XS"
				|| $size11s !=="S"
				|| $size12m !=="M"
				|| $size13l !=="L"
				|| $size14xl !=="XL"
				|| $size15xxl !=="XXL"
				|| $ctnqty16 !=="CTN-QTY"
				|| $invoice17 !=="INVOICE"
				|| $kcgmt18 !=="KCGMT"
				|| $season19 !=="SEASON"
				|| $buyer20 !=="BUYER"
				|| $factory21 !=="FACTORY") {	
             echo "Not mathhhng!<form><button formaction='input.php'>Back</button></form><form><button formaction='index.php'>Home</button></form>";
				}
				else{//03
				
	    $queryex = "SELECT excelid FROM freddyshipment ORDER BY excelid DESC LIMIT 1";
        $resultex = mysqli_query($conn,$queryex);
		$row = mysqli_fetch_array($resultex);
		echo "Last XL ID:".$dd=$row['excelid'];
		echo "</br>New XL ID:".$dc=$dd+1;
		echo "--".$de=$dd+$excelrow; echo "</br>";						
			
			$x=2;
			while($x<=$connection->sheets[$sheet]['numRows']) {//04		
		    $style = isset($connection->sheets[$sheet]['cells'][$x][1]) ? $connection->sheets[$sheet]['cells'][$x][1] : '';
			$order = isset($connection->sheets[$sheet]['cells'][$x][2]) ? $connection->sheets[$sheet]['cells'][$x][2] : '';
			$color = isset($connection->sheets[$sheet]['cells'][$x][3]) ? $connection->sheets[$sheet]['cells'][$x][3] : '';
			$s4s = isset($connection->sheets[$sheet]['cells'][$x][4]) ? $connection->sheets[$sheet]['cells'][$x][4] : '';
			$s6s = isset($connection->sheets[$sheet]['cells'][$x][5]) ? $connection->sheets[$sheet]['cells'][$x][5] : '';
			$s8s = isset($connection->sheets[$sheet]['cells'][$x][6]) ? $connection->sheets[$sheet]['cells'][$x][6] : '';
			$s10s = isset($connection->sheets[$sheet]['cells'][$x][7]) ? $connection->sheets[$sheet]['cells'][$x][7] : '';
			$s12s = isset($connection->sheets[$sheet]['cells'][$x][8])? $connection->sheets[$sheet]['cells'][$x][8] : '';
			$s14s = isset($connection->sheets[$sheet]['cells'][$x][9]) ? $connection->sheets[$sheet]['cells'][$x][9] : '';
			$sxss = isset($connection->sheets[$sheet]['cells'][$x][10]) ? $connection->sheets[$sheet]['cells'][$x][10] : '';
			$sss = isset($connection->sheets[$sheet]['cells'][$x][11]) ? $connection->sheets[$sheet]['cells'][$x][11] : '';
			$sms = isset($connection->sheets[$sheet]['cells'][$x][12]) ? $connection->sheets[$sheet]['cells'][$x][12] : '';
			$sls = isset($connection->sheets[$sheet]['cells'][$x][13]) ? $connection->sheets[$sheet]['cells'][$x][13] : '';
			$sxls = isset($connection->sheets[$sheet]['cells'][$x][14]) ? $connection->sheets[$sheet]['cells'][$x][14] : '';
			$sxxls = isset($connection->sheets[$sheet]['cells'][$x][15]) ? $connection->sheets[$sheet]['cells'][$x][15] : '';
			$ctnqty = isset($connection->sheets[$sheet]['cells'][$x][16]) ? $connection->sheets[$sheet]['cells'][$x][16] : '';
			$invoice = isset($connection->sheets[$sheet]['cells'][$x][17]) ? $connection->sheets[$sheet]['cells'][$x][17] : '';
			$kcgmt = isset($connection->sheets[$sheet]['cells'][$x][18]) ? $connection->sheets[$sheet]['cells'][$x][18] : '';
			$season = isset($connection->sheets[$sheet]['cells'][$x][19]) ? $connection->sheets[$sheet]['cells'][$x][19] : '';
			$buyer = isset($connection->sheets[$sheet]['cells'][$x][20]) ? $connection->sheets[$sheet]['cells'][$x][20] : '';
			$factory = isset($connection->sheets[$sheet]['cells'][$x][21]) ? $connection->sheets[$sheet]['cells'][$x][21] : '';

			$friendslist = "$s4s,$s6s,$s8s,$s10s,$s12s,$s14s,$sxss,$sss,$sms,$sls,$sxls,$sxxls";
			$friendarray = explode(",", $friendslist);
			//echo $ttlsize =count($friendarray);
			$frienduserarray = array();
			$sizelists ="4,6,8,10,12,14,XS,S,M,L,XL,XXL";
			//echo $totalsizer=count($sizelists);
		    $sizearray = explode(",",$sizelists);
			$sizeuserarray = array();		
			
			for ($n = 0; $n < count($friendarray) && $n < count($sizearray); $n++) {
			
			$friendidpush = "('".$style."','".$order."','".$color."','".$sizearray[$n]."','".$friendarray[$n]."','".$ctnqty."','".$invoice."','".$kcgmt."','".$season."','".$buyer."','".$factory."'),";
			//array_push($frienduserarray,$sizeuserarray,$exceliduserarray,$friendidpush);
			
			}
			$query = "INSERT INTO freddyshipment (style, orderno, col, sizes, qty, ctnqty, invoice, kcgmt, season, buyer, factory) VALUES ";
			$friendarray = explode(",", $friendslist);
			$sizearray = explode(",", $sizelists);
			foreach ($friendarray as $index => $s666s) {
				$s888s = $sizearray[$index];
				$query .= "('".$style."','".$order."','".$color."','".$s888s."','".$s666s."','".$ctnqty."','".$invoice."','".$kcgmt."','".$season."','".$buyer."','".$factory."'),";	
			} 
			$query = substr($query, 0, -1);
											
			$x++;
			$insert=mysqli_query($conn,$query);
			//if (mysqli_query($conn,$query)) 
			}//04-x2numrows	
			
			if (!$insert)
			        {
				 //echo $query;
                        echo "Data not saved,Clik to go in <a href='index.php'>System</a>" . mysqli_error($conn);		
		}//line188 insert success-data save in db
		elseif ($insert) 
		{
      		echo "Data saved in Database successfully,Clik to go in <a href='index.php'>System</a><br /> "; 					
			//new code start for data updating.
					
     $formattedNumbers = [];
     for($i = $dc; $i <= $de; $i++) {
    $formattedNumbers[] = sprintf($i);
	   // $formattedNumbers[] = sprintf('%03d', $i);
     }
     $array = $formattedNumbers;
     $ttlexlid3=$excelrow*12;//total no of excel data row & total size 5*12=60
     $desiredLength = $ttlexlid3;
     $newArray = array();
     // create a new array with AT LEAST the desired number of elements by joining the array at the end of the new array
     while(count($newArray) <= $desiredLength){
    $newArray = array_merge($newArray, $array);
     }
     $array = array_slice($newArray, 0, $desiredLength);
     sort($array);
	$arrlength = count($array);
	for($y = 0; $y < $arrlength; $y++) {
		print_r ($array[$y]);
	}
	echo "<br>";
    echo $totalarray = sizeof($array);
	
	$rowids = mysqli_query($conn,"SELECT id FROM freddyshipment WHERE excelid IS NULL ORDER BY id");
if  ($rowids) {//rowid
  // Loop over your array, and do one update per array elem
  foreach ($array as $cat) {
     $row = mysqli_fetch_assoc($rowids);
     $id = $row['id'];
     // Your values are ints, so no need to quote and escape.
     // If you do use other string values, be sure to mysql_real_escape_string() them and single-quote
     $upd = mysqli_query($conn,"UPDATE freddyshipment SET excelid = $cat WHERE id = $id");
            // Report error on this iteration
     if (!$upd){
		 echo "There is no null row OR". mysqli_error($conn);
		echo "$id";
		echo "</br>";
		     }//if-upd
	 elseif ($upd) {
		 echo "</br>";
		 echo "Update done";
		 
		 //new code start for blank or empty qty row deletion. 
		   $sql="DELETE FROM freddyshipment WHERE qty IN ('');";
             $result=mysqli_query($conn,$sql);
			   if(!$result){
				   echo "Error deleting record: " .mysqli_error($conn); 
								}    
							elseif($result)
							{
					echo " Empty Row Record Deleted Successfully, Clik to go in <a href='index.php'>System</a><br />";					
					}		 
				else
		{
		echo "Something terrible happen,with deleting the data.";
			}									
					
    }
  }//foreach
}//rowids 
else {
   echo "Update failed,couldn't get row ids. " . mysqli_error($conn); 
}//couldntget
	}//first insert data is success
	else
		{
		echo "Something terrible happen,while inserting data in db";
			}								
}//03-after matching excelifile tiltle.			
		
}//01-import		

$conn->close();			
  ?>
11

All you do is check that the query executed without any problems, you don’t check whether or not any rows were deleted.

12

Finally get it done, what I want. With bellow. All 3 section working nicely, but please advise on below points,

is it okay to insert data, update data & delete data with the one script, or have to do 3 process separately?

This daily job & with many file, so I don’t want to do the three job separately for so many file. Please advise your opinion and comments.

<?php
		include 'db_connection.php';
		include 'reader.php';	

		if(isset($_POST["Import"])){ //01-import
        echo $filename=$_FILES["file"]["tmp_name"];		
	    
    		$file="$filename";
			$sheet = (isset($_POST['sheet'])) ? $_POST['sheet'] : '';
			$connection=new Spreadsheet_Excel_Reader();
			$connection->read($file);	

		echo"<table>";
			$x=1;
			while($x<=$connection->sheets[$sheet]['numRows']) {
      echo "\t<tr>\n";
			$y=1;
      while($y<=$connection->sheets[$sheet]['numCols']) {
        $cell = isset($connection->sheets[$sheet]['cells'][$x][$y]) ? $connection->sheets[$sheet]['cells'][$x][$y] : '';
        echo "\t\t<td>$cell</td>\n";  
        $y++;
			}  
      echo "\t</tr>\n";
      $x++;
		}
		echo "</table>";  
	$xx=$x-1;
	$excelrow=$xx-1;
	echo "Excel rows: ".$xx;
	echo "</br>";
	echo "Excel column: ".$y;
	echo "</br>";	
	echo "Excel data Row: ".$excelrow;
	echo "</br>";	
	echo "Total Size: 12";
	echo "</br>";
	echo "Total Record:".$excelrow*12;//12ttl-in-size
	echo "</br>";
		
		echo "</br>";		
		
			$startrow=1;
			$endrow=2;
	
			for($i=$startrow;$i<$endrow;$i++){ //02-excel title row/1st row
			$style1= $connection->sheets[$sheet]["cells"][1][1]."";		
			$order2= $connection->sheets[$sheet]["cells"][1][2]."";		
			$col3= $connection->sheets[$sheet]["cells"][1][3]."";					
			$size44= $connection->sheets[$sheet]["cells"][1][4]."";		
			$size56= $connection->sheets[$sheet]["cells"][1][5]."";		
			$size68= $connection->sheets[$sheet]["cells"][1][6]."";	
			$size710= $connection->sheets[$sheet]["cells"][1][7]."";	
			$size812= $connection->sheets[$sheet]["cells"][1][8]."";	
			$size914= $connection->sheets[$sheet]["cells"][1][9]."";	
			$size10xs= $connection->sheets[$sheet]["cells"][1][10]."";	
			$size11s= $connection->sheets[$sheet]["cells"][1][11]."";	
			$size12m= $connection->sheets[$sheet]["cells"][1][12]."";	
			$size13l= $connection->sheets[$sheet]["cells"][1][13]."";	
			$size14xl= $connection->sheets[$sheet]["cells"][1][14]."";	
			$size15xxl= $connection->sheets[$sheet]["cells"][1][15]."";	
			$ctnqty16= $connection->sheets[$sheet]["cells"][1][16]."";	
			$invoice17= $connection->sheets[$sheet]["cells"][1][17]."";	
			$kcgmt18= $connection->sheets[$sheet]["cells"][1][18]."";	
			$season19= $connection->sheets[$sheet]["cells"][1][19]."";	
			$buyer20= $connection->sheets[$sheet]["cells"][1][20]."";	
			$factory21= $connection->sheets[$sheet]["cells"][1][21]."";	
			
			echo ($style1);
			echo ($order2);
			echo ($col3);
			echo ($size44);
			echo ($size56);
			echo ($size68);
			echo ($size710);
			echo ($size812);
			echo ($size914);
			echo ($size10xs);
			echo ($size11s);
			echo ($size12m);
			echo ($size13l);
			echo ($size14xl);
			echo ($size15xxl);
			echo ($ctnqty16);
			echo ($invoice17);
			echo ($kcgmt18);
			echo ($season19);
			echo ($buyer20);
			echo ($factory21);
			echo "<br /> ";
			}//02-excel title row
			if ($size44 !== "4" 
				|| $size56 !=="6"
				|| $size68 !=="8"
				|| $size710 !=="10"
				|| $size812 !=="12"
				|| $size914 !=="14"
				|| $size10xs !=="XS"
				|| $size11s !=="S"
				|| $size12m !=="M"
				|| $size13l !=="L"
				|| $size14xl !=="XL"
				|| $size15xxl !=="XXL"
				|| $ctnqty16 !=="CTN-QTY"
				|| $invoice17 !=="INVOICE"
				|| $kcgmt18 !=="KCGMT"
				|| $season19 !=="SEASON"
				|| $buyer20 !=="BUYER"
				|| $factory21 !=="FACTORY") {	
             echo "Not mathhhng!<form><button formaction='input.php'>Back</button></form><form><button formaction='index.php'>Home</button></form>";
				}
				else{//03
				
	    $queryex = "SELECT excelid FROM freddyshipment ORDER BY excelid DESC LIMIT 1";
        $resultex = mysqli_query($conn,$queryex);
		$row = mysqli_fetch_array($resultex);
		echo "Last XL ID:".$dd=$row['excelid'];
		echo "</br>New XL ID:".$dc=$dd+1;
		echo "--".$de=$dd+$excelrow; echo "</br>";						
			
			$x=2;
			while($x<=$connection->sheets[$sheet]['numRows']) {//04		
		    $style = isset($connection->sheets[$sheet]['cells'][$x][1]) ? $connection->sheets[$sheet]['cells'][$x][1] : '';
			$order = isset($connection->sheets[$sheet]['cells'][$x][2]) ? $connection->sheets[$sheet]['cells'][$x][2] : '';
			$color = isset($connection->sheets[$sheet]['cells'][$x][3]) ? $connection->sheets[$sheet]['cells'][$x][3] : '';
			$s4s = isset($connection->sheets[$sheet]['cells'][$x][4]) ? $connection->sheets[$sheet]['cells'][$x][4] : '';
			$s6s = isset($connection->sheets[$sheet]['cells'][$x][5]) ? $connection->sheets[$sheet]['cells'][$x][5] : '';
			$s8s = isset($connection->sheets[$sheet]['cells'][$x][6]) ? $connection->sheets[$sheet]['cells'][$x][6] : '';
			$s10s = isset($connection->sheets[$sheet]['cells'][$x][7]) ? $connection->sheets[$sheet]['cells'][$x][7] : '';
			$s12s = isset($connection->sheets[$sheet]['cells'][$x][8])? $connection->sheets[$sheet]['cells'][$x][8] : '';
			$s14s = isset($connection->sheets[$sheet]['cells'][$x][9]) ? $connection->sheets[$sheet]['cells'][$x][9] : '';
			$sxss = isset($connection->sheets[$sheet]['cells'][$x][10]) ? $connection->sheets[$sheet]['cells'][$x][10] : '';
			$sss = isset($connection->sheets[$sheet]['cells'][$x][11]) ? $connection->sheets[$sheet]['cells'][$x][11] : '';
			$sms = isset($connection->sheets[$sheet]['cells'][$x][12]) ? $connection->sheets[$sheet]['cells'][$x][12] : '';
			$sls = isset($connection->sheets[$sheet]['cells'][$x][13]) ? $connection->sheets[$sheet]['cells'][$x][13] : '';
			$sxls = isset($connection->sheets[$sheet]['cells'][$x][14]) ? $connection->sheets[$sheet]['cells'][$x][14] : '';
			$sxxls = isset($connection->sheets[$sheet]['cells'][$x][15]) ? $connection->sheets[$sheet]['cells'][$x][15] : '';
			$ctnqty = isset($connection->sheets[$sheet]['cells'][$x][16]) ? $connection->sheets[$sheet]['cells'][$x][16] : '';
			$invoice = isset($connection->sheets[$sheet]['cells'][$x][17]) ? $connection->sheets[$sheet]['cells'][$x][17] : '';
			$kcgmt = isset($connection->sheets[$sheet]['cells'][$x][18]) ? $connection->sheets[$sheet]['cells'][$x][18] : '';
			$season = isset($connection->sheets[$sheet]['cells'][$x][19]) ? $connection->sheets[$sheet]['cells'][$x][19] : '';
			$buyer = isset($connection->sheets[$sheet]['cells'][$x][20]) ? $connection->sheets[$sheet]['cells'][$x][20] : '';
			$factory = isset($connection->sheets[$sheet]['cells'][$x][21]) ? $connection->sheets[$sheet]['cells'][$x][21] : '';

			$friendslist = "$s4s,$s6s,$s8s,$s10s,$s12s,$s14s,$sxss,$sss,$sms,$sls,$sxls,$sxxls";
			$friendarray = explode(",", $friendslist);
			//echo $ttlsize =count($friendarray);
			$frienduserarray = array();
			$sizelists ="4,6,8,10,12,14,XS,S,M,L,XL,XXL";
			//echo $totalsizer=count($sizelists);
		    $sizearray = explode(",",$sizelists);
			$sizeuserarray = array();		
			
			for ($n = 0; $n < count($friendarray) && $n < count($sizearray); $n++) {
			
			$friendidpush = "('".$style."','".$order."','".$color."','".$sizearray[$n]."','".$friendarray[$n]."','".$ctnqty."','".$invoice."','".$kcgmt."','".$season."','".$buyer."','".$factory."'),";
			//array_push($frienduserarray,$sizeuserarray,$exceliduserarray,$friendidpush);
			
			}
			$query = "INSERT INTO freddyshipment (style, orderno, col, sizes, qty, ctnqty, invoice, kcgmt, season, buyer, factory) VALUES ";
			$friendarray = explode(",", $friendslist);
			$sizearray = explode(",", $sizelists);
			foreach ($friendarray as $index => $s666s) {
				$s888s = $sizearray[$index];
				$query .= "('".$style."','".$order."','".$color."','".$s888s."','".$s666s."','".$ctnqty."','".$invoice."','".$kcgmt."','".$season."','".$buyer."','".$factory."'),";	
			} 
			$query = substr($query, 0, -1);
											
			$x++;
			$insert=mysqli_query($conn,$query);
			//if (mysqli_query($conn,$query)) 
			}//04-x2numrows	
			
			if (!$insert)
			        {
				 //echo $query;
                        echo "Data not saved,Clik to go in <a href='index.php'>System</a>" . mysqli_error($conn);		
		}//line188 insert success-data save in db
		elseif ($insert) 
		{
      		echo "Data saved in Database successfully,Clik to go in <a href='index.php'>System</a><br /> "; 					
			//new code start for data updating.
					
     $formattedNumbers = [];
     for($i = $dc; $i <= $de; $i++) {
    $formattedNumbers[] = sprintf($i);
	   // $formattedNumbers[] = sprintf('%03d', $i);
     }
     $array = $formattedNumbers;
     $ttlexlid3=$excelrow*12;//total no of excel data row & total size 5*12=60
     $desiredLength = $ttlexlid3;
     $newArray = array();
     // create a new array with AT LEAST the desired number of elements by joining the array at the end of the new array
     while(count($newArray) <= $desiredLength){
    $newArray = array_merge($newArray, $array);
     }
     $array = array_slice($newArray, 0, $desiredLength);
     sort($array);
	$arrlength = count($array);
	for($y = 0; $y < $arrlength; $y++) {
		print_r ($array[$y]);
	}
	echo "<br>";
    echo $totalarray = sizeof($array);
	echo "<br>";
	$rowids = mysqli_query($conn,"SELECT id FROM freddyshipment WHERE excelid IS NULL ORDER BY id");
if  ($rowids) {//rowid
  // Loop over your array, and do one update per array elem
  foreach ($array as $cat) {
     $row = mysqli_fetch_assoc($rowids);
     $id = $row['id'];
     // Your values are ints, so no need to quote and escape.
     // If you do use other string values, be sure to mysql_real_escape_string() them and single-quote
     $upd = mysqli_query($conn,"UPDATE freddyshipment SET excelid = $cat WHERE id = $id");
            // Report error on this iteration
     if (!$upd){
		 echo "There is no null row OR". mysqli_error($conn);
		echo "$id";
		echo "</br>";
		     }//if-upd
	 elseif ($upd) {
		
		 echo "Update done successfully";
	 }
	 else {
   echo "Update failed,couldn't get row ids. " . mysqli_error($conn); 
}//couldntget

  }//foreach
}//rowids 


		 //new code start for blank or empty qty row deletion. 
	$searchempty = mysqli_query($conn,"SELECT id FROM freddyshipment WHERE qty='' ORDER BY id");
	$found=0;
     while($row = mysqli_fetch_array($searchempty)){
    //$found=1;
	//echo $found;
	echo "</br>";
	echo $found = $row['id'];
		$sql="DELETE FROM freddyshipment WHERE qty IN ('');";
             $result=mysqli_query($conn,$sql);
	if($result){
		echo " Empty Row Record Deleted Successfully, Clik to go in <a href='index.php'>System</a><br />";					
				}
							
    }//end while loop
    if ($found==0) {
		echo "</br>";
    echo "No empty record found to delete";
     }
	}//first insert data is success
	else
		{
		echo "Something terrible happen,while inserting data in db";
			}								
}//03-after matching excelifile tiltle.			
		
}//01-import		

$conn->close();			
  ?>
13

I would say it’s completely your choice - if the three jobs are always to be done in the same sequence and together, then it makes no sense to make the user do the jobs in separate scripts. You might choose to organise the code into separate files and include them into a “master” script, just to make it a bit easier to read, but if stuff in the second section relies on stuff from the first, then that might not make things better.

I’m a bit confused about the first loop, that starts with

for($i=$startrow;$i<$endrow;$i++){ //02-excel title row/1st row

You’re obviously just looping for a single row given the values of $startrow and $endrow, but you don’t use the index variable at all inside the loop, and as it only has one iteration, I wonder why you don’t just remove the loop.

Another thing to consider while you are inserting data into your table as you loop through the spreadsheet rows - if one of them fails, should you then go back and remove the previous inserts? For example if it has ten rows, you insert the first eight but then the ninth fails, what about the first eight that you already inserted?

14

I didn’t think about it. I have no idea how to check this & add code in the script to do this function. how will I know that the ninth row/or any row has been failed to insert data in the table?

I have remove the first loop , as you advise.

15

There are several things you could do, and I’m not experienced enough to suggest which is “best”, if indeed any one is better than another.

First, you could run the loop through each row and do some testing of the data, to make sure that it’s valid, if you can do such a test. In a similar way to how you check that the column headings are correct, are there some value checks you can do on the data itself? If there are, then you could do that before you actually start storing the data.

You can check to see how many rows were affected by the last query that you ran. So if your INSERT queries should always add a row, then you can check whether or not that happened: http://php.net/manual/en/mysqli.affected-rows.php

I have also read about “transactions” in MySQL - you mark the start of a transaction, run your queries, and mark the end of the transaction, but the data is not committed until you mark the end of the transaction. On the face of it this sounds ideal, as you’d just wrap the whole thing in a transaction which wouldn’t get ended until all your queries have executed correctly. Any of your error checking code would just result in the effects being rolled back. You’d need to read up more on that, though.

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.