Page 1 of 2

selecting the most recent

Posted: Wed Aug 04, 2004 8:16 am
by buddok
hey well on my site for the most recent news i have a form which goes to my database that works fine but i want to have the most recent in box one below and the second most recent in the box marked 2 and the 3 and fourth in the boxes marked 3 and 4

___ ___
| 1 | 2 |
|__|__|
| 3 | 4 |
|__|__|

and wen i add a new record i want number 4 to go an 3 to move into 4 2 into 3 1 into 2 and the new one in 1 anyone now how im new at PHP if anyone could help id be very greatfull

thank....

Posted: Wed Aug 04, 2004 8:22 am
by feyd
a combo of

Code: Select all

ORDER BY somefield DESC LIMIT 4
+[php_man]array_reverse[/php_man]() and some math in volving % (modulous) could do this easy.

i tried that :(

Posted: Wed Aug 04, 2004 8:26 am
by buddok
i tried that but becus of the layout it dont work i have to put that thing in each box but how do i get each one to select the 2nd 3rd and 4th most recent alone ?

Posted: Wed Aug 04, 2004 12:07 pm
by feyd
store off the data in your variable space. then drop each bit, as you hit them in the output

Posted: Sat Aug 07, 2004 8:43 am
by buddok
im a newbie to this an i didnt get that :( sorry could you explain ? maybe some code?

Posted: Sat Aug 07, 2004 9:42 am
by Joe
You could use a unix timestamp perhaps and then ORDER BY?

Posted: Sat Aug 07, 2004 10:07 am
by buddok
ok your probably beina great help but i dont get any of it sorry :( can someone give like a script iv jus been tryin

<?php
$conn = mysql_connect("localhost", "name", "pass");
mysql_select_db("database", $conn);
$update1 = mysql_query("SELECT date, comments FROM news order by id desc limit 0,1");
while ($row = mysql_fetch_row($update1))
{
print("

<ul>
<li>
$row[1]
</li>
</ul>
<p>On : <b>$row[0]</b></font>

");
}
?>

and ive been tryin to adjust the limit ? :S ? but i realy dont no how to do it hep need urgently

Posted: Sat Aug 07, 2004 10:13 am
by Joe
Say you wanted 5 records to be shown from the query, just write:

Code: Select all

$update1 = mysql_query("SELECT date, comments FROM news ORDER BY id DESC LIMIT 5");

Posted: Sat Aug 07, 2004 10:17 am
by buddok
i no wat u mean but i need the most recent in the first box second most recent second box so on but jus to number four its realy weird cus of my layout i cant jus keep repeat it i need that script abouve in each box some how :S u get me ?

Posted: Sat Aug 07, 2004 10:18 am
by Joe
Is the id column of your database an integer with auto_increment attributes?

Posted: Sat Aug 07, 2004 10:19 am
by buddok
yer

Posted: Sat Aug 07, 2004 10:19 am
by Joe
And do you have any timestamps being inserted?

Posted: Sat Aug 07, 2004 10:20 am
by buddok
nop i jus have the date as text and the input box puts the date for me

Posted: Sat Aug 07, 2004 10:24 am
by Joe
You should add a new column named 'unixtimestamp' and when 'INSERTING' into the database use:

Code: Select all

list($utime, $time) = explode(" ", microtime());

$query = "INSERT INTO news VALUES('$headline','$news','$time')";
Where headline and news is the actual news content and where $time is the unix timestamp: list($utime, $time) = explode(" ", microtime());

You should then make the query like:

Code: Select all

$update1 = mysql_query("SELECT * FROM news ORDER BY unixtimestamp DESC");

Posted: Sat Aug 07, 2004 10:25 am
by buddok
and do i do that in each of the four boxes ?