...

View Full Version : How to limit search with 10 rows per page



gilgalbiblewhee
11-28-2007, 10:34 PM
I'm using MSAccess 2000 with the Apache server. I'm getting 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'.

Fumigator
11-28-2007, 11:46 PM
I'm using MSAccess 2000

You have my sympathies.

Anyway, I Googled "ms access +limit" and the second result has an answer:

http://lists.evolt.org/archive/Week-of-Mon-20030428/140040.html

I dunno if it actually works. If not, then maybe one of the other million search results will have an answer that works.

gilgalbiblewhee
11-29-2007, 12:44 AM
MS Access
"SELECT TOP N WHERE ..."

This works but if I want to post pages of 10 records per page ...how would that work?

thesavior
11-29-2007, 01:03 AM
the easy way to do pagination is along the following theory:

in your url you have a variable like: page



http://www.yoursite.com/search.php?page=1


then in your php, you have something along the lines of:



$page = $_GET['page'];
$resultspertpage = 10;

$end = $page*$resultsperpage;
$start = ($page*$resultsperpage)-$resultsperpage;


then you will fetch the resulting rows of your query that are between $start and $end.

With mysql, the query you would use would be something like:


$query = "SELECT * FROM tbl LIMIT ".$start.",".$end;

gilgalbiblewhee
11-29-2007, 01:28 AM
Where do I put page=1? in the action of the previous page? I tried that but it ignores that.

thesavior
11-29-2007, 04:25 AM
okay, lets say you have a search box, the first time you go to search.php:



<form action="search.php?page=1" method="post" />
blah blah blah
</form>


then you have all your processing and page 1 shows up.

or you can just do like
<a href="http://www.mysite.com/search.php?page=1" />
<a href="http://www.mysite.com/search.php?page=2" />
<a href="http://www.mysite.com/search.php?page=3" />
<a href="http://www.mysite.com/search.php?page=4" />

gilgalbiblewhee
11-29-2007, 06:18 AM
the easy way to do pagination is along the following theory:

in your url you have a variable like: page



http://www.yoursite.com/search.php?page=1


then in your php, you have something along the lines of:



$page = $_GET['page'];
$resultspertpage = 10;

$end = $page*$resultsperpage;
$start = ($page*$resultsperpage)-$resultsperpage;


then you will fetch the resulting rows of your query that are between $start and $end.

With mysql, the query you would use would be something like:


$query = "SELECT * FROM tbl LIMIT ".$start.",".$end;

Oh yeah MS Access 2000 doesn't allow the LIMIT. It allows TOP 10 before the * after SELECT.

thesavior
11-29-2007, 03:48 PM
this will be slower, but what if you fetch all of them, put them into an array, then have php do the pagination without msaccess. Like have php take the results from keys 10-20.

aedrin
11-29-2007, 04:08 PM
Why bother with that when there already is a far more efficient solution?

thesavior
11-29-2007, 10:37 PM
Because I am trying to be helpful. We don't know of any more efficient solution with msaccess, that is the problem that we are trying to address.

aedrin
11-29-2007, 10:47 PM
The second post had a perfect solution posted.

gilgalbiblewhee
12-19-2007, 10:07 PM
I changed to mysql ( from MS Access ).

I have this so far:

$perpage = 10;
$start = (!empty($_GET['start'])) ? $_GET['start'] : 0;

$sql.= " LIMIT " . $start . "," . $perpage;

But I still don't understand. What am I missing to go on to page 2?

gilgalbiblewhee
02-06-2008, 05:44 AM
any ideas? I didn't get a response.

StupidRalph
02-06-2008, 06:50 AM
I changed to mysql ( from MS Access ).

I have this so far:

$perpage = 10;
$start = (!empty($_GET['start'])) ? $_GET['start'] : 0;

$sql.= " LIMIT " . $start . "," . $perpage;

But I still don't understand. What am I missing to go on to page 2?


<a href="http://www.mysite.com/search.php?page=2" />

Or you can check the Post a PHP Snippet forum for Paging Class by Firepages.

