Solved

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

Posted on 2014-01-08
16
250 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 108

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 108

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
 
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 108

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 108

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

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

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 108

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 108

Accepted Solution

by:
Ray Paseur earned 500 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 108

Expert Comment

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now