database query page limit

[Champak]

I have a program that absolutely needs the ability to query the database and limit the results per page. The problem, the creators of this program refuse to do this, because they don't want to mess with the core code. I know how to do the query limit, I just can't figure how to implement it because of how they have the programs query result is displayed in blocks instead of just a simple query and result. Meaning; this particular query grabs the name, company name from one table and row, then goes and gets the address, city, state, phone, fax, and cell from another table split over 6 rows. Now it associates everything by the id associated to them and combines them into blocks that are ordered alphabetically in the result. I don't know why they designed it like this, but it is what I have to work with.

So here is my database query and limit result first:
//////
//////

$rowsPerPage = 15;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

//$query = "SELECT userdb_user_first_name FROM realty_en_userdb LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the query
while(list($val) = mysql_fetch_array($result))
{
echo "$val <br>";
}

echo '<br>';

// how many rows we have in database
$query = "SELECT COUNT(userdb_user_first_name) AS numrows FROM realty_en_userdb";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First Page] '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable 'next' link
$last = ' [Last Page] '; // nor 'last page' link
}

// print the page navigation link
echo $first . $prev . " page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

//////
//////
//////
//////
//////
//////
//////
//////
Now here is the script that I need to combine this with:
/////
/////

function view_users()
{
global $conn, $config, $lang;
require_once($config['basepath'] . '/include/misc.inc.php');
require_once($config['basepath'] . '/include/images.inc.php');
require_once($config['basepath'] . '/include/class/template/core.inc.php');
$misc = new misc();
$display = '';
$user_section = '';
$page = new page_user();
$page->load_page($config['template_path'] . '/view_users_default.html');

$sql = "SELECT userdb_user_first_name, userdb_user_last_name, userdb_id FROM " . $config['table_prefix'] . "userdb where userdb_is_agent = 'yes' and userdb_active = 'yes' order by userdb_user_first_name";
$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$misc->log_error($sql);
} while (!$recordSet->EOF) {
$first_name = $misc->make_db_unsafe($recordSet->fields['userdb_user_first_name']);
$last_name = $misc->make_db_unsafe($recordSet->fields['userdb_user_last_name']);
$company_detail = $misc->make_db_unsafe($recordSet->fields['userdb_user_company_detail']);
$argo_page = $misc->make_db_unsafe($recordSet->fields['userdb_user_argo_page']);
$agent_id = $misc->make_db_unsafe($recordSet->fields['userdb_id']);
$agent_contact_link = user::contact_agent_link($agent_id);
$agent_fields = user::renderUserInfo($agent_id);

$user_section .= $page->get_template_section('user_block');
$user_section = $page->parse_template_section($user_section, 'agent_first_name', $first_name);
$user_section = $page->parse_template_section($user_section, 'agent_last_name', $last_name);
$user_section = $page->parse_template_section($user_section, 'agent_company_detail', $company_detail);
$user_section = $page->parse_template_section($user_section, 'agent_argo_page', $argo_page);
$user_section = $page->parse_template_section($user_section, 'agent_id', $agent_id);
$user_section = $page->parse_template_section($user_section, 'agent_contact_link', $agent_contact_link);
$user_section = $page->parse_template_section($user_section, 'agent_fields', $agent_fields);
// Insert Agent Image
$sql2 = "SELECT userimages_thumb_file_name FROM " . $config['table_prefix'] . "userimages WHERE userdb_id = $agent_id ORDER BY userimages_rank";
$recordSet2 = $conn->Execute($sql2);
if ($recordSet2 === false) {
$misc->log_error($sql2);
}
$num_images = $recordSet2->RecordCount();
if ($num_images == 0) {
if ($config['show_no_photo'] == 1) {
$agent_image = '<img src="/agent/images/nophoto.gif" alt="' . $lang['no_photo'] . '" />';
}else {
$agent_image = '';
}
$user_section = $page->parse_template_section($user_section, 'agent_image_thumb_1', $agent_image);
}
$x = 1;
while (!$recordSet2->EOF) {
$thumb_file_name = $misc->make_db_unsafe ($recordSet2->fields['userimages_thumb_file_name']);
if ($thumb_file_name!= "") {
// gotta grab the image size
$imagedata = GetImageSize("$config[user_upload_path]/$thumb_file_name");
$imagewidth = $imagedata[0];
$imageheight = $imagedata;
$shrinkage = $config['thumbnail_width'] / $imagewidth;
$displaywidth = $imagewidth * $shrinkage;
$displayheight = $imageheight * $shrinkage;
$agent_image = '<img src="' . $config['user_view_images_path'] . '/' . $thumb_file_name . '" height="' . $displayheight . '" width="' . $displaywidth . '" alt="' . $thumb_file_name . '" />';
} // end if ($thumb_file_name!= "")
// We have the image so insert it into the section.
$user_section = $page->parse_template_section($user_section, 'agent_image_thumb_' . $x, $agent_image);
$x++;
$recordSet2->MoveNext();
} // end while
$user_section=preg_replace('{agent_image_thumb_(.*?)}', '', $user_section);
$recordSet->MoveNext();
}
$page->replace_template_section('user_block', $user_section);
return $page->page;
}