gilgalbiblewhee
02-06-2008, 07:04 AM
How am I supposed to insert page=1 in the url? If the page =1 isn't there I can't use the get method.

I have:

echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl . $urlsnip . "'>Page 2</a><br />";
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl . $urlsnip . "'>Page 3</a><br />";
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl . $urlsnip . "'>Page 4</a><br />";
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl . $urlsnip . "'>Page 5</a><br />";

where $baseurl is:

$baseurl = "/*******/search/cat/tableformat.php?";
and the $urlsnip is:

keyword1=john&keyword2=smith&submit=+Search+&optAction=on

StupidRalph
02-06-2008, 07:11 AM
page=1&keyword1=john&keyword2=smith&submit=+Search+&optAction=on
You might also want to rename $urlsnip to be $querystring since that what it is.

gilgalbiblewhee
02-06-2008, 07:26 AM
Yeah but how?


<form action="tableformat?page=1">
Like that?
I don't think it works.

StupidRalph
02-06-2008, 08:20 AM
Post all of your code and ask what you're trying to do...I really don't get what you're asking I think whatever it is its been stated.

You simply want paging....

On the page that pulls up the results, you dynamically write in the links with what page number it is...as thesavior has pointed out.

You can get the number of pages needed by dividing the results returned total by however man you are requesting per page. Then run a for loop that prints out that many pages that dynamically writes the page
WARNING: This is untested psuedocode. As such it may contain simple syntax errors and/or other omissions in code. But it should be enough to guide you on the right path but it is not meant to be copy and pasted then used as a solution.



$sql = SELECT * FROM table WHERE `` = 'value';
$q = mysql_query($sql);

$total = mysql_num_rows($q) or die(mysql_error());

$numOfPages = $total / $resultsperpage;

for ($i=0; $i < $numOfPages; ++$i) {
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl .
"page=" . $i . "&" . $urlsnip . ">Page" . $i . "</a><br />";
}




Upon page load you check which page is being loaded and set your start and how many to return.


$page = $_GET['page'];
$resultspertpage = 10;

$end = $page*$resultsperpage;
$start = ($page*$resultsperpage)-$resultsperpage;

gilgalbiblewhee
02-06-2008, 10:06 PM
Post all of your code and ask what you're trying to do...I really don't get what you're asking I think whatever it is its been stated.

You simply want paging....

On the page that pulls up the results, you dynamically write in the links with what page number it is...as thesavior has pointed out.

You can get the number of pages needed by dividing the results returned total by however man you are requesting per page. Then run a for loop that prints out that many pages that dynamically writes the page
WARNING: This is untested psuedocode. As such it may contain simple syntax errors and/or other omissions in code. But it should be enough to guide you on the right path but it is not meant to be copy and pasted then used as a solution.



$sql = SELECT * FROM table WHERE `` = 'value';
$q = mysql_query($sql);

$total = mysql_num_rows($q) or die(mysql_error());

$numOfPages = $total / $resultsperpage;

for ($i=0; $i < $numOfPages; ++$i) {
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl .
"page=" . $i . "&" . $urlsnip . ">Page" . $i . "</a><br />";
}




Upon page load you check which page is being loaded and set your start and how many to return.


$page = $_GET['page'];
$resultspertpage = 10;

$end = $page*$resultsperpage;
$start = ($page*$resultsperpage)-$resultsperpage;

I don't understand the logic behind this.

$page = $_GET['page'];
How can the last part you posted be applied in the query if there is no page in the url?

StupidRalph
02-06-2008, 10:20 PM
//by using an if statement
if (empty($_GET['page'])) {
$page = 1;
}
else {
$page = $_GET['page'];
}

$page = (empty($_GET['page'])) ? 1 : $_GET['page']; //or you can use the ternary operator


Also, you should find a less demonstrative way to communicate here.

gilgalbiblewhee
02-06-2008, 10:36 PM
I didn't mean to yell. I was trying to emphasize when I put in bold.

