[Resolved] Showing only last 15 entries

[Schwalbach]

This is not resolved. I didn't mean to click the button.

Hey,

I have a chat script, and basically want php to show only the last 15 entries from a db? Also, if I could display the last entry at the bottom of the page, that would be nice.

I have searched, but I can't find a good search term('last rows' 'show last rows' etc)

Thanks,
Chase

[Sxooter]

select * from table order by entry_date desc limit 15;

[Schwalbach]

Hey,

Thanks, I will try that...I am using an on enter frame action to calls this, which means I am going to be reloading this file constantly. By just using:

select * from chat order by entry_date desc limit 15;

I will be able to make the new entries show up automatically?

Also, On this enterframe, I am going to load a php file that is going to tell the time, so it will keep updating the time in mysql. Is there a way to have mysql just delete an entry after a specified amount of time? This is because I want to do a users online list, and if I can just delete the old usernames, it would make the updating of the users alot easier...if not, is there any easy way to make one of these?

[Sxooter]

something like

delete from table where entry_date > now() - interval (1 hour)

or something like it. MySQL has a different syntax for time, but it's in the docs...

[Schwalbach]

Hey,

Thanks for the help, I almost have it..here is my php:

 <?php
$user = $_POST['user'];
$timestamp = time();
$timeoutseconds = 300; 
$timeout = $timestamp-$timeoutseconds; 
$dbh=mysql_pconnect ("****", "**", "**") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("chasesch_chat"); 
$delete = mysql_query("DELETE FROM usersonline WHERE times < '$timeout'");
$query = "SELECT Username FROM usersonline WHERE Username = '$user'"; //Select the correct row for the username
$rows = mysql_query($query) or die ("Chase has an Error in query; $query.". mysql_error()); //Query the db
$numrows = mysql_numrows($rows); //Count the rows that return w/ the matching username
if($numrows < 1) {
$sql = "INSERT INTO usersonline SET Username='$user'";
mysql_query($sql);
} else {
$sql = "UPDATE usersonline SET Username='$user' WHERE Username='$user'";
mysql_query($sql);
}
echo("&users=");
$result = mysql_query("SELECT Username FROM usersonline");
while ( $row = mysql_fetch_array($result) ) {
 echo($row['Username']."\n");
 }
?> 

Here are my problems:

It is not deleting anything after the 5 minute interval

$delete = mysql_query("DELETE FROM usersonline WHERE times < '$timeout'");

It is not updating, so that any time the php is loaded the time gets reset to now

My DB has a field with Username, which is just a null varchar(255)

and then has a times row with timestamp(14)

Thanks again

[Schwalbach]

Just bumping this up for you Sxooter. Just read my last post, I have had a couple problems,

Thanks for all your help,
Chase

[Sxooter]

I'm not gonna be a lot of help in the time thing in MySQL. I do all my calendar / time stuff in postgresql.

Try printing out the query to the screen and cutting and pasting it into the mysql monitor to see what it says.

[Schwalbach]

Hey,

Thanks anyways man. I will try to figure out the time thing. Any help on the other problem, with the updating and all that? Like why wouldn't it update if I reload this php file. It should update the time that it was entered, right?

[LordShryku]

How are you recording the "times" field during the initial INSERT query? MySQL's timestamp field is very much different than using the time() funtion. So if you're not using the time() function at insert, this could be your problem...

[Schwalbach]

Hey,

I am not doing anything with the time field. I am just letting it set itself. How should I set it? In my code(about 3 posts up) I only set one area, not the time. How should I set it?

 <?php
$user = Schwalbach;
$timestamp = time();
$timeoutseconds = 300; 
$timeout = $timestamp-$timeoutseconds; 
$dbh=mysql_pconnect ("***", "***", "**") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("chasesch_chat"); 
$delete = mysql_query("DELETE FROM usersonline WHERE times < '$timeout'");
$query = "SELECT Username FROM usersonline WHERE Username = '$user'"; //Select the correct row for the username
$rows = mysql_query($query) or die ("Chase has an Error in query; $query.". mysql_error()); //Query the db
$numrows = mysql_numrows($rows); //Count the rows that return w/ the matching username
if($numrows < 1) {
$sql = "INSERT INTO usersonline SET Username='$user', times='$timestamp'";
mysql_query($sql);
} else {
$sql = "UPDATE usersonline SET Username='$user' WHERE Username='$user'";
mysql_query($sql);
}
echo("&users=");
$result = mysql_query("SELECT Username FROM usersonline");
while ( $row = mysql_fetch_array($result) ) {
 echo($row['Username']."\n");
 }
?> 

You can see my inserts and updates in there. Whenever I do this, even if I refresh my page the time stays the same in the tables. I just have two tables now, each varchar(255) one named times and other named Username

[Schwalbach]

Ok,

I guess no one knows the answer to that, but this is an easier one..

You told me to do the desc thing, but I wanted it to show the other way, so I did asc. But now when I do that it only shows the first 15 entries in the db, instead of the last.

I want to show the last 15 entries in the db with the newest entry on the bottom.

$sql = "SELECT comments FROM chat ORDER BY ID desc limit 15";
$result = mysql_query($sql);
echo("&display=");
while ( $row = mysql_fetch_array($result) ) {
 echo($row['comments']."\n");
 }

[Sxooter]

unix timestamp versus sql timestamp...

In unix, a time stamp is the number of seconds since a certain date, usually jan 1 1970 or something like (could be 1972, I'm note sure...) they look like this:

1074328302

In SQL a timestamp can represent dates from way way back to (usually) a few thousand years into the future. Since they won't fit as a simple 32 bit int, they are stored in a higher resolution internally, either as text or as a very large integer (64 bits). A SQL timestamp looks like this:
2004-01-31 12:34:55-0700

A timestamp field in mysql autoupdates to a value like the second example shows. You'll likely do better using a < symbol and a SQL style timestamp:

delete from table where timestampfield < now - cast ('5 minutes' as interval);

I'm pretty sure intervals aren't there yet in MySQL, maybe in 5.0.0 if you're feeling brace.

If not, there's got to be a standard alternate syntax in the docs on timestamp types.

[Sxooter]

Two options, either use a subselect or do it in code.

select * from (SELECT comments FROM chat ORDER BY ID desc limit 15) as a order by a.ID asc

Not sure MySQL can do that, there's probably a work around I'm not familiar with for doing such a thing.

Or, since you're only showing 15, just reverse it:

for ($i=15;$i--;$i>0){
  $row = pg_fetch_array($res,$i);
  print $row['fieldname'];
}

and so on.

[LordShryku]

Intervals are in MySQL. I think date_sub would fit the bill here...

http://www.mysql.com/doc/en/Date_and_time_functions.html

[Schwalbach]

Ok guys, I am getting there(almost)

So where should I use the date_sub? On the INSERT or when comparing to see?

For Sxooter. I tried your command, and it ended up saying that it was invalid. Then I tried your for, but changed it around a little:

for ($i=15;$i--;$i>0){
  $row = mysql_fetch_array($result,$i);
  print $row['comments']."\n";
}

This isn't working, it is still just returning the same thing, same order..

Thanks again

[Schwalbach]

Ok, I update sxooter's code. It is displaying them backwards, the way I want it to be, but it is displaying the first ones. Any way to start from the bottom?

for ($i=15;$i--;$i>0){
  $row = mysql_fetch_array($result);
  print $row['comments']."\n";
}

Basically, if I have less that 15 comments, then it shows fine, but if I have like 16, then it will show the first 15, the latest at the bottom and all, but it won't show the 16th at all..

Thanks,
Chase

[Sxooter]

just switch the asc for desc or vice versa and you should be gold.

[Schwalbach]

$sql = "SELECT comments FROM chat ORDER BY ID asc";
$result = mysql_query($sql);
echo("&display=");
for ($i=30;$i>0;$i--){
  $row = mysql_fetch_array($result,$i);
  echo($row['comments']);
}

Hey, Here is my code. Every line is for some reason skipping a line..the output is:

Hi

Hello

Like that? Any reason why?

Edit: Also, it isn't displaying the results correct. It is only showing the first 8 results. What in the world?

[Schwalbach]

Ok, update again. This is ALMOST working..

$sql = "SELECT comments FROM chat ORDER BY ID asc";
$result = mysql_query($sql);
echo("&display=");
for ($i=15;$i>0;$i--){
  $row = mysql_fetch_array($result);
  $row2=array_reverse($row);
  echo($row2['comments']."\n");
}

I tried desc, and it goes in the wrong direction. With asc, it goes in the right direction, everything shows up correctly, but it isn't reading correct. Like it is showing only first 15 entries and not the last 15. I tried you $result,$i thing and it seems to be causing the problems. It makes everything out of order. Any ideas?

[Schwalbach]

Also, For Lordshryku.

I am totally lost with this time stuff. How should I go about this? Like what do I do when I insert it into db, and when I check to see if it is 5 minutes old, and all that. I am really confused with this 🙁

Thanks both of you,
Chase