Please help.

[1][edited by: Champak at 1:39 pm (utc) on Mar. 1, 2007]

[eelixduppy]

Champak, have you had any luck putting this together?

If you only want to have the results from the queries then I'd start from scratch right there--with the two queries:

$sql = "SELECT userdb_user_first_name, userdb_user_last_name, userdb_id FROM " . $config['table_prefix'] . "userdb where userdb_is_agent = 'yes' and userdb_active = 'yes' order by userdb_user_first_name"; 
#
$sql2 = "SELECT userimages_thumb_file_name FROM " . $config['table_prefix'] . "userimages WHERE userdb_id = $agent_id ORDER BY userimages_rank"; 

Note: make sure you handle

$config['table_prefix']

properly :)

Now using both of these queries, apply the "limit feature" to create a pagination effect [google.com], and you should be on your way to a reasonable solution. If you run into any roadblocks along the way, we'll be here :)

I wish you the best of luck!

[mcibor]

If you want to paginate by users. Then put the LIMIT into first query.

If you want to paginate pictures, then you've got a problem - you cannot do that with preservation of blocks:
you would need to use some query like:

$sql2 = "SELECT {$config['table_prefix']}userimages.userimages_thumb_file_name FROM {$config['table_prefix']}userimages, {$config['table_prefix']}userdb WHERE {$config['table_prefix']}.userimages.userdb_id = {$config['table_prefix']}userdb.userdb_id
AND {$config['table_prefix']}userdb.userdb_is_agent = 'yes'
AND {$config['table_prefix']}userdb.userdb_active = 'yes'
ORDER BY {$config['table_prefix']}userimages.userimages_rank, {$config['table_prefix']}userdb.userdb_user_first_name
LIMIT...";

correct me if I'm wrong.
Michal

[Champak]

Thank you.

It is amazing how simple solutions can be. I kept looking at the fact that I needed to display blocks instead of a standard query and assumed what I had wasn't enough to do what I needed. But I did exactly what you said and put the rest of the code around and everything works fine now...almost.

Two problems...the first more important than the other.

1/ With what I have, the last page is always "useless" and causes the entire web page to get thrown off center. Basically ex. if I have ten blocks to display, and I set the script to display five per page. I get back three pages. The first two displaying as they should, but the last page just has the navigation(with the rest of the site of course) but the site is thrown out of whack and the screen doubles in width. I want to know how I can suppress the final unnecessary page.

2/ The way this CMS works is the script you see in the first post is a function in a separate file from the actual html page that is displayed. So the html page calls this through some kind of function...looks like "{agent_fields}". The problem is I have to put the first part of the second script in both files:
$rowsPerPage = 15;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

And then I have to put everything in the second file...the displaying html file. I would like to just be able to put everything within the function file.

I hope I am clear on that.

P.S. I only had to put the limit on the first query.

[edited by: Champak at 2:53 am (utc) on Mar. 10, 2007]

[mcibor]

1. I would use a bit different LIMIT to count that -
$offset = $total_number_of_rows - $rowsPerPage; // so that you retrieve last N
rows per page, then the problem of correct showing data would vanish
you need to know total number of rows to know how many pages there will be.

2. I don't see any problem, $_GET is available without any problem from inside of the function.
However better solution would be to write an own solution to that: write a new function that will return html or even that will write html to page and call it in proper placec in both htmls.

Hope this helps you

Regards
Michal

[Champak]

1. I found a different solution, I tried your suggestion and a couple other ways but with the similar format in mind and they didn't work, they caused an error on the page and everything shut down. So what I did instead was change "$maxPage = ceil($numrows/$rowsPerPage)" to "$maxPage = ceil($numrows/$rowsPerPage) - 1" and that fixed the peoblem.

2. Usually I can strike out and do something once I get direction like you've given because I have some idea how something is working and can get the rest from reading the manuals at php.net or something, but with this, I have no idea what to do, or accomplish what I want other than the way I have it.

[mcibor]

1. Glad you found it

2. I think that you are using some kind of a template system, then I would suggest you insert this into your function:
$user_section = $page->parse_template_section($user_section, 'agent_pagination', 'Here insert whole html that is doing the pagination: <a href="index.html?page=2...etc');

and in html page insert sth like:

{agent_pagination}

at least that's similar in smarty engine

Hope this helps

Michal