[Resolved] How can I echo the id and cur_timestamp field?

badgoat

I have a MySQL database set up with two columns being id and cur_timestamp. When I display the data which was just added, I would like to display the id and the timestamp.

I know I need another sql query statement after the first INSERT sql query statement, so I added one, but I am unsure if the syntax is correct. Also, I am pretty sure that the 'id' and 'cur_timestamp' table entries are set incorrectly as well.

Could someone point me in the right direction with these two little issues?

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Untitled</title>
</head>
<body>
<?php

$connect= mysql_connect("localhost","****","****") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("testdiw") 
   or die("Could not select that database !");
$sqlquery = "INSERT INTO deponent VALUES('','','". $deponentname ."','". $casenum ."','". $forumst ."','". $county ."','". $plaintiff ."','". $defendant ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

$sqlquery = "SELECT * from deponent";

echo "<table border=1 align=center  width=500>";
echo "    <td > ";
echo "      ID Number";
echo "    </td>";
echo "    <td >".id. "</td>";
echo "  </tr>";
echo "    <td > ";
echo "      Timestamp";
echo "    </td>";
echo "    <td >".cur_timestamp. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td> ";
echo "     Deponent Name";
echo "    </td>";
echo "    <td>".$deponentname. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Case Number";
echo "    </td>";
echo "    <td >".$casenum. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Forum State";
echo "    </td>";
echo "    <td >".$forumst. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      County";
echo "    </td>";
echo "    <td >".$county. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Plaintiff";
echo "    </td>";
echo "    <td >".$plaintiff. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Defendant";
echo "    </td>";
echo "    <td >".$defendant. "</td>";
echo "  </tr>";
echo "</table>";
?>
Add another record <a href="http://localhost/tempo.php">LINK</a>
</body>
</html>

dalecosp

Is the "id" field an auto_increment field? If so:

 echo mysql_insert_id(); // will print the number used in last successful insert

You could of course, set a variable to that function call and use it in a formatted string or whatnot.

As for the timestamp, the timestamp was right then, right? So take a look at [man]date/man.

HTH,

badgoat

Thank you dale! I added that line and fixed a few html errors that I was echoing and I can now echo the ID field. The timestamp field doesn't work though.. When I view the query in MySQL the timestamp shows as eight zeros. When I was searching the forum I saw a few threads about the correct usage of the date command, looking more into it now.

While I have this code up, how to I prevent from adding a duplicate entry, based on deponentname? (searching for that now too..)

New code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Untitled</title>
</head>
<body>
<?php

$connect= mysql_connect("localhost","root","cpyter") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("testdiw") 
   or die("Could not select that database !");
$sqlquery = "INSERT INTO deponent VALUES('','','". $deponentname ."','". $casenum ."','". $forumst ."','". $county ."','". $plaintiff ."','". $defendant ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

$sqlquery = "SELECT * from deponent";

echo "<table border=1 align=center  width=500>";
echo "  <tr> ";
echo "    <td > ";
echo "      ID Number";
echo "    </td>";
echo "    <td > ";
echo mysql_insert_id(); // will print the number used in last successful insert
echo "    </td > ";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Timestamp";
echo "    </td>";
echo "    <td >".cur_timestamp. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td> ";
echo "     Deponent Name";
echo "    </td>";
echo "    <td>".$deponentname. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Case Number";
echo "    </td>";
echo "    <td >".$casenum. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Forum State";
echo "    </td>";
echo "    <td >".$forumst. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      County";
echo "    </td>";
echo "    <td >".$county. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Plaintiff";
echo "    </td>";
echo "    <td >".$plaintiff. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Defendant";
echo "    </td>";
echo "    <td >".$defendant. "</td>";
echo "  </tr>";
echo "</table>";
?>
Add another record <a href="http://localhost/tempo.php">LINK</a>
</body>
</html>

dalecosp

Well, a UNIX timestamp, or a MySQL one? Hmm, 8 characters....

I'm not up on the mysql "Date" stuff too well. IIRC, there's a CURRDATE or CURR_DATE or somesuch. If you want a UNIX timestamp:

$connect= mysql_connect("localhost","root","cpyter")  

   or die("Could not connect to database in localhost !"); 
$result=mysql_select_db("testdiw")  

   or die("Could not select that database !"); 
$now=time();
$sqlquery = "INSERT INTO deponent VALUES('','$now','". $deponentname ."','". $casenum ."','". $forumst ."','". $county ."','". $plaintiff ."','". $defendant ."')"; 
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

Of course, if your field type is a MySQL Date field, it won't work. Would have to be bigint, I'd think.

badgoat

Before I added in the $now variable, I echoed it to see what it would be, and the number it gives me is 1109792622

I am unsure where that comes from.. Any ideas?

dalecosp

Sure, that is a UNIX timestamp. It is the exact number of seconds since the "epoch", that is, midnight on January 1, 1970. So I'm guessing a UNIX timestamp wasn't what you wanted? It's still pretty useful:

$now=time();
echo date("M/d/Y", $now); // "Mar/02/2005"
echo date("Y-m-d" $now+3600);  // one hour from now, MySQL Style