asked on

Link to open database record details in web page

Hi:

I have the code below to display the database records in the table. In the table, there is 'Description' field which is a text area field that can take unlimited characters. In the table I am displaying only 255 characters and the remaining I want to display as a form, when you click on 'read more...' link. The new page must also display other fields too. How can this be done?

Thanks,

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Home page</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<div id='fg_membersite_content'>
<h2>Home Page</h2>
Welcome back <?= $fgmembersite->UserFullName(); ?>!
<?php 
$orderCol=$_POST['orderCol'];
$orderWay=$_POST['orderWay'];
$filterCat=$_POST['filterCat'];
$filterCo=$_POST['filterCo']; 

if (!$orderCol) $orderCol="resid";
if (!$orderWay) $orderWay="DESC";

$sql="SELECT * FROM responses WHERE 1=1";
if ($filterCat) $sql.=" AND categories='{$filterCat}'";
if ($filterCo) $sql.=" AND Country='{$filterCo}'";
$sql.=" ORDER BY {$orderCol} {$orderWay}";


        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database 
        $result = mysql_query($sql)  
                or die(mysql_error());   

$sqlCat="SELECT DISTINCT categories FROM responses ORDER BY 1";
        $rsCat = mysql_query($sqlCat)  
                or die(mysql_error());   

$sqlCo="SELECT DISTINCT Country FROM responses ORDER BY 1";
        $rsCo = mysql_query($sqlCo)  
                or die(mysql_error());   
                 
        // display data in table 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
        echo "<form method=POST>";
        echo "<table border='0' cellpadding='10';>";
	echo "<tr><td>&nbsp;</td><td>&nbsp;</td>";
                echo "<th><select name=filterCat size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCat )) { 
		$isSel="";
		if ($row[0]==$filterCat) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td><td>&nbsp;</td>";
	echo "<th><select name=filterCo size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCo )) { 
		$isSel="";
		if ($row[0]==$filterCo) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td>";
        echo "<tr> <th>ID</th> <th>Title</th> <th>Category</th> <th>Description</th> <th>City</th><th>Country</th><th>Zip Code</th></tr>";	
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                 
                // echo out the contents of each row into a table
                echo "<tr align='center' valign='top'>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
                echo '<td>' . $row['title'] . '</td>';
                echo '<td>' . $row['categories'] . '</td>';
                //$position=255; // Define how many character you want to display.
                //$descp = substr($row['description'], 0, $position); 
	//echo '<td style="WORD-BREAK:BREAK-ALL">' . $descp . '</td>'; 
echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
             ( (strlen($row['description'])>255) ? ' <a href="http://page.php">read more&hellip;</a>' : '').
             '</td>';  

                echo '<td>' . $row['city'] . '</td>'; 
                echo '<td>' . $row['country'] . '</td>';
                echo '<td>' . $row['zipcode'] . '</td>'; 
                //echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>'; 
                //echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>'; 
                echo "</tr>"; 
                echo "<tr><td colspan='9'><hr></td></tr>";  
                      }  
        // close table> 
       echo "</table>"; 

?> 
<p><a href="new.php">Add a new record</a></p><p><a href='logout.php'>Logout</a></p>
</div>

</body>
</html>

Open in new window

PranjalShah

You will have to create a new php page to display those records. Say resid is your unique key in the table. Then you can do page.php?id=$row['resid']

On your page.php, just retrieve the information for that particular id and display the information

adamssap

ASKER

I have not worked on dynamic php page. Can I get some helping hand on this?

Thanks,

adamssap

ASKER

I tried this but I get only blank table. No data is retrieved/displayed.

Please help.

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Single record</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<?php 
        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database 
        $result = mysql_query("SELECT * FROM responses where resid = '$resid'")  
                or die(mysql_error());   
                 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
         
        echo "<table border='1' cellpadding='10'>"; 
        echo "<tr> <th>ID</th> <th>Category</th> <th>Description</th> <th></th> <th></th></tr>"; 
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                 
                // echo out the contents of each row into a table 
                echo "<tr>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
                echo '<td>' . $row['category'] . '</td>'; 
                echo '<td>' . $row['description'] . '</td>'; 
                echo '<td><a href="edit.php?id=' . $row['resid'] . '">Edit</a></td>'; 
                echo '<td><a href="delete.php?id=' . $row['resid'] . '">Delete</a></td>'; 
                echo "</tr>";  
        }  
 
        // close table> 
        echo "</table>"; 
?> 

</body>
</html>

Open in new window

PranjalShah

What is your page URL? If you are doing page.php?id=$row['resid'], then you need to get the id from URL. For that do,

$resid = $_GET['id'];

before writing the query.

adamssap

ASKER

My page url is http://domain.com/registration/partrecord.php, I added $resid = $_GET['id']; before the query but no change.

