Empty array though queries return results manually

[thenorman138]

I'm creating an array out of a select statement but it's currently printing an empty array. I've tested the query manually and know for a fact that it should contain information but I could be missing something in translating the data from the odbc connection over to the mysql connection.

 

I'm putting the entire code block below, the print_r statement at the end is the array being printed that's empty.

 

Everything else in the code is working perfectly, but my ```$dealerSkuCheck``` query should be pulling orders from mysql by equating those fields to the previously retrieved Db2 values, in the ```$Db2ShipArr``` which prints all of the necessary information.

 

Maybe I'm just missing a call to those variables somewhere? 

    $DB2Shipped = " 
SELECT DISTINCT 
invnoc as INVOICE,
        cstnoc AS DEALER,
        framec AS FRAME,
        covr1c AS COVER,
        colr1c AS COLOR ,
        extd2d AS SHIPDATE,
        orqtyc AS QUANTITY
FROM GPORPCFL
WHERE invnoc = '{$order_id}'
group by invnoc,cstnoc, slsnoc, orqtyc, framec, covr1c,colr1c, extd2d
order by invnoc asc
";


//print_r($order_id);


$Db2ShipRslt = odbc_exec($DB2Conn,$DB2Shipped);
if ( $Db2ShipRslt === false ) {
     exit (odbc_errormsg($DB2Conn));
}


$Db2ShipArr = array();
//print_r($order_id);


while($db2ShipRow = odbc_fetch_array($Db2ShipRslt)){
//print_r($Db2ShipArr);
{
    $Db2ShipArr[] = $db2ShipRow;
    }


foreach($Db2ShipArr as $Db2Ship){


try{


while($arr = odbc_fetch_array($Db2ShipRslt)) 
{
//Check to see if there are any records in jfi_sales.placements for the dealer/sku combo
$dealerSkuCheck = "
SELECT 
sku_id,
dealer_id
FROM placements p
INNER JOIN skus s
ON p.sku_id = s.id
WHERE p.dealer_id = '{$db2ShipRow['DEALER']}'
AND   s.frame = '{$db2ShipRow['FRAME']}'
AND   s.cover1 = '{$db2ShipRow['COVER']}'
AND   s.color1 = '{$db2ShipRow['COLOR']}'
AND   p.order_num = '{$db2ShipRow['INVOICE']}'
";


$existingCheckRslt = mysqli_query($mysqlConn, $dealerSkuCheck);
$existingRecords = array();


while ($existingRow = mysqli_fetch_array($existingCheckRslt)){
    $existingRecords[] = $existingRow;
}


print_r($existingRecords); /*This is printing an empty array*/

 

[ginerjm]

How many rows were returned in your last query?

 

Can you explain what you are doing with a foreach that runs a while loop to re-fetch the same data yet you never use the foreach value?

 

Do you have php error checking turned on?

[kicken]

Your loops are all kinds of messed up in the code you posted. Not sure if that is how your code actually looks, or if you made an error posting.

For example, you either have an extra brace, or unnecessary braces here:

while($db2ShipRow = odbc_fetch_array($Db2ShipRslt)){
//print_r($Db2ShipArr);
{
    $Db2ShipArr[] = $db2ShipRow;
    }

Notice you have a { at the end of the while statement and a couple lines down. I'm going to assume one of those opening braces is extra and your code should instead be:

while($db2ShipRow = odbc_fetch_array($Db2ShipRslt))
{
	$Db2ShipArr[] = $db2ShipRow;
}

Next, you have a foreach, then inside that a while loop which makes no sense

	foreach($Db2ShipArr as $Db2Ship){
		try{
			while($arr = odbc_fetch_array($Db2ShipRslt)) 
			{

You're looping over the array you just created, then trying to loop over your previous result set again. Your previous result set is empty at this point though so your while loop will never execute meaning your query inside it will never execute.

 

Since your print_r is part of that while loop's code, it should also never execute and you shouldn't be getting output at all from it.

[thenorman138]

@guru I see what you mean. I did in fact fix the curly brace issue and overlooked correcting it here unfortunately. However, are you saying I should move the while loop outside of the foreach? So I"d have while -> while -> foreach?

[ginerjm]

As I said before - why do you use a foreach AND a while loop together? Makes NO SENSE at all!

 

Also - why don't you consolidate your queries to get the data you want all at once?

[thenorman138]

I actually refactored this a bit for the Foreach/while issue, but I can't edit my question. As far as consolidating, I haven't been able to do that because there's so much interplay betwteen DB2 and Mysql, and I'm also trying to put multiple transactions in one script here

[ginerjm]

Ok - you refactored the code. Want to share?

 

And - why is it that you find it necessary to use multiple db interfaces? Stick with one - PDO preferably - and make your life that much simpler.

[thenorman138]

Thank you, I'm going to go ahead and modify my connections so that they all use PDO and then I'll share the new code here in a new reply. I was able to get the arrays returned properly but I want to make sure I'm using the values after that properly