limit issue

[yerfdog7]

I know how to limit the number of returns from a query, but that is not effective enough for what I want.

I want to be able to return everything, but limit how many at a time. So need help figuring out how to for example;

32 results returned
1-10 | next

or

32 results returned
1 | 2 | 3 | 4

//Brandon

 

[sleipnir214]

Insufficient data for a meaningful answer. I am assuming you are referring to database queries against a MySQL database.

Take a look at the "LIMIT" clause of the SELECT statement. "SELECT foo from bar LIMIT 0,10" begins with the very first row of the return set, and returns 10 rows. To get the next 10, perform "SELECT foo FROM bar LIMIT 10,10" and so on.

Look here

http://www.mysql.com/doc/en/SELECT.html

about halfway down the page for more information.

Combine what with a query to count the total number of rows that would be returned, and you can then construct your links. ______________________________________________________________________
TANSTAAFL!

 

[yerfdog7]

Ok read that and understood some of it but zoom over the head. I started teaching myself about a week ago to get through a project, so my understanding to the point is very basic and below is what I have so far.

What I want to do is if there are say 65 results, display the first 10 and then have links that are generated in the code that go to the next set and so on.

So I'm thinking like; if num_results > 10 then

anyway here's what I got;

<!-- php starts here -->
<?php
trim($searchterm);
if (!$searchtype || !$searchterm)
{
echo &quot;<p align=center><b>You have not entered search details. Please go back and try again.</b></p>&quot;;
exit;
}
/* Connecting, selecting database */
@ $db = mysql_connect(&quot;sqldb.globalhosting.com&quot;, &quot;amplifi&quot;, &quot;hisword&quot
or die(&quot;Could not connect&quot;
mysql_select_db(&quot;churches&quot or die(&quot;Could not select database&quot;

/* Performing SQL query */
$query = &quot;SELECT * FROM church WHERE &quot;.$searchtype.&quot; LIKE '%&quot;.$searchterm.&quot;%' order by 'name' asc&quot;;
$result = mysql_query($query) or die(&quot;Query failed&quot;
$num_results = mysql_num_rows($result);
print &quot;<p><b>Your search produced &quot;.$num_results.&quot; matches.</b></p>&quot;;
/* Printing results in HTML */
print &quot;<table border=\&quot;1\&quot; cellspacing=\&quot;0\&quot; cellpadding=\&quot;4\&quot; width=\&quot;500\&quot; align=\&quot;center\&quot;>\n&quot;;
print &quot;<tr>\n&quot;;
print &quot;<td bgcolor=#0099CC colspan=\&quot;3\&quot; width=\&quot;50%\&quot;><p><b>Organization</b></p></td>\n&quot;;
print &quot;<td bgcolor=#0099CC colspan=\&quot;5\&quot; align=\&quot;right\&quot;><p><b>Information</b></p></td>\n&quot;;
print &quot;</tr>\n&quot;;
while ($row = mysql_fetch_assoc($result)) {
print &quot;<tr>\n&quot;;
printf(&quot;<td colspan=\&quot;3\&quot;><p>%s<br> %s<br> %s</td><td align=\&quot;right\&quot; colspan=\&quot;5\&quot;><p>%s<br> %s, %s, %s<br> %s</td>\n&quot;, $row['Name'], $row['Pastor'], $row['Denomination'], $row['Address'], $row['City'], $row['State'], $row['Zip'], $row['Phone']);
print &quot;\t</tr>\n&quot;;
}
print &quot;</table>\n&quot;;

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($db);
?>
<!-- php ends here -->

 

[yerfdog7]

FYI...

noticed soon as i hit submit i blew it, so u bad eggs out there, I already changed the user and password... sorry

 

[sleipnir214]

Important security tip. Never post real server names, usernames and passwords in example code. It looks like your hosting provider locked down your userid and password the way he should have (yes, I checked), but you never know.

On to the problem at hand.

Your script needs to know how many total rows will match your user's search criterion. Performing a query like &quot;SELECT count(*) FROM church WHERE &quot;.$searchtype.&quot; LIKE '%&quot;.$searchterm.&quot;%'&quot; and fetching the result will do that. With that number and knowing how many results you want to display to a page (in your case, I think 10), your script can calculate how many pages it will take.

For the sake of argument, let's say that 32 rows in your table match. The formula ceil(total/rows_per_page) (ceil() function:

http://www.php.net/manual/en/function.ceil.php

for more information) will get the correct number. (In this case, 4 pages).

using a for loop, you can then print out four links of the form &quot;<a href=$PHP_SELF?searchtype=$searchtype&searchterm=$searchterm&jump=0>&quot;, &quot;<a href=$PHP_SELF?searchtype=$searchtype&searchterm=$searchterm&jump=10>&quot;, &quot;<a href=$PHP_SELF?searchtype=$searchtype&searchterm=$searchterm&jump=20>&quot;, &quot;<a href=$PHP_SELF?searchtype=$searchtype&searchterm=$searchterm&jump=30>&quot;

Check whether $jump exists, and if not, set it to zero.

Then your script performs the SQL statment to retrieve only those rows of the data pertinent to this page. Something like &quot;SELECT * FROM church WHERE &quot;.$searchtype.&quot; LIKE '%&quot;.$searchterm.&quot;%' order by name asc LIMIT $jump, 10&quot;

The LIMIT clause at the end of your SQL statment will tell MySQL to start at matching record number $jump (it starts counting at zero, not one) and return 10 records.

Then loop through your return set, outputting data.


If the LIMIT clause has you befuddled, play with it and see what it does. In my earlier post, I put a link to the MySQL online documentation site for the SELECT statement. ______________________________________________________________________
TANSTAAFL!

 

[yerfdog7]

You're right and I new that, lack of sleep and not proofing the post were to blame. Thanks for testing it.

I went to the link and read through it, and I'm catching on, but I'm still very fresh at php.

Will try your suggestion and let you know.

Thanks