Hello:
Show the information you need could be done in two steps:
-First: Extract the data from the table calculating the ammount of
registrations each month:
Let's take this table:
#
# Table structure for table 'table_test'
#
CREATE TABLE table_test (
id int(3) unsigned NOT NULL auto_increment,
date date default NULL,
suscriptions int(10) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table 'table_test'
#
INSERT INTO table_test VALUES("1", "2003-07-01", "2");
INSERT INTO table_test VALUES("2", "2003-07-02", "5");
INSERT INTO table_test VALUES("3", "2003-07-05", "10");
INSERT INTO table_test VALUES("4", "2003-08-01", "50");
INSERT INTO table_test VALUES("5", "2003-08-02", "30");
To extract the data in a way it's easy to calculate the information we
are looking for, we need an SQL sentence like this:
select id, date, sum(suscriptions) as total from table_test group by
month(date)
With the data I provide, this shows:
id date total
1 2003-07-01 17
4 2003-08-01 80
So you have two records with the total for each month.
-Second: Show the data calculating the accumulated field
<?php
$db=mysql_connect("localhost","root","");
mysql_select_db("groupped",$db);
$tb=mysql_query("select id, date, sum(suscriptions) as total from
table_test group by month(date)");
$rows=mysql_fetch_array($tb);
$acc=0;
while ($rows)
{
$total=$rows['total'];
$date=$rows['date'];
$acc+=$total;
echo "$date $total $acc<br>";
$rows=mysql_fetch_array($tb);
}
mysql_close($db);
?>
This script iterates through all the rows and calculates the
accumulated in the $acc var. There is no need to store this value in
the table.
I hope this is what you were looking for, if not, don't hesitate to
request for any clarification.
Regards. |
Request for Answer Clarification by
catullus13-ga
on
09 Aug 2003 12:17 PDT
One area where I'm still confused:
On your example, you have the total coming from the database field
"subscriptions," which is an integer. On the table I'm using, I have
to obtain the total using SELECT COUNT(*) since there is no integer
field in the table to obtain a count from.
How would this affect the example you gave me? Thanks.
|
Clarification of Answer by
joseleon-ga
on
09 Aug 2003 12:23 PDT
Hello:
If you post the structure of the table you are using, I will adapt
the example to your table.
Regards.
|
Clarification of Answer by
joseleon-ga
on
09 Aug 2003 12:36 PDT
Hello:
At first sight you can change the SQL sentence to this:
select *,count(*) as total from subscriptions group by month(thedate)
The key is to group by the month and count(*) will show the records of
each month as a field called total.
Regards.
|
Request for Answer Clarification by
catullus13-ga
on
09 Aug 2003 12:52 PDT
Thanks for your help so far. I can't access the table from home, but
if memory serves, these are the basic fields:
email varchar(254) PRIMARY KEY,
regdate int(11),
fname varchar(254),
lname varchar(254),
and so on...
The regdate is added using the date() function from the form.
I hope this info is what you need. Let me know if you need
clarification. Thanks again.
|
Clarification of Answer by
joseleon-ga
on
09 Aug 2003 13:02 PDT
Hello:
If you store the date as unix time, then you have to convert it back
to a datetime field to be able to use the month function, so your SQL
is something like this:
select *, count(*) as total from your_table
group by month(from_unixtime(regdate))
Once you send me the complete table structure, including it's name, I
will adapt the full script.
Regards.
|
Request for Answer Clarification by
catullus13-ga
on
09 Aug 2003 13:14 PDT
Hello,
Here's the query I've used to obtain the number of users signing up
each month/year, as much I can recall it:
SELECT month(from_unix(regdate)) as mo, year(from_unix(regdate)) as
yr,
COUNT(*) as enews
FROM wnemail
WHERE regdate > 0
GROUP BY mo,yr
ORDER BY mo DESC yr DESC;
So, I'm getting the total number of users signing up for the
newsletter by month/year. My problem is getting the running total of
all accruing signups by month/year so that if it is 10,000 in August,
then it was 9,500 in July, and so on. Thanks.
|
Clarification of Answer by
joseleon-ga
on
11 Aug 2003 00:06 PDT
Hello:
There should be no problem with such table structure, just send me
the complete table structure and table name and I will return back the
complete script.
Regards.
|
Request for Answer Clarification by
catullus13-ga
on
11 Aug 2003 03:51 PDT
Here's the complete table description:
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| email | varchar(70) | | PRI | | |
| activity | varchar(254) | YES | | NULL | |
| function | varchar(100) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| company | varchar(50) | YES | | NULL | |
| regdate | int(11) | YES | | NULL | |
| private | varchar(50) | YES | | NULL | |
| format | varchar(4) | | | | |
| test_flag | char(1) | | | | |
+-----------+--------------+------+-----+---------+-------+
And here's the query I'm using to get the monthly total of email
signups, ordered by mo,yr and grouped by mo,yr:
$query = "SELECT month(from_unixtime(regdate)) as mo,
year(from_unixtime(regdate)) as yr,
COUNT(*) as unsubtotal
FROM wnemail_remove
GROUP BY mo, yr
ORDER BY yr DESC, mo DESC";
$result = mysql("wnews", $query);
Thanks for your help!
|
Clarification of Answer by
joseleon-ga
on
11 Aug 2003 04:40 PDT
Hello:
Thanks for the table structure, and here is the script, I have used
your query because it's ok, no problem with it:
<?php
$db=mysql_connect("localhost","root","");
mysql_select_db("a",$db);
//First, calculate the total of subscriptions
$tb=mysql_query("SELECT month(from_unixtime(regdate)) as mo,
year(from_unixtime(regdate)) as yr, COUNT(*) as unsubtotal FROM
wnemail_remove GROUP BY mo, yr ORDER BY yr DESC, mo DESC");
$rows=mysql_fetch_array($tb);
$acc=0;
while ($rows)
{
$unsubtotal=$rows['unsubtotal'];
$acc+=$unsubtotal;
$rows=mysql_fetch_array($tb);
}
//Then, shows the data performing the calculations
$tb=mysql_query("SELECT month(from_unixtime(regdate)) as mo,
year(from_unixtime(regdate)) as yr, COUNT(*) as unsubtotal FROM
wnemail_remove GROUP BY mo, yr ORDER BY yr DESC, mo DESC");
$rows=mysql_fetch_array($tb);
while ($rows)
{
$mo=$rows['mo'];
$yr=$rows['yr'];
echo "$mo, $yr, $acc<br>";
$unsubtotal=$rows['unsubtotal'];
$acc-=$unsubtotal;
$rows=mysql_fetch_array($tb);
}
mysql_close($db);
?>
First, calculates the total of subscriptions to the present time,
stored in $acc, and then shows the data and performs the needed
calculations to show the subscriptions accumulated until an specified
month. There is no need to store the results in a separate table
unless you are going to store a HUGHE amount of data and the report is
going to be executed a lot of times, but if you want, I will post such
script. You decide.
I hope this is what you were looking for, in any case, don't hesitate
to request for any clarification, we are here to help you.
Regards.
|
,
0 Comments
)