...

View Full Version : Help with SQL query



adammc
08-29-2006, 05:18 AM
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 ?

Fumigator
08-29-2006, 05:50 AM
What you do is a "group by" on just the month of the date. You can get just the month by calling the date_format() function.


$query = "
SELECT date_format(stats.date, '%M'), count(*)
FROM stats
GROUP BY date_format(stats.date, '%M')
";


That query produces the month and the row count for that month in an array. Use a foreach to echo them out in your table.

adammc
08-30-2006, 12:51 AM
Hi, thanks for your reply :)

I was unable to work out how to get your idea to work, so I tried a similiar 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 ?

guelphdad
08-30-2006, 01:52 AM
did you run the query above? the main problem with your query is that you are going to run your query 12 separate times which is a very poor use of resources.

adammc
08-30-2006, 02:14 AM
yes I ran it, I echoed "$query" thats how I worked out that my code wasnt going to work.

It inserts the correct data for all months up to 09, then it stuffs up cause instead of looking for 12 its looking for '012'

Fumigator
08-30-2006, 02:22 AM
It sounds like your date field is not a "date" field, but rather a text field (char, varchar, etc). If this is true then you're right, the "group by" method will not work. And, I would strongly encourage you to change it to a "date" field. You can take advantage of MySQL's date formatting if you do.

It will also allow you to avoid the sticky mess of "08" "09" "010" "011" "012" as you have described. It's something you definitely want to avoid, lemme tell you.

adammc
08-30-2006, 02:28 AM
No, my date column is correct:
$SQL = $SQL . " Date DATETIME NOT NULL default '0000-00-00 00:00:00', ";

Thanks for your help guys, I have achieved the desired result like this:




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

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%'";



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum