Page 1 of 2
HELP - ARRAY Problem
Posted: Fri Nov 28, 2003 5:31 pm
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();
}
?>
Posted: Fri Nov 28, 2003 10:03 pm
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...
Posted: Sat Nov 29, 2003 10:59 am
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
Posted: Sat Nov 29, 2003 12:58 pm
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
Posted: Sat Nov 29, 2003 1:21 pm
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 />';
}
}
?>
Posted: Sat Nov 29, 2003 2:11 pm
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 :
Posted: Sat Nov 29, 2003 2:23 pm
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.
Posted: Sat Nov 29, 2003 2:31 pm
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?
Posted: Sat Nov 29, 2003 11:00 pm
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....
Posted: Sun Nov 30, 2003 3:01 am
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 />';
}
}
?>
Posted: Sun Nov 30, 2003 12:06 pm
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...
Posted: Sun Nov 30, 2003 1:03 pm
by nwoutback
just a blank page now ... some of the fields are blank, but most of them have data.
Posted: Sun Nov 30, 2003 6:49 pm
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 />';
}
?>
Posted: Mon Dec 01, 2003 1:02 pm
by nwoutback
...
Posted: Mon Dec 01, 2003 1:36 pm
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 :
, :