How to write a query for PHP and MySQL?

Simple PHP/MySql Question Regarding Arithmetic Function

• I'm a very novice PHP/MySql programmer, and I'm building a page that will display results from a MySql database, using PHP. It's quite elementary, but I've developed a brainlock over it. Here are the facts: The MySql database logs in user registrations for a daily email newsletter, with the primary key as email address and also has a date field. Using PHP, I've created an HTML table that displays various info from the table, but the person I'm doing this for wants to show the total number of registrations by month. That is, if there are 10,100 total registrations in July 2003, and then 500 more signed up in August, they want: Aug. 2003 10,600 July 2003 10,100 and so on in descending order. I've made a query that gives the total number of new email signups by month, using a From_Unix(regdate) function. And I've done a SELECT COUNT(*) on the table to get the current total of email signups. But my programming puzzle is how to write the PHP code that will put the current total in the table, and then display what the total was for each preceeding month. I know that one possible way to do it is to take the current total as August, and then subtract the number who signed up after July to get the total number for July, and repeat that process throughout the table. Can someone please help me with this, since I'm pretty clumsy with loops and arrays? If you need more info, let me know. Thanks.

• Answer:

  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.