On Fri, 29 Aug 2003 07:54:45 -0400, you wrote:
>Correct on the brackets - but not on the results still working... The code
>I had in the email was just a sample - here is the real code...
Ack. For anyone watching, if you have a problem please post a minimal code
snippet that illustrates the problem. Test it before you send. Otherwise you
may be wasting the time of anyone who tries to help you.
>$res=mysql_query("select distinct dealercode from transactions order by
>dealercode",$intranet);
>$transactions=Array();
>while($row=mysql_fetch_array($res)) {
> $amt=mysql_query("select sum(amount) as total from transactions where
>date between '2003-04-01' and '2003-06-31' and
>dealercode='$row[dealercode]'",$intranet);
> $afftotal=mysql_fetch_array($amt);
>
>array_push($transactions,$row[dealercode]=>Array("onlineu",$afftotal[total])
>);
> $amt=mysql_query("select usercountry,sum(amount) as total from
>orders_placed where date between '2003-04-01' and '2003-06-31' and
>dealercode='$row[dealercode]' and sent='y' group by usercountry",$intranet);
> while($cdtotal=mysql_fetch_array($amt)) {
>
>array_push($transactions[$row[dealercode]],$cdtotal[usercountry]=>$cdtotal[t
>otal]);
> }
>}
Ok, is this a MySQL database? date is a reserved word.
"$row[dealercode]" should be "$row['dealercode']", same goes for the other
dictionaries. Your code should be throwing a notice on that, which suggests
that your error-level is set low and you may be missing other problems.
You want a structure like:
array (
33 = array (
'a' = 52.00
'b' = 53.00
'c' = 54.00
)
34 = array (
'a' = 55.00
'b' = 56.00
'c' = 57.00
)
)
What's it supposed to look like when 33=>'a' exists more than once? Is that
possible?
That array_push looks like a mess. Try something like...
$data = array();
for each row in query :
$dealerid = $row[0];
$data[$dealerid] = array();
for each subrow in subquery :
$country = $subrow[0];
$amount = $subrow[1];
$data[$dealerid][$country] = $amount;
>And as for your offline suggestion of putting it into one query - I did try
>that first of course, but due to the large amount of data, and the shoddy
>nature of the database design (inherited not built) the query times out.
>This atleast returns within 1 min with the data when I can retrieve it :)
Do you have a seperate dealer table? You're pulling "SELECT DISTINCT
dealercode FROM transactions", which is odd. Somewhere you may have a table
where dealername is associated with dealercode. That's what you should be
doing the join against.
If I were you I'd ask on a database-specific mailing list, because it's
never faster to do it in software when you can push it all up into the
database instead.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
