I am having a few problems returning records from a mySQL database using PHP.
Firstly I must say I am trying to create a video album using MySQL, PHP and using pagination.
My first problem is if I want to retrieve the first 3 records, only the first record is retrieved and then the next two records are duplicates of the first record.
My second problem is if I want to retrieve all of the records (there are currently 3), only the total-1 records are returned - I can not retrieve the last record.
Below is my PHP code for the script: - <?php
-
-
//Connect to the database
-
$user="USERNAME";
-
$password="PASSWORD";
-
$database="DATABASE";
-
$con = mysql_connect("localhost",$user,$password) or die ('Could not connect: ' . mysql_error());
-
-
-
-
mysql_select_db($database, $con) or die( "Unable to select database");
-
// find out how many rows are in the table
-
$sql = "SELECT * FROM vids";
-
$result = mysql_query($sql, $con);
-
$r = mysql_fetch_row($result);
-
$numrows = $r[0];
-
-
// number of rows to show per page
-
$rowsperpage = 1;
-
-
// find out total pages
-
$totalpages = ceil($numrows / $rowsperpage);
-
-
// get the current page or set a default
-
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
-
// cast var as int
-
$currentpage = (int) $_GET['currentpage'];
-
} else {
-
$currentpage = 1;
-
}
-
-
// if current page is greater than total pages...
-
if ($currentpage > $totalpages) {
-
// set current page to last page
-
$currentpage = $totalpages;
-
} // end if
-
// if current page is less than first page...
-
if ($currentpage < 1) {
-
// set current page to first page
-
$currentpage = 1;
-
}
-
-
// the offset of the list, based on current page
-
$offset = ($currentpage - 1) * $rowsperpage;
-
-
// get the info from the db
-
$sql = "SELECT * FROM vids LIMIT $offset, $rowsperpage";
-
$result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
-
-
// Assign variables for videos
-
$query = "SELECT * FROM vids";
-
$result = mysql_query($query) or die ('Error: '.mysql_error ());
-
$row = mysql_fetch_row($result);
-
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];
-
-
// while there are rows to be fetched...
-
while ($list = mysql_fetch_assoc($result)) {
-
// echo data
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];
-
echo "<div class='sky_cont'>
-
<div class='sky_vid'><a class='video' href=\"$url\"><img src='http://www.netlinksurveyors.co.uk/test/images/lgo.jpg' alt=\"$title\" Border='0' /></a></div>
-
<p><h4><a href=\"$page_url\" target='_blank'>$title</a></h4>$desc</p>
-
</div>";
-
-
}
-
-
-
-
/****** build the pagination links ******/
-
// if not on page 1, don't show back links
-
if ($currentpage > 1) {
-
// show << link to go back to page 1
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
-
// get previous page num
-
$prevpage = $currentpage - 1;
-
// show < link to go back to 1 page
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
-
}
-
-
// range of num links to show
-
$range = 3;
-
-
// loop to show links to range of pages around current page
-
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
-
// if it's a valid page number...
-
if (($x > 0) && ($x <= $totalpages)) {
-
// if we're on current page...
-
if ($x == $currentpage) {
-
// 'highlight' it but don't make a link
-
echo " [<b>$x</b>] ";
-
// if not current page...
-
} else {
-
// make it a link
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
-
}
-
}
-
}
-
-
// if not on last page, show forward and last page links
-
if ($currentpage != $totalpages) {
-
// get next page
-
$nextpage = $currentpage + 1;
-
// echo forward link for next page
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
-
// echo forward link for lastpage
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
-
} // end if
-
/****** end build pagination links ******/
-
-
-
?>
-
-
-
4 1869 Dormilich 8,658
Recognized Expert Moderator Expert
it is good style to name all the fields in an SQL query explicitly, because that can save you much memory, esp. when you don’t need all fields. additionally, you can see by looking at the query, what it should contain (doesn’t require knowledge of the DB itself). - // inefficient
-
SELECT * FROM table
-
// better
-
SELECT `field_1`, `field_2`, `field_3` FROM table
one more point, you can pass the fetch mode in the mysql_fetch_* function, so that PHP doesn’t create more array members than necessary (currently you have a named and numerically indexed array). - // get the info from the db
-
$sql = "SELECT * FROM vids LIMIT $offset, $rowsperpage";
- $result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
-
-
// Assign variables for videos
-
$query = "SELECT * FROM vids";
- $result = mysql_query($query) or die ('Error: '.mysql_error ());
you’re overwriting the first query immediately … - while ($list = mysql_fetch_assoc($result)) {
-
$id = $row[0];
do you see something obvious?
I can see what the problem is with the overwritting of the variable $results, simply rename the second variable?
I dont understand what is wrong with the following: -
-
while ($list = mysql_fetch_assoc($result)) {
-
$id = $row[0];
-
-
I have modified the script a little, but it still does not display the correct information - only the first record.
PHP code is below: - <?php
-
-
//Connect to the database
-
$user="USERNAME";
-
$password="PASSWORD";
-
$database="DATABASE";
-
$con = mysql_connect("localhost",$user,$password) or die ('Could not connect: ' . mysql_error());
-
-
mysql_select_db($database, $con) or die( "Unable to select database");
-
-
// find out how many rows are in the table
-
$sql = "SELECT * FROM vids";
-
$result = mysql_query($sql, $con);
-
$r = mysql_fetch_row($result);
-
$numrows = $r[0];
-
-
// number of rows to show per page
-
$rowsperpage = 3;
-
-
// find out total pages
-
$totalpages = ceil($numrows / $rowsperpage);
-
-
// get the current page or set a default
-
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
-
// cast var as int
-
$currentpage = (int) $_GET['currentpage'];
-
} else {
-
$currentpage = 1;
-
}
-
-
// if current page is greater than total pages...
-
if ($currentpage > $totalpages) {
-
// set current page to last page
-
$currentpage = $totalpages;
-
} // end if
-
// if current page is less than first page...
-
if ($currentpage < 1) {
-
// set current page to first page
-
$currentpage = 1;
-
}
-
-
// the offset of the list, based on current page
-
$offset = ($currentpage - 1) * $rowsperpage;
-
-
-
-
// Assign variables for videos
-
/*$query = "SELECT * FROM vids";
-
$result = mysql_query($query) or die ('Error: '.mysql_error ());
-
$row = mysql_fetch_row($result);
-
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];*/
-
-
// get the info from the db
-
$sql = "SELECT * FROM vids LIMIT $offset, $rowsperpage";
-
$result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
-
$row = mysql_fetch_row($result);
-
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];
-
-
-
// while there are rows to be fetched...
-
while ($list = mysql_fetch_assoc($result)) {
-
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];
-
-
echo "<div class='sky_cont'>
-
<div class='sky_vid'><a class='video' href=\"$url\"><img src='http://www.netlinksurveyors.co.uk/test/images/lgo.jpg' alt=\"$title\" Border='0' /></a></div>
-
<p><h4><a href=\"$page_url\" target='_blank'>$title</a></h4>$desc</p>
-
</div>";
-
-
}
-
-
-
-
/****** build the pagination links ******/
-
// if not on page 1, don't show back links
-
if ($currentpage > 1) {
-
// show << link to go back to page 1
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
-
// get previous page num
-
$prevpage = $currentpage - 1;
-
// show < link to go back to 1 page
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
-
}
-
-
// range of num links to show
-
$range = 3;
-
-
// loop to show links to range of pages around current page
-
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
-
// if it's a valid page number...
-
if (($x > 0) && ($x <= $totalpages)) {
-
// if we're on current page...
-
if ($x == $currentpage) {
-
// 'highlight' it but don't make a link
-
echo " [<b>$x</b>] ";
-
// if not current page...
-
} else {
-
// make it a link
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
-
}
-
}
-
}
-
-
// if not on last page, show forward and last page links
-
if ($currentpage != $totalpages) {
-
// get next page
-
$nextpage = $currentpage + 1;
-
// echo forward link for next page
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
-
// echo forward link for lastpage
-
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
-
} // end if
-
/****** end build pagination links ******/
-
-
-
?>
-
-
-
Thanks,
Gary
JKing 1,206
Recognized Expert Top Contributor
The problem is that you create your loop with the variable $list and it loops three times but you are echo'ing the variables stored in $row not $list. The variables in $row were set just above your loop to the first record in the results which would explain why you get the same result duplicated. - // get the info from the db
-
$sql = "SELECT * FROM vids LIMIT $offset, $rowsperpage";
-
$result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
-
-
// while there are rows to be fetched...
-
while ($row = mysql_fetch_assoc($result)) {
-
-
$id = $row[0];
-
$url = $row[1];
-
$page_url = $row[2];
-
$title = $row[3];
-
$desc = $row[4];
-
$date_add = $row[5];
-
$date_rec = $row[6];
-
$place = $row[7];
-
$altitude = $row[8];
-
$jump_no = $row[9];
-
-
echo "<div class='sky_cont'>
-
<div class='sky_vid'><a class='video' href=\"$url\"><img src='http://www.netlinksurveyors.co.uk/test/images/lgo.jpg' alt=\"$title\" Border='0' /></a></div>
-
<p><h4><a href=\"$page_url\" target='_blank'>$title</a></h4>$desc</p>
-
</div>";
-
-
}
Try that.
Thanks JKing, your code got me thinking and I managed to tweak it so it works now.
The full working code is below:
The pagination works, but does not limit the number of records shown. I have set the number of rows (records) to be shown per page as 2. I have 4 records in my database and the pagination should show 2 records per page, with a total of two pages. The problem is, it displays all 4 records on pages 1 and two.
How can I restrict the number of records shown on each page? I thought my code would have done that, but as it seems, it shows all records on the calculated number of pages.
Thanks for your help :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: hansyin |
last post by:
HI, I got a problem about restoring data in mysql:
I have 2 or more PCs installed with mysql database, with same or almost
same table structure, but with different data. Can I combine those
data...
|
by: donpro |
last post by:
Hi,
I have a varchar field in a MySQL database that contains a line of text
like so:
"This is a line if text"
The double quotes are included in the database field.
I cannot seem to...
|
by: Hans |
last post by:
Hi,
That's my first time to send mail to this address for asking help.
Sorry for my poor english firstly.
My case is like this:
Many guys are using a mysql database, each guy has a database...
|
by: madan26 |
last post by:
Dear Friends
I am getting a problems. I have data display page. If the records remains below 5000 it works very fine. but if records goes beyond 5000 it gives a problem as given below:-
...
|
by: menmysql |
last post by:
i am getting the following error while connecting mysql database using jsp
java.sql.SQLException: Communication link failure: Bad handshake
can any one tell what is the actual problem
| |
by: menmysql |
last post by:
i am not bale to solve this problem since two weeks
i am trying to access records from mysql database using jsp. inside this jsp program i wrote all my JDBC code. it is working very nicely and...
|
by: bimeldip |
last post by:
Hi,
i would like to display the list of tables in a database in a drop down list
then when user selects a table, the table will be dispalyed on the page.
So far i've done this:
<?
$dbname =...
|
by: fpcreator2000 |
last post by:
Hello everyone. I'm having problems with a page I've created that is used to insert data into a database.
It uploads two files into two distinct folder, and it takes the filenames and inserts...
|
by: John Kirkpatrick |
last post by:
Hi all,
I am having difficulty displaying records on a frontend MS Access 2000 form using a MySQL backend. The following code works well with the Jet database engine but doesn't work properly...
|
by: Atli |
last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t.
In situations where your PHP application...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |