Jump to content

Mysql tabel field is not updating in PHP


mehidy

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);
                    }  
Link to comment
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.

Link to comment
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.

Link to comment
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. 

Link to comment
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);
		}		

Link to comment
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);
}	

Link to comment
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();
?>

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.