Output not displaying newest row from db

maggie_b

I'm just starting out with PHP/MySQL and have created a database which is fed from an HTML form.

The table concerned (has 4 fields - news_id (primary, auto increment), news_heading, news_article, and news_date (timestamp8).

I have 2 problems:

1) Using phpmyadmin, I can see all 12 rows which have been input, but in trying to display them in a web page, only the first 11 records are shown - the most recent never displays.

2) I don't know how to translate the time from 8 characters (year first) to show as a text type date, eg. 5 January 2003.

My script is:

<?php
$host="blah";
$user="blah";
$password="blah";
$database="apyfc_admin";
$connection = mysql_connect($host, $user, $password)
or die ("couldn't connect to the server - please try again later");
$db = mysql_select_db($database,$connection)
or die ("the database is unavailable - please try again later");
$query = "select * from club_news order by news_id desc";
$result = mysql_query($query)
or die ("Sorry, there are no news items available. Please try later");
$row = mysql_fetch_array($result,mysql_assoc);

/ Display results /
while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<h5>$news_heading</h5>\n";
echo nl2br($news_article)."<br><br>\n";
echo "date: ".$news_date;
echo "<br><br>";
echo "<hr>";
}

Any help please? I can't find anything in my books on the first problem and have tried many many ways to solve the second but still haven't managed it.

My brain hurts.

dalecosp

Hmm, on the first issue, have you tried foreach? I'm a bit braindead myself today, & can't see what's wrong, but it could be the while(), I guess.

On the second, you could use some mysql date function, probably, or maybe would this do it?

$year=substr($news_date, 0, 4);
$month=substr($news_date, 5, 2);
$day=substr($news_date, 7, 2);
echo $month."-".$day."-".$year;

Definitely a long kluge. I dunno if [man]strtotime[/man] or [man]getdate[/man] could read a string like "20030905", though...

weevil

Another option for problem #2 is the mysql date_format function
http://www.mysql.com/doc/en/Date_and_time_functions.html

In your select statement do:

select news_id, news_heading, news_article, date_format(news_date, '%M %e, %Y) AS dispdate from club_news order by news_id desc

Then just echo $dispdate rather than $news_date

weevil

I think the answer to Problem #1 lies here:

$row = mysql_fetch_array($result,mysql_assoc);

You enter the loop and grab the 2nd row and so on

while ($row = mysql_fetch_array($result))

without printing the first row.
Try just removing the first mysql_fetch_array.

stolzyboy

yes, doing this line

$row = mysql_fetch_array($result,mysql_assoc);

will grab the first result, then you are doing the while loop and that is getting the first available result, which is the second, since you already took the first one with the

$row = mysql_fetch_array($result,mysql_assoc);

simply take that line out and it will work

maggie_b

Thanks guys. The answer to problem #1 was in the $row row, as you said. I'll wait 'till tomorrow to try the date answer. Will keep you posted.