Page 1 of 1

PHP and MySQL Limit Function

Posted: Mon Aug 21, 2006 7:37 pm
by Abs
Hi! :)

I've taken a bit of a hiatus from PHP once I discovered that my webhost didn't support mysqli, and that's how I had learned it. Needless to say, I'm back. Now I'm trying to learn PHP with MySql. I need to create a script that will search a database and automatically provide links to the next record.

I have taken some steps, and even managed to make it work in a roundabout way. Here is my code:

Code: Select all

<?php
	// Call includes
	include("./includes/inc.php");
	// Connect to DB
	$conn = dbconn();
	// Gets ID from URL
	if (!$_GET['id']) {
		$f = 0;
	} else {
		$f = $_GET['id'];
	}
	// Sets the second number for the limit query.
	$s = 1;
	$qry = "select * from office where Deleted='0' limit $f,$s";
	$result = mysql_query($qry);
	if (!$result)
	{
		die ("Could not query the database: <br />". mysql_error());
	}

while($row = mysql_fetch_array($result)){

	$id = $row['C_ID'];
	$title = $row['Title'];
	$loan = $row['LoanAmt'];
	$borr = $row['Borrower'];
	$term = $row['Term'];
	$amort = $row['Amortization'];
}
echo <<<HTML
	This entry has the ID of $id<br />
	Title: $title <br />
	Loan: $loan <br />
	Borrower: $borr<br />
	Term: $term <br />
	Amortization: $amort<br />
	<a href="./test.php?id=$id">Next</a>
HTML;
?>
I have 4 entries in the database. Here is the code I used to create it:

Code: Select all

CREATE TABLE `office` (
  `C_ID` int(25) NOT NULL auto_increment COMMENT 'The ID of the entry',
  `Deleted` tinyint(1) NOT NULL default '0' COMMENT 'Checks to see if the entry is to appear',
  `Title` varchar(99) NOT NULL default '' COMMENT 'The title to appear on the page',
  `LoanAmt` varchar(25) NOT NULL default '' COMMENT 'The loan amount',
  `Borrower` varchar(99) NOT NULL default '' COMMENT 'Who borrowed it?',
  `Term` varchar(20) NOT NULL default '' COMMENT 'The term of the loan',
  `Amortization` varchar(20) NOT NULL default '' COMMENT 'Length of amortization period',
  PRIMARY KEY  (`C_ID`),
  UNIQUE KEY `C_ID` (`C_ID`,`Title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
The second field in the database is "Deleted". This is there to stop the script from pulling these records one the user decides to delete it. It works fine most of the time. If it's all '0' (the PHP script can read all records) it works. When I make the value in the Deleted field '1' for the C_ID of '2', then it skips '2' like it's supposed to, but doesn't display C_ID '4'.

I know the code is really messy, but I work in steps. It's missing code to find out if it's the first record, and doesn't stop the 'next' link from being displayed on the last record. Again, it's a work in process.

I would appreciate any help I can get here, as I'm a little lost. :(

Abs

Posted: Mon Aug 21, 2006 8:01 pm
by Ollie Saunders
You seem like an intelligent guy. Have a look at what you wrote here:

Code: Select all

while($row = mysql_fetch_array($result)){

        $id = $row['C_ID'];
        $title = $row['Title'];
        $loan = $row['LoanAmt'];
        $borr = $row['Borrower'];
        $term = $row['Term'];
        $amort = $row['Amortization'];
}
echo <<<HTML
        This entry has the ID of $id<br />
        Title: $title <br />
        Loan: $loan <br />
        Borrower: $borr<br />
        Term: $term <br />
        Amortization: $amort<br />
        <a href="./test.php?id=$id">Next</a>
HTML;
?>
I'm sure you'll be kicking yourself in a matter of moments

Posted: Mon Aug 21, 2006 8:10 pm
by feyd
Your selection is based on the ID in an odd way: You're using it as the offset into the result set. Now this is fine if your database never skips an ID, but since you are skipping an ID, you're effectively doing a page+1 after encountering a "deleted" record.

To help facilitate keeping things straight in your head, I'd change the $f that is involved with $_GET['id'] to $page, the rest of the $f to $pageStart, $s to $pageLength, and $_GET['id'] to $_GET['page']. Now, because you could some day change how many records you want to show on a page at a time, we need to perform some math to correctly calculate the $pageStart. The math follows this paragraph. This newfound $pageStart should be set between the original $s = 1 and $qry being set.

Code: Select all

$page = max($page, 1);
$pageStart = ($page - 1) * $pageLength;
Now lets tweak the "next" link: change "id=$id" to, you guessed it, "page=$page"

To help keep good coding practices, I'll suggest making all database, table and field references in query strings be placed in what are called backticks. They'll look like this

Code: Select all

SELECT * FROM `backtickedTable` WHERE `backtickedField` = 'someStringValue'
Further things to make it fancier: add a "previous" link, add some logic to know if there are "previous" or "next" records to show and adjust the output accordingly.

Posted: Mon Aug 21, 2006 8:29 pm
by Abs
Thanks for the replies, guys. Feyd, I will give your proposal a shot. The thing that bugs me is that the code works when I'm missing the third record out of four. In that case it goes 1 > 2 > 4. But when I'm missing the second one, it just goes 1 > 3.

Again, I will try what you suggested, and will report back.

PS: The previous links and such were in the books. I just take it one step at a time. :)

Abs