PranjalShah

This is the page where you are displaying just one record from the list of records which has more than 255 characters in description, right? Here you need to tell the page which ID you are looking for, so in your main page you need to add the id to this page, so that you can get the specific resid. Show me your main page code and I will explain.

adamssap

ASKER

Main page code

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Home page</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<div id='fg_membersite_content'>
<h2>Home Page</h2>
Welcome back <?= $fgmembersite->UserFullName(); ?>!
<?php 
$orderCol=$_POST['orderCol'];
$orderWay=$_POST['orderWay'];
$filterCat=$_POST['filterCat'];
$filterCo=$_POST['filterCo']; 

if (!$orderCol) $orderCol="resid";
if (!$orderWay) $orderWay="DESC";

$sql="SELECT * FROM responses WHERE 1=1";
if ($filterCat) $sql.=" AND categories='{$filterCat}'";
if ($filterCo) $sql.=" AND Country='{$filterCo}'";
$sql.=" ORDER BY {$orderCol} {$orderWay}";


        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database 
        $result = mysql_query($sql)  
                or die(mysql_error());   

$sqlCat="SELECT DISTINCT categories FROM responses ORDER BY 1";
        $rsCat = mysql_query($sqlCat)  
                or die(mysql_error());   

$sqlCo="SELECT DISTINCT Country FROM responses ORDER BY 1";
        $rsCo = mysql_query($sqlCo)  
                or die(mysql_error());   
                 
        // display data in table 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
        echo "<form method=POST>";
        echo "<table border='0' cellpadding='10';>";
	echo "<tr><td>&nbsp;</td><td>&nbsp;</td>";
                echo "<th><select name=filterCat size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCat )) { 
		$isSel="";
		if ($row[0]==$filterCat) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td><td>&nbsp;</td>";
	echo "<th><select name=filterCo size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCo )) { 
		$isSel="";
		if ($row[0]==$filterCo) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td>";
        echo "<tr> <th>ID</th> <th>Title</th> <th>Category</th> <th>Description</th> <th>City</th><th>Country</th><th>Zip Code</th></tr>";	
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                 
                // echo out the contents of each row into a table
                echo "<tr align='center' valign='top'>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
                echo '<td>' . $row['title'] . '</td>';
                echo '<td>' . $row['categories'] . '</td>';
                //$position=255; // Define how many character you want to display.
                //$descp = substr($row['description'], 0, $position); 
	//echo '<td style="WORD-BREAK:BREAK-ALL">' . $descp . '</td>'; 
echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
             ( (strlen($row['description'])>255) ? ' <a href="http://page.php">read more&hellip;</a>' : '').
             '</td>';  

                echo '<td>' . $row['city'] . '</td>'; 
                echo '<td>' . $row['country'] . '</td>';
                echo '<td>' . $row['zipcode'] . '</td>'; 
                //echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>'; 
                //echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>'; 
                echo "</tr>"; 
                echo "<tr><td colspan='9'><hr></td></tr>";  
                      }  
        // close table> 
       echo "</table>"; 

?> 
<p><a href="new.php">Add a new record</a></p><p><a href='logout.php'>Logout</a></p>
</div>

</body>
</html>

Open in new window

PranjalShah

In your link for the description, do exactly what I said in my first response. And if you just want to test your new page then get and resid from your table and do page.php?id=4, this will give you the record for your resid 4. To make it a dynamic link from your main page, you will have to do page.php?id=$row['resid']

Ray Paseur

Regarding this, I have not worked on dynamic php page.

Get this book. Very readable with excellent examples. It will be a flash of glorious light if you have not worked much with dynamic PHP pages!
http://www.sitepoint.com/books/phpmysql4/

Also, learn about this function before some hacker destroys your data base.
http://php.net/manual/en/function.mysql-real-escape-string.php

adamssap

ASKER

Thanks Pranjalshah:

When I used resid = 4 it worked, but when I used partrecord.php?id=$row['resid']"] I got this error while opening the page. "Parse error: syntax error, unexpected T_STRING"

PranjalShah

Did you use partrecord.php?id=$row['resid'] in your URL or on the main php page? please read the pages Ray has suggested. You might also want to look at the simple php get started tutorials online.

Ray Paseur

Suggested learning resources in addition to the SitePoint book:
http://php.net/manual/en/intro-whatcando.php
http://php.net/tut.php
http://extension.berkeley.edu/cat/course124.html

You might also want to make a Google search for "Learn PHP" and investigate some of the links.

adamssap

ASKER

I am getting error in the line:

echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
( (strlen($row['description'])>255) ? ' <a href="http://domain.com/registration/partrecord.php?id=$row['resid']">read more…</a>' : ''). '</td>';

