Good tutorial on date/time?

Basically need to know how to set up the field, how to populate it via php, how to select date/time in descending order, and how to limit the number of records return. Sort of the latest 10 Palin non-sensical statements.

Have been hunting around the net but can't find anything :sick:

What are you trying to do, sort by given date/time or sort after you retrieve the data/time?

Quote:

---

Originally Posted by Nightwalker83

What are you trying to do, sort by given date/time or sort after you retrieve the data/time?

---

Basically I want to do an SQL select of the last ten articles published to a database, but am unsure of what data type etc to use.

if you can design this, I would suggest using the Unix timestamp. I prefer using this method for all dates because of its ease of use with PHP and the native PHP date functions. a Unix timestamp is the number of seconds since the Epoch (1970), and so is an int type with 11 digits. you can make an 11 or 12 length int field for safety!

for an SQL query to get the last 10 articles, you can simply add the LIMIT clause, and you can also add an ORDER BY clause to sort by the date (descending). for example:

if this was your regular SQL query:

SELECT * FROM table;

then you could add the following:

SELECT * FROM table ORDER BY date DESC LIMIT 10;

that should achieve what you're trying to do.

if you load the date from a table in the database you can just load it into a variable and then echo it for example...

Code:

---

<?php

$dburl = "localhost";
$dbuser = "root"; //default username
$dbpass = ""; //default no password
$db = "database";
$tbl = "date"; //table

$con = mysql_connect("$dburl","$dbuser","$dbpass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("$db", $con);

$result = mysql_query("SELECT * FROM $tbl ORDER BY date DESC LIMIT 10");

while($row = mysql_fetch_array($result))
  {
  $time = $row['date'];
  echo date("m-d/Y h:i:s", $time) . "<br>";
  }

?>

---

code is untested but should work, the code above will open the database called "database" and go under table called "date" and then find the row called "date" and just print it out in the date function which will make it a valid date/time and sort it by the DESC(highest number on top).

and to the experts correct me if i'm wrong in what i said xD

Quote:

---

Originally Posted by kows

... an int type with 11 digits. you can make an 11 or 12 length int field for safety!

---

An int is an int. The number in parentheses merely signifies how many digits it should be truncated or expanded to. This number is not used by default.

Quote:

---

Originally Posted by [url=http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html]10.2 Numeric Types[/url]

[An] extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

---

I prefer using the MySQL date and time types, so that date arithmetic can be done using SQL rather than PHP.