[RESOLVED] Help with SQL query

[adammc]

Hi Guys,

I am trying to fill a table with data my from MYSQL DB.
I have created a basic affiliate program script and am trying to do the code to display the affiliate stats for each month (traffic)

I am hoping to get The html table to look like this:

Jan Feb Mar April May ....
55 66 5 45 67

My DB table looks like this:

TABLE stats
    	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ";
    	Affiliate_ID VARCHAR(8) NOT NULL, ";
   	Date DATETIME NOT NULL default '0000-00-00 00:00:00', ";
   	IP VARCHAR(20) NOT NULL, ";
    	Referer VARCHAR(150) NOT NULL, ";

I am having trouble working out a way to count the number of hits that an affiliate has sent and dropm them into the right column in my html table.

I assume I need to use something like the 'foreach' function?
I have got this far, it doesnt work - actually the way it is now... I would have to do 12 of those queries to echo into my table(1 for each month)

<strong><span class="bluelarge">Traffic Stats - 2006</span></strong>
<br /><br />
<table width="80%" border="0" cellspacing="3" cellpadding="3" class="1">
  <tr>
  <th align="center"><span class="maintext">Jan</span></th>
  <th align="center"><span class="maintext">Feb</span></th>
  <th align="center"><span class="maintext">Mar</span></th>
  <th align="center"><span class="maintext">April</span></th>
  <th align="center"><span class="maintext">May</span></th>
  <th align="center"><span class="maintext">June</span></th>
  <th align="center"><span class="maintext">July</span></th>
  <th align="center"><span class="maintext">Aug</span></th>
  <th align="center"><span class="maintext">Sept</span></th>
  <th align="center"><span class="maintext">Oct</span></th>
  <th align="center"><span class="maintext">Nov</span></th>
  <th align="center"><span class="maintext">Dec</span></th>
  </tr>
   <tr>

<?php

// This script retrieves the traffic stats from the stats table



// Connect to the db.
require_once ('includes/mysql_connect.php');

// make the query to get the postings
$query = "SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=$_SESSION[affiliate_id] AND date LIKE '2006-08%'"; 

// run the query
$result = @mysql_query ($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 

// get the number of rows
$num = mysql_num_rows($result);


while($row=mysql_fetch_array($result)) 
   {

echo '<p>',$query;

echo "<td align=\"center\" class=\"row1\"><span class=\"link\">$num</span></td>";


}  //end of while statement


// Free up the resources.	
	mysql_free_result ($result); 	

// close the database connection 
  	mysql_close($dbc); 

?>


</tr>
  </table>

Can anyone please help ?

[Roger_Ramjet]

This is one that you definitely need to use GROUP BY and the Aggregate Functions for

You will have to also use date functions to allow grouping by months - converting each date into it's month and year.

$sql = "SELECT Affiliate_ID,Year(Date) as year, Month(Date) as month, Count(ID) as count
FROM  stats 
WHERE Affiliate_ID=$_SESSION[affiliate_id] 
GROUP BY Affiliate_ID, year, month

That will give you the count for each month for that affiliate, omit the where clause for all affiliates.

[adammc]

Hi, thanks for your reply 🙂

I was unable to work out how to get your code to work, so I tried another approach that kinda worked.

$month = 1;
while($month < 13){

// make the query to get the postings
$query = "SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=$_SESSION[affiliate_id] AND date LIKE '2006-0$month%'";

// run the query
$result = @mysql_query ($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

// get the number of rows
$num = mysql_num_rows($result);

echo '<p>',$query;

echo "<td align=\"center\" class=\"row1\"><span class=\"link\">$num</span></td>";

$month++;
} 

However, because my date format is being stored like:
2006-08-16 14:34:12

I added an extra '0' into the query to combat this, however this has caused a problem when it is looking for the 10,11 & 12th months, cause the query is now doing this:

SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=2 AND date LIKE '2006-010%'

SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=2 AND date LIKE '2006-011%'

SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=2 AND date LIKE '2006-012%'

How can I get it to drop the zero when the month is 10,11,12 ?

[ClarkF1]

Right at the beginning inside the while loop you could add a second variable and remove the 0 from the query

while($month < 13){
$mon = $month;
if ($month < 10)
$mon = "0".$month;

// make the query to get the postings
$query = "SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=$_SESSION[affiliate_id] AND date LIKE '2006-$mon%'"; 

[adammc]

Clarke, thank you so much.
It works a treat 🙂

[Roger_Ramjet]

So what was the error generated by my query?? Some simple syntax problem no doubt, like misspelling a column name, etc.

What you are currently doing is stupid, running 12 queries when it can all be done with one query.