Help with Displaying the Output from a table in a Database

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

Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Help with Displaying the Output from a table in a Database

Post by Kingo »

I'm trying to display the output from a table in a database. I wanted to display only 5 rows per page. The remaining pages should have links as this FORUM has. I tried this code...But only first 5 rows get displayed, when i click the links , I still remain on the first page. Any help is really appreciated.

Here is the Code

Code: Select all

<html> 
<head> 
<title>Example</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 

<body> 
<?php 
$hostname = 'localhost'; 
$username ='abc'; 
$password ='123'; 
$dbName = 'phptest'; 

MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect"); 
@MYSQL_SELECT_DB("$dbName") OR DIE("Unable to select database"); 

$rows_per_page = 5; 

$result = mysql_query("SELECT Name FROM header "); 

$total_records = mysql_num_rows($result); 

$pages = ceil($total_records / $rows_per_page); 

mysql_free_result($result); 


if (!isset($screen)) 
{ 
   $screen = 0; // Everything was being reset to 0 
} 
$start = $screen * $rows_per_page; 

$result = mysql_query("SELECT Name FROM header LIMIT $start, $rows_per_page"); 

$rows = mysql_num_rows($result); 

for ($i = 0; $i < $rows; $i++) { 
   $Name = mysql_result($result, $i, 0); 
   echo "$Name<br>\n"; 
} 
echo "<p><hr></p>\n"; 
// let's create the dynamic links now 
if ($screen > 0) { 
   $url = "example.php?screen=" . ($screen - 1); 
   echo "<a href="$url">Previous</a>\n"; 
} 
// page numbering links now 
for ($i = 0; $i < $pages; $i++) { 
   $url = "example.php?screen=" . $i; 
   echo " | <a href="$url">$i</a> | "; 
} 
if ($screen < $pages) { 
   $url = "example.php?screen=" . ($screen + 1); 
   echo "<a href="$url">Next</a>\n"; 
} 
?> 
</body> 
</html>
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

change this

Code: Select all

if (!isset($screen)) 
{ 
$screen = 0; // Everything was being reset to 0 
} 
$start = $screen * $rows_per_page;
to

Code: Select all

//check if $screen is set
if (!isset($_POST['screen'])) 
{ 
  $screen = 0; // Everything was being reset to 0 
}else{
  $screen = $_POST['screen'];
}  
$start = $screen * $rows_per_page;
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

This might just be me, but isn't he encoding the page number into the URL?

Code: Select all

....
// let's create the dynamic links now 
if ($screen > 0) { 
$url = "example.php?screen=" . ($screen - 1); 
echo "<a href="$url">Previous</a>\n"; 
} 
// page numbering links now 
for ($i = 0; $i < $pages; $i++) { 
$url = "example.php?screen=" . $i; 
echo " | <a href="$url">$i</a> | "; 
} 
if ($screen < $pages) { 
$url = "example.php?screen=" . ($screen + 1); 
echo "<a href="$url">Next</a>\n"; 
}
Shouldn't it then be $_GET['screen']? Mak, If the code Bastien wrote doesn't work, try replacing $_POST with $_GET.

A Torontonian Hello. :D
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

entirely true, my bad
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

It worked

Post by Kingo »

Thanx very much "evilmonkey"(I donn know the name) and Bastenian. My code worked. with _GET. But I stilla have one more problem. When the last set of records is displayed , I still have a 'NEXT' link showing up and when i click that one, I'm being taken to a blank page. ANy help is really appreciated.

Mak
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

just add a check to see if the ($screen+1) * $page > $total_records...if yes then don't show the link
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Can you please show me hwo to not show the link in coding
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

try

Code: Select all

if (($screen+1)*$rows_per_page)<$total_records){
  if ($screen < $pages) { 
    $url = "example.php?screen=" . ($screen + 1); 
    echo "<a href="$url">Next</a>\n"; 
  } 
}
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Thanx very much. It worked. I really apreciate your help.
One last question. I'm trying to modify my code.I have a file in Connections directory by name "mysql_conn.php"

Code: Select all

<?php

# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_mysql_conn = "localhost";
$database_mysql_conn = "php_proj";
$username_mysql_conn = "abc";
$password_mysql_conn = "123";
$mysql_conn = mysql_pconnect($hostname_mysql_conn, $username_mysql_conn, $password_mysql_conn) or die(mysql_error());

?>

And when I try to use this code, I get an error.

<html> 
<head> 
<title>Example</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 

<body> 
<?php require_once('Connections/mysql_conn.php'); ?> 

<?php 

$rows_per_page = 5; 

$result = mysql_query("SELECT Name FROM contact "); 

$total_records = mysql_num_rows($result); 

$pages = ceil($total_records / $rows_per_page); 

mysql_free_result($result); 


if (!isset($_GET['screen'])) 
{ 
$screen = 0; // Everything was being reset to 0 
} else{ 
$screen = $_GET['screen']; 
}
$start = $screen * $rows_per_page; 

$result = mysql_query("SELECT Name FROM contact LIMIT $start, $rows_per_page"); 

$rows = mysql_num_rows($result); 

for ($i = 0; $i < $rows; $i++) { 
$Name = mysql_result($result, $i, 0); 
echo "$Name<br>\n"; 
} 
echo "<p><hr></p>\n"; 
// let's create the dynamic links now 
if ($screen > 0) { 
$url = "example.php?screen=" . ($screen - 1); 
echo "<a href="$url">Previous</a>\n"; 
} 
// page numbering links now 
for ($i = 0; $i < $pages; $i++) { 
$url = "example.php?screen=" . $i; 
echo " | <a href="$url">$i</a> | "; 
} 
/*if ($screen < $pages) { 
$url = "example.php?screen=" . ($screen + 1); 
echo "<a href="$url">Next</a>\n"; 
} */
if ((($screen+1)*$rows_per_page)< $total_records)
{ 
  if ($screen < $pages)
   { 
    $url = "example.php?screen=" . ($screen + 1); 
    echo "<a href="$url">Next</a>\n"; 
   } 
}



?> 
</body> 
</html>
I'm using the same code, except that I'm defining the connections in a file.
Thanx very much for your help.
Last edited by Kingo on Thu Jun 03, 2004 8:35 am, edited 1 time in total.
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I get the following error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\PHP_RemoteFiles\example.php on line 16

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\PHP_RemoteFiles\example.php on line 20

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\PHP_RemoteFiles\example.php on line 33
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

How do i dsplay more than one column from the table. This code works only for one column.
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I got it.

Code: Select all

echo "<table border="1">";

while ($row = mysql_fetch_array($result))
{ 
$Name = $row['Name'] ; 
$Phone = $row['Phone']; 
//make a display block to display the results on a html table row at a time 
echo "<tr><td width="25%">$Name</td> <td width="25%">$Phone</td> </tr>" ; 
} 
echo "</table>";
This will work to display more than one column from a table
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Hello,
Can any one tell me "How to sort each column after they are being displayed by Clicking the Header in the Table Column-----Extension of the above code
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Add an href to the column title and use the href to control the order by clause

Code: Select all

echo "<a href="".$_SERVER['PHP_SELF']."?sort=name"">Name</a>";
Post Reply