Page 1 of 1

comparing results from MySQL table with PHP

Posted: Tue Jul 25, 2006 4:54 am
by hairyjim
Hi all,

I have a tricky problem that I am sure that you guru'swill think is fairly straight forward.

I have a table of Actions with a Time Stamp and a support ID. These were imported from another DB which only allowed 250 chars per line so many of the actions are actually meant to be 1 action it is just the limitation forced the user to enter new lines and therefore create a new action (new record).

What I want to do is to grab the records in the DB based on the support ID which I know how to do, but then I want to start with the very first entry returned (sorted on the timestamp) get its timestamp and compare it with the preceeding records to see if the next entry was enetered wihtin 600 seconds (10 minutes) of the currently returned result. If it was then concatenate the action decription and insert them into the new table I have ready and waiting.

So in pseudo.

Grab all the results based on the support ID.

Get the timestamp of the first returned record.

Go to the next result in the set compare its timestamp with the previous record.

If the difference is less than 600 seconds then concatenate the action strings, then step to the next record and repeat until the timestamp difference is greater than 600 seconds.

If the difference is greater than 600 then insert the record into the new table. Skip any records that have already being concatenated and move to the next record and repeat the timestamp check process until there are no more results.

Hmm sounds easy when I say it like this but I have tried to do this and I have gotten in rather a mess. Im not going to post it since it does not work and it looks like the code of a small child :wink:

Please someone help me out here, im lost in a sea of arrays, loops and debug echo statements.

Posted: Tue Jul 25, 2006 5:50 am
by jamiel
Does this point you in the right direction?

Code: Select all

$sql = "SELECT id, timestamp FROM table";
    $result = mysql_query($sql);
    $rows =  mysql_fetch_assoc($result);
    $timestamp = 0;

    while ($rows) {

        $row = array_pop($rows);
        if ($row['timestamp'] < $previousTimestamp + 600)
            doStuff();
        $previousTimestamp = $row['timestamp'];
    }

Posted: Tue Jul 25, 2006 5:54 am
by hairyjim
Thanks for the reply - I will check this out and see where I get too.

Will let you know what happens.

Posted: Tue Jul 25, 2006 6:29 am
by hairyjim
Hmm sorry. Would that actually work?

I mean array_pop will remove the last record in the array and return it which is fine for the check process but I actually want to check the next element in the array.

So element 0 is compared to element 1, 2, 3... until one of the comparisons comes back false and when it does insert the record into the DB (either this be concatenated or not), then move onto the record which produced the false return and start checking from that record.

So lets say record 0 is checked against record 1.

Record 1 was created wihtin 600 seconds of record 0 so I concatenate the descriptions.

I then move on and compare record 0 with record 2 and do the same check. This comes back false. So I insert the new action with the concatenated descritpion into a new record in a new table.

Then I go back and start at Record 2 (since 0 & 1 were merged) and check this against record 3 and so on until no more records are in the result set.

Perhaps I have misunderstood what you posted!

Posted: Tue Jul 25, 2006 6:43 am
by jamiel
What format is your timestamp? I'll knock up the code.

Posted: Tue Jul 25, 2006 8:11 am
by hairyjim
Hi Jamiel,

The timestamp is just in this format: 1067297633 created using the strtotime() function.

James

Posted: Tue Jul 25, 2006 10:56 am
by hairyjim
Can someone help me out here Im on the remaining few strands of hair.... :evil:

Posted: Tue Jul 25, 2006 11:09 am
by RobertGonzalez
Maybe you can do something like...

Code: Select all

<?php
$array_total = count($array_to_loop);
$counter_current = 0;
$counter_next = 1;

while ($current_counter < $array_total)
{
    if ($array_to_loop[$counter_next]['timestamp'] - $array_to_loop[$counter_current]['timestamp'] < 600)
    {
        // Time difference is less than 600, do what you will
    }

    $counter_current++;
    $counter_next++;
}
?>
This is only a guide. I am sure there is more that needs to be included, but the logioc is what I was shooting for.

EDIT | Added counter increments.

Posted: Tue Jul 25, 2006 11:28 am
by jamiel

Code: Select all

$sql = "SELECT * FROM table ORDER BY `support_id`";

$result = mysql_query($sql, $dbHandle);

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

    if (is_array($primaryRecord)) {
        if ($row['Timestamp'] < ($primaryRecord['Timestamp'] + 600)) {
            $primaryRecord['Description'] .= ' ' . $row['Description'];
        } else {
            $sql = "INSERT INTO new_table (new_description) VALUES ('{$primaryRecord['Description']}')";
            mysql_query($sql, $dbHandle);
            unset($primaryRecord);
        }
    } else {
        $primaryRecord = $row;
    }
}
Untested. Your column names will need to match my array keys. You can insert additional data obviously, but the data will be that of the first action we started that had fell out of the 600 seconds loop other than the description which will have the descriptions of the previous records concatenated.

Posted: Wed Jul 26, 2006 6:36 am
by hairyjim
Thanks for both replies.

I worked with the Jamiel response since he offered the initial advice and effort.

I am currently working with this Jamiel, now you have put it down quite plainly in front of me I completly understand what I should have been doing, lordy the garbage I was working with was somewhere near to what you have done but I was missing the primaryrecord steps you took and worked with a single array of results...needless to say I got rather lost and confused.

Anyway yes I tweaked it a little and have nearly got it doing exactly what I am expecting, I will let you know when I get this complete but I just wanted to say thanks you have pointed me down the path of enlightenment 8)

Thankyou!