getting last 180 records of recordset

[chelnov63]

hi guys i want to return only the last 180 records of a recordset (but i want to keep the id in ASC order):

 

$selectSQL = "SELECT * FROM messages ORDER by id ASC";
$rs = mysql_query($selectSQL,$conn);

 

is there anyway to do this? thanks in advance

[rajivgonsalves]

you should looks at

 

http://php.net/manual/en/function.mysql-result.php

[cags]

you should looks at

 

http://php.net/manual/en/function.mysql-result.php

How would that help?

 

Unless I'm going mad, if you want the last 180 items when ordered ASC, this is the same as the first 180 when ordered DESC, the only difference is the returned dataset is in the opposite order. That being said, you could probably just fetch them and then use something like mysql_data_seek to iterate through the array backwards (using a for loop and mysql_num_rows).

[emopoops]

no u need to use LIMIT $realstart, 180

the real start is a variable when u find the number of rows with mysq_num_rows()

then minus 180 from it.

 

180 is the limit

 

your welcome honeei

[rajivgonsalves]

you should looks at

 

http://php.net/manual/en/function.mysql-result.php

 

How would that help?

 

Unless I'm going mad, if you want the last 180 items when ordered ASC, this is the same as the first 180 when ordered DESC, the only difference is the returned dataset is in the opposite order. That being said, you could probably just fetch them and then use something like mysql_data_seek to iterate through the array backwards (using a for loop and mysql_num_rows).

 

did not know about that function anyways mysql_result will be the same in a for loop, its just a matter of preference

 

[cags]

no u need to use LIMIT $realstart, 180

the real start is a variable when u find the number of rows with mysq_num_rows()

then minus 180 from it.

 

180 is the limit

 

your welcome honeei

That is another option, but it relies on two queries and no changes being made to the table between the two queries, which can't be guaranteed on a busy table.

[emopoops]

a busy table? are u saying they let the mysql datbases change while the script is run?

[cags]

If an UPDATE, DELETE or INSERT statement occurs between...

 

// here
$result = mysql_query("SELECT COUNT(id) AS c FROM table");
$row = mysql_fetch_assoc($result);
$start = $row['c'] - 180;
// and here
mysql_query("SELECT * FROM table LIMIT $start, 180");

You won't necessarily get the right values. In fact if rows are deleted, you could end up with less than 180 records returned.

 

[emopoops]

r u joking me?

i use mysql_num_rows right before i do my querys all the time. php is done in milascrnds

[cags]

No I am not joking you, you use it do you? Oh well there's a definitive answer let's all start using your coding practices.

 

The fact is your a.) using mysql_num_rows, which means you must be using a SELECT statement without a WHERE clause, thus returning either all the data in the table or at least all the information in a single field of the table. This is extremely inefficient in itself especially if you are doing nothing with the data, how many rows do you have in your table? A dozen? Try running that code on a table with thousands or tens of thousands or more rows and see what happens. b.) how many people have ever used anything you have coded at the same time? Two, perhaps? A popular site could have 100's, 1000's or even 10s of 1000's of hits every second. The fact is though, even if your site has only ever had two hits, if the other person clicks the link at approximately the same time as you, they could still remove one of the last 180 items in the midst of your script running.

 

Don't assume that just because you do something, that it is the definitive method. There's a good chance that the method I suggested isn't the most efficient/effective way, but it would be more efficient/reliable than your code. Do we know the OP requires a site that can cope with large data and/or lots of hits? No. Should we assume this means they don't? No!

[emopoops]

i dont understand. i thot php did things in order. like whoever clicks first gets the stuff nd then the  next user gets the next. i dont understand what ur supposed to do then if it changes? HOW THE HECK ARE U SUPPOSED TO BE SURE THEN? i dont understand what im supposed to change in my script i use mysql_num_rows for like everything

[cags]

mysql_num_rows is fine for certain things, that's why it's there, but it's not always the best solution. If you are doing something with the data that has been selected then it is the best solution, but if you only wish to know how many items are in a table you do not need all the information in the table so it is pointless fetching it all. Using SELECT COUNT(id) as row_count FROM table will be far more effective.

[Daniel0]

mysql_num_rows() can be used for exactly what it's supposed to do: tell you how many rows your query returned. There is no guarantee that if you execute the query again, you'll get the same number unless you lock the table.

[emopoops]

i dont understand how to lock  a tbake.

and also

i dont understand how to use count() in the actual query being used?

so its the query plus the count inside the query not two seps?

[premiso]

HOW THE HECK ARE U SUPPOSED TO BE SURE THEN?

 

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

 

Read up on MySQL Transactions as you can Lock tables from being changed while running a script.

 

EDIT: As Daniel stated.

 

Also a decent article here: http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm

[Daniel0]

i dont understand how to lock  a tbake.

 

Really simple:

LOCK TABLES the_table WRITE;

and then

UNLOCK TABLES;

when you're done.

[emopoops]

i still dont understand if u lock a table then what happens when a nother user tries to update it while its locked? does it auto unlock?

 

[Daniel0]

The query will be queued until the table is unlocked again.

[emopoops]

so thats all i need t o change in all my scripts with mysql_num_rows()

when do i lock them? when it begins? and unlock at the end of the stuff?

taht is the definite answer for my site? is there a way to tell if the table is being locked?

so i can echo to the user the table is in use please wait'?