Results 1 to 3 of 3
  1. #1

    Tough mySQL algorithm


    I'm not sure if it's because this is a really complex problem or if I'm just really tired from programming for 13+ hours, but I have a tough on for you pros. I have a client that sends traffic data to a mysql table on my server. The important fields are:
    CID: Client ID
    Real_Date: Date of data recorded in the format yyyy-mm-dd
    Hour: 0-23
    Hits: How many hits in that hour.

    The problem is, if there were no hits to the client in a given hour, then there will be no record for a given Real_Date, Hour.

    The other piece of data that gets received is the startdate. Assuming the startdate is 2006-10-01, then this field is telling the server that it will be receiving data representative of 2006-10-02 forward. On top of that, if it is transmitting today at 1pm, then it will also represent today's data for the hours 0-13.

    Here's what I have so far:
    PHP Code:
    // delete duplicate records before import
    mysql_query("DELETE FROM SMStats WHERE CID='$ClientID' AND Real_Date > '$STARTDATE'") or die(mysql_error());  

        foreach (
    $pushdata['stat_info'] as $key => $value) {
    $date $value['date'];
    $hour $value['hour'];
    $hits $value['hits'];
    mysql_query("INSERT INTO SMStats (CID, REAL_DATE, HOUR, HITS ) 
    $ClientID', '$date', '$hour', '$hits' )") or die(mysql_error()); 
    // Zero-fill any missing data
    $result mysql_query("SELECT REAL_DATE, HOUR FROM SMStats WHERE WHERE CID='$ClientID
                AND Real_Date > '
    $STARTDATE'  ORDER BY REAL_DATE, HOUR") or die (mysql_error());
    $row mysql_fetch_array($resultMYSQL_ASSOC) ) {
    // if the record doesn't exist, create it

    The zero-fill part is the part I'm not getting. I need to check the hours 0-23 for startdate+1 until the max hour of the max real_date of the data. Then I need to create a record with zero hits if it doesn't exist.

    Any help would be appreciated.

  2. #2
    Join Date
    Sep 2005
    Southern California
    This might be the easy way out, but why don't you just insert every possible record for that given time?

    PHP Code:
    $res mysql_query("INSERT IGNORE INTO `SMStats` (`REAL_DATE`,`HOUR`,`HITS`) VALUES ('$STARTDATE',1,0),('$STARTDATE',2,0),('$STARTDATE',3,0)"); 
    Its obviously not complete, and partially incorrect becuase i dont' know your DB, but you get the idea.

    It is necessary to use INSERT IGNORE so the query does not fail.

  3. #3

    I was close to getting it to work last night, but it was really kludgy. I created a for-next loop and was querying the database on every pass. I never new about INSERT IGNORE.

    Thanks again,

Posting Permissions

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