problem in selecting certain rows from table

[yllai]

i have 100 records in my database table A, they do have id(index for the table). however, 100 records in this table not means that the index was from 1 to 100..the last row index was 123, that is because i had deleted some of the rows in among these 100 rows.

Now I want select data from row 10 to row 30..how can i do it? i can't coded as "SELECT * FROM A WHERE id BETWEEN 10 and 30" bcos that might be some row have been deleted in the interval...

i tried code as "SELECT * FROM A LIMIT 9,20", it can work at all...

any idea?

[Netizen]

"SELECT * FROM A LIMIT 9,20" should work - what results did it give you?

[henry0]

Try:

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 be integer constants. 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):
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

review:
[dev.mysql.com...]

[coopster]

Which database are you using? The

LIMIT

clause is MySQL-specific so it makes it impossible to do the equivalent query on another database as-is. However, it is possible to write the query using that database's version of

LIMIT

(e.g. MS-SQL's

TOP

, Oracle's

ROWNUM

). I believe PostgreSQL is the only other database implementing

LIMIT

, but their syntax is actually slightly different than MySQL due to some technical misunderstandings when they attempted to implement the feature as copied from the MySQL syntax.

[yllai]

i'm using MySQL database. I had tried with both of these suggestions- LIMIT, ROWNUM...but I din't get any result! It didn't return any data to me.

LINIT:
SELECT * FROM table_A ORDER BY id LIMIT 0,20;

ROWNUM:
SELECT * FROM table_A WHERE ROWNUM>=0 AND ROWNUM<=20;

any other ideas?

[dreamcatcher]

Whats the rest of your code?

[ergophobe]

yllai,

Can you get the query to work in the MySQL client? That's the first step - find out whether it's the PHP or the SQL part that's failing. If the query works fine on the MySQL command line (or via phpMyAdmin or mysql-front), then you perhaps are not connecting correctly to the DB server. If it has problems, debug it from the mysql command line.

Cheers,

Tom

[yllai]

I had tried it with phpAdmin, I sound work well..then what should I do with my php script, since it can't work well eventhough I coded it with same script that I code in phpAdmin.

database connection problem? How to fix it?

[henry0]

yllai
In order to have a better idea on what's going on
Why don't you post the connection part of your script

<edit>Addition
Did you run a query in PHPmyAdmin?
what I mean is copy and paste in PHPmyAdmin (query) the query section of your PHP code and see if any error is coming up
Ususaly it will read "SQL syntax error" indeed if any error exists!
</edit>