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

PHP
1

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:

{
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

   <?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 :frowning:

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 :slight_smile: (Oh and I have searched)

2

The structure of your sql is more like what is used for UPDATE. You were also missing date() elements. Give this a try.

<?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

If you’re storing the current time, you could use NOW() in the query.

4

Thanks for the thought but storing all sorts of user inputted time.

5

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

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
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

@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 :slight_smile:

8