I used the code you gave but clicking on the links looks like this:

page=1&page=2&submit=+Search+&keyword3=a+voice&optAction=on


echo "<a href='tableformat.php?page=1&". $urlsnip . "'>Page 1</a><br />";
echo "<a href='tableformat.php?page=2&". $urlsnip . "'>Page 2</a><br />";
echo "<a href='tableformat.php?page=3&". $urlsnip . "'>Page 3</a><br />";
echo "<a href='tableformat.php?page=4&". $urlsnip . "'>Page 4</a><br />";
echo "<a href='tableformat.php?page=5&". $urlsnip . "'>Page 5</a><br />";

The $urlsnip started when I exploded the url:

$myarray = explode('?', $_SERVER["REQUEST_URI"]);
foreach($myarray as $value)
{
$urlsnip = $value;
}
And then I used the replace function to cancel out all the empty fields:

$urlsnip = str_replace("keyword1=&","", $urlsnip);
$urlsnip = str_replace("keyword2=&","", $urlsnip);
$urlsnip = str_replace("keyword3=&","", $urlsnip);
$urlsnip = str_replace("keyword4=&","", $urlsnip);
$urlsnip = str_replace("keyword5=&","", $urlsnip);
$urlsnip = str_replace("keyword6=&","", $urlsnip);
$urlsnip = str_replace("keyword7=&","", $urlsnip);
$urlsnip = str_replace("keyword8=&","", $urlsnip);
But I don't understand why it adds page=1&page=2...

StupidRalph
02-06-2008, 10:51 PM
Post your whole script and let me check it out....if its more than one page label which is which.

gilgalbiblewhee
02-06-2008, 11:01 PM
If I paste the whole script it's going to take me time. And it's going to confuse you because it's too long.

gilgalbiblewhee
02-06-2008, 11:18 PM
I'm considering your advice:

$sql = SELECT * FROM table WHERE `` = 'value';
$q = mysql_query($sql);

$total = mysql_num_rows($q) or die(mysql_error());

$numOfPages = $total / $resultsperpage;

for ($i=0; $i < $numOfPages; ++$i) {
echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl .
"page=" . $i . "&" . $urlsnip . ">Page" . $i . "</a><br />";
}

But I'm not sure if I declared any of the variables in another name:

if (isset($_GET['page'])) {
$page = $_GET['page'];
}
else {
$page = 1;
}

$page = isset($_GET['page']) ? $_GET['page'] : 1; //or you can use the ternary operator

//$resultspertpage = 10;
$perpage = 10;
$end = $page*$perpage;
//$start = ($page*$perpage)-$perpage;
$start = ($page*$perpage)-$perpage;

//$start = (!empty($_GET['start'])) ? $_GET['start'] : 0;

$sql.= " LIMIT " . $start . "," . $end;

StupidRalph
02-06-2008, 11:27 PM
//this is one way to do it.
//by using an if statement
if (empty($_GET['page'])) {
$page = 1;
}
else {
$page = $_GET['page'];
}

//this is a different way to do it in a single line of code
$page = (empty($_GET['page'])) ? 1 : $_GET['page']; //or you can use the ternary operator


Also, you should find a less demonstrative way to communicate here.

I'm sorry I wasn't more clear here. There are two things going on in that code block. The If/else statement and the ternary (? :) operation do the exact same things. I prefer to use the ternary (the second part of that code) b/c its a cleaner approach.

gilgalbiblewhee
02-07-2008, 12:38 AM
I know where the problem is coming from:


$myarray = explode('?', $_SERVER["REQUEST_URI"]);
foreach($myarray as $value)
{
$urlsnip = $value;
}
so it takes the url and explodes from ? even if the url has page=1 or whatever. And it adds on page=1&page=2...
The following is the replace function to cancel out all the empty fields:

