Page 1 of 1

MySql database problem

Posted: Mon Oct 06, 2008 9:36 am
by tropicallanterns
Hi everyone,

I've been studying php for a couple of weeks now, and I've came across a problem in my coding that I can't for the life of me figure out.

This is suppose to retrieve data from my database, with five listings on each page, but unfortunately, the results are the same for each.

Code: Select all

<?php
 
require_once('db.php');
 
$pagenumber = $_GET['page'];
$rowsperpage = 5;
$offset = ($pagenumber - 1) * $rowperpage;
 
$sql = "SELECT fishbreed, difficulty, temperature, food, waterrequirement FROM fishcatalog LIMIT $offset, $rowsperpage";
 
$result = mysql_query($sql);
 
$numrows = mysql_num_rows($result);
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "<font color = 'red'>fishbreed :{$row['fishbreed']} </font><br>" .
         "difficulty :{$row['difficulty']} <br>" .
         "temerature :{$row['temperature']} <br>" .
         "food : {$row['food']} <br>" .
         "waterrequirement : {$row['waterrequirement']} <br><br>";
}
 
///
 
$sql = "SELECT fishbreed, difficulty, temperature, food, waterrequirement FROM fishcatalog";
 
$result = mysql_query($sql);
 
$pagenumber = $_GET['page'];
$rowsperpage = 5;
$numrows = mysql_num_rows($result);
 
 
$maxpage = ceil($numrows / $rowsperpage);
 
for($page = 1; $page <= $maxpage; $page++)
{
    //echo $page;
    echo "<a href = 'http://tropicallanterns.net84.net/listings.php?page=" . $page . "'>" . $page . " " . "</a>";
}
 
?>
 
<!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 content="en-us" http-equiv="Content-Language">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
<style type="text/css">
.newStyle1 {
    color: #00FF00;
}
</style>
</head>
 
<body>
</body>
 
</html>
 


Thank you so much for your time and help! :D

Dave

Re: MySql database problem

Posted: Tue Oct 07, 2008 2:44 pm
by Joey_X
Assign a column 'id' to fishcatalog.

Code: Select all

alter table fishcatalog add id int not null auto_increment primary key
Then change your query to only look for id's greater than the offset.

Code: Select all

$sql = "SELECT fishbreed, difficulty, temperature, food, waterrequirement FROM fishcatalog where id>$offset LIMIT $rowsperpage";

Re: MySql database problem

Posted: Tue Oct 07, 2008 2:53 pm
by onion2k
You've got a spelling mistake..

Code: Select all

$rowsperpage = 5;
$offset = ($pagenumber - 1) * $rowperpage;
The second line is using $rowperpage instead of $rowsperpage ... missing the s. You might find it easier to spot those problems if you use camel case (eg rowsPerPage) or underscores (eg rows_per_page) in your variable names.

Ignore Joey_X, his solution is completely wrong. He's ignoring the fact you might delete rows. Which is quite important really...