HELP - ARRAY Problem
Moderator: General Moderators
HELP - ARRAY Problem
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();
}
?>
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();
}
?>
mchaggis wrote:Ok, your problem is with the SQL...
Try something like this (had to gues the cols tho)
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?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(); } ?>
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
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
<?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
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 />';
}
}
?>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?
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?
then you have to change your query from this :
to this...
hope this helps....
Code: Select all
$sql="SELECT sum(".$column."), distributors FROM distreport03 where distributors = 'smith' and distributors ='gardner' GROUP BY distributors";Code: Select all
$sql="SELECT sum(".$column."), RptDist FROM distreport03 where RptDist = 'smith' and RptDist ='gardner' GROUP BY RptDist";hope this helps....
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 ...
,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 />';
}
}
?>your code should work... try putting some error handling in your code though..
as in change your $sql to this :
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...
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());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...
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)
#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 />';
}
?>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 :
, :
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 :
, :