Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

MySql/PHP Application Development. Navigating through a 'Result Set'

P

pfealey

macrumors member
Original poster
Aug 27, 2009
35
0
Weston-super-Mare, UK
Hi All,

Hoping someone can help me out here. I've googled loads and consulted a reference textbook and now I'm pulling my hair out.

I'm designing a web-based front-end for a customer's database and have hit a snag whilst implementing a required feature from the client.

I have a database of 14000+ records to work with, and the essential idea is that you search the database to list only the relevant results. You then click the desired result from the list to display company information, such as address, which products they produce etc.

This part is fine, however once you're viewing a record (on a page called viewrecord.php), my client requires that you can have buttons to navigate through the result set. e.g. Click > to see the next record (company, product, address info on the viewrecord.php page) returned from the original search. Click << to see the first result returned from the search... etc. This is much like an Ms Access form I suppose.

I've searched everywhere to see how to implement this, however all the seemingly relevant google results point to how to implement pagination. i.e. view 10 list results at a time with buttons for previous 10 and next 10.

If anyone had any hints as to how to achieve this, then I would be most grateful. I'm thinking it may involve a temporary table, but with php, once the script executes, the temporary table is apparantly destroyed.

Many Thanks in advance.

Edit

For those interested, I solved the issue partly. Just need to ensure the 'Next' link can be made not a hyperlink once you reach the end of the recordset.

Any feedback on this code would be appreciated. Thanks.

Code: 
<?php

mysql_connect("xxx","xxx","xxx"); 

mysql_select_db("xxx");

// define record that you want to view from tempid in list page
$currentrecord = $_GET['tempid'];
$currentrecord -= 1;
$previousrecord = $currentrecord;
$nextrecord = $currentrecord + 2;

// get list of records from the created list
$sqlresults = "SELECT @rownum:=@rownum+1 'tempid', p.* from companies p, (SELECT @rownum:=0) r ORDER BY companyname ASC";
$searchresult = mysql_query($sqlresults) or die('Could not Connect: ' . mysql_error());
mysql_data_seek($searchresult,$currentrecord);
$searchcountrows = mysql_num_rows($searchresult);
$row = mysql_fetch_row($searchresult);
$num = mysql_num_rows($searchresult);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>View Record</title>
</head>

<body>
<? 

echo $row[3];
echo "<br><br>";

if ($previousrecord <= 0) {

echo "Previous - <a href=\"testrecord.php?tempid=$nextrecord\">Next</a>";

} else {

echo "<a href=\"testrecord.php?tempid=$previousrecord\">Previous</a> - <a href=\"testrecord.php?tempid=$nextrecord\">Next</a>";

}
?>
</body>
</html>
 
I find it hard to believe you cannot find an article to teach you the basics of Pagination in PHP, and I refuse to post a link to a basic google query.

However, the general gist of what you want to do is...

Obtain your result set total (say 100),
decide how many a page (say 10)

you now have ten pages, using the LIMIT in the sql query you can define a start and end of the result set.

So you could query

first page 0, 10

then add a parameter using $_POST or $_GET, have an offset (+10)

so the next page is results 10,20 for LIMIT

and you keep on going for ten pages (to show all 100).

Last tip: These would reduce the DB strain... "LIMIT 10 OFFSET 10" investigate how those work.
 
pfealey said:
Hi All,


Edit

For those interested, I solved the issue partly. Just need to ensure the 'Next' link can be made not a hyperlink once you reach the end of the recordset.
Click to expand...

You should be able to accomplish that easily with a while statement, if you're generating all of the pages at once, or an if statement, if each page generates the next one.
 
darrylhj said:
I find it hard to believe you cannot find an article to teach you the basics of Pagination in PHP, and I refuse to post a link to a basic google query.
Click to expand...

pfealey said:
I've searched everywhere to see how to implement this, however all the seemingly relevant google results point to how to implement pagination. i.e. view 10 list results at a time with buttons for previous 10 and next 10.
Click to expand...

Sorry - My fault. I thought that would make it clear I wasn't looking for pagination. I have that already alphabetically. So you don't have to worry about pasting a google link..! :rolleyes:

Thanks to both of you for taking the time to reply.

In terms of the next/previous buttons, I'd used the If method you suggested already - was wondering if there was a more efficient way, but off the top of my head I couldn't work anything better out, so I'm glad you had the same idea as me.

In hindsight - it's as good a way as any, and I think my SQL statement is succint enough that it won't cause too much trouble for efficiency.

Thanks again Guys.
 
Roughly tought, I have two ideas in mind for you :

- Re-process the query each time you click on "Next" and find a way to skip to the next record. Might be slow but very reliable.

- More smart. When you first process your query, generate an array and store your records (14,000) ids ordered in it. You can then use this array to find the next record by skipping index. Look for php arrays functions on Google.
 
Here is my code from my original post in a little test-page.

http://zeus.insightdata.co.uk/test.php

The test DB has only 5 records in, and very limited information, but it should give people an idea as to what specifically I've been looking to do, and think I've achieved.

Angelo95210, I'm pretty familiar with arrays, and have used one to an extent in this particular little conundrum - thanks for your suggestions though :)
 
Brilliant! That way, by storing the array in session vars I can write it so if they navigate to another part of the system, they can return to their search results without having to re-input the parameters. (I have a 'save' feature anyway, but it potentially adds a nice level of redundancy (e.g 'Ah, crap, I clicked the back button without saving')

Jeez, that seems to obvious now...

It's amazing how much you learn when you take on a massive project - I'm used to developing reasonably simple PHP/MySQL stuff - more along the lines of blogging pages and simple, easy to use CMSes, for small local businesses, musicians, that sort of thing.

To give you a snapshot of this project:

This system is designed to be a CRM system with a team of full-time researchers in the office constantly updating the data - it's for the Fenestration industry and a lot of people regularly change company name, addresses, which manufacturing process they use etc. Users can add new companies, and have non-industry related contacts and companies stored in their own private table.

A subscription is sold to interested parties who have access to almost real-time data. Any changes that are requested are then verified; and approved, amended or rejected by the research team thus ensuring the data maintains integrity.

None of 'This customer is a boring fat git' in notes fields!

Users can set tasks for each other, and managers can see things such as sales pipeline reports, and user activity logs. Once the system goes live, I shall be integrating with Sage CRM and other systems once I get hold of some APIs.

The previous version was written in ColdFusion, with a MSAccess back-end. Slow as hell.

It seemed like a relatively simple thing to set up a simple 'Next Record' & 'Previous Record' link, however I didn't bank on it taking on more than couple of lines of code to do properly.

Just out of interest, is it typically best practice to
Code: 
echo "<html>";
or to close the ?> tag and write html before then opening the php again; or is it a bit of a 'swings and roundabouts' thing, and as long as i'm consistent wouldn't really matter?

Once again, thanks for helping, it's much appreciated.
 
re: echos vs closing and opening PHP, it's much easier to maintain your HTML if you close and open PHP (I learned this from experience). It's easier still if you have one function/include file to make the header and another to make the footer (or as many as you need to seperate the dynamic info). The next step is templates, if your site is complex enough.

e.g.
PHP: 
<?php
include('myFunctions.php');
makeHeader('My Page');

//content

makeFooter();

PHP: 
<?php
//myFunctions.php
public function makeHeader($title = '') {
?>
<html>
<head>
<title><?php echo $title; ?></title>
</head>
<body>
<div id="contentHolder">
<?php
}

public function makeFooter() {
?>
</div><!-- /contentHolder -->
</body>
</html>
<?php
}
 
Happy if you like my idea...

I prefer to close php when possible before writing html. It's neater to read and sometimes easier to validate with W3C.
 
Brilliant Stuff - Makes perfect sense.

The production version has a menu bar which is common to all pages, so I'll be incorporating that into functions as suggested. Saves a few kb which is always good for a software package accessed from the web, and tidies up my pages.

Also, I have thousands of lines of code so far - once the system is complete and works, then any dynamic code will be better off in functions as you described.

Can you give me a little pointer for 'templates' so I can go away and research them fully?

Thanks Guys.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back