PranjalShah

Do..

partrecord.php?id=$row[resid]

adamssap

ASKER

Thanks again, the error is gone but it opens the URL - http://domain.com/registration/partrecord.php?id=$row[resid] and gives an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

I think we are close. Thanks again.

PranjalShah

Can you post the URL of the page and code and once again I suggest you to read the php mysql tutorials online. On EE you can get the ideas of how to implement but no one is going to write code for you.

adamssap

ASKER

First code is for the main page

Second code is for the page that opens when you click read more in the table row.

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Home page</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<div id='fg_membersite_content'>
<h2>Home Page</h2>
Welcome back <?= $fgmembersite->UserFullName(); ?>!
<?php 
$orderCol=$_POST['orderCol'];
$orderWay=$_POST['orderWay'];
$filterCat=$_POST['filterCat'];
$filterCo=$_POST['filterCo']; 

if (!$orderCol) $orderCol="resid";
if (!$orderWay) $orderWay="DESC";

$sql="SELECT * FROM responses WHERE 1=1";
if ($filterCat) $sql.=" AND categories='{$filterCat}'";
if ($filterCo) $sql.=" AND Country='{$filterCo}'";
$sql.=" ORDER BY {$orderCol} {$orderWay}";


        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database 
        $result = mysql_query($sql)  
                or die(mysql_error());   

$sqlCat="SELECT DISTINCT categories FROM responses ORDER BY 1";
        $rsCat = mysql_query($sqlCat)  
                or die(mysql_error());   

$sqlCo="SELECT DISTINCT Country FROM responses ORDER BY 1";
        $rsCo = mysql_query($sqlCo)  
                or die(mysql_error());   
                 
        // display data in table 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
        echo "<form method=POST>";
        echo "<table border='0' cellpadding='10';>";
	echo "<tr><td>&nbsp;</td><td>&nbsp;</td>";
                echo "<th><select name=filterCat size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCat )) { 
		$isSel="";
		if ($row[0]==$filterCat) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td><td>&nbsp;</td>";
	echo "<th><select name=filterCo size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCo )) { 
		$isSel="";
		if ($row[0]==$filterCo) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td>";
        echo "<tr> <th>ID</th> <th>Title</th> <th>Category</th> <th>Description</th> <th>City</th><th>Country</th><th>Zip Code</th></tr>";	
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                 
                // echo out the contents of each row into a table
                echo "<tr align='center' valign='top'>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
                echo '<td>' . $row['title'] . '</td>';
                echo '<td>' . $row['categories'] . '</td>';

                //$position=255; // Define how many character you want to display.
                //$descp = substr($row['description'], 0, $position); 
	//echo '<td style="WORD-BREAK:BREAK-ALL">' . $descp . '</td>'; 

echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
             ( (strlen($row['description'])>255) ? ' <a href="http://domain.com/registration/partrecord.php?id=$row[resid]">read more&hellip;</a>' : ''). '</td>';  

                echo '<td>' . $row['city'] . '</td>'; 
                echo '<td>' . $row['country'] . '</td>';
                echo '<td>' . $row['zipcode'] . '</td>'; 
                //echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>'; 
                //echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>'; 
                echo "</tr>"; 
                echo "<tr><td colspan='9'><hr></td></tr>";  
                      }  
        // close table> 
       echo "</table>"; 

?> 
<p><a href="new.php">Add a new record</a></p><p><a href='logout.php'>Logout</a></p>
</div>

</body>
</html>

Open in new window

<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Single record</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<?php 
        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database $row['resid']

$resid = $_GET['id'];


        $result = mysql_query("SELECT * FROM responses where resid=resid")  
                or die(mysql_error());   
                 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
         
        echo "<table border='1' cellpadding='10'>"; 
        echo "<tr> <th>ID</th> <th>Category</th> <th>Description</th> <th></th> <th></th></tr>"; 
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                 
                // echo out the contents of each row into a table 
                echo "<tr>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
                echo '<td>' . $row['category'] . '</td>'; 
                echo '<td>' . $row['description'] . '</td>'; 
                echo '<td><a href="edit.php?id=' . $row['resid'] . '">Edit</a></td>'; 
                echo '<td><a href="delete.php?id=' . $row['resid'] . '">Delete</a></td>'; 
                echo "</tr>";  
        }  
 
        // close table> 
        echo "</table>"; 
?> 

</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

PranjalShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamssap

ASKER

Thanks, but no luck in displaying particular row data on the second page. I get a blank table.

PranjalShah

Do some error checking. echo your query and try that query in your mysql browser and see what you get.

adamssap

ASKER

Not accurate

PranjalShah

Without testing it and not doing any error checking how can you say this is not accurate. No one is going to write code for you here. The query I posted is correct.