MySQL/PHP problem

[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

[Thomas]

Re: MySQL/PHP problem

*Jim wrote:
[color=blue]
> 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?[/color]


SELECT * FROM guestbook SORT BY COUNTER DESC

--
Thomas

SELECT date FROM wife WHERE bitching = '0' AND sex = '1'

[sharma]

Re: MySQL/PHP problem

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...

[Andy Hassall]

Re: MySQL/PHP problem

On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <news.thomas@no spam.netcom.no>
wrote:
[color=blue]
>*Jim wrote:
>[color=green]
>> 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?[/color]
>
>SELECT * FROM guestbook SORT BY COUNTER DESC[/color]

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 / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Thomas]

Re: MySQL/PHP problem

*Andy Hassall wrote:
[color=blue]
> *Thomas wrote:[color=green]
>>
>> SELECT * FROM guestbook SORT BY COUNTER DESC[/color]
>
> That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only
> wants one
> row.[/color]


Doh! Most certainly ORDER BY!




--
Thomas

SELECT date FROM wife WHERE bitching = '0' AND sex = '1'

[Norman Peelman]

Re: MySQL/PHP problem

"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:5c64s0toa2 i7583eo9sdn23oi r0s5k4i6r@4ax.c om...[color=blue]
> On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <news.thomas@no spam.netcom.no>
> wrote:
>[color=green]
> >*Jim wrote:
> >[color=darkred]
> >> 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[/color][/color][/color]
is?[color=blue][color=green][color=darkred]
> >> 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?[/color]
> >
> >SELECT * FROM guestbook SORT BY COUNTER DESC[/color]
>
> That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only[/color]
wants one[color=blue]
> row. And ordering by the ID might not even give the latest entry; depends[/color]
when[color=blue]
> it was committed (and whether you consider the latest by initial insertion[/color]
vs.[color=blue]
> when it was committed to the database).
>
> --
> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]

Well, since he doesn't mention anything about 'editing' posted entries and
the id is auto_incremente d 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_arr ay($gb_result);
echo "<p>$gb_ent ry[date]</p>";
echo "<p>$gb_ent ry[name]</p>";
echo "<p>$gb_ent ry[message]</p>";
}

if ($gb_msg == '')
{
$gb_start += $gb_max_entry;
$gb_link = "<br><br><p
align='center'> http://www.your.domain/guestbook.php?s tart=$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

[Richards Noah \(IFR LIT MET\)]

Re: MySQL/PHP problem


"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:5c64s0toa2 i7583eo9sdn23oi r0s5k4i6r@4ax.c om...[color=blue]
> On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <news.thomas@no spam.netcom.no>
> wrote:
>[color=green]
> >*Jim wrote:
> >[color=darkred]
> >> 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[/color][/color][/color]
is?[color=blue][color=green][color=darkred]
> >> 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?[/color]
> >
> >SELECT * FROM guestbook SORT BY COUNTER DESC[/color]
>
> That'd be ORDER BY, not SORT BY. And then LIMIT 1, since the OP only[/color]
wants one[color=blue]
> row. And ordering by the ID might not even give the latest entry; depends[/color]
when[color=blue]
> it was committed (and whether you consider the latest by initial insertion[/color]
vs.[color=blue]
> when it was committed to the database).[/color]

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 :) )

[JV]

Re: MySQL/PHP problem

Jim wrote:[color=blue]
> 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.[/color]

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

mysql_query returns resourse thats array of records

uses the mysql_fetch_row or mysql_fetch_arr ay 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 autoincrementin g 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($qu ery);

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>$messag e</q>\n";

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

echo $entry;
}
}
mysql_close($db l);
}
--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