Page 1 of 6

Help with Displaying the Output from a table in a Database

Posted: Wed Jun 02, 2004 7:26 am
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>

Posted: Wed Jun 02, 2004 9:37 am
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;

Posted: Wed Jun 02, 2004 8:46 pm
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

Posted: Wed Jun 02, 2004 8:48 pm
by lostboy
entirely true, my bad

Posted: Wed Jun 02, 2004 8:59 pm
by McGruff

It worked

Posted: Thu Jun 03, 2004 8:05 am
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

Posted: Thu Jun 03, 2004 8:08 am
by lostboy
just add a check to see if the ($screen+1) * $page > $total_records...if yes then don't show the link

Posted: Thu Jun 03, 2004 8:15 am
by Kingo
Can you please show me hwo to not show the link in coding

Posted: Thu Jun 03, 2004 8:18 am
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"; 
  } 
}

Posted: Thu Jun 03, 2004 8:28 am
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.

Posted: Thu Jun 03, 2004 8:34 am
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

Posted: Thu Jun 03, 2004 8:53 am
by Kingo
How do i dsplay more than one column from the table. This code works only for one column.

Posted: Thu Jun 03, 2004 9:17 am
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

Posted: Thu Jun 03, 2004 9:38 am
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

Posted: Thu Jun 03, 2004 9:45 am
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>";