CODEWITHSUNDEEP
phpmysqlarraysloops
user1459766
user1459766

Reputation: 118

mysql query looped with results stored in an array

I have a shipping module I'm working to wrap up and am trying to query a mysql table, count the number of rows for a given line item on a PO, and store that result in an array. I don't think I can do group by within mysql as it will not provide a result for a line item that hasn't had any shipments against it. The intent is to take the original order quantity, count the number of units shipped against that via my query, and then subtract the units shipped from the original amount providing the remaining units to be shipped against that line item.

To ensure I receive even the zero qty for line items without shipments and to store that in the array I am trying to loop my query and store each single result as a value within my array. I'm open to suggestions on changing the approach if there is a better way.

Here is what I have for my query:

// I have a previous query that provides the number of line items for a given po.  that number is stored in variable $num1 
$a=1;
$LiShipped = array();

while($a<$num1){

$query2="SELECT count(E3_SN) AS SCount FROM Shipped WHERE Cust_Ord_Num = '$SO_Num' AND LineItem=$a";
$LiShipped[] = mysql_fetch_array($query2);
$a++;
}

Unfortunately when I go to iterate through my array it appears as though nothing is stored in the array.

<?php
echo $LiShipped[0]; //results nothing
echo var_dump($LiShipped); // results array(1) { [0]=> NULL } array(2) { [0]=> NULL [1]=> NULL } array(3) { [0]=> NULL [1]=> NULL [2]=> NULL }
?>

Looks like all null values.

Upvotes: 0

Views: 167

Answers (2)

Jocelyn
Jocelyn

Reputation: 11393

You are not executing your query, it can't work. Try this code:

// I have a previous query that provides the number of line items for a given po.  that number is stored in variable $num1 
$a=1;
$LiShipped = array();

while($a<$num1){
    $query2="SELECT count(E3_SN) AS SCount FROM Shipped WHERE Cust_Ord_Num = '$SO_Num' AND LineItem=$a";
    $res = mysql_query($query2);
    while($LiShipped[] = mysql_fetch_array($res));
    $a++;
}

Upvotes: 0

nickb
nickb

Reputation: 59699

You need to execute the query (by calling mysql_query()) before you try and attempt to retrieve the result:

$query2="SELECT count(E3_SN) AS SCount FROM Shipped WHERE Cust_Ord_Num = '$SO_Num' AND LineItem=$a";
$query2 = mysql_query( $query_2); // <-- NEED THIS
$LiShipped[] = mysql_fetch_array( $query2);

Note the above omits basic error checking and sanitation of the SQL query to prevent SQL injection.

Upvotes: 1

Related Questions