10 records per page?

[gilgalbiblewhee]

How does PHP display 10 records per page?

[gilgalbiblewhee]

LIMIT seems to be the answer but it's not working.

Select * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 0, 10

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 0, 10'., SQL state 37000 in SQLExecDirect in C:\xampp\htdocs\wheelofgod\search\cat\query.php on line 262
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 0, 10'.

[Deadeye]

I dont know php, but why dont you query for all the results and in your while loop just iterate it 10 times?

This would work in C# and perl but I dont know about php

Wesley

[curtiss]

Originally Posted by Deadeye

I dont know php, but why dont you query for all the results and in your while loop just iterate it 10 times?

This would work in C# and perl but I dont know about php

Wesley

It will work in PHP, too, but that seems like an incredible waste of resources. Imagine that you have a few million records and how many extra resources it would use to retrieve them all just to use 10 of them.

Limit is most likely the answer you're looking for, Gil, but you're using it incorrectly. Limit only accepts one parameter: the total amount of records you want returned. Therefore, rather than:

PHP Code:

Select * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 0, 10 


it should simply be:

PHP Code:

Select * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10 


[gilgalbiblewhee]

I got the following error.

Select * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10'., SQL state 37000 in SQLExecDirect in C:\xampp\htdocs\wheelofgod\search\cat\query.php on line 263
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10'.

[curtiss]

Oh. I didn't even notice you were using Access.

Access doesn't support "Limit". Instead, you need to use a query like:

PHP Code:

Select TOP 10 * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' 


[gilgalbiblewhee]

But if I want to lay out the following pages 10 at a time then is this still good?

[Deadeye]

Originally Posted by curtiss

It will work in PHP, too, but that seems like an incredible waste of resources. Imagine that you have a few million records and how many extra resources it would use to retrieve them all just to use 10 of them.

I understand this, it was just a quick fix until a better solution was found.

Wesley

[curtiss]

Originally Posted by gilgalbiblewhee

But if I want to lay out the following pages 10 at a time then is this still good?

It is if you have some sort of incremental ID by which you can order them. For instance, if you are using an autonumber as the primary key for the table, then you can do something like:

SELECT TOP 10 * FROM mytable WHERE myid > lastid

where "lastid" is the highest ID that you retrieved that last time you pulled the records.

For instance, the "next" and "prev" links on your third page might look something like:

<a href="mypage.php?lastid=34>Previous page</a>
<a href="mypage.php?lastid=34>Next Page</a>

Then, your SQL statements would look something like:

nextSQL = "SELECT TOP 10 * FROM mytable WHERE myid > ".$_GET['lastid']." ORDER BY myid"
prevSQL = "SELECT TOP 10 * FROM mytable WHERE myid < ".$_GET['lastid']." ORDER BY myid DESC"

[gilgalbiblewhee]

I don't understand how myid and lastid will be recognized. Are they defined?

[gilgalbiblewhee]

I don't know where I'm going wrong:

Select TOP 10 * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' AND id='' > lastid

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\wheelofgod\search\cat\query.php on line 268
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

[dropship-dvd]

spam elsewhere you pig.

[gilgalbiblewhee]

spamspamspamspamspamspamspam

[Leprakawn]

Nice report, whee!