Newest/Latest First?

[350D]

If I have 22 objects in my MySQL Database and I want the newest one to always be first on the startpage, how do I do that?

When you enter www.mygallery.com (for example) the latest picture added to the gallery will show up. The latest one added is identified by how high the "id" is.

$sql = "SELECT * FROM object ORDER BY id LIMIT " . $p . ", 1" or exit (mysql_error());

That is my current SQL code. If I put "DESC" after "id" I can see the latest picture first but the "id"´s will be turned backwards. So the newest picture, who should have the id: 22 now has the id: 0.

I need to have the "id" right otherwise it will be wrong with the "Next/Previous" buttons which always have to have the right "id".

Can someone please help me?

[Vectorman211]

Doing that should not change the `id` field at all. Assuming `id` is an auto-increment field, once you insert the record it permanantly reserves that id for the record. Perhaps you're confusing the `id` field with the row number?

[bokeh]

SELECT * FROM `object` ORDER BY `id` DESC LIMIT 1

[350D]

Doing that should not change the `id` field at all. Assuming `id` is an auto-increment field, once you insert the record it permanantly reserves that id for the record. Perhaps you're confusing the `id` field with the row number?

The thing is that I have Next/Previous buttons also that need a number to display the picture. When its not descending everythings normal although the first picture added shows up on the startpage. And it has the number 0. The newest picture has number 22. And if I add "DESC" to the SQL code the newest picture gets the number 0. So when you type in the URL: http://localhost/test/?picture=22 the first picture added shows up.

[Vectorman211]

I think you're confused. The first record will have row number 0 but it retains it's original id stored in the actual `id` column.

Example Table:
id photo
1 photo1.jpg
2 photo2.jpg
3 photo3.jpg
4 photo4.jpg

SELECT * FROM `photos` ORDER BY `id` DESC

should return:

row# id photo
0 4 photo4.jpg
1 3 photo3.jpg
2 2 photo2.jpg
3 1 photo1.jpg

Hope that helps, otherwise I'm not sure what you're getting at and we'd have to see your code.

[350D]

I think you're confused. The first record will have row number 0 but it retains it's original id stored in the actual `id` column.

Example Table:
id photo
1 photo1.jpg
2 photo2.jpg
3 photo3.jpg
4 photo4.jpg

SELECT * FROM `photos` ORDER BY `id` DESC

should return:

row# id photo
0 4 photo4.jpg
1 3 photo3.jpg
2 2 photo2.jpg
3 1 photo1.jpg

Hope that helps, otherwise I'm not sure what you're getting at and we'd have to see your code.

I will try to explain it in a little more detail for you.

Imagine that you lay out 12 objects in front of you. Number 12 is to your far left and number 1 is to your far right. Thats the way I want it. Number 12 is the latest added into my database. The real problem begins when I want my Next/Previous buttons to work in a ASCending way. From the left to the right. Although in DESCending mode it will be the other way. The latest added photo will be to the far right and vice versa. I would like to have the lineup like the ASCending way, just pull it to the far side so the 12 is the viewing/start object and the number 1 is to the far right and is taken to be the first and oldest object in the database. Perhaps there isnt a way to accomplish this without redirecting the visitors to the exact page, which would be difficult also because the number 12 will increase as I insert more objects.

I hope you get a clearer picture of this.

( Excuse my english, Im from Sweden. :P )

[bokeh]

Sounds like a simple formating job to me. Can you post the code?

[350D]

Sounds like a simple formating job to me. Can you post the code?

<?php

$p=$_GET["pic"]; if(!isset($p)) $p = 0;

$sql2 = "SELECT count(*) as count FROM object";
$result = mysql_query($sql2);
$row = mysql_fetch_array($result);
$numrows = $row['count'];

$sql1 = "SELECT * FROM object ORDER BY id LIMIT " . $p . ", 1" or exit (mysql_error());
$result = mysql_query($sql1);
$r = mysql_fetch_assoc($result);

echo '<div id="EXHIBITION"><img src="-/'.$r['pic'].'.jpg" alt=" "></div>';

if($numrows > ($p + 1)) echo "<a href=\"?p=exhibition&pic=" . ($p + 1) . "\" id=\"A2\"></a>";
if($p > 0) echo "<a href=\"?p=exhibition&pic=" . ($p - 1) . "\" id=\"A3\"></a>";

?>

Thats the ASCending way. When I go to the startpage the 1:st photo shows. I want to view the latest added but still have the older objects to my right, so to speak.

[Vectorman211]

try:

if($numrows > ($p + 1)){
echo "<a href=\"?p=exhibition&pic=" . ($p + 1) . "\" id=\"A2\"></a>";
}else{
echo "<a href=\"?p=exhibition&pic=1\" id=\"A2\"></a>";
}
if($p > 0) echo "<a href=\"?p=exhibition&pic=" . ($p - 1) . "\" id=\"A3\"></a>";

[350D]

try:

if($numrows > ($p + 1)){
echo "<a href=\"?p=exhibition&pic=" . ($p + 1) . "\" id=\"A2\"></a>";
}else{
echo "<a href=\"?p=exhibition&pic=1\" id=\"A2\"></a>";
}
if($p > 0) echo "<a href=\"?p=exhibition&pic=" . ($p - 1) . "\" id=\"A3\"></a>";


That didnt help at all?

[bokeh]

Any chance you could post a link to the page.

[350D]

Any chance you could post a link to the page.

http://ilikeitsimple.com/test/

There you go. The third image is the one I would like to appear on the start page. That is the newest added to the database.

[bokeh]

..

[350D]

if($p > 0) echo "<a href=\"?p=exhibition&pic=" . ($p - 1) . "\" id=\"A3\">PREVIOUS</a> ";
if($numrows > ($p + 1)) echo " <a href=\"?p=exhibition&pic=" . ($p + 1) . "\" id=\"A2\">NEXT</a>";

That didnt make the third image appear firstly when I pressed http://ilikeitsimple.com/test/.

Please read what I wrote in #6. That is what I would like to get help with.

[bokeh]

ok. Change the second query from:$sql1 = "SELECT * FROM object ORDER BY id LIMIT " . $p . ", 1"; to this:$sql1 = "SELECT * FROM object ORDER BY id LIMIT 1 OFFSET ".($numrows - 1);Sorry if my previous reply was not what you were after but the question is a bit confusing.

[350D]

ok. Change the second query from:$sql1 = "SELECT * FROM object ORDER BY id LIMIT " . $p . ", 1"; to this:$sql1 = "SELECT * FROM object ORDER BY id LIMIT 1 OFFSET ".($numrows - 1);Sorry if my previous reply was not what you were after but the question is a bit confusing.

Sadly that didnt work either..

[bokeh]

I think this is what you are after:<?php

// connect stuff here

$sql2 = "SELECT COUNT(*) as `count` FROM `object`";
$result = mysql_query($sql2) or die($query .' - '. mysql_error());
$row = mysql_fetch_array($result);
$numrows = $row['count'];

$p = (isset($_GET["pic"]) and is_numeric($_GET["pic"]) and ($_GET["pic"] > 0) and ($_GET["pic"] <= $numrows))
? $_GET["pic"]
: $numrows ;

$sql1 = "SELECT * FROM `object` ORDER BY id LIMIT 1 OFFSET ".($p - 1);
$result = mysql_query($sql1) or die($query .' - '. mysql_error());
$r = mysql_fetch_assoc($result);

echo '<div id="EXHIBITION"><img src="-/'.$r['pic'].'.jpg" alt=" "></div>';


if($p > 1)
{
echo "<a href=\"?p=exhibition&pic=" . ($p - 1) . "\" id=\"A3\">PREVIOUS</a> ";
}
if($numrows > ($p + 1))
{
echo " <a href=\"?p=exhibition&pic=" . ($p + 1) . "\" id=\"A2\">NEXT</a>";
}

?>

[bokeh]

Did that cure it?