Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help with SQL query

    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:

    Code:
    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)

    PHP Code:
    <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 ?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.
    PHP Code:
    $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.

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    PHP Code:
    $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 ?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #5
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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'

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #7
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    PHP Code:
        
    $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%'"


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •