Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy SQLite: Fixing the time/date in guestbook

    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 Code:
    <?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;
    }
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Last edited by saxchick1; 09-25-2011 at 02:59 AM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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:
    Code:
    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
    Code:
        $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Feb 2009
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just to make sure I am doing this right.

    Code:
    <?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;
    }
    ?>

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •