MySql database problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tropicallanterns
Forum Newbie
Posts: 1
Joined: Mon Oct 06, 2008 9:30 am

MySql database problem

Post 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
Joey_X
Forum Newbie
Posts: 4
Joined: Tue Aug 19, 2008 4:41 am

Re: MySql database problem

Post 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";
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: MySql database problem

Post 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...
Post Reply