?
Solved

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

Posted on 2014-01-08
16
Medium Priority
?
269 Views
Last Modified: 2014-01-09
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!
0
Comment
Question by:tjyoung
  • 8
  • 8
16 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39765774
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
 
LVL 1

Author Comment

by:tjyoung
ID: 39765804
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39766083
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:tjyoung
ID: 39766106
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39766143
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
 
LVL 1

Author Comment

by:tjyoung
ID: 39766174
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39766536
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
 
LVL 1

Author Comment

by:tjyoung
ID: 39766660
Ou power went out....ugghhh
0
 
LVL 1

Author Comment

by:tjyoung
ID: 39766664
I'm on my phone... Darn winter
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39766734
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
 
LVL 1

Author Comment

by:tjyoung
ID: 39767380
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39767978
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39768358
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
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 39768880
It works perfectly. Man, I wish I knew a 10th of what you do.
Thanks Ray your time and patience is always appreciated.
0
 
LVL 1

Author Comment

by:tjyoung
ID: 39768910
Here's a screenshot of the finished product.
Couldn't have got anywhere without your help past year or two.
samplescreen.jpg
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39769214
That looks great!  Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question