Can't get expected results running a while loop inside another and returning an array

Hi,
I'm trying to do 2 queries below and the second one I suspect isn't correct. If I remove the second query, I get my $results array (which I then encode to json and send back to my view) as expected. When I add in the additional loop, the array comes over as 'false'. I am thinking my logic is wrong.

First query:
Gets the total COUNT for each make of vehicle there is a deal for plus the make (name of the manufacturer ie: Mazda).

The second query is supposed to use the $trade_make from the first query and get the model of the vehicle that has the most deals done. Returning in the same array the COUNT and the name of the model for each of the makes from the first query.

*I realize I'm using the mysql_query approach but this is an inhouse endeavour and won't see the light of day/commercial use and I'm a newbie :)


//get top makes traded
$result = mysql_query("SELECT COUNT(trades.make) AS makeCount, trades.make AS trade_make FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.make ORDER BY makeCount DESC");  
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
	$results = array();
	while ($row = mysql_fetch_assoc($result)) {
	$makeCount = $row["makeCount"];
	$trade_make = $row["trade_make"];
	
		//get top models traded
		$result = mysql_query("SELECT COUNT(trades.model) AS modelCount, trades.model AS trade_model FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE trades.make = '$trade_make' AND deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.model ORDER BY modelCount DESC LIMIT 1");  
		$num_results = mysql_num_rows($result);
			if ($num_results > 0){ 
				while ($row = mysql_fetch_assoc($result)) {
				$modelCount = $row["modelCount"];
				$trade_model = $row["trade_model"];
				}
			}
	$results[] = $row;
	}
}

Open in new window

Can you see where I'm screwing up?
Thanks!
LVL 1
tjyoungAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
Please see http://www.laprbass.com/RAY_temp_tjyoung.php

Overall, it's my sense that this could be done in the SQL, but I can't do the brain today; I have the dumb.  So doing it this way in PHP by manipulating the results set will probably work OK.

<?php // RAY_temp_tjyoung.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28333448.html#a39767380

/* DATA FROM THE POST AT EE

THE QUERIES:

SELECT COUNT(trades.make) AS makeCount, trades.make AS trade_make
FROM trades JOIN deals ON trades.deal_id = deals.id
WHERE deals.parent_id = '$current_id'
AND deals.purchaseStatus = 'Deal'
AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from'
AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to'
GROUP BY trades.make
ORDER BY makeCount DESC

SELECT COUNT(trades.model) AS modelCount, trades.model AS trade_model
FROM trades JOIN deals ON trades.deal_id = deals.id
WHERE trades.make = '$trade_make'
AND deals.parent_id = '$current_id'
AND deals.purchaseStatus = 'Deal'
AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from'
AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to'
GROUP BY trades.model
ORDER BY modelCount DESC LIMIT 1

WHAT WE WANT IN THE JSON STRING:

{"modelCount":"1","trade_model":"FOCUS","makeCount":"3","trade_make":"FORD"},

array(14) {
  [0]=>
  array(2) {
    ["modelCount"]=>
    string(1) "2"
    ["trade_model"]=>
    string(6) "FIESTA"
  }
  [1]=>
  array(2) {
    ["makeCount"]=>
    string(1) "4"
    ["trade_make"]=>
    string(4) "FORD"
  }
  [2]=>
  array(2) {
    ["modelCount"]=>
    string(1) "2"
    ["trade_model"]=>
    string(13) "GRAND CARAVAN"
  }
  [3]=>
  array(2) {
    ["makeCount"]=>
    string(1) "3"
    ["trade_make"]=>
    string(5) "DODGE"
  }
  [4]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(2) "CC"
  }
  [5]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(10) "VOLKSWAGEN"
  }
  [6]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(7) "LIBERTY"
  }
  [7]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(4) "JEEP"
  }
  [8]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(6) "SONATA"
  }
  [9]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(7) "HYUNDAI"
  }
  [10]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(6) "ALTIMA"
  }
  [11]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(6) "NISSAN"
  }
  [12]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(3) "C30"
  }
  [13]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(5) "VOLVO"
  }
}
*/

