reverse order

[radiohomer]

hey guys

 

i have this code below that grabs the data from the database

 

<?php
include("connect.php");

$query = "SELECT * FROM new_news ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$new_news_day = mysql_result($result,$i,"new_news_day");
$new_news_month = mysql_result($result,$i,"new_news_month");
$new_news_year = mysql_result($result,$i,"new_news_year");
$new_news_hour = mysql_result($result,$i,"new_news_hour");
$new_news_minute = mysql_result($result,$i,"new_news_minute");
$new_news_am_pm = mysql_result($result,$i,"new_news_am_pm");
$new_news = mysql_result($result,$i,"new_news");
$id = mysql_result($result,$i,"id");

echo "$new_news_day ";
echo "$new_news_month ";
echo "$new_news_year - ";
echo "$new_news_hour:";
echo "$new_news_minute ";
echo "$new_news_am_pm<br>";
echo "$new_news";
echo "<br><br>";

++$i; } } else { echo "No News"; }?>

 

here is the database structure with 2 test entries (please note that there will be more than 2 entries, the code is looped so it will repeat the output)

they are sorted via ID

 

here is a basic layout of the database

 

ID 

new_news_day 

new_news_month 

new_news_year 

new_news_hour 

new_news_minute 

new_news_am_pm 

new_news

 

1

8th

June

2011

10

32

am

Testing the news.

 

2

9th

June

2011

2

15

pm

Testing the news again.

 

now when it shows on the page its shown like this

 

8th June 2011 - 10:32 am
Testing the news.

9th June 2011 - 2:15 pm
Testing the news again.

 

everything works and connects ok ect ect BUT

as you can see by the PHP code above it gets the data from the database and repeats to display all the data in the database

so if there is only one entry it will only show one entry

if there were 200 entries it will show all 200 entries

 

now as you see the code is arranging them in order oldest at the top and newest at the bottom...

is there any way to change the code so it shows the newest at the top and oldest at the bottom??

 

 

hope people can understand what im after

 

in short the code is showing this

8th June 2011 - 10:32 am
Testing the news.

9th June 2011 - 2:15 pm
Testing the news again.

 

i want it to show this

9th June 2011 - 2:15 pm
Testing the news again.

8th June 2011 - 10:32 am
Testing the news.

 

thanks guys

[Pikachu2000]

Why on earth are you storing all of the components of the date separately like that? Date and time information should be stored in a field with a DATETIME data type, in YYYY-MM-DD HH:MM:SS format. Then you can use all of the functions available in MySQL for manipulation and calculation of date and time values.

[radiohomer]

Why on earth are you storing all of the components of the date separately like that? Date and time information should be stored in a field with a DATETIME data type, in YYYY-MM-DD HH:MM:SS format. Then you can use all of the functions available in MySQL for manipulation and calculation of date and time values.

to be totally honest... i used a code generator to give me that code and its working perfect apart from its showing the old at the top and the new at the bottom

[Pikachu2000]

I would strongly recommend you create a new DATETIME field in the table to store the information in the proper format, run an UPDATE query to populate it, then drop the fields you currently have. The current structure is going to cause innumerable problems in the long run.

 

If you want to make those changes, just say so and I'll help you with it.

[radiohomer]

If you want to make those changes, just say so and I'll help you with it.

 

please if you would be so kind to help i will be VERY great full

 

here is the website i used to get the code

http://www.turningturnip.co.uk/free-mysql-php-generator/web-code-creator.php

[PFMaBiSmAd]

Once you change your table so that it has a proper DATETIME column, all your code can be replaced by -

 

<?php
include("connect.php");
//						 date_format: 8th June 2011 - 10:32 am
$query = "SELECT *, DATE_FORMAT(date,'%D %M %Y - %l:%i %p') as fdate FROM new_news ORDER BY date DESC";
$result = mysql_query($query);
if (mysql_num_rows($result)){
while ($row = mysql_fetch_assoc($result)){
	echo $row['fdate'] . "<br />";
	echo $row['new_news'] . "<br /><br />";
} 
} else {
echo "No News";
}
?>

 

You (or the code generator) have prefixed each column name and variable names with new_. That just makes the code more cluttered and makes reading, finding things, and understanding the code harder. Also, mysql_result() is the slowest way of accessing any data and it should only really be used when you are getting one piece of data from a result set that has one row or when you need to randomly access the data in a result set.

 

[Pikachu2000]

OK. This is probably going to seem like a pain, but the end result will be worth it. Give me a few minutes to set up a table locally, and test the queries . . .

 

 

[radiohomer]

Ok thank you, i will wait till your next reply, also if i dont reply its because my isp has gone down, i dont even have a dial tone on my phone line, im having to use my mobile to reply, 

 

Again thank you for helping me

 

[Pikachu2000]

I just realized that the entries in the table you've shown above aren't actual entries, but tests. Is there actual data in the table that needs to be converted, or is test data the only thing in there?

[radiohomer]

The two entries in there are only test data, i will be entering data through a different form

[Pikachu2000]

Then that makes it pretty easy. Just add a field via phpMyAdmin, give it an appropriate name, and set its type to DATETIME. Then post the form and code that you'll be using to insert the data to the table.

[radiohomer]

ok so this is the code that im now using from the code generator

 

i have added the field "datetime" to the database and removed all the single time and date fields

 

this is the code i use to add things to the database

<form id="FormName" action="added.php" method="post" name="FormName">
<table width="448" border="0" cellspacing="2" cellpadding="0">
<tr>
	<td><input id="new_news_date" name="new_news_date" type="text" size="25" value="" maxlength="255"></td>
</tr>
<tr>
	<td><textarea id="new_news" name="new_news" rows="4" cols="40"></textarea></td>
</tr>
<tr>
	<td><input type="submit" name="submitButtonName" value="Add"></td>
</tr>
</table>
</form>

 

 

then i click on submit and it sends the data to this page

<?php
include("connect.php");
$new_news_date = trim($_POST['new_news_date']);
$new_news = trim($_POST['new_news']);

$query = "INSERT INTO new_news (id, new_news_date, new_news)
VALUES ('', '$new_news_date', '$new_news')";
$results = mysql_query($query);

if ($results)
{
echo "Details added.";
}
mysql_close();
?>

 

 

the index page which displays all the data is this code

<?php
include("connect.php");

$query = "SELECT * FROM new_news ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$new_news_date = mysql_result($result,$i,"new_news_date");
$new_news = mysql_result($result,$i,"new_news");
$id = mysql_result($result,$i,"id");

echo "<b>new_news_date:</b> $new_news_date<br>";
echo "<b>new_news:</b> $new_news<br>";
echo "<br><br>";

++$i; } } else { echo "Sorry No News"; }?>

 

 

and here is the sql query

CREATE TABLE `new_news` (
`id` int(6) NOT NULL auto_increment,
`new_news_date` datetime NOT NULL default '0000-00-00 00:00:00',
`new_news` text NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)
)

 

if there is anything else you need Pikachu2000 let me know

again i really appreciate your help

[Pikachu2000]

The only things i see that I'd probably change are in the code that displays the data, like so . . .

 

<?php
include("connect.php");
$query = "SELECT id, new_news, DATE_FORMAT(`new_news_date`, '%D %M, %Y at %r') AS news_timestamp FROM new_news ";
$result = mysql_query($query);
if ( mysql_num_rows($result) > 0 ) {
   while ( $array = mysql_fetch_assoc($result) ) {
      echo "<b>new_news_date:</b>{$array['news_timestamp']}<br>";
      echo "<b>new_news:</b>{$array['new_news']}<br>";
      echo "<br><br>";
   }
} else {
   echo "Sorry No News";
}
?>

[radiohomer]

its still showing them in the wrong order

its showing the newest at the bottom

[Pikachu2000]

Forgot to add the order by clause. Add this to the end of query string . . .

ORDER BY new_news_date DESC

[radiohomer]

Forgot to add the order by clause. Add this to the end of query string . . .

ORDER BY new_news_date DESC

that is perfect im so grateful thank you

 

one last question

is there any way to only show 5 results?

if not dont worry about it

[Pikachu2000]

Just add LIMIT 5 to the query string, and you should be good to go.

[radiohomer]

Just add LIMIT 5 to the query string, and you should be good to go.

 

man you are the best

i thank you sooo much for everything

i will learn this one day