Solved

Link to open database record details in web page

Posted on 2011-09-06
22
294 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:adamssap
  • 10
  • 10
  • 2
22 Comments
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36492677
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
0
 
LVL 1

Author Comment

by:adamssap
ID: 36496779
I have not worked on dynamic php page.  Can I get some helping hand on this?

Thanks,
0
 
LVL 1

Author Comment

by:adamssap
ID: 36497757
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

0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36497783
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.
0
 
LVL 1

Author Comment

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

0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36497855
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.
0
 
LVL 1

Author Comment

by:adamssap
ID: 36497876
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

0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36497904
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']
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36497956
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
0
 
LVL 1

Author Comment

by:adamssap
ID: 36498051
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"
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36498109
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36498180
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.
0
 
LVL 1

Author Comment

by:adamssap
ID: 36498624
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&hellip;</a>' : ''). '</td>';
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36498984
Do..

partrecord.php?id=$row[resid]
0
 
LVL 1

Author Comment

by:adamssap
ID: 36499062
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.
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36499220
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.
0
 
LVL 1

Author Comment

by:adamssap
ID: 36499259
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

0
 
LVL 8

Accepted Solution

by:
PranjalShah earned 500 total points
ID: 36499320
I am not sure if my post through mobile went through, but here is what you are missing

change line 28 to this,


$result = mysql_query("SELECT * FROM responses where resid='".$resid."'")  or die(mysql_error());

Open in new window

0
 
LVL 1

Author Comment

by:adamssap
ID: 36499362
Thanks, but no luck in displaying particular row data on the second page.  I get a blank table.
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36499371
Do some error checking. echo your query and try that query in your mysql browser and see what you get.
0
 
LVL 1

Author Closing Comment

by:adamssap
ID: 37084845
Not accurate
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 37084885
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this tutorial viewers will learn how to position overlapping items using z-index in CSS. They will also learn the restrictions on the z-index property.  Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Red.…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now