// SIMULATE THE RESULTS
$results = array
( array( "modelCount" => "2", "trade_model" => "FIESTA" )
, array( "makeCount"  => "4", "trade_make"  => "FORD" )
, array( "modelCount" => "2", "trade_model" => "GRAND CARAVAN" )
, array( "makeCount"  => "3", "trade_make"  => "DODGE" )
, array( "modelCount" => "1", "trade_model" => "CC" )
, array( "makeCount"  => "1", "trade_make"  => "VOLKSWAGEN" )
, array( "modelCount" => "1", "trade_model" => "LIBERTY" )
, array( "makeCount"  => "1", "trade_make"  => "JEEP" )
, array( "modelCount" => "1", "trade_model" => "SONATA" )
, array( "makeCount"  => "1", "trade_make"  => "HYUNDAI" )
, array( "modelCount" => "1", "trade_model" => "ALTIMA" )
, array( "makeCount"  => "1", "trade_make"  => "NISSAN" )
, array( "modelCount" => "1", "trade_model" => "C30" )
, array( "makeCount"  => "1", "trade_make"  => "VOLVO" )
)
;

// DOES THE SIMULATION LOOK RIGHT? YES.
var_dump($results);
echo PHP_EOL;

// REFORMAT THE RESULTS ARRAY
$out = array();
$ndx = 0;
$max = count($results);
while ($ndx < $max)
{
    $mod   = $results[$ndx];
    $mak   = $results[$ndx+1];
    $out[] = array_merge($mak, $mod);
    $ndx   = $ndx + 2;
}

// WHAT DOES THE NEW RESULTS SET LOOK LIKE?
print_r($out);
echo PHP_EOL;

// MAKE THE JSON STRING
$jso = json_encode($out, JSON_PRETTY_PRINT);
print_r($jso);

Open in new window

Best regards, ~Ray
0
 
Ray PaseurCommented:
Getting off MySQL is not really that hard.  The easiest path is to use object-oriented MySQLi.  This article shows you how.  Once you've made the transition even once it will be very easy to continue!  And you'll need those new habits before you know it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

In the instant case it looks like the $result variable is overwritten.  Check also $row
0
 
tjyoungAuthor Commented:
Hi,
Thank you, I'm getting close to having it right, but can't figure out how to get the 'model and model count' in with the make and make count of each?

I changed this(simply tried placing a'2' after row & result etc. in the second loop and put in another $results[] = $row2;:
//get top makes traded
$result = mysql_query("SELECT COUNT(trades.make) AS makeCount, trades.make AS trade_make FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.make ORDER BY makeCount DESC");  
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
	$results = array();
	while ($row = mysql_fetch_assoc($result)) {
	$makeCount = $row["makeCount"];
	$trade_make = $row["trade_make"];
	
		//get top models traded
		$result2 = mysql_query("SELECT COUNT(trades.model) AS modelCount, trades.model AS trade_model FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE trades.make = '$trade_make' AND deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.model ORDER BY modelCount DESC LIMIT 1");  
		$num_results2 = mysql_num_rows($result2);
			if ($num_results2 > 0){ 
				while ($row2 = mysql_fetch_assoc($result2)) {
				$modelCount = $row2["modelCount"];
				$trade_model = $row2["trade_model"];
				$results[] = $row2;
				}
			}
	$results[] = $row;
	}
} else {
$results = "";
}

Open in new window


Now I get below... which is close ie:
{"modelCount":"1","trade_model":"FOCUS"},{"makeCount":"3","trade_make":"FORD"},

but really need:
{"modelCount":"1","trade_model":"FOCUS","makeCount":"3","trade_make":"FORD"},

results":[{"modelCount":"1","trade_model":"FOCUS"},{"makeCount":"3","trade_make":"FORD"},{"modelCount":"2","trade_model":"SONATA"},{"makeCount":"2","trade_make":"HYUNDAI"},{"modelCount":"1","trade_model":"CC"},{"makeCount":"1","trade_make":"VOLKSWAGEN"},{"modelCount":"1","trade_model":"LIBERTY"},{"makeCount":"1","trade_make":"JEEP"},{"modelCount":"1","trade_model":"CALIBER"},{"makeCount":"1","trade_make":"DODGE"},{"modelCount":"1","trade_model":"ALTIMA"},{"makeCount":"1","trade_make":"NISSAN"},{"modelCount":"1","trade_model":"C30"},{"makeCount":"1","trade_make":"VOLVO"}]

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ray PaseurCommented:
Please show me the instructions that use $results and that creates the JSON string, thanks.

