Jump to content
mehidy

Mysql tabel field is not updating in PHP

Recommended Posts

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),(8),(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);
                    }  

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by mac_gyver

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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();
?>

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.