SQLite: Fixing the time/date in guestbook

[saxchick1]

I am using SQLite for my guestbook and right now my time is not in the right format, right now it reads something like 21:03, when I would like it to read something like 1:00PM. Any help is welcomed! Thanks!


<?php

////////////////////////////
//Part 1: Script Setup
////////////////////////////
ob_start();

//We need to strip the slashes that have been added to our POST data!
if (ini_get('magic_quotes_gpc')) {

function array_clean(&$value) {
$value = stripslashes($value);
}
//php 5+ only
array_walk_recursive($_GET, 'array_clean');
array_walk_recursive($_POST, 'array_clean');
}


// Cleans text of all bad characters
function sanitize_text(&$text){
//Delete anything that isn't a letter, number, or common symbol - then HTML encode the rest.
trim(htmlentities(preg_replace("/([^a-z0-9!@#$%^&*()_\-+\]\[{}\s\n<>:\\/\.,\?;'\"]+)/i", '', $text), ENT_QUOTES, 'UTF-8'));
}






////////////////////////////
//Part 2: Connect to DB
////////////////////////////

//If the DB file does NOT exist - Create it
if (!is_file("data.sqlite")){
//Open a connection
$dbc = sqlite_open("data.sqlite");
//Create table
$query = "CREATE TABLE guestbook (inputId PRIMARY KEY, inputText TEXT NOT NULL);";
sqlite_query($dbc,$query);

} else {
//Open a connection
$dbc = sqlite_open("data.sqlite");
}



////////////////////////////
//Part 3: Add new comments and show guestbook
////////////////////////////

if (isset($_POST['message'])){
if ($_POST['message']){

//Clean the Message
sanitize_text($_POST['message']);
//Clean the Name
sanitize_text($_POST['name']);

$tid = date("H:i:s m-d-y");

//Create Guest Book log
$mess = "<b>Posted by: <i>{$_POST['name']}</i> on $tid</b><br/><br/>{$_POST['message']}<br/><hr/>";
$query = "INSERT INTO guestbook (inputText) VALUES ('$mess');";
sqlite_query($dbc,$query);
header("Location: {$_SERVER['PHP_SELF']}");
}
}

//Select all the entries
$query = "SELECT inputText FROM guestbook ORDER BY inputId DESC;";
$array = sqlite_single_query($dbc,$query);

//If more than 15 pages
$extrapages = 0;
if(count($array)>15){
$extrapages = floor(count($array)/15);
$extrapages++;
if (count($array)%15 == 0){
$extrapages--;
}
if($_GET['page']){
$num = (int)$_GET['page'] * 15;
for($i=$num;$i<count($array);$i++){
$extra[] = array_pop($array);
}
for($i=0;$i<$num-15;$i++){
$extra[] = array_shift($array);
}
} else {
for($i=15;$i<count($array);$i++){
$extra[] = array_pop($array);
}
}
}

$return_to = $_SERVER['PHP_SELF'];
sanitize_text($return_to);


echo "<table border=\"0\" cellpadding=\"10\" cols=\"50\">"
. "<tr><td><form action=\"$return_to\" method=\"POST\">"
. "<b>Name: </b><input type=\"text\" name=\"name\" /><br/>"
. "<b>Comment:</b><br/><textarea cols=\"30\" rows=\"10\" name=\"message\"></textarea><br/>"
. "<input type=\"submit\" /></form></td></tr>";

if($array && is_array($array)){
foreach ($array as $input){
echo "<tr><td width=\"20\">$input</td></tr>\n";
}
} elseif ($array){
echo "<tr><td width=\"20\">$array</td></tr>";
} else {
echo "<td><tr>Please leave a comment.</td></tr>";
}
echo "</table>";
if ($extrapages != 0){
echo extrapages($extrapages);
}

function extrapages($num){
$to = "<table borders=\"0\" cellpadding=\"10\"><tr><td>";
for($i=0;$i<$num;$i++){
$top = $i+1;
$to .= "<a href=\"?page=$top\">$top</a> ";
}
$to .= "</td></tr></table>";
return $to;
}
?>

[Old Pedant]

You do *NOT* want to change the way dates and times are stored in the DB.

Instead, you want to use PHP code to change the display of the time.

But having said that... I don't see any place in that code where you store any time into the DB *OR* attempt to retrieve a time. So what are you talking about?

The ONLY value you store and retrieve in all that code is a field named inputText.

The text in there is completely dependent on PHP for content, so this has nothing whatsoever to do with your database.

WHY would you store the name and posting time as part of the text, instead of in separate fields??? You just made searching the DB for all posts by one person or, example, all posts less than a week old essentially impossible.

Anyway, it's your code:
$tid = date("H:i:s m-d-y");

http://www.php.net/manual/en/function.date.php

H 24-hour format of an hour with leading zeros 00 through 23
i Minutes with leading zeros 00 to 59
s Seconds, with leading zeros 00 through 59

So it's all PHP and nothing to do with SQL, let alone MySQL.

[saxchick1]

You do *NOT* want to change the way dates and times are stored in the DB.

Instead, you want to use PHP code to change the display of the time.

But having said that... I don't see any place in that code where you store any time into the DB *OR* attempt to retrieve a time. So what are you talking about?

The ONLY value you store and retrieve in all that code is a field named inputText.

The text in there is completely dependent on PHP for content, so this has nothing whatsoever to do with your database.

WHY would you store the name and posting time as part of the text, instead of in separate fields??? You just made searching the DB for all posts by one person or, example, all posts less than a week old essentially impossible.

Anyway, it's your code:
$tid = date("H:i:s m-d-y");

http://www.php.net/manual/en/function.date.php

So it's all PHP and nothing to do with SQL, let alone MySQL.

Is this what I need to do:

$tid = date("h:i:s:A:e m-d-y"); (I just tested this code and I got some what, what I wanted, but no quite what I am looking for).

Also, I am curious, how would I store the name and posting time in separate fields? As I said, I am new to using SQLite and my knowledge of PHP is very little, plus this is my first time I've ever had to make a guestbook.

[Old Pedant]

Well, SQLLite doesn't have DATETIME data type. It only has INT, REAL, TEXT, and BLOB.

But the docs day you can store datetime values in ISO format (YYYY-MM-DD hh:mm:ss) and then use builtin functions to manipulate them as if they were datetime values, so that's probably what I'd do.

So maybe:

CREATE TABLE guestbook (
id PRIMARY KEY,
postedBy TEXT NOT NULL,
whenPosted TEXT DEFAULT CURRENT_TIMESTAMP,
message TEXT NOT NULL);

Then you don't even have to explicitly store the posting time, as SQLLite will automatically put the current date and time into that whenPosted field for you.

So now you would do
$query = "INSERT INTO guestbook (postedBy,message) "
. " VALUES ('{$_POST['name']}','{$_POST['message']}');";
sqlite_query($dbc,$query);

And, yes, the whenPosted field will have a date/time of the form 2011-09-24 20:13:11 in it, but you can then use either PHP or SQLLite functions to convert it to the format you want.

[Old Pedant]

Look here in the docs:
http://www.sqlite.org/lang_datefunc.html

So I take it back. SQLLite doesn't have any functions that will convert the date to the format you want. You'd have to do it in PHP.

[saxchick1]

Just to make sure I am doing this right. :o


<?php

////////////////////////////
//Part 1: Script Setup
////////////////////////////
ob_start();

//We need to strip the slashes that have been added to our POST data!
if (ini_get('magic_quotes_gpc')) {

function array_clean(&$value) {
$value = stripslashes($value);
}
//php 5+ only
array_walk_recursive($_GET, 'array_clean');
array_walk_recursive($_POST, 'array_clean');
}


// Cleans text of all bad characters
function sanitize_text(&$text){
//Delete anything that isn't a letter, number, or common symbol - then HTML encode the

rest.
trim(htmlentities(preg_replace("/([^a-z0-9!@#$%^&*()_\-+\]\[{}\s\n<>:\\/\.,\?;'\"]+)/i",

'', $text), ENT_QUOTES, 'UTF-8'));


CREATE TABLE guestbook (
id PRIMARY KEY,
postedBy TEXT NOT NULL,
whenPosted TEXT DEFAULT CURRENT_TIMESTAMP,
message TEXT NOT NULL);

}






////////////////////////////
//Part 2: Connect to DB
////////////////////////////

//If the DB file does NOT exist - Create it
if (!is_file("data.sqlite")){
//Open a connection
$dbc = sqlite_open("data.sqlite");
//Create table
$query = "CREATE TABLE guestbook (inputId PRIMARY KEY, inputText TEXT NOT NULL);";
sqlite_query($dbc,$query);

} else {
//Open a connection
$dbc = sqlite_open("data.sqlite");
}



////////////////////////////
//Part 3: Add new comments and show guestbook
////////////////////////////

if (isset($_POST['message'])){
if ($_POST['message']){

//Clean the Message
sanitize_text($_POST['message']);
//Clean the Name
sanitize_text($_POST['name']);

$tid = date("YYYY-MM-DD HH:MM:SS.SSS");

//Create Guest Book log
$mess = "<b>Posted by: <i>{$_POST['name']}</i> on

$tid{
$query = "INSERT INTO guestbook (postedBy,message) "
. " VALUES ('{$_POST['name']}','{$_POST['message']}');";
sqlite_query($dbc,$query);
}
}

//Select all the entries
$query = "SELECT inputText FROM guestbook ORDER BY inputId DESC;";
$array = sqlite_single_query($dbc,$query);

//If more than 15 pages
$extrapages = 0;
if(count($array)>15){
$extrapages = floor(count($array)/15);
$extrapages++;
if (count($array)%15 == 0){
$extrapages--;
}
if($_GET['page']){
$num = (int)$_GET['page'] * 15;
for($i=$num;$i<count($array);$i++){
$extra[] = array_pop($array);
}
for($i=0;$i<$num-15;$i++){
$extra[] = array_shift($array);
}
} else {
for($i=15;$i<count($array);$i++){
$extra[] = array_pop($array);
}
}
}

$return_to = $_SERVER['PHP_SELF'];
sanitize_text($return_to);


echo "<table border=\"0\" cellpadding=\"10\" cols=\"50\">"
. "<tr><td><form action=\"$return_to\" method=\"POST\">"
. "<b>Name: </b><input type=\"text\" name=\"name\" /><br/>"
. "<b>Comment:</b><br/><textarea cols=\"30\" rows=\"10\"

name=\"message\"></textarea><br/>"
. "<input type=\"submit\" /></form></td></tr>";

if($array && is_array($array)){
foreach ($array as $input){
echo "<tr><td width=\"20\">$input</td></tr>\n";
}
} elseif ($array){
echo "<tr><td width=\"20\">$array</td></tr>";
} else {
echo "<td><tr>Please leave a comment.</td></tr>";
}
echo "</table>";
if ($extrapages != 0){
echo extrapages($extrapages);
}

function extrapages($num){
$to = "<table borders=\"0\" cellpadding=\"10\"><tr><td>";
for($i=0;$i<$num;$i++){
$top = $i+1;
$to .= "<a href=\"?page=$top\">$top</a> ";
}
$to .= "</td></tr></table>";
return $to;
}
?>

[Old Pedant]

Not even close.

You have the CREATE TABLE using the fields I suggested sitting in the middle of a PHP function. Doing nothing at all except breaking the PHP code.

Tell you what. Ignore my advice. Stick with what works for you.