$urlsnip = str_replace("keyword1=&","", $urlsnip);
$urlsnip = str_replace("keyword2=&","", $urlsnip);
$urlsnip = str_replace("keyword3=&","", $urlsnip);
$urlsnip = str_replace("keyword4=&","", $urlsnip);
$urlsnip = str_replace("keyword5=&","", $urlsnip);
$urlsnip = str_replace("keyword6=&","", $urlsnip);
$urlsnip = str_replace("keyword7=&","", $urlsnip);
$urlsnip = str_replace("keyword8=&","", $urlsnip);

gilgalbiblewhee
02-07-2008, 01:22 AM
I finally figured it out. In case anyone else is wondering:


$baseurl = "/wheelofgod/search/cat/tableformat.php?";
function curPageURL() {
$pageURL = 'http';
if ($_SERVER["HTTPS"] == "on") {$pageURL .= "s";}
$pageURL .= "://";
if ($_SERVER["SERVER_PORT"] != "80") {
$pageURL .= $_SERVER["SERVER_NAME"].":".$_SERVER["SERVER_PORT"].$_SERVER["REQUEST_URI"];
} else {
$pageURL .= $_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"];
}
return $pageURL;
}
$myarray = explode('?', $_SERVER["REQUEST_URI"]);
foreach($myarray as $value)
{
$urlsnip = $value;
}
$urlsnip = str_replace("keyword1=&","", $urlsnip);
$urlsnip = str_replace("keyword2=&","", $urlsnip);
$urlsnip = str_replace("keyword3=&","", $urlsnip);
$urlsnip = str_replace("keyword4=&","", $urlsnip);
$urlsnip = str_replace("keyword5=&","", $urlsnip);
$urlsnip = str_replace("keyword6=&","", $urlsnip);
$urlsnip = str_replace("keyword7=&","", $urlsnip);
$urlsnip = str_replace("keyword8=&","", $urlsnip);
$myarray = explode('optAction=on', $urlsnip);
foreach($myarray as $value)
{
$Pageurlsnip = $value;
}

$urlsnip = str_replace($Pageurlsnip, "",$urlsnip);
echo "<a href='tableformat.php?". $urlsnip . "&page=1'>Page 1</a><br />";
echo "<a href='tableformat.php?". $urlsnip . "&page=2'>Page 2</a><br />";
echo "<a href='tableformat.php?". $urlsnip . "&page=3'>Page 3</a><br />";
echo "<a href='tableformat.php?". $urlsnip . "&page=4'>Page 4</a><br />";
echo "<a href='tableformat.php?". $urlsnip . "&page=5'>Page 5</a><br />";
You might not need the whole thing.
Let me explain I did one explode where the ?. I took out all the empty fields with the str_replace. I placed the page= at the end of the URLs.I exploded optAction=on which is right before page= making the variable $Pageurlsnip = $value; which is page=. Used another replace function to take out the existing page= .

This way it doesn't add up page=1&page=2&page=3...

gilgalbiblewhee
02-07-2008, 01:38 AM
I want to use a for loop for paging and after using this:

$q = mysql_query($sql);

$total = mysql_num_rows($q) or die(mysql_error());

$numOfPages = $total / $resultsperpage;

for ($i=0; $i < $numOfPages; ++$i) {
echo "<a href='tableformat.php?". $urlsnip . "&page=" . $i . "&" . $urlsnip . ">Page" . $i . "</a><br />";
//echo "<a href='http://" . $_SERVER['SERVER_NAME'] . $baseurl . "page=" . $i . "&" . $urlsnip . ">Page" . $i . "</a><br />";
}
I got this warning:

Warning: mysql_query() [function.mysql-query]: Access denied for user 'ODBC'@'localhost' (using password: NO) in ...\tableformat.php on line 255

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in ...\tableformat.php on line 255

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...\tableformat.php on line 257
Access denied for user 'ODBC'@'localhost' (using password: NO)

StupidRalph
02-07-2008, 01:52 AM
You have to first connect to the DB. Typically since the same connections are used throughout the entire site they are put in thier own file and included with the require() or similar functions.

mysql_connect() (http://www.php.net/mysql-connect)
mysql_select_db() (http://www.php.net/mysql-select-db)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum