PHP Programming - MySQL/PHP problem

Author

MySQL/PHP problem

Jim

2004-12-16, 8:56 pm

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

2004-12-16, 8:56 pm

*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'

sharma

2004-12-16, 8:56 pm

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

2004-12-16, 8:56 pm

On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <news.thomas@nospam.netcom.no>
wrote:

>*Jim 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. 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.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Thomas

2004-12-16, 8:56 pm

*Andy Hassall wrote:

> *Thomas wrote:
>
> 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'

Norman Peelman

2004-12-17, 8:56 am

"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:5c64s0toa2i7583eo9sdn23oir0s5k4i6r@
4ax.com...
> On Fri, 17 Dec 2004 00:15:43 +0100, Thomas <news.thomas@nospam.netcom.no>
> wrote:
>
is?[color=darkred]
>
> 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.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 "$gb_entry[date]";
echo "$gb_entry[name]";
echo "$gb_entry[message]";
}

if ($gb_msg == '')
{
$gb_start += $gb_max_entry;
$gb_link = " http://www.your.domain/guestbook.php?start=$gb_start";
}
else
{
echo " $gb_msg";
}
-- 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$

2004-12-17, 3:56 pm

2004-12-30, 3:56 am

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 ";
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 = "By : $author \n";
$dt = "Date : $dtime \n";
$ms = "<q>$message</q>\n";

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

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

Sponsored Links