Page 2 of 2

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 4:46 pm
by califdon
Foxy999 wrote:that leads me to my next question, is it possible to read the table starting from row 2 or 3 when rows are being constantly changed and deleted?
You need to understand a truly fundamental concept: within a table, there is no defined order to the rows. That may seem counterintuitive, but it is essential to understand if you're going to work with databases. The logic of SQL is based on this concept. Naturally, the rows are in some order in the physical storage medium, but they are considered to not have any particular order, so you must never rely on what you imagine the order to be. The order is always determined by the data contained in the rows, using indexes. That's what queries are for, among other things. For instance, it is quite possible for a backup process to rewrite a table in a different physical sequence, and that is perfectly allowable and properly written database applications won't care.

So it is possible to do what you asked, but it is a meaningless concept. Putting it another way, it is meaningless to speak of "row 2" in the abstract. You can speak of "the second lowest ID number", but that might be the 37th row stored in the table. It's always the data that is meaningful, not how you think it might be stored in the table. This is crucially important and it is one of the important ways in which databases are completely unlike spreadsheets.

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 6:18 pm
by Foxy999
I understand what you are saying, so how can I select the id's that are larger than the 10th id?

Foxy

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 6:57 pm
by Ollie Saunders
Use LIMIT for that.
MySQL manual wrote: The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 9:51 pm
by califdon
Foxy999 wrote:I understand what you are saying, so how can I select the id's that are larger than the 10th id?

Foxy
It's not clear to me what you are trying to do. When you use a SELECT SQL statement, you typically use an ORDER BY clause at the end, which determines the order in which the rows are returned, for example:

SELECT lastname, firstname, address, balance FROM myTable ORDER BY lastname, firstname

If you are asking about something like pagination, where you want to return the first 10 rows, then the next 10 rows, etc., then you would use the LIMIT clause, as Ollie said, but that has nothing at all to do with id's. But your question troubles me, because you asked about selecting "id's that are larger than the 10th id", which can be interpreted to mean that you want to ignore the ten lowest numbered id's and return records with larger numbers in the id field. That doesn't make much sense to me. If you would explain just what it is you are interested in DOING, I could give you a better answer.