get last entry

[kidgeek_dfw]

I'm logging to mysql the capacity of 23 parking lots.

The log gets very huge...
For a simple example this is the mysql structure:

park_lot_id, current_level, status

The only time a new entry is made, is if the current_level has changed.

The only way I know to get the last entry for each parking lot is to build an array for each parking lot and extract the last entry by counting the array....

Because the log is so huge...
Is there a better way to select the last entry for each parking lot?

Thanks...

[ZibingsCoder]

Though it might increase the size a bit, you should consider adding another field, maybe a timestamp, or even a unique identifier. Either of those would aid in getting you an easier way of retrieving only the last entry.

[kidgeek_dfw]

yeah I simplified the log a bit here...
I do have a datetime field...
A timestamp field may work better...

However because the logs can get huge in one day...
I'm trying to find the best way to get the last entry for each lot without building arrays an extracting the highest count()

The other animal is that some lots may not get updated for X amount of days... And X can not be determined...

So surely there is a better way to ask mysql for the last entry of each lot...

The only other unscalable way I can think of is to loop through each lot calling a SELECT statement for each grabbing the last entry... ugh...

[ZibingsCoder]

$string = "SELECT * FROM `table` ORDER BY `timestamp` DESC LIMIT 0,1";

That might help a teensy bit.

[bpat1434]

You could always group by the current_level and read the highest ID.

<?php

$result2 = mysql_query("SELECT current_level FROM testlvl GROUP BY current_level");

while($row2 = mysql_fetch_array($result2))
{
	$levels[] .= $row2['current_level'];
}

foreach($levels as $lvl)
{
	$qry = "SELECT * FROM testlvl WHERE current_level = '$lvl'";
	$result = mysql_query($qry) or die(mysql_errno()."<br>".mysql_error());
	while($row = mysql_fetch_array($result))
	{
		$level[$row['current_level']] = mysql_num_rows($result);
	}
}

var_dump($level);

?>

Should output something like:

array(X) {
[level_1] =>int(num of rows)
[level_2] =>int(num of rows)
}

That should give you what you want.

~Brett

[ZibingsCoder]

Just don't forget to add the LIMIT part of the SQL query. That way the database doesn't work hard pulling everything only for you to grab one thing.

[bpat1434]

Well, my code is meant to grab and count the number of rows, because that seems to be what he wants. If he just wants the highest ID that there is, then it's even easier. Just group them, sort them descending.

Limits don't work with grouping. If you limit the group, then it will only return that many. If you have more than 1 row being returned, you'll always only get one, but not all the different ones. The code I've posted above works to count the number of entries there are in the database on that given level. It's as easy as can be.

It executes 2 queries: 1) Find the different levels. 2) Find all different entries which are on that level.

Easy as pie.

~Brett

[ZibingsCoder]

Ah...think we saw him asking very different things. I thought he was literally just trying to grab the latest record in any group.

[bpat1434]

Of course, if he has multiple tables, and he only wants the next ID number, there's an easier way!!

<?php

$query = "SHOW TABLE STATUS LIKE 'tablename'";
$result = mysql_query($result);
while($row = mysql_fetch_assoc($result))
{
    $id = $row['Auto_increment'];
}

echo $id;

?>

That will give you the next auto_increment ID for the database. If you take that number, and subtract it by 1, you'll get the number of entries. That is true of course if you never delete rows.

~Brett

[kidgeek_dfw]

Sorry went to sleep on my keyboard... hmmm

I'm actually grouping by parking lots into an array, taking the last entry to the gui....

The problem with that is if I have a million inserts for one parking lot... That would be one huge array.... And I have 23 parking lots in the log to bring back into one gui.

I just need the last insert for each parking lot...

But I think your both right...
My only choice may be to build an array between the last 5 days or something... Anything older than that should be a warning anyway...

[bpat1434]

Are you trying to get just the ID, or are you trying to find out the last numeric post (i.e. the total of all posts, plus 1). If you're looking for the last ID, then just use my second code post. If you don't have an ID field, you should really have an auto_increment ID field.

~Brett

[kidgeek_dfw]

Thanks Brett....

Yeah I can group by the parking lot id (not primary id)...

[dhodge]

Well if you need to get the id right after the insert...what is wrong with using

$id = mysql_insert_id();

?

returns that last id that was inserted within the same script.

$query = "Insert...."
$id = mysql_insert_id();

if you need the $id for like every page then I would just make a lookup table ie a db table with 1 field and put that id in it. Update it every time you insert something. Then just retrieve that to get your id. Won't hug up as much space.

[kidgeek_dfw]

yeah I wish it was that simple...

Its not right after the insert, but good try...

[dhodge]

So its inserted then you need the value later? Why not then get the id after the insert and focus on saving that value throughout instead of computing that value later.

[kidgeek_dfw]

dhodge,

HOLY CRAP!!!

You may have just come up with something there...

This is for airport terminal parking so you probably know what I'm dealing with here... many applications accessing this data...

But I could keep a log and a temp table for recent changes for current operational status...