1 Replies - 2055 Views - Last Post: 24 September 2011 - 09:17 PM Rate Topic: -----

#1 swim_fan08  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 238
  • Joined: 19-February 09

Formatting the time in SQLite

Posted 24 September 2011 - 01:17 PM

I have this guestbook that I am working on using SQLite. Everything works like its suppose to, except for the time. The come out for example 20:06 when I would like it to show such as 1:00 PM. I am new to using SQLite, so any help is much welcomed.

<?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);
   $query = "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, t TIMESTAMP DEFAULT 	

CURRENT_TIMESTAMP);  ";
    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\" name=\"Submit\" value=\"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;
}
?> 




Is This A Good Question/Topic? 0
  • +

Replies To: Formatting the time in SQLite

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Formatting the time in SQLite

Posted 24 September 2011 - 09:17 PM

Hey.

It looks like your time is being generated by PHP, which would be this line:
$tid = date("H:i:s m-d-y"); 


To change the format, see the date() entry in the manual. They've got a complete list of letters you can use to customize the format.


However, there are a few things in that code you should take a look at.

  • The most important would be your sanitize_text function. The way it is designed, it will never have any effect. You pass in a variable and run it through a bunch of functions, but you don't capture the return value. The preg_replace function returns the modified string, but it does not update the original.
    So when you do this:
    sanitize_text($_POST['message']);
    

    the $_POST['message'] element remains unchanged and as dangerous as ever.

    You are going to want to do one of two things: either assign the return value of the functions to the reference parameter, or return the value out of the function and capture it on the outside. Normally I would recommend the latter; it's more in line with how PHP functions normally operate.
    // So either do:
    function sanitize_text(&$text){
        $text = trim(preg_replace("/([^a-z0-9!@#$%^&*()_\-+\]\[{}\s\n<>:\\/\.,\?;'\"]+)/i", '', $text));
    }
    sanitize_text($original);
    // And then continue using $original
    
    // Or do this:
    function sanitize_text($text){
        return trim(preg_replace("/([^a-z0-9!@#$%^&*()_\-+\]\[{}\s\n<>:\\/\.,\?;'\"]+)/i", '', $text));
    }
    $new = sanitize_text($original);
    // And use $new from now on.
    
    // The second one is usually prefered, as it minimizes the chances 
    // that you'll accidentally use an unsanitized value after forgetting 
    // to run it through the function.
    
    

    Also notice that I removed the htmlentities function from there. You should avoid pre-formatting data to a specific output medium before entering it into the database.


  • And that is another point I'd like to make: your database design. You are approaching your database in the wrong manner. A database should store data, not output. By that I mean: if you want to store guestbook entries, you should be storing the raw data needed to create the entries, not the completed HTML. Consider what would happen if, a year from now, your page has become wildly popular and you decide to change the look of the site. If all your guestbook entries are stored in the manner your code does, such an update would be much more difficult that it should be.

    And besides that, relational database are all about data integrity, and the fact is that the three values your guestbook is storing, the name, the time and the message, are all masked in HTML; hidden from the database and it's ability to manage it properly. How would you, for example, search for guestbook entries by a specific poster? Or messages posted between a certain time period? Those are very basic database operations that are damn near impossible in your design.

    So, my suggestion would be to update your table design to something along the lines of:
    CREATE TABLE guestbook (
        entryID INTEGER PRIMARY KEY,
        posterName TEXT NOT NULL,
        message TEXT NOT NULL,
        created INTEGER NOT NULL
    )
    
    

    Then you could insert your data with a query like this:
    INSERT INTO guestbook (posterName, message, created)
    VALUES ('$name', '$message', strftime('%s','now'))
    
    



  • Another issue is the way you fetch the data. Your approach seems to be fetching ALL the data and then trim the result set until you have only those rows you need. This is generally not a great idea, because the database itself does a great job of filtering result sets. All you have to do is LIMIT the selected rows to fit your needs. For instance:
    SELECT posterName, message, created
    FROM guestbook ORDER BY entryID DESC
    LIMIT 15 OFFSET 30
    
    

    This query would fetch 15 rows starting at row 30. In other words, it would give your rows 30-44, or page #3 of a 15 per-page pagination system.

    In your code, you could figure that out fairly easily. Assuming $_GET['page'] provides the page number: 0 indexed, higher values meaning older posts.
    $perPage = 15;
    $offset = (int)@$_GET['page'] * $perPage;
    $sql = "
        SELECT posterName, message, created
        FROM guestbook ORDER BY entryID DESC
        LIMIT {$perPage} OFFSET {$offset}";
    
    

    Now you will only get 15 entries from the database, starting 15 entries down the list for every $_GET['page'] number.


  • Another concern I have regarding your database code is the functions you are using. As far as I understand them, they are SQLite 2 specific functions. That's an old version, and you would do better to move up to version 3. - In fact, I meant to test your code on my machine but I couldn't find the DLLs required to make it work on PHP 5.3.

    My suggestion would be to use PDO instead. It's much more powerful, but can be used fairly simply if you don't want to get into that whole thing. Only three things would have to change: The connection code, the query calls, and the way you read the result sets.
    // Connection code
    $dbc = new PDO('sqlite:data.sqlite');
    if (!$dbc) {
        die("Failed to connect to the SQLite database.");
    }
    
    // Execute a query
    $sql = "SELECT posterName, message FROM guestbook ORDER BY entryID DESC";
    $res = $dbc->query($sql) or die("Failed to execute query!");
    
    // Read the results
    while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
        echo "<b>Poster:</b> {$row['posterName']}<br/>";
        echo "<b>Message:</b> {$row['message']}<br/><hr/>";
    }
    
    

    This if of course just a suggestion, and you can continue using the SQLite2 functions if you prefer it, but I highly recommend using PDO instead. It won't just help you in this case, but if you are using other databases, like MySQL, Postgres, Orace, etc... you will benefit greatly from being familiar with PDO.


  • And I simply must mention the HTML, and the way it's fused into the PHP. That's generally a bad thing. CTphpnwb talks more about this in his turorial on Code Seperation, which I recommend reading.


Let me show you an example of how I would write something like this, in a non-OOP way. It's of course not perfect, but demonstrates some of the things I'm talking about. (I always find it easier to understand things by seeing them in action :))
<?php
ob_start();

/* **********************************************
 * Create HTML templates to use later. Variables
 * that need to be injected are marked like:
 * {{VARIABLE}}, so we can str_replace them later
 * with their proper values.
 * 
 * It's important to separate the PHP from the HTML
 * to make the code more easily readable. Mixing
 * two languages is rarely good, and it can drive
 * you crazy to read code that fuses HTML and PHP
 * so closely together you can barely tell one
 * from the other.
 * *********************************************/
$HTML5_SKELETON = <<<'HTML'
<!DOCTYPE html>
<html>
    <head>
        <title>My Guestbook, or some such.</title>
        <meta charset="UTF-8">
    </head>
    <body>
        {{BODY}}
    </body>
</html>
HTML;

$FORM_ACTION_TPL = <<<'HTML'
<h1>Add a comment</h1>
<form style="width: 550px;" action="{{ACTION}}">
    <b>Name: </b><input type="text" name="name" /><br/>
    <b>Comment:</b><br/>
    <textarea rows="5" cols="20" name="message"></textarea><br/>
    <input type="submit" name="Submit" value="Submit"/>
</form>

HTML;

$COMMENTS_CONTAINER_TPL = <<<'HTML'
<h1>Comments</h1>
<div style="width: 550px;">
{{COMMENTS}}
</div>

HTML;

$COMMENT_POST_TPL = <<<'HTML'
    <div>
        <b>Posted by: <i>{{NAME}}</i> on {{CREATED}}</b>
        <p>{{MESSAGE}}</p>
    </div>
    <hr/>

HTML;

$COMMENT_NOPOST_TPL = <<<'HTML'
    <div>
        <b>Please post a reply!</b>
    </div>
    <hr/>

HTML;

$COMMENT_PAG_TPL = <<<'HTML'
<div style="width: 550px; text-align: center;">
    {{LINKS}}
</div>

HTML;

$COMMENT_PAG_LINK_TPL = <<<'HTML'
<a href="?page={{INDEX}}">{{NUMBER}}</a>

HTML;

$COMMENT_PAG_CURRENT_TPL = <<<'HTML'
<span style="weight: bold;">{{NUMBER}}</span>

HTML;


/* **********************************************
 * Set up the input sanitation. The magic_quotes_gpc
 * feature is practically dead, so you could probably
 * do without that part, but it doesn't hurt I suppose.
 * *********************************************/
if (get_magic_quotes_gpc()) {
    function array_clean(&$value) {
        $value = stripslashes($value);
    }
    array_walk_recursive($_GET, 'array_clean');
    array_walk_recursive($_POST, 'array_clean');
}

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

/* **********************************************
 * Initialize the database.
 * *********************************************/

// Connect to the database
$dbc = new PDO('sqlite:data.sqlite');
if (!$dbc) {
    die("Failed to connect to the SQLite database.");
}

// Check to see if the "guestbook" table exists and create
// it if it doesn't.
$sql = "SELECT COUNT(*) AS 'count' FROM sqlite_master
        WHERE type='table' AND name='guestbook'";
$exists = $dbc->query($sql);

if (!$exists || $exists->fetch(PDO::FETCH_OBJ)->count == '0') {
    $query = "
        CREATE TABLE guestbook (
            entryID INTEGER PRIMARY KEY,
            posterName TEXT NOT NULL,
            message TEXT NOT NULL,
            created INTEGER NOT NULL
        )";
    if (!$dbc->query($query)) {
        die('Failed to create database');
    }

}

/* **********************************************
 * Add comments to the database if needed.
 * Note that there is no need to redirect, like you
 * did in your code. The row is added to the SQLite
 * database here and will become immediately available
 * later when we query it for the comments.
 * *********************************************/
if (isset($_POST['message'], $_POST['name']) &&
    !empty($_POST['message']) && !empty($_POST['name']))
{
    $message = sanitize_text($_POST['message']);
    $name = sanitize_text($_POST['name']);

    $query = "
        INSERT INTO guestbook (posterName, message, created)
        VALUES ('$name', '$message', strftime('%s','now'))";
    if (!$dbc->query($query)) {
        die("Failed to insert comment.");
    }
}

// Print the comment form
echo str_replace('{{ACTION}}', $_SERVER['PHP_SELF'], $FORM_ACTION_TPL);

/* **********************************************
 * Show the comment page.
 * *********************************************/

// Figure out the limits for the SELECT query
$perPage = 2;
$offset = (int)@$_GET['page'] * $perPage;
$sql = "
    SELECT posterName, message, created
    FROM guestbook ORDER BY entryID DESC
    LIMIT {$perPage} OFFSET {$offset}";
$resulth = $dbc->query($sql);

$comments = '';
if($resulth) {
    while ($row = $resulth->fetch(PDO::FETCH_ASSOC)) {
        // Format the data for output
        $name = htmlentities($row['posterName'], ENT_QUOTES, 'UTF-8');
        $message = htmlentities($row['message'], ENT_QUOTES, 'UTF-8');
        $created = date('Y-m-d H:i:s', (int)$row['created']);

        $placeholders = array('{{NAME}}', '{{CREATED}}', '{{MESSAGE}}');
        $values = array($name, $created, $message);

        // Add the post to the $comments string so we
        // can add them all into the container template
        // after we've finished them all.
        $comments .= str_replace($placeholders, $values, $COMMENT_POST_TPL);
    }
}
else {
    // Add the "Please post" to the container.
    $comments .= $COMMENT_NOPOST_TPL;
}

// Add the comments to the container and print them
echo str_replace('{{COMMENTS}}', $comments, $COMMENTS_CONTAINER_TPL);

/* **********************************************
 * Show the paginator: the page numbers at the
 * bottom of the page that you can use to navigate
 * between them.
 * *********************************************/

// Get the number of rows in the database.
$sql = "SELECT COUNT(*) AS 'count' FROM guestbook";
$resulth = $dbc->query($sql);
$numRows = (int) $resulth->fetch(PDO::FETCH_OBJ)->count;

// Calculate the number of pages that are
// available, and find the current page no.
$numPages = ceil($numRows / $perPage);
$currPage = (int)@$_GET['page'];

// Print a <div> with all the numbers, where
// the current page is bold.
$links = array();
$placeholders = array('{{INDEX}}', '{{NUMBER}}');
for ($pageIndex = 0; $pageIndex < $numPages; $pageIndex++) {
    $pageNumber = $pageIndex + 1;
    $values = array($pageIndex, $pageNumber);
    if ($pageIndex != $currPage)
        $links[] = str_replace($placeholders, $values, $COMMENT_PAG_LINK_TPL);
    else
        $links[] = str_replace($placeholders, $values, $COMMENT_PAG_CURRENT_TPL);
}
$links = implode(' | ', $links);
echo str_replace('{{LINKS}}', $links, $COMMENT_PAG_TPL);

/* **********************************************
 * Fetch the output we've created so far and place
 * it into an actual HTML document.
 * *********************************************/

$contents = ob_get_clean();
echo str_replace('{{BODY}}', $contents, $HTML5_SKELETON);
?>


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1