[SOLVED] Very simple problem, i hope.

[martin_fell]

Hi,

 

I've searched the web for a solution but just keep finding arguments about the pros and cons of resetting auto increment. Anyhow here goes.

 

I've got a php and mysql blog. Each blog entry is given a unique id in a field called post_id of a mysql database.

 

post_id uses the auto increment feature and so the entries count up as they're added. 1, 2, 3, 4...

 

I've got a script that the displays the last 11 entries. Perfect.

 

If I delete an entry there is a gap, but the auto increment ensures the next entry has the highest value. I.e. if the entries are numbered 1, 2, 3, 4, 5 and I delete entry 4 I'm left with 1, 2, 3, 5. If I then add another entry however I now have 1, 2, 3, 6, 5. The latest entry is given the post_id="6", but it fills the gap in the database left  by entry 4. So when I display my blog entries the latest entry (ie "6") is displayed where number 4 would be.

 

I'm very new to this php thing, but If anyone could help I would be very grateful.

 

Martin Fell.

[martin_fell]

This is the code I'm using to access the database. If that helps.

 

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die("Unable to select database");

$query="SELECT * FROM blog_entries";

$result=mysql_query($query);

 

$num=mysql_numrows($result);

 

mysql_close();

 

$i=($num-1);

$j=($i-11);

 

while ($i > $j) {

 

$post_id=mysql_result($result,$i,"post_id");

$post_date=mysql_result($result,$i,"post_date");

$post_title=mysql_result($result,$i,"post_title");

$post_body=mysql_result($result,$i,"post_body");

 

echo "<h2>$post_title</h2><p>$post_body</p><h3><a class=mainl href=article.php?article_id=$post_id>More...</a> $post_date </h3><br /><br />";

 

$i--;

 

}

 

?>

[Orio]

You should look into "ORDER BY".

 

I don't know how your query looks, but this is how you are supposed to add the order by part:

SELECT * FROM `blogs_table` ORDER BY post_id DESC LIMIT 11

 

The part I added (ORDER BY post_id DESC) sorts the results by the post_id in a descending order- most recent ones come first.

 

Orio.

[Orio]

ok after you gave the script it's much easier.

 

You can make your code more effective.

Here's how the script should be with the order by part:

 

<?php

mysql_connect(localhost, $username, $password);
@mysql_select_db($database) or die("Unable to select database");
$query = "SELECT * FROM blog_entries ORDER BY post_id DESC LIMIT 11";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
echo "<h2>$row['post_title']</h2><p>$row['post_body']</p><h3><a class=mainl href=article.php?article_id=$row['post_id']>More...</a> $row['post_date']</h3>";

?>

 

 

Orio.

[per1os]

ORDER BY post_id

 

should solve it.

[martin_fell]

Thanks Orio. Problem solved.

 

It was more difficult translating my php into English than it was understanding your code, lol.

 

Kind regards, Martin.