Two array with different length in Php & Mysql

[mehidy]

Is it possible to insert two array value which is holding different length than each other, with one mysql query in php?

Like as,

 

$a=array (1,2,3,4,5);

$b=array (1,2,3,4,5,6,7,8,9,10) ;

 

Array $a, will insert twice in its field-1,2,3,4,5,1,2,3,4,5

And array $b, will insert once in its field-1,2,3,4,5,6,7,8,9,10 

 

If yes, what will be the query look like?

 

Or, should I insert array $a first in its field & then update array $b in its filed,with some condition?

 

Right now I'm updating array $b, after inserting array $a.

 

Please advise.

[ginerjm]

Stop right now.   You are apparently trying to save a comma-separated string in a table column.  No.  That is not how to build a proper database table.  One never combines multiple values into a single field, which your post seems to be demonstrating.

[mehidy]

Stop right now.   You are apparently trying to save a comma-separated string in a table column.  No.  That is not how to build a proper database table.  One never combines multiple values into a single field, which your post seems to be demonstrating.

Ok, thanks for your comments.

[cyberRobot]

Could you provide more information on what you're trying to do? What is purpose behind the code? Will the arrays always be either 5 or 10 elements?

 

Also what does your code currently look like? What have you tried?

[mehidy]

Could you provide more information on what you're trying to do? What is purpose behind the code? Will the arrays always be either 5 or 10 elements?

 

Also what does your code currently look like? What have you tried?

On this script currently I'm updating the excelid field, after inserting data. I wanted to avoid the update query by inserting excelid id with the first query.

 

input data from excel file

https://ibb.co/iaRUrG

 

Column 4,5,----xl,xxl are inserting under field sizes & qty. for this portion for each row of excel i need to add a excelid(whic i'm generating dynamically in my code) in excelid field to retrieve data later on.Hope you got my point.

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

		include 'db_connection.php';
		include 'reader.php';	

		if(isset($_POST["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,$order2,$col3,$size44,$size56,$size68,$size710,$size812,$size914,$size10xs,$size11s,$size12m,$size13l,$size14xl,$size15xxl,$ctnqty16,$invoice17,$kcgmt18,$season19,$buyer20,$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 id 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";
			$sizelists ="4,6,8,10,12,14,XS,S,M,L,XL,XXL";
			
	$insert = $conn->prepare('
	INSERT INTO freddyshipment (
		style, orderno, col, sizes,
		qty, ctnqty, invoice, kcgmt, 
		season, buyer, factory
	) VALUES (
		?, ?, ?, ?,
		?, ?, ?, ?,
		?, ?, ?
	)
   ');
    $insert->bind_param(
	'sssssssssss',
	$style, $order, $color, $s888s,
	$s666s, $ctnqty, $invoice, $kcgmt,
	$season, $buyer, $factory
    );
		
    $friendarray = explode(",", $friendslist);
	$sizearray = explode(",", $sizelists); 	
	for ($w = 0; $w < count($friendarray) && $w < count($sizearray); ++$w) {
    $s666s = $friendarray[$w];
    $s888s = $sizearray[$w];
	$insert->execute();
     }				
	$x++;
	
	}//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('%010d', $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'];

     $upd = mysqli_query($conn,"UPDATE freddyshipment SET excelid = $cat WHERE id = $id");
      
     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();			
  ?>

[ginerjm]

It sounds like your point is to build an RDBMS table like an excel spreadsheet.

 

This is not Excel.  And mimicing the look of a spreadsheet is NOT making a valid database table.

[mac_gyver]

this is why you don't write any code until you have defined most of what you are trying to accomplish.

 

we still don't know what goal you are trying to accomplish. i think you are trying to -

 

1) insert the unique order information into an `orders` table, with an auto-increment id column to produce the id.

 

2) get the last insert id from the above query.

 

3) insert the order details (item id/size and quantity) into an order_items table, one row per item, using the last insert id from above to relate the row(s) back to the order they belong to. is suspect that the 'style' is either an item id or an item name. you would only insert rows for data that exists, so, there will only be rows where the quantity is > 0.

 

 

some notes about what your current code is trying to do -

 

1) don't get the last/highest id and increment it yourself in code. when there are concurrent visitors trying to do the same thing, they will get the same value and try to use it, resulting in cross-linked data. use an auto-increment column in the table to produce id's.

 

