Jump to content

getting a timestamp to show up


netboy541

Recommended Posts

Hello everyone.

I have been battling this issue for days, and have finally turned to the internet for help.

I am attempting to display via PHP a timestamp in a MySQL database.

I can get it to give me the current time/date, and 1/1/1970. that's it.

Basically, the point of this is where someone enters the data, I want it to show on a paticular web site that is visited when these stats where last updated.

I will include the code, in sequential order that they run.


The database is like this..

DATABASE NAME
|
--- TABLE1
|
-- subtable1
|
-- subtable2
|
-- timestamp

--- TABLE2
|
-- subtable1
|
-- subtable2
|
-- timestamp

--- TABLE3
|
-- subtable1
|
-- subtable2
|
-- timestamp

(hopefully that makes sense)

Here's the SQL Dump....

[code]
-- phpMyAdmin SQL Dump
-- version 2.8.0.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 14, 2006 at 10:34 PM
-- Server version: 5.0.18
-- PHP Version: 4.4.2
--
-- Database: `rates`
--

-- --------------------------------------------------------

--
-- Table structure for table `auths`
--
-- Creation: Jun 01, 2006 at 07:26 PM
--

DROP TABLE IF EXISTS `auths`;
CREATE TABLE IF NOT EXISTS `auths` (
  `id` int(11) NOT NULL auto_increment,
  `today` float(5,2) default NULL,
  `week` float(5,2) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2;

--
-- Dumping data for table `auths`
--

INSERT INTO `auths` (`id`, `today`, `week`, `timestamp`) VALUES (1, 0.54, 1.11, '2006-06-14 15:45:20');

-- --------------------------------------------------------

--
-- Table structure for table `financial_institutions`
--
-- Creation: Jun 01, 2006 at 07:26 PM
--

DROP TABLE IF EXISTS `financial_institutions`;
CREATE TABLE IF NOT EXISTS `financial_institutions` (
  `id` int(11) NOT NULL auto_increment,
  `today` float(5,2) default NULL,
  `week` float(5,2) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2;

--
-- Dumping data for table `financial_institutions`
--

INSERT INTO `financial_institutions` (`id`, `today`, `week`, `timestamp`) VALUES (1, 0.67, 0.73, '2006-06-14 15:45:20');

-- --------------------------------------------------------

--
-- Table structure for table `merchant`
--
-- Creation: Jun 01, 2006 at 07:27 PM
--

DROP TABLE IF EXISTS `merchant`;
CREATE TABLE IF NOT EXISTS `merchant` (
  `id` int(11) NOT NULL auto_increment,
  `today` float(5,2) default NULL,
  `week` float(5,2) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;

--
-- Dumping data for table `merchant`
--

INSERT INTO `merchant` (`id`, `today`, `week`, `timestamp`) VALUES (2, 0.94, 3.09, '2006-06-14 15:45:20');
[/code]



First, the back end for the form...


[code]
<?php
//Fire up the connection the the SQL server
$con = mysql_connect("localhost","rates","2NAwUMbwaY3y5Bev");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//Connect to the database
mysql_select_db("rates", $con);

//Shove the data in the Merchant table, and REPLACE it if it's there....

// Week --
$sql="UPDATE merchant
SET week = '$_POST[merchant_week]'";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Merchant Skills - Week's Rates!";

// Today --

$sql="UPDATE merchant
SET today = '$_POST[merchant_today]'";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Merchant Skills - Today's Rates!";

// END Merchant Skills Replacement....


//Now on to the Auths and Lost and Stolen Skills

// Week --
$sql1="UPDATE auths
SET week = '$_POST[auth_week]'";


if (!mysql_query($sql1,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Auths and Lost & Stolen - Week's Rates!";

// Today --

$sql2="UPDATE auths
SET today = '$_POST[auth_today]'";

if (!mysql_query($sql2,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Auth & Lost and Stolen Skills - Today!";


// END Auths & Lost and Stolen Replacement.....

// Now, mooovin on to the Financial Skills...


// Week --
$sql3="UPDATE financial_institutions
SET week = '$_POST[financial_week]'";


if (!mysql_query($sql3,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Financial Institutions - Week's Rates!";

// Today --

$sql4="UPDATE financial_institutions
SET today = '$_POST[financial_today]'";

if (!mysql_query($sql4,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<br>Successfully REPLACED Financial Institutions - Today!";


// END  Financial_Institutions Replacement


?>
[/code]


Then, the file that displays the data in the DB...

[code]
<?php
// Make a MySQL Connection
mysql_connect("localhost","rates","2NAwUMbwaY3y5Bev") or die(mysql_error());
mysql_select_db("rates") or die(mysql_error());

// Grab the time and date it was last modified
//$query = "SELECT UNIX_TIMESTAMP() AS timestamp FROM merchant;";
$query2 = "SELECT UNIX_TIMESTAMP() AS timestamp FROM merchant;";
//$result = mysql_query($query);
$result2 = mysql_query($query2);
//$row_date = mysql_fetch_array($result);
$row_time = mysql_fetch_array($result2);

echo "Rates are current as of ";
//echo date("m/d", $row_date['timestamp']);

echo date("m/d - H:i:s", $row_time['timestamp']);
echo "<br><b>date/time</b> is invalid. please disregard. i'm aware of it. - R";

//echo



// MERCHANT

// Get the data from the "rates" table
$result_merchant = mysql_query("SELECT * FROM merchant")
or die(mysql_error());
echo "<p></p><p></p>";
echo "<table border='1'>";
echo "<tr>Merchant Skills<td>Today</td> <td>Week</td> </tr>";
// keeps getting the next row until there are no more to get
while($row_merchant = mysql_fetch_array( $result_merchant )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row_merchant['today'];
echo "%</td><td>";
echo $row_merchant['week'];
echo "%</td></tr>";
}
echo "</table>";

// AUTHORIZATIONS AND LOST AND STOLEN

// Get the data from the "rates" table
$result_auths = mysql_query("SELECT * FROM auths")
or die(mysql_error());
echo "Auths / LS Skills";
echo "<table border='1'>";
//echo "<tr><td>Today</td> <td>Week</td> </tr>";
// keeps getting the next row until there are no more to get
while($row_auths = mysql_fetch_array( $result_auths )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row_auths['today'];
echo "%</td><td>";
echo $row_auths['week'];
echo "%</td></tr>";
}
echo "</table>";


// FINANCIAL INSTITUTIONS

// Get the data from the "rates" table
$result_fi = mysql_query("SELECT * FROM financial_institutions")
or die(mysql_error());
echo "FI / Client Skills";
echo "<table border='1'>";
//echo "<tr><td>Today</td> <td>Week</td> </tr>";
// keeps getting the next row until there are no more to get
while($row_fi = mysql_fetch_array( $result_fi )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row_fi['today'];
echo "%</td><td>";
echo $row_fi['week'];
echo "%</td></tr>";
}
echo "</table>";





?>
[/code]



As it sits, here's exactly what I get every time...


Rates are current as of 06/14 - 22:20:18 <---- this is the CURRENT date and time.
date/time is invalid. please disregard. i'm aware of it. - R


Merchant Skills Today Week
0.94% 3.09%
Auths / LS Skills
0.54% 1.11%
FI / Client Skills
0.67% 0.73%


Any help would be appreciated...
Link to comment
Share on other sites

[!--quoteo(post=384064:date=Jun 14 2006, 10:16 PM:name=NetBoy541)--][div class=\'quotetop\']QUOTE(NetBoy541 @ Jun 14 2006, 10:16 PM) [snapback]384064[/snapback][/div][div class=\'quotemain\'][!--quotec--]
$query2 = "SELECT UNIX_TIMESTAMP() AS timestamp FROM merchant;";
[/quote]

I think that's your problem.

From the MySQL manual:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer.[/quote]

If you want to return the column from that table, you'll need to call "SELECT UNIX_TIMESTAMP(timestamp) AS timestamp FROM merchant". You can also do something like DATE_FORMAT(timestamp,'%c/%d - %T').

(Also you don't need to end your SQL queries with a semi-colon when calling them within PHP like you would in mysql client.)
Link to comment
Share on other sites

still not working...

here's what i get now


[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
12/31 - 19:00:00
[/quote]

using

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
SELECT UNIX_TIMESTAMP(timestamp) AS timestamp FROM merchant
[/quote]

That is NOT correct.

the time in the DB is: 2006-06-14 19:55:07



I'm gonna warn you guys, I am NOT proficient at all with MySQL. I only know enough to be deadly. [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]

I have 3 SQL manuals i have been sifting through, and I just can't figure out why it won't work.

Everything else works tho!

Link to comment
Share on other sites

alright, after consulting with a friend of mine with 2 degrees in database management...


he suggested this snippet of code...



[code]
$query2 = "SELECT timestamp FROM `merchant` WHERE id = '1';";
[/code]

i also modified the
[code]
echo date("m/d - H:i:s", $row_time['timestamp']);
[/code]

to
[code]
echo  $row_time ['timestamp'];
[/code]



low and behold, that did it.



it now returns the correct time and date.

thanks for your help guys!
Link to comment
Share on other sites

[!--quoteo(post=384421:date=Jun 15 2006, 09:03 PM:name=NetBoy541)--][div class=\'quotetop\']QUOTE(NetBoy541 @ Jun 15 2006, 09:03 PM) [snapback]384421[/snapback][/div][div class=\'quotemain\'][!--quotec--]
i also modified the
[code]
echo date("m/d - H:i:s", $row_time['timestamp']);
[/code]

to
[code]
echo  $row_time ['timestamp'];
[/code]
[/quote]
No surprise there -- you were thwarting your own efforts... that's why it's important to keep the code simple, and MOST IMPORTANTLY, test your queries _outside_ of your scripts in a front-end client. MySQL [b]always[/b] returns the data you ask for... trust me.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.