Mysql tabel field is not updating in PHP

mehidy · September 30, 2017

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

Field is excelid datatype Varchar.

Below code updating the fiel 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),(,(9),(10),(11),(12)

$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 IS NOT 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);
                    }

ginerjm · September 30, 2017

Have no idea what you are trying to do. I"m sure English is not your language, but you really have to come across better.

mehidy · September 30, 2017

Have no idea what you are trying to do. I"m sure English is not your language, but you really have to come across better.

I'm trying to update my table field from array value. After inserting data, my hidden input (auto generate from php auto increment no#) will update the field "excelid" in MYSQL-DB. I'm not being able to update the multiple row of excelid from the array value. Hope you got my point.

ginerjm · September 30, 2017

No.

mehidy · September 30, 2017

Okay, thanks anyway.for response.

mac_gyver · September 30, 2017

your sql syntax is not correct. you are updating the column to be the string you are building. there is NOT a multi-value UPDATE query, through, you can use a multi-value INSERT ... ON DUPLICATE KEY UPDATE ...query to implement one, but that's likely not what you are trying to do.

when you UPDATE data, you would typically retrieve existing data, populate form fields with that data, then upon form submission, update the rows of data. each value should be tied to a specific row through an existing id. do you have such an id/value relationship that you can use to cause the correct row to be updated with the correct value? if not, you will need to somehow describe or show what you are trying to do. show what the initial data in a table looks like, and for the example $myarray of data, what result you want.

Edited September 30, 2017 by mac_gyver

benanamen · September 30, 2017

Fellow experts, if you were privy to the OP's posts on other forums you would realize this is an XY Problem.

mehidy · October 1, 2017

your sql syntax is not correct. you are updating the column to be the string you are building. there is NOT a multi-value UPDATE query, through, you can use a multi-value INSERT ... ON DUPLICATE KEY UPDATE ...query to implement one, but that's likely not what you are trying to do.

when you UPDATE data, you would typically retrieve existing data, populate form fields with that data, then upon form submission, update the rows of data. each value should be tied to a specific row through an existing id. do you have such an id/value relationship that you can use to cause the correct row to be updated with the correct value? if not, you will need to somehow describe or show what you are trying to do. show what the initial data in a table looks like, and for the example $myarray of data, what result you want.

Actually i am using this data as hidden in my form. When user upload the data, if upload/inserting data is successful then these value will be updated in NOT NULL row of excelid filed.Please help me to do the correct way of coding for this.

mehidy · October 1, 2017

your sql syntax is not correct. you are updating the column to be the string you are building. there is NOT a multi-value UPDATE query, through, you can use a multi-value INSERT ... ON DUPLICATE KEY UPDATE ...query to implement one, but that's likely not what you are trying to do.

when you UPDATE data, you would typically retrieve existing data, populate form fields with that data, then upon form submission, update the rows of data. each value should be tied to a specific row through an existing id. do you have such an id/value relationship that you can use to cause the correct row to be updated with the correct value? if not, you will need to somehow describe or show what you are trying to do. show what the initial data in a table looks like, and for the example $myarray of data, what result you want.

FINALLY, I DID IT AS BELOW

$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);
		}

mehidy · October 1, 2017

Here again, 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 <= 105; $i++) {
$formattedNumbers[] = sprintf('%03d', $i);
}

$array = $formattedNumbers;
$desiredLength = 60;
$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 ";
} 
else
{
echo "Sorry,Record not insert" . mysqli_error($conn);
}

mehidy · October 2, 2017

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();			

  ?>

Sign In

Mysql tabel field is not updating in PHP

Recommended Posts

mehidy

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

benanamen

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information