SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2014
    Posts
    48
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Passing time back and forth between PHP and MySQL (Newb question)

    I'm trying to take a time from a form or from a date() function and store it in a simple table and be able to retrieve and display it. I have tried lots of things but can get no sensible results either in the DB itself or displaying.

    At present I have:

    Code:
    { 
    date_default_timezone_set("Pacific/Honolulu");
    $settime=date();
    
      try
      {
    
    	echo  ("Helllllllllllllllllllllllloooooooooooo");
    
        $sql = 'INSERT INTO joke SET
            joketext = :joketext,
            timetest1 = :timeholder';
        $s = $pdo->prepare($sql);
        $s->bindValue(':joketext', $_POST['joketext']);
        $s->bindValue(':timeholder', $settime);
        $s->execute(); 
      }
      catch (PDOException $e)
    and then to read it

    Code:
       <?php foreach ($jokes as $joke): ?>
          <blockquote>
            <p><?php echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8'); ?></p>
            <p><?php 
    		$timestamp = strtotime($joke);
    echo date("Y-m-d H:i:s", $timestamp);
    echo $timestamp;
    echo date("h:i:sa",$joke);
    		 ?></p>
    I have tried storing as INT, TIME, DATETIME in MySQL. I have tried strtotime (which was recommended but seems completely wrong) and various other things that I forget to read the stored time returned from MySQL (which was coming back as something like 835:00:00!).

    At present with field set to TIME I'm getting back right date but the time is 00:00:00. Sorry I am flummoxed :-(

    ALL of initial build (IE for next 4 months at least) will be in Hawaiian time zone so that should make things a little simpler.

    I would be grateful for any pointers.

    I am quite a newb at PHP and VERY at MySQL and I am a refugee from Stack Overflow (nasty people!) Please be gentle :-) (Oh and I have searched)

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    772
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    The structure of your sql is more like what is used for UPDATE. You were also missing date() elements. Give this a try.
    PHP Code:
    <?php
    if(isset($_POST['joketext']))

        
    date_default_timezone_set("Pacific/Honolulu");
        
    $settime=date('Y-m-d H:i:s');
        
        try
        {
            
    $sql "INSERT INTO joke 
            (joketext,timetest1) VALUES
            (:joketext,:timeholder)"
    ;
            
    $s $pdo->prepare($sql);
            
    $s->bindParam(':joketext'$_POST['joketext']);
            
    $s->bindParam(':timeholder'$settime);
            
    $s->execute(); 
        }
            catch (
    PDOException $e
        {
            
    //echo ONLY during testing!!!! 
            
    echo 'Connection failed: ' $e->getMessage(); 
        } 
    }
    ?>

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    If you're storing the current time, you could use NOW() in the query.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2014
    Posts
    48
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    If you're storing the current time, you could use NOW() in the query.
    Thanks for the thought but storing all sorts of user inputted time.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2014
    Posts
    48
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    OK think I have found 90% solution

    Check out https://www.youtube.com/watch?v=pYl4he2dxas. Last few minuted exclusively on MySQL

    Will try and come back in a couple of weeks and dom a thorough little brief. I have wasted hours trying to sort this out.

    BTW the preceding video dealing with Unix epoch time https://www.youtube.com/watch?v=8LLVpUqbRik is also pretty good. You may want to watch that first. Thank you Lynda.com (hope that is OK to say here)

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    772
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what the problem is that you are having. I can post to this page and add new jokes to the DB and jokes are shown. I am basing example on provided code.
    PHP Code:
    <?php
    if(isset($_POST['joketext']))

        
    date_default_timezone_set("Pacific/Honolulu");
        
    $settime=date('Y-m-d H:i:s');
        
        try
        {
            
    $sql "INSERT INTO joke 
            (joketext,timetest1) VALUES
            (:joketext,:timeholder)"
    ;
            
    $s $pdo->prepare($sql);
            
    $s->bindParam(':joketext'$_POST['joketext']);
            
    $s->bindParam(':timeholder'$settime);
            
    $s->execute(); 
        }
            catch (
    PDOException $e
        {
            
    //echo ONLY during testing!!!! 
            
    echo 'Connection failed: ' $e->getMessage(); 
        } 
    }
    ///Retrive data
    $jokes=array();    
        try
        {
            
    $sqla "SELECT joketext,timetest1 FROM joke";
            
    $query $pdo->prepare($sqla);
            
    $query->execute();                             
            while(
    $row $query->fetch(PDO::FETCH_ASSOC)){
                
    $jokes[] = $row;
            }
        }
            catch (
    PDOException $e
        {
            
    //echo ONLY during testing!!!! 
            
    echo 'Connection failed: ' $e->getMessage(); 
        } 
    ?>
    <html>
    <body>
        <?php foreach ($jokes as $joke): ?>
            <blockquote>
                <p><?php echo htmlspecialchars($joke['joketext'], ENT_QUOTES'UTF-8'); ?></p>
                <p><?php 
                
    echo date("Y-m-d H:i:s"strtotime($joke['timetest1']));
                
    ?></p>    
            </blockquote>
        <?php endforeach; ?> 
    </body>
    </html>

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2014
    Posts
    48
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    @Drummin ;
    Sorry should have said thanks for that! Have moved on to another part of the site.

    I [I]think[I] my time problems are sorted but we shall see next week or wek after :-)


Tags for this Thread

Bookmarks

Posting Permissions

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