Getting greatest 30 id's from a database

[Jalenack]

This is a quick and easy one:

I need to get the last 30 greatest id's from a MySQL database:


$sql = "SELECT * FROM ".$table_prefix."liveshoutbox WHERE id < ?? ORDER BY id ASC LIMIT 30";
$results = $wpdb->get_results( $sql );
$results = array_reverse($results);


I'd prefer not the have to reverse with php the results, but I need it with reverse chronalogical order here's an example:

Entry 330, the latest one, comes up first
Entry 329 comes next
...
Stop at 300

I'm sure this is pretty easy, but I haven't dealt with MySQL much and google didn't quite turn up anything...thanks!

Oh, and don't worry about the $wpdb stuff...thats just wordpress and it all works fine there

[Brandoe85]

Try this:

$sql = "SELECT * FROM ".$table_prefix."liveshoutbox ORDER BY id DESC LIMIT 30";
$results = $wpdb->get_results( $sql );
$results = array_reverse($results);

[Jalenack]

perfect, thanks.

Any good sites where I can find stuff like that out?

[Kurashu]

You just learn it over time. That's along the lines of the code I thought of when I saw the topic name. But I'm pretty sure you want to take that array_reverse out if you want 330, 329, 328...302, 301, 300.

[Jalenack]

Yea I figured that out right away...

Now something a little more complex..

What if I wanted to all of the thirty greatest except for the actual greatest:

Entry 330, the latest one, DOES NOT SHOW
Entry 329 is FIRST
Entry 328 is second
..
Entry 300 or so is last.

[Brandoe85]

What version of mysql are you running? If its 4.1 or later, you could use a subquery. Otherwise, run a query to get the max(id), store that, then run a query without selecting that record.

[Jalenack]

I'm running MySQL 4.0.24 but the thing is this is a Wordpress Plugin so it needs to run on all sorts of funky installs..

I can just hack my way around for now...but if you have a solution I'd love to hear it.

[Brandoe85]

Try getting the max(id) and running with that...you can tweak with this see if you can't get it going:

$getId = "select max(id) from " . $table_prefix . "liveshoutbox";
$idResult = mysql_query($getId) or die(mysql_error());
$maxId = mysql_result($idResult, 0,0);

$sql = "SELECT * FROM ".$table_prefix."liveshoutbox where id != $maxId ORDER BY id DESC LIMIT 30";

[Jalenack]

That works great...but it seems a little wasteful..

would it possible to just make it move down one row before the loop starts?


<?php foreach( $results as $r ) { echo '<li><span>'.$r->name.' : </span>'.$r->text.'</li>'; } ?>

[Brandoe85]

Maybe a counter?

<?php
$count = 0;
foreach( $results as $r )
{
if($count != 0)
{
echo '<li><span>'.$r->name.' : </span>'.$r->text.'</li>';
}
$count++;
}
?>

[Jalenack]

Wow. Problem solved. That's just what I wanted... Thanks

[Brandoe85]

You're welcome :thumbsup:

[Kurashu]

$sql = "SELECT * FROM ".$table_prefix."liveshoutbox ORDER BY id DESC LIMIT 1,29";

That'll get the 29 rows you want. More often than not, there is a way to get the data you want from a table without using third party tools (in this case PHP). It just takes a little searching sometimes. =D

[Jalenack]

Even better!

Now I'm working with timestamps. I am using a time_since function, and it needs UNIX TIME input. Right now, the mysql database creation defaults to a mysql timestamp, but it needs to be unix. I can convert it with php, but it's a pain and I think it's messing up for some people. It ends up where the time-since function says 34 years have passed, when it should say a couple minutes. So i'd like the mysql end to do the time info. Here is my current code:


function jal_getData($lastID) {
global $wpdb;
include( dirname( dirname( dirname( dirname( __FILE__ ) ) ) )."/wp-config.php" );
$sql = "SELECT * FROM ".$table_prefix."liveshoutbox WHERE id > ".$lastID." ORDER BY id DESC LIMIT 1";
$results = $wpdb->get_results( $sql);
foreach( $results as $r ) {
echo $r->id." ---".$r->name." ---".$r->text." ---".time_since(strtotime( $r->time ))." ago ---"; // --- is being used to separate the fields in the output
}
}

[Kurashu]

That'll take some PHP code. Change your table definition to drop the MySQL time stamp, create a new time field, then when your shout box submits create a variable that is set to time("u") and place that in as the value.

So something like:


$time = time("u");
$sql = 'INSERT INTO ' . $table_prefix. 'liveshoutbox VALUES ('.$thing.','.$stuff.', '$time.')';


Obviously, that wouldn't be your SQL, but you get the idea.

[Jalenack]

Worked perfectly after of editing the mysql structure...nice

[Kurashu]

No problem. Glad I could help.