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?

[henry0]

Try something around those lines
<<<<

$query_rows = "SELECT Affiliate_ID FROM stats WHERE Affiliate_ID=$_SESSION[affiliate_id] AND date LIKE '2006-08%'";

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

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

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

$affiliate_id=$row{"affiliate_id"];

//echo "<td align=\"center\" class=\"row1\"><span class=\"link\">$num</span></td>";
echo( ("\t\t <td><div class=\"row1\">" . "$affiliate_id" ) . "</div></td>\n");
} //end of while statement

>>>>

[eelixduppy]

Just so this doesn't cause any problems in your script, the line:

$affiliate_id=$row{"affiliate_id"];

Should look more like this:

$affiliate_id=$row["Affiliate_ID"];

;)

[henry0]

Thanks
Good eyes :)

[adammc]

Hi, thanks for your reply :)

I have tried another approach that kinda worked.

[php]
$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++;
}
[/php]

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?

[adammc]

I got it sorted :)