How to save a date from php to SQL table?

Question

i have a php file that works OK. That php makes recursive search in a local folder, and calculates the last modification DATE of those files. My question: Every time that i open that php file i want that date to be stored in a SQL table. -i have created a table with 2 columns (ID, date) where ID=1 then go and update the date field.

but how can i call FROM php file to save the date into sql field?

My php file:

<?php
// LAST FILES UPDATE
function getAllFiles($directory, $recursive = true) {
     $result = array();
$handle =  opendir($directory);
     while ($datei = readdir($handle))
     {
          if (($datei != '.') && ($datei != '..'))
          {
               $file = $directory.$datei;
               if (is_dir($file)) {
                    if ($recursive) {
                         $result = array_merge($result, getAllFiles($file.'/'));
                    }
               } else {
                    $result[] = $file;
               }
          }
     }
     closedir($handle);
     return $result;
}

function getHighestFileTimestamp($directory, $recursive = true) {
     $allFiles = getAllFiles($directory, $recursive);
     $highestKnown = 0;
     foreach ($allFiles as $val) {
          $currentValue = filemtime($val);
          if ($currentValue > $highestKnown) $highestKnown = $currentValue;
     }
     return $highestKnown;
}

echo '<div align="center" style=" padding-top:5px; margin-top:5px; border-top:dotted #777; border-width:1px;">Last Update Date:<br>';

date_default_timezone_set('Europe/Athens');
setlocale(LC_ALL, array('el_GR.UTF-8','el_GR@euro','el_GR','greek'));
echo strftime('%d %b %Y, %H:%M:%S', getHighestFileTimestamp('../'));
echo '</div>';

$host="localhost"; // Host name
$username="..."; // Mysql username
$password="..."; // Mysql password
$db_name="..."; // Database name
$tbl_name="..."; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql="UPDATE $tbl_name SET date='HUMMMM HERE HOW TO PULL THE VALUE?' WHERE id='1'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
}
else {
echo "ERROR";
}

?>

rizalp1 · Accepted Answer · 2012-09-27 20:37:58Z

1

Try something like this:

// use the function to calculate highest timestamp
$highestdate = getHighestFileTimestamp("../");

// Insert the highest timestamp into the db
$sql= sprintf("UPDATE $tbl_name SET date='%s' WHERE id='1'",
$highestdate);

--EDIT-- merged with Hiroto's answer

You can use these other styles to concatenate string constants with variables

$sql = "UPDATE {$tbl_name} SET date='{$date}' WHERE id='{$id}';";

Or, you could use concatenation.

$sql = "UPDATE " . $tbl_name . " SET date='" . $date . "' WHERE id='1';";

edited Sep 27, 2012 at 20:37

answered Sep 27, 2012 at 20:26

rizalp1

6,5942 gold badges20 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Amelia Over a year ago

You've added one variable with a format function, and the other one directly (to the string).

rizalp1 Over a year ago

I thought the question wanted '1' for the id. I don't see an $id variable anywhere :)

rizalp1 Over a year ago

@Hiroto Also, not sure where the $date is coming from??

rizalp1 Over a year ago

I'm basing my code off the code that was posted on the question.

Amelia Over a year ago

$date is implied to be the string he wants to insert via variable name.

|

Amelia · Accepted Answer · 2012-09-27 20:23:16Z

0

$sql = "UPDATE {$tbl_name} SET date='{$date}' WHERE id='{$id}';";

Or, you could use concatenation.

$sql = "UPDATE " . $tbl_name . " SET date='" . $date . "' WHERE id='1';";

answered Sep 27, 2012 at 20:23

Amelia

2,9712 gold badges27 silver badges39 bronze badges

5 Comments

nemmy Over a year ago

seems to not working, i have replacent that line with yours but nothing happenes to the table. it says succefull updated but has 0 values, sorry i dont have many knwoledge what do you mean by that. All i can see is that there is a function getHighestFileTimestamp and then that line gives me the exact date in php file, echo strftime('%d %b %Y, %H:%M:%S', getHighestFileTimestamp('../')); and that date i want to store in SQL.

Amelia Over a year ago

set $date to the date you want. in this case, $date = strftime('%d %b %Y, %H:%M:%S', getHighestFileTimestamp('../'));

nemmy Over a year ago

i love you!!! that worked...!!! but i have the date with chiness stradge letters in the sql... i need to make any encoding??? hummm

Amelia Over a year ago

mysql_query("SET NAMES 'UTF-8';");

nemmy Over a year ago

threat closed. SOLVED. thnk you Hiroto! youhoooouuuu

Collectives™ on Stack Overflow

How to save a date from php to SQL table?

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related