By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

MySQL/PHP problem

P: n/a
Jim
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it
is? For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?

Any help would be appreciated.
--
Cheers,

Jim
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
Jul 17 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
*Jim wrote:
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it is?
For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?

SELECT * FROM guestbook SORT BY COUNTER DESC

--
Thomas

SELECT date FROM wife WHERE bitching = '0' AND sex = '1'
Jul 17 '05 #2

P: n/a
store the entries by maintaing a column for timestamp. later select
the entries on desc order of time. This will show the latest entries on
top every time...

Jul 17 '05 #3

P: n/a
On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <ne*********@nospam.netcom.no>
wrote:
*Jim wrote:
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it is?
For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?


SELECT * FROM guestbook SORT BY COUNTER DESC


That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only wants one
row. And ordering by the ID might not even give the latest entry; depends when
it was committed (and whether you consider the latest by initial insertion vs.
when it was committed to the database).

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #4

P: n/a
*Andy Hassall wrote:
*Thomas wrote:

SELECT * FROM guestbook SORT BY COUNTER DESC


That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only
wants one
row.

Doh! Most certainly ORDER BY!


--
Thomas

SELECT date FROM wife WHERE bitching = '0' AND sex = '1'
Jul 17 '05 #5

P: n/a
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:5c********************************@4ax.com...
On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <ne*********@nospam.netcom.no>
wrote:
*Jim wrote:
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it is? For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?
SELECT * FROM guestbook SORT BY COUNTER DESC


That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only

wants one row. And ordering by the ID might not even give the latest entry; depends when it was committed (and whether you consider the latest by initial insertion vs. when it was committed to the database).

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool


Well, since he doesn't mention anything about 'editing' posted entries and
the id is auto_incremented then wouldn't the latest entry be the highest id
number? Second, does he want to show only one entry at at time or a pagefull
at a time? Using LIMIT 1 with a loop would require many database accesses
and page reloads (or extra javascript) to look through the guestbook.

SELECT * FROM guestbook ORDER BY counter DESC
would give him all the entries from last to first...

or:

--code--
// set defaults if none received
$gb_start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$gb_end = 10;
$gb_msg = '';
$gb_link = '';

$gb_query = "SELECT * FROM guestbook ORDER BY counter DESC LIMIT $start,
$end";
$gb_result = mysql_query($gb_query,$dbc); //$dbc is the database connection

if (!$gb_result)
{
die('Error performing query. '.mysql_errno($dbc).': '.mysql_error($dbc));
}

$gb_max_entry = mysql_num_rows($gb_result);
if ($gb_max_entry < 10)
{
$gb_msg = 'End of guestbook.';

}

for($loop = 0; $loop <= $gb_max_entry; $loop++)
{
$gb_entry = mysql_fetch_array($gb_result);
echo "<p>$gb_entry[date]</p>";
echo "<p>$gb_entry[name]</p>";
echo "<p>$gb_entry[message]</p>";
}

if ($gb_msg == '')
{
$gb_start += $gb_max_entry;
$gb_link = "<br><br><p
align='center'>http://www.your.domain/guestbook.php?start=$gb_start</p>";
}
else
{
echo "<br><br><p align='center'>$gb_msg</p>";
}
-- end of code --

This would go through all the entries backwards until none were left,
providing a link to the next page each time.

Norm
---
FREE Avatar Hosting at www.easyavatar.com


Jul 17 '05 #6

P: n/a

"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:5c********************************@4ax.com...
On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <ne*********@nospam.netcom.no>
wrote:
*Jim wrote:
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it is? For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?
SELECT * FROM guestbook SORT BY COUNTER DESC


That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only

wants one row. And ordering by the ID might not even give the latest entry; depends when it was committed (and whether you consider the latest by initial insertion vs. when it was committed to the database).


I think the OP was only resorting to the "one at a time" idea since he was
getting it in ascending order and didn't know any other way of reversing the
list, so the proper statement would be (untested):

SELECT * FROM guestbook ORDER BY counter DESC

(I believe :) )
Jul 17 '05 #7

P: n/a
JV
Jim wrote:
I'm using PHP & MySQL to create a simple guestbook. I've created my
table and I'm able to load my information in as usual. I would like it
to display the latest entry first though. I set an id to each entry
that is auto-incremented. The idea seems real easy in theory:

1. Create a loop that starts at the last entry and goes until it
finishes the first.
2. Each time around display the entire entry.

My question is, how do I find the last entry if I don't know what it is?
For example, right now I have three entries and I can display them
starting with the first. I know there are three so I set my counter to
start at 0 and end on 2 (0,1,2) now if I want to reverse it and start
with the last, how do I get the last id?

Any help would be appreciated.


heh counter thats funny... dont use counter.. please :)

mysql_query returns resourse thats array of records

uses the mysql_fetch_row or mysql_fetch_array as listed below

the rows will already be sorted into correct order by the DB query
actual working code follows
1: dbconnect() is a simple function that logs into the db with set
username and password returning the resource handle
2: indx is an autoincrementing value in the table.

sorting by index will sort by entry order :D

--START CODE--

if ( ($dbl = dbconnect()) == DBCONNERROR){
echo "<h2>Guest book is currently unavailable for viewing. Please
try again later.</h2>\n";
} else {
$author = ""; $dtime=""; $message="";
$query = "SELECT * FROM GuestBook ORDER BY indx DESC";
$result = mysql_query($query);

if (!($result)){
echo "Error reading Guestbook<br>";
print_r($result);
} else {
$posts = mysql_num_rows($result);

while ($record = mysql_fetch_row($result)) {
$enid = $record[0];
$author = $record[2];
$dtime = $record[3];
$message = $record[4];
$pub = $record[1];

$by = "<b>By</b> : <i>$author</i><br>\n";
$dt = "<b>Date</b> : <i>$dtime</i><br>\n";
$ms = "<q>$message</q>\n";

$entry = "<hr><p>";
$entry .= "$by";
$entry .= "$dt";
$entry .= "$ms</p>";

echo $entry;
}
}
mysql_close($dbl);
}
--END CODE--

as i said this code works on actual site.

mysql creation statement for table GuestBook is :
CREATE TABLE `GuestBook` (
`indx` int(11) NOT NULL auto_increment,
`public` enum('yes','no') NOT NULL default 'yes',
`author` varchar(50) NOT NULL default '',
`tstamp` varchar(100) NOT NULL default '',
`message` blob NOT NULL,
PRIMARY KEY (`indx`)
) TYPE=MyISAM;

with this table and the preceding code you should be up in no time :D

hth
JV
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.