getting a timestamp to show up

[netboy541]

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

[Wildbug]

[!--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.)

[fenway]

Sounds like a good reason... it's hard to see with all that code. You definitely should not have those trailing semi-colons, though.

[netboy541]

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!

[netboy541]

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!

[fenway]

[!--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.