for each returning rows depending on qty

[Paulqvz]

Good day

 

I have a label script where i have to print labels. the problem i have is that it is not returning the right amount of rows dependant on qty column.

 

$sql="SELECT so.`name` AS so_name, sol.`name` AS sol_name,sol.`quantity` AS sol_quantity,`delivery_date`,DATE_ADD(`delivery_date`, INTERVAL 10 DAY) AS used_day
FROM `sales_orders` so INNER JOIN `sales_order_lines` sol
ON so.id = sol.`sales_orders_id`
WHERE sol.`parent_id` IS NULL AND so.id = '$id'";

$result = mysqli_query($connect, $sql);
$degree = "STORAGE: KEEP REFRIGERATED BELOW 5`C";
//var_dump($degree);
while($row = mysqli_fetch_array($result))  
           { 
	 		//$so_name = $row['so_name'];
			$sol_name[] = $row['sol_name'];
			$sol_quantity[] = $row['sol_quantity'];
			$sol_quantityy = $row['sol_quantity'];
			$delivery_date[] = $row['delivery_date'];
			$used_day[] = $row['used_day'];
		   }

		  foreach($sol_quantity as $index => $value)
		  
	
{  	
 for($i=1; $i <=$value;$i++)
		   {
		  
		 
		   $text = sprintf(
		   "%s\n%s  %s\n%s     %s\n%s\n%s",
		
		   "$sol_name[$index]","<br>",
		   'Delivery Date', "<br>",
		   'Use By Date', "<br>",
		   "$delivery_date[$index]","<br>",
		   "$used_day[$index]","<br>",
		   'PRODUCT OF SOUTH AFRICA',"<br>",
		   "{$degree}"
		   );

$sol_quantity[] for all three products here is 3 . It is returning

 

CARROT & POTATO MIX
Delivery Date
Use By Date
2021-09-07

POTATO
Delivery Date
Use By Date
2021-09-07

POTATO
Delivery Date
Use By Date
2021-09-07

ONION
Delivery Date
Use By Date
2021-09-07

 

Instead of 

 

CARROT & POTATO MIX
Delivery Date
Use By Date
2021-09-07

CARROT & POTATO MIX
Delivery Date
Use By Date
2021-09-07

CARROT & POTATO MIX
Delivery Date
Use By Date
2021-09-07

POTATO
Delivery Date
Use By Date
2021-09-07

POTATO
Delivery Date
Use By Date
2021-09-07

POTATO
Delivery Date
Use By Date
2021-09-07

ONION
Delivery Date
Use By Date
2021-09-07

ONION
Delivery Date
Use By Date
2021-09-07

ONION
Delivery Date
Use By Date
2021-09-07

[Barand]

You don't show what you finally do with $text.

However, at the start

$text = '';

Then, inside the for() loop, ...

            $text .= sprintf(
                  ^

... might help.

[Psycho]

You are making things harder than they need to be.

1. Why are you including the sales_order table in your query when you don't use any data from that table and you can use the lookup value on the sales_order_lines table?
2. Why do you iterate through the DB results to put them into other arrays just to iterate over those?

$sql="SELECT `name`, `quantity`, `delivery_date`, DATE_ADD(`delivery_date`, INTERVAL 10 DAY) AS used_day
      FROM `sales_orders` so INNER JOIN `sales_order_lines` sol
      WHERE sol.`parent_id` IS NULL AND sales_orders_id = '$id'";

$result = mysqli_query($connect, $sql);
$degree = "STORAGE: KEEP REFRIGERATED BELOW 5`C";
//var_dump($degree);

$labels = "";
while($row = mysqli_fetch_array($result))  
{
    $curent_label = sprintf(
        "%s\n%s  %s\n%s     %s\n%s\n%s",
        "{$row['name']}","<br>",
        'Delivery Date', "<br>",
        'Use By Date', "<br>",
        "{$row['delivery_date']}","<br>",
        "{$row['used_day']}","<br>",
        'PRODUCT OF SOUTH AFRICA',"<br>",
        "{$degree}"
        );
        
    $labels .= str_repeat($curent_label, $row['quantity'])
}

echo $labels;