2) freddyshipment - don't have separate tables for same meaning data and don't use data values as part of table names. if this data is for orders and order_details, that is what the tables should be named.

 

3) the buyer column should hold a buyer_id, not the buyer's name. you should have a `buyer` table that holds the unique buyer information and assigns buyer_id's via an auto-increment column. if you want to insert a new buyer name and get the new id for that buyer or get the existing id if the buyer is already in the table, you can use an INSERT ... ON DUPLICATE KEY UPDATE query.

 

4) the unique order information should include a date that the order was made or if that information doesn't exist, the date the order was entered into the database.

[mac_gyver]

the following is the logic needed to accomplish what i think you are (should be) trying to do - 

// produce, prepare, and bind input parameters for the queries
$query = "
    INSERT INTO orders (
        style, orderno, col, ctnqty,
        invoice, kcgmt, season, buyer,
        factory
    ) VALUES (
        ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?
    )";

$stmt_order = $conn->prepare($query);
$stmt_order->bind_param(
        'sssssssss',
        $style, $order, $color, $ctnqty,
        $invoice, $kcgmt, $season, $buyer,
        $factory
    );

$query = "INSERT INTO order_details (order_id, size, qty) VALUES (?,?,?)";

$stmt_order_details = $conn->prepare($query);
$stmt_order_details->bind_param(
        'isi',
        $order_id, $size, $quantity
    );

$sizelist = array(4=>'4','6','8','10','12','14','XS','S','M','L','XL','XXL'); // starting with y = 4

// loop over the data, populate the bound input variables, and execute the queries
foreach(range(2,$xls->sheets[$sheet]['numRows']) as $x)
{
    // 1) insert the unique order information into an `orders` table, with an auto-increment id column to produce the id.
    $style = $xls->val($x,1,$sheet);
    $order = $xls->val($x,2,$sheet);
    $color = $xls->val($x,3,$sheet);
    $ctnqty = $xls->val($x,16,$sheet);
    $invoice = $xls->val($x,17,$sheet);
    $kcgmt = $xls->val($x,18,$sheet);
    $season = $xls->val($x,19,$sheet);
    $buyer = $xls->val($x,20,$sheet);
    $factory = $xls->val($x,21,$sheet);

    $stmt_order->execute();

    // 2) get the last insert id from the above query.
    $order_id = $conn->insert_id;

    // 3) insert the order details (item id/size and quantity) into an order_items table, one row per item, using the last insert id from above to relate the row(s) back to the order they belong to. is suspect that the 'style' is either an item id or an item name. you would only insert rows for data that exists, so, there will only be rows where the quantity is > 0.
    foreach($sizelist as $y => $value) // y = 4-15
    {
        $quantity = $xls->val($x,$y,$sheet);
        if($quantity > 0)
        {
            $size = $value;
            $stmt_order_details->execute();
        }
    }
}

if you have more logic and queries than this, you are doing things that don't have anything to do with the goal of storing this data.

 

notes:

 

1) i renamed the $connection variable to $xls, to shorten it and to name it having something to do with what it is.

 

2) the excel reader you are using should have a ->val() method. if you use it, it will simplify all the logic that's accessing the cell values.

 

3) the excel reader you are using is out of date, throws a ton of errors (if php's error reporting is setup properly), and won't work at all under php 7. a more universal method to read the data would be to save it as a CSV (comma separated value) file and either use php's  fgetcsv() or str_getcsv() to read/parse the data from each line into an array.

[mehidy]

I will work on above & revert.