Refs:
http://php.net/manual/en/function.json-encode.php
http://php.net/manual/en/json.constants.php
http://json.org/
0
 
tjyoungAuthor Commented:
The code is as below which is supposed to generate a $results array which I echo back to my ui as json (see second code block). There are a number of other variables that are passed back, I just shortened it so you don't have to see copious amounts of unrelated code. Once I receive it back, I simply am using jquery to place it in the appropriate divs ie:
$('#mydiv').html(data.results[0].makeCount);

Works fine without the second loop, I can place the makeCount and the trade_make no issues. Its that second loop...

//get top makes traded
$result = mysql_query("SELECT COUNT(trades.make) AS makeCount, trades.make AS trade_make FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.make ORDER BY makeCount DESC");  
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
	$results = array();
	while ($row = mysql_fetch_assoc($result)) {
	$makeCount = $row["makeCount"];
	$trade_make = $row["trade_make"];
	
		//get top models traded
		$result2 = mysql_query("SELECT COUNT(trades.model) AS modelCount, trades.model AS trade_model FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE trades.make = '$trade_make' AND deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.model ORDER BY modelCount DESC LIMIT 1");  
		$num_results2 = mysql_num_rows($result2);
			if ($num_results2 > 0){ 
				while ($row2 = mysql_fetch_assoc($result2)) {
				$modelCount = $row2["modelCount"];
				$trade_model = $row2["trade_model"];
				$results[] = $row2;
				}
			}
	$results[] = $row;
	}
} else {
$results = "";
}

Open in new window

Here is the json encode echoing back the $result (among other variables I require)
echo json_encode(array(
"deals"				=>$deals,
"average_deals"		=>$average_deals,
"deal_total"			=>$deal_total,
"results"				=>$results
));

Open in new window


Like I mentioned earlier: with the above I'm getting close:
{"modelCount":"1","trade_model":"FOCUS"},{"makeCount":"3","trade_make":"FORD"},

but trying to get all 4 values together.
{"modelCount":"1","trade_model":"FOCUS","makeCount":"3","trade_make":"FORD"},
0
 
Ray PaseurCommented:
Can you please create the array separately?  Instead of this:
echo json_encode(array(
"deals"				=>$deals,
"average_deals"		=>$average_deals,
"deal_total"			=>$deal_total,
"results"				=>$results
));

Open in new window

Do something like this so we can see the data structure
$jso = array(
"deals"				=>$deals,
"average_deals"		=>$average_deals,
"deal_total"			=>$deal_total,
"results"				=>$results
);
var_dump($jso);

Open in new window

0
 
tjyoungAuthor Commented:
Heres what I got:
array(1) {
  ["results"]=>
  array(14) {
    [0]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(5) "FOCUS"
    }
    [1]=>
    array(2) {
      ["makeCount"]=>
      string(1) "3"
      ["trade_make"]=>
      string(4) "FORD"
    }
    [2]=>
    array(2) {
      ["modelCount"]=>
      string(1) "2"
      ["trade_model"]=>
      string(6) "SONATA"
    }
    [3]=>
    array(2) {
      ["makeCount"]=>
      string(1) "2"
      ["trade_make"]=>
      string(7) "HYUNDAI"
    }
    [4]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(2) "CC"
    }
    [5]=>
    array(2) {
      ["makeCount"]=>
      string(1) "1"
      ["trade_make"]=>
      string(10) "VOLKSWAGEN"
    }
    [6]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(7) "LIBERTY"
    }
    [7]=>
    array(2) {
      ["makeCount"]=>
      string(1) "1"
      ["trade_make"]=>
      string(4) "JEEP"
    }
    [8]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(7) "CALIBER"
    }
    [9]=>
    array(2) {
      ["makeCount"]=>
      string(1) "1"
      ["trade_make"]=>
      string(5) "DODGE"
    }
    [10]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(6) "ALTIMA"
    }
    [11]=>
    array(2) {
      ["makeCount"]=>
      string(1) "1"
      ["trade_make"]=>
      string(6) "NISSAN"
    }
    [12]=>
    array(2) {
      ["modelCount"]=>
      string(1) "1"
      ["trade_model"]=>
      string(3) "C30"
    }
    [13]=>
    array(2) {
      ["makeCount"]=>
      string(1) "1"
      ["trade_make"]=>
      string(5) "VOLVO"
    }
  }
}

Open in new window

0
 
Ray PaseurCommented:
OK, one more var_dump() please.  After the closing else{} block, when the $results variable is completely populated, please show me that.  I think it may need some modest reformatting and if I can see the "before" I'm pretty sure I can find the way to the "after."
0
 
tjyoungAuthor Commented:
Ou power went out....ugghhh
0
 
tjyoungAuthor Commented:
I'm on my phone... Darn winter
0
 
Ray PaseurCommented:
We're lucky here - bitterly cold but the power stayed at least.

Just post it whenever it's convenient, I'll check back later or first thing tomorrow.
0
 
tjyoungAuthor Commented:
Hi,
I placed it here and get what looks like the same output as above:
$result = mysql_query("SELECT COUNT(trades.make) AS makeCount, trades.make AS trade_make FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.make ORDER BY makeCount DESC");  
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
	$results = array();
	while ($row = mysql_fetch_assoc($result)) {
	$makeCount = $row["makeCount"];
	$trade_make = $row["trade_make"];
	
		//get top models traded
		$result2 = mysql_query("SELECT COUNT(trades.model) AS modelCount, trades.model AS trade_model FROM trades JOIN deals ON trades.deal_id = deals.id  WHERE trades.make = '$trade_make' AND deals.parent_id = '$current_id' AND deals.purchaseStatus = 'Deal' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(deals.purchaseModified,'%Y-%m-%d') <= '$to' GROUP BY trades.model ORDER BY modelCount DESC LIMIT 1");  
		$num_results2 = mysql_num_rows($result2);
			if ($num_results2 > 0){ 
				while ($row2 = mysql_fetch_assoc($result2)) {
				$modelCount = $row2["modelCount"];
				$trade_model = $row2["trade_model"];
				$results[] = $row2;
				}
			}
	$results[] = $row;
	}var_dump($results);
} else {
$results = "";
}

Open in new window

array(14) {
  [0]=>
  array(2) {
    ["modelCount"]=>
    string(1) "2"
    ["trade_model"]=>
    string(6) "FIESTA"
  }
  [1]=>
  array(2) {
    ["makeCount"]=>
    string(1) "4"
    ["trade_make"]=>
    string(4) "FORD"
  }
  [2]=>
  array(2) {
    ["modelCount"]=>
    string(1) "2"
    ["trade_model"]=>
    string(13) "GRAND CARAVAN"
  }
  [3]=>
  array(2) {
    ["makeCount"]=>
    string(1) "3"
    ["trade_make"]=>
    string(5) "DODGE"
  }
  [4]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(2) "CC"
  }
  [5]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(10) "VOLKSWAGEN"
  }
  [6]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(7) "LIBERTY"
  }
  [7]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(4) "JEEP"
  }
  [8]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(6) "SONATA"
  }
  [9]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(7) "HYUNDAI"
  }
  [10]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(6) "ALTIMA"
  }
  [11]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(6) "NISSAN"
  }
  [12]=>
  array(2) {
    ["modelCount"]=>
    string(1) "1"
    ["trade_model"]=>
    string(3) "C30"
  }
  [13]=>
  array(2) {
    ["makeCount"]=>
    string(1) "1"
    ["trade_make"]=>
    string(5) "VOLVO"
  }
}

Open in new window

0
 
Ray PaseurCommented:
OK, that's not after the closing else{} block, but it's good enough I think.  Let me tinker with the data a little bit and I'll post a code suggestion shortly.
0
 
tjyoungAuthor Commented:
It works perfectly. Man, I wish I knew a 10th of what you do.
Thanks Ray your time and patience is always appreciated.
0
 
tjyoungAuthor Commented:
Here's a screenshot of the finished product.
Couldn't have got anywhere without your help past year or two.
samplescreen.jpg
0
 
Ray PaseurCommented:
That looks great!  Thanks for the points and thanks for using EE, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.