Solved

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

Posted on 2014-01-08
16
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 110

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 110

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 110

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 110

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 110

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 110

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 110

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 110

Expert Comment

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

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

691 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