HELP - ARRAY Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

HELP - ARRAY Problem

Post by nwoutback »

Below is the code I'm using to try and get a SUM of $columns per $distributor, and what I'm getting is this ...

Gardner, AST210 : 2
Smith, AST210 :
Gardner, AST210Sales : 8
Smith, AST210Sales :
Gardner, TBC2251 : 16
Smith, TBC2251 :
Gardner, TBC2251Sales : 12
Smith, TBC2251Sales :

The totals on the Gardner line are the totals for both Smith and Gardner, but I want to be able to get an array of the column SUMs then list them by the appropriate distributor ... can you help? Thanks.

<?php

$connection=mysql_connect("localhost","username","password") or die('Could not connect to the database server');
$db = mysql_select_db("tanaka-ism_com", $connection) or die ("Unable to select database.");

$distributors = array("Gardner","Smith");

$columns = array("AST210","AST210Sales","TBC2251","TBC2251Sales");

foreach($columns as $columns)

{
$query = "SELECT SUM($columns) FROM distreport03";
$mysqlresult = mysql_query($query);
$result = mysql_fetch_array($mysqlresult);
echo " $distributors[0], {$columns} : {$result[0]}<br />";
echo " $distributors[1], {$columns} : {$result[1]}<br />";

mysql_free_result();
}


?>
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

$result[1] is empty every time because the SUM()
is resulting in one row on every query.
You need to tie the distibutor to the column with
a key like distributor_id, or rewrite the arrays so
each distributor has a list of specific AST/TBC data columns...
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

mchaggis wrote:Ok, your problem is with the SQL...

Try something like this (had to gues the cols tho)

Code: Select all

<?php

$connection=mysql_connect("localhost","username","password") or die('Could not connect to the database server');
$db = mysql_select_db("tanaka-ism_com", $connection) or die ("Unable to select database.");

$distributors = array("Gardner","Smith");

$columns = array("AST210","AST210Sales","TBC2251","TBC2251Sales");

foreach($columns as $column)

{
$query = "SELECT SUM($column) FROM distreport03 GROUP BY distributors";
$mysqlresult = mysql_query($query);
list($result1) = mysql_fetch_array($mysqlresult);
list($result2) = mysql_fetch_array($mysqlresult);
echo " $distributors[0], {$column} : {$result1}<br />";
echo " $distributors[1], {$column} : {$result2}<br />";

mysql_free_result();
}

?>
This doesn't seem to be an ellegant way of doing it though... What exactly are you trying to do? And what is you table schema?



edit : this was posted on the second thread created.

NWOutBack, I deleted your duplicate post. please reframe from crossposting ( creating multiple posts on the same issue on different forums ). It's recommended that you just post on the same thread you create originally in order to help those of us who are tryign to help resolve your issue without havign to guess which post to post answers on.

thanks
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

Thank you for the reply ... It's now listing each distributor and column name, but not the totals from the columns ... from this code:

<?php

$connection=mysql_connect("localhost","tanaka","tanakaDBone") or die('Could not connect to the database server');
$db = mysql_select_db("tanaka-ism_com", $connection) or die ("Unable to select database.");

$distributors = array("Gardner","Smith");

$columns = array("AST210","AST210Sales","TBC2251","TBC2251Sales");

foreach($columns as $columns)

{
$query = "SELECT SUM($columns) FROM distreport03 GROUP BY distributors";
$mysqlresult = mysql_query($query);
list($result1) = mysql_fetch_array($mysqlresult);
list($result2) = mysql_fetch_array($mysqlresult);
echo " $distributors[0], {$columns} : {$result1[0]}<br />";
echo " $distributors[1], {$columns} : {$result2}<br />";

mysql_free_result();
}

?>

This is the result:

Gardner, AST210 :
Smith, AST210 :
Gardner, AST210Sales :
Smith, AST210Sales :
Gardner, TBC2251 :
Smith, TBC2251 :
Gardner, TBC2251Sales :
Smith, TBC2251Sales :

Each AST210, AST210Sales, etc. should have the total of that column ... what am I missing? Thank you.

Paul
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

this should work :

Code: Select all

<?php 
$columns = array("AST210","AST210Sales","TBC2251","TBC2251Sales"); 
$distro=array(); 
$totals=array(); 

// should change the columns and distributors to reflect the actual field name in your table for these fields... 
foreach ($columns as $column)
{
   $sql="SELECT sum(".$column."), distributors FROM distreport03 where distributors = 'smith' and distributors ='gardner' GROUP BY distributors"; 
   $result=mysql_query($sql); 
   $rows=mysql_fetch_array($result); 

   while ($rows=mysql_fetch_array($result)) 
   { 
      // do this if you are wanting to save these entries for later... 
      $totals[] = $row['sum('.$column.')']; 
      $distro[]=$row['distributors']; 
   } 
} 

// then echo it out like this : 
foreach($columns as $column)
{
      for($i=0; $i<=count($columns); $i++) 
      { 
         echo $distro[$i].','.$column.' : '.$totals[$i]; 
         echo '<br />'; 
      }
}
?>
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

produced the following:

,AST210 :
,AST210 :
,AST210 :
,AST210 :
,AST210 :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

but did you change the query statement to reflect the fields that is in your mysql table?

if not, could you post your table info (that includes the columns and distributors) so we can figure this out?

thanks.
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

Here is the beginning portion of the table:


ReportID int(11)
RptDist varchar(30)
RptMonth varchar(15)
AST210 char(3)
AST210Sales char(3)
TBC2251 char(3)
TBC2251Sales char(3)

The RptDist is the Distributor ... what's going on here is this is a Distributor Inventory Report filed monthly by each of eight Distributors ... so over the course of a year each of the eight will have 12 submissions. The AST210 AST210Sales, etc. hold the values of total sold and total on hand.

So I'm trying to get a list per distributor of how many units sold and how many on hand ... does this help?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

then you have to change your query from this :

Code: Select all

$sql="SELECT sum(".$column."), distributors FROM distreport03 where distributors = 'smith' and distributors ='gardner' GROUP BY distributors";
to this...

Code: Select all

$sql="SELECT sum(".$column."), RptDist  FROM distreport03 where RptDist  = 'smith' and RptDist  ='gardner' GROUP BY RptDist";

hope this helps....
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

I tried that. I figured maybe I wasn't calling the fields correctly ... but this is what I get ...

,AST210 :
,AST210 :
,AST210 :
,AST210 :
,AST210 :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,AST210Sales :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251 :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :
,TBC2251Sales :

with this code ...

Code: Select all

<?php 

$connection=mysql_connect("localhost","username","password") or die('Could not connect to the database server'); 
$db = mysql_select_db("tanaka-ism_com", $connection) or die ("Unable to select database."); 

$columns = array("AST210","AST210Sales","TBC2251","TBC2251Sales"); 
$distro=array(); 
$totals=array(); 

// should change the columns and distributors to reflect the actual field name in your table for these fields... 
foreach ($columns as $column) 
{ 
   $sql="SELECT sum(".$column."), RptDist  FROM distreport03 where RptDist  = 'smith' and RptDist  ='gardner' GROUP BY RptDist"; 
	$result=mysql_query($sql); 
   $rows=mysql_fetch_array($result); 

   while ($rows=mysql_fetch_array($result)) 
   { 
      // do this if you are wanting to save these entries for later... 
      $totals[] = $row['sum('.$column.')']; 
      $distro[]=$row['RptDist']; 
   } 
} 

// then echo it out like this : 
foreach($columns as $column) 
{ 
      for($i=0; $i<=count($columns); $i++) 
      { 
         echo $distro[$i].','.$column.' : '.$totals[$i]; 
         echo '<br />'; 
      } 
} 
?>
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

your code should work... try putting some error handling in your code though..

as in change your $sql to this :

Code: Select all

$sql="SELECT sum(".$column."), RptDist  FROM distreport03 where RptDist  = 'smith' and RptDist  ='gardner' GROUP BY RptDist" or die(MySQL()); 

$result=mysql_query($sql) or die(MySQL_Error()); 
$rows=mysql_fetch_array($result) or die(MySQL_Error());
and see if you get an error.

try changin the column fields to Integer instead of char.

other than that, your code should work, just don't see why it isn't unless the fields are empty...
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

just a blank page now ... some of the fields are blank, but most of them have data.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

god i'm retarded sometimes... #1, i was putting $row instead of $rows..

#2, it was wrong all the way through.. try this lol.. promise it works, i tested this time (sorry was at my sister's house doing this without any means of testing)

Code: Select all

<?php
$connection=mysql_connect("localhost","username","password") or die('Could not connect to the database server'); 
$db = mysql_select_db("tanaka-ism_com", $connection) or die ("Unable to select database."); 

$columns = array('AST210','AST210Sales','TBC2251','TBC2251Sales'); 
$distro=array('Gardner', 'Smith'); 
$cols = array();
$distrib = array();
$total = array();

foreach ($columns as $column) 
{
   	for($i=0; $i<=count($distro); $i++)
   	{
   		$sql="SELECT sum(".$column.") FROM distreport03 where RptDist  = '".$distro[$i]."'"; 
   		$result=mysql_query($sql); 
   		$rows=mysql_fetch_assoc($result); 
   		$cols[] = $column;
		$distrib[] = $distro[$i];
		$total[] = $rows['sum('.$column.')'];
	}
} 

for($i=0; $i<=count($total); $i++) 
{
	echo $distrib[$i].','.$cols[$i].' : '.$total[$i]; 
	echo '<br />';
}
?>
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

...
Last edited by nwoutback on Mon Dec 01, 2003 1:37 pm, edited 1 time in total.
nwoutback
Forum Newbie
Posts: 17
Joined: Mon Nov 24, 2003 1:50 pm

Post by nwoutback »

Thank you so much for your help ... it adding up per distributors perfectly!!!

One more question ... it seems to add an extra instance of each unit, and the ,: as welll ... any 'if is_null' type thing to get rid of that?

there are 3 records currently in the DB ...

Gardner ...
Gardner ...
Smith ...

Gardner,AST210 : 1
Smith,AST210 : 1
,AST210 :
Gardner,AST210Sales : 4
Smith,AST210Sales : 4
,AST210Sales :
Gardner,TBC2251 : 7
Smith,TBC2251 : 9
,TBC2251 :
Gardner,TBC2251Sales : 9
Smith,TBC2251Sales : 3
,TBC2251Sales :
, :
Post Reply