Php/Mysql question

[wagnj1]

I'm writing a section of code using PHP which gets the last 5 newest news posts from my MySQL database. The table description is this:

n_id -> int(6) unsigned, not null, auto_increment, primary key (used as a unique ID for each news post)
date -> varchar(8) not null (used to store the date of the entry which is sent with the PHP script when the post is added to the db)
heading -> varchar(40) not null (stores the heading of the news post)
n_post -> varchar(255) not null (stores the actual news post)

my current PHP is set up like this:

$news = mysql_connect("localhost","newseditor","n3ws3dit0r");
if (!$news)
{
	echo "Error: Could not connect to database for news: ".mysql_error()."<br />";
	exit;
}
mysql_select_db("archied");
$last_five_posts=mysql_query("select date,heading,n_post from news order by date limit 5");
echo "Error with news query: ".mysql_error()."<br />";

...I haven't finished it yet but I'm wondering something...lets say that there is only 1 news post in the database, I'm going to get an error when trying to select 5 rows, correct? how can I prevent this? I want to be able to determine if there is 5 rows, and if so, select the 5 newest...am I going to get an error when trying to order the date column and its a varchar? how can I make sure I get the 5 newest posts?

 

[sleipnir214]

Don't select your rows in PHP. Do it in MySQL.

SELECT queries can use a LIMIT clause. For example, the query "SELECT * FROM foo ORDER BY bar LIMIT 5" would select only the first 5 records from foo. If there are fewer qualifying records, MySQL will only return those records.

Then on the PHP side, you just loop through all the records returned, confident that you will show at most 5 records.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[wagnj1]

ok I've done that...what about trying to order by date when date is a varchar? will this work?

 

[sleipnir214]

There is a "gotcha" of text-based date formats. If you are storing a date as a string in the format YYYY-MM-DD (the way MySQL stored dates), then alphabetical order is the same thing as date order. A simple "ORDER BY datecolumnname" will suffice.

If you are storing the date as the US-standard MM-DD-YYYY or the European-standard DD-MM-YYYY, it is more difficult. You must sort the column in parts using MySQL's substring() function:

SELECT * FROM foo order by substr(bar,7,4), substr(bar,1,2), substr(bar, 4,2) LIMIT 5

will order US-standard MM-DD-YYYY formatted dates.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

Just for completeness, I will also say that you could have used PHP's mysql_num_rows() function with your original query to see whether less than 5 rows had been returned by your original query.

But I strongly recommend that you use MySQL's LIMIT clause instead. There's no point fetching from the database more rows than you're going to need.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[wagnj1]

thanks for the help...what I found with my date problem was that (I was using DD/MM/YY format) if I made two news posts on the same day it would display them in the order it found them in the database, which isn't what I want (I want newest first). So I changed up the PHP and altered the table column in my database so that the date is now DD/MM/YY:HH:MM:SS. I figure this should work...however, I don't want the :HH:MM:SS part displaying on my site, is there a string function that I can use to just grab the first 8 characters and display them?

 

[wagnj1]

actually nevermind I just noticed your substr function upon reexamining your code.