Joomla - Updating a table field with variable from another SELECT query

Question

My site runs in Joomla 1.5.26 and I am using a SELECT query to convert a filename path to a last modified date and print the contents n the page:

$db =& JFactory::getDBO();
$query = "
SELECT `filename`
FROM `#__joomlatable`
WHERE `filename` != '';
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
if (file_exists($o[filename])) {
    echo date ("F d Y H:i:s.", filemtime($o[filename]));
    $test = date ("F d Y H:i:s.", filemtime($o[filename]));

}
}

As you can see I am printing the various dates on the screen and at the same time assigning the dates to the value $test.

My table also contains a field called date with empty values. What I would like to do is populate the values of %test inside the date field values inside the table.

So after the first piece of code I write:

$db2 =& JFactory::getDBO();
$query2 = "
UPDATE `#__joomlatable`
SET `date` = '$test'
WHERE `filename` != '';
";
$db2->setQuery($query2);
$options2 = $db2->loadAssocList();

but what it does is filling the date column in all records with the same date value which seems to be the last date value requested from the first query. I think it needs to be in a loop but due to my inexperience with PHP/Mysql I can't think of anything.

Can you please help?

Answer 1

Indeed you need to do a loop over your results and update each file record separately.

Here is how the code might look like:

$db =& JFactory::getDBO();
$query = "
SELECT `filename`
FROM `#__joomlatable`
WHERE `filename` != '';
";
$db->setQuery($query);
$options = $db->loadAssocList();

$fileInfo = array();

//store in fileInfo the modified time for each file
foreach ( $options as $o ) {
    if (file_exists($o['filename'])) {
        $fileInfo[ $o['filename'] = filetime( $o['filename'] );
    }
}

//now for each file we found we store the modified time in the database
foreach( $fileInfo as $filename => $time ){
    $query2 = "
    UPDATE `#__joomlatable`
    SET `date` = '$time'
    WHERE `filename` = '$filename';
    ";
    $db2->setQuery($query2);
    $options2 = $db2->query(); 
}