Creating a small news page..

[Dyvn]

Hi there, I've created a small news page for my website using PHP/SQL but I'm having some issues with sorting the news to display easily in the correct manner. I want the newest information at the top of the page and the news only to show the latest 5 submissions to the database. I can't figure out how to log a date automatically when someone submits the form and the specifications of the field it would go in. The code is below, please help!

 

News.php

      <form method="post" action="submit.php">
<table align="center" cellpadding="6" cellspacing="0">
<tr>
  <td>Name :</td>
  <td><input type="text" name="name"></td>
</tr>
<tr>
  <td>Subject :</td>
  <td><input type="text" name="subject"></td>
</tr>
<tr>
  <td valign="top">Message :</td>
  <td><textarea name="message" cols="30" rows="6"></textarea></td>
</tr>
<tr>
  <td valign="top">Pass :</td>
  <td><input type="password" name="password"</td>
</tr>
<tr>
<td> </td>
  <td><input type="submit" name="submit" value="Add" style="cursor:pointer"> <input type="reset" name="reset" value="Clear" style="cursor:pointer"></td>
</tr>
</table> 

 

Submit.php

<?php

if ($_POST[password] == "password") {
$con = mysql_connect("editforpost","jtdatabase","editforpost"");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("jtdatabase", $con);

$sql="INSERT INTO news (name, message, subject)
VALUES
('$_POST[name]','$_POST[message]','$_POST[subject]',)";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Your news had been added.";

mysql_close($con);
}
else {
echo "Incorrect password.";
}
?>

 

Display:

<?


$con = mysql_connect("editforpost","jtdatabase","editforpost") or die(mysql_error());
mysql_select_db(jtdatabase) or die(mysql_error());

$query = "SELECT name, subject, message, date
FROM news order by date DESC";
$result = mysql_query($query);

echo "<br><center>";

while($r = mysql_fetch_array($result))
  {
  echo "<div class='title'><u>" . $r['subject'] . "</u></div>";
echo $r['message'];
echo "<br>Added by: " . $r['name'] . " | Date: " . $r['date'];
echo "<hr color='#EAEAEA' width='80%'>";
  }
  
mysql_close($con);
?>

 

Thanks for your help!

[Gighalen]

You can have two 'date' fields in your table.

 

The first, being a timestamp. This will be the field that you 'sort by' because its only numbers

<?php
$time = time();
?>

 

The second 'date', can be like this:

<?php
$b = time ();  
$date = date("l, F jS Y g:i A",$b);
?>

This will return a date like Tuesday, May 26th, 2006 5:45 PM.

You can use that to display a date on each subject.

 

To retrieve the first 5 posts, and have the newest ones displayed at the top of your output, do something like:

SELECT * FROM news ORDER BY time DESC LIMIT 5

[LooieENG]

column called "time" type "int" value "time()"

 

And when you need to post the time like 05:42:21, you would do

 

<?php

echo date('H:i:s', $r['time']);

?>

 

Edit: Column, not table lol

[Gighalen]

by two date fields in your table, i meant have one labeled time and the other labeled date.

 

Sorry

[craygo]

make your date field a timestamp.

TIMESTAMP

A timestamp

The range is ‘1970-01-01 00:00:00’ to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format, depending on whether M is 14 (or missing), 12, 8 or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don’t give it a value yourself

 

now when you run your query you can order and limit it by using

 

$query = "SELECT `name`, `subject`, `message`, `date`
FROM `news` order by `date` DESC";

 

You will have to enclose your field names in ticks because DATE is a mysql reserved word

 

Ray

[Gighalen]

What looie said works too

 

lmao I feel like a DA for not thinking of that .

[Dyvn]

Thanks so much for the help everyone! Such fast, intelligent posts - I'll definatley be coming back if I need any more help.

[DarkWater]

Or you could make your column a DATETIME, so then you can use date_format to pull it out however you want later.