displaying MySQL data
Hi
im trying to make a basic content management system. i can insert data into the mysql database via a html form no problem.
just having a little difficulty displaying/retrieving the data.
When displaying the data i want it to be neatly formatted in a table, with the headings(which are fields from my mysql table): date created, memberid and subject. i want the subject to be a hyperlink to the record relating to the database contents. so when the subject is clicked it will take the user to the full contents of that listing displaying it in a form.
I have managed to get half way there. this is the code:
<?php
include 'dbconnect.php';
// if no id is specified, list the available articles
if(!isset($_GET['ticketid']))
{
$self = $_SERVER['PHP_SELF'];
$query = "SELECT ticketid, subject, created FROM technicalproblems ORDER BY ticketid";
$result = mysql_query($query) or die('Error : ' . mysql_error());
// create the article list
$content = '<ol>';
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
list($ticketid, $title) = $row;
$content .= "<li><a href=\"$self?ticketid=$ticketid\">$title</a></li>\r\n";
}
$content .= '</ol>';
$title = 'Open Tickets';
} else {
// get the article info from database
$query = "SELECT subject, message FROM technicalproblems WHERE ticketid=".$_GET['ticketid'];
$result = mysql_query($query) or die('Error : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$title = $row['ticketid'];
$content = $row['message'];
}
include 'dbclose.php';
?>
and this code is used to display the data once the subject is clicked:
<html>
<head>
<title>
<?php echo $title;?>
</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
h1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 18px;
color: #006699;
font-weight: bold;
}
.main {
padding: 10px;
border: 1px solid #006699;
position: relative;
width: 580px;
margin-top: 20px;
margin-bottom: 20px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</head>
<body>
<table width="600" border="0" align="center" cellpadding="10" cellspacing="1" bgcolor="#336699">
<tr>
<td bgcolor="#FFFFFF">
<h1 align="center"><?php echo $title;?></h1>
<?php
echo $content;
// when displaying an article show a link
// to see the article list
if(isset($_GET['ticketid']))
{
?>
<p> </p>
<p align="center"><a href="<?php echo $_SERVER['PHP_SELF']; ?>">Ticket list</a></p>
<?php
}
?>
</td>
</tr>
</table>
</body>
</html>
ive tried messing around with the code all day long but no luck. ive got the basic concept working but any help and guidance
will be greatly appreciated. thanks in advance.
# 1 Re: displaying MySQL data
If you want to output something into a table, you would do something like this:
<table>
<thead>
<tr><th>Date created</th><th>Member ID</th><th>Subject</th></tr>
</thead>
<tbody>
<?php
$result = mysql_query("SELECT datecreated, memberid, subject FROM table");
while ($row = mysql_fetch_object($result)) {
?>
<tr><td><?php echo $row->datecreated; ?></td><td><?php echo $row->memberid; ?></td><td><?php echo htmlentities($row->subject); ?></td></tr>
<?php
}
?>
</tbody>
</table>
The use of the htmlentities() function ( http://php.net/htmlentities ) is so that if the subject contains < or >, for example, they will be converted to < and > so that the design doesn't get ruined.
One issue I see in your code is that it's open to SQL injection. Wherever user input is placed in an SQL query, you must make sure that it is valid data, otherwise the query can be altered to behave differently than intended (in some cases this can lead to whole databases being wiped out or even allowing access to the whole MySQL server.) To do this, you can use MySQL's built-in escape function, mysql_real_escape_string ( http://php.net/mysql_real_escape_string ). For numbers, you can simplify things by putting (int) before the user input:
$query = 'SELECT column FROM table WHERE id = ' . (int)$_GET['id'];
I recommend you don't use $_GET etc. directly when building the query, but instead check if they are set (isset($_GET['id'])) and use a default value or redirect the user if there isn't any value.
# 2 Re: displaying MySQL data
Two things.
1. Well, your code is correct, you just haven't echoed the results.
$variable = "This is a string";
echo $variable;
2. Your queries are open to SQL Injection (http://en.wikipedia.org/wiki/SQL_injection). You need to use mysql_real_escape_string() (http://us3.php.net/manual/en/function.mysql-real-escape-string.php) on any variable you put into a sql command.
EDIT: It looks like Andreas beat me to it!
# 3 Re: displaying MySQL data
hi thanks for the replies.
this is a test application, so im trying to nail the basic function of the system. I will definitely be dealing with sql injections after :)
yes thank you thats how i wanted it to display in a table, but how i would make each subject a hyperlink to take me to the full contents of that listing (like how ive done in the original code). just getting abit confused.
thanks again
# 4 Re: displaying MySQL data
but how i would make each subject a hyperlink to take me to the full contents of that listing
Just echo the contents into an <a> tag.
<table>
<tr>
<td>Date created</td>
<td>Member ID</td>
<td>Subject</td>
</tr>
<?php
$sql = mysql_query("SELECT datecreated, memberid, subject FROM table");
while($row = mysql_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['datecreated']; ?></td>
<td><?php echo $row['memberid']; ?></td>
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row['subject']); ?></a></td>
</tr>
<?php } ?>
</table>
# 5 Re: displaying MySQL data
hi when i use this line
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row->subject); ?></a></td>
it throws me this error:
Fatal error: Cannot use object of type stdClass as array in /public_html/Storm Broadband/test.php on line 83
any ideas why this is happening :S
# 6 Re: displaying MySQL data
Because you have both associative and object calls to $row.
If you are using mysql_fetch_assoc, then use...
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row['subject']); ?></a></td>
If you are using mysql_fetch_object, then use...
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row->ticketid; ?>"><?php echo htmlentities($row->subject); ?></a></td>
# 7 Re: displaying MySQL data
okay thanks alot that cleared that problem!
but now the hyperlinks are not referring to anything. its just a blank ticketid.
how do I get the hyperlink to display the data for that listing in a form? I got it to display some data atleast with my original code but this way is completely new to me so am alittle lost.
thanks again for any help in advance!
# 8 Re: displaying MySQL data
The following code will output yourpage.php?ticketid=whatever your database returns. Therefore, now your $_GET['ticketid'] will grab that. Make sure you are echoing the right column. I made up [b]$row['ticketid'].
<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>
# 9 Re: displaying MySQL data
thanks alot for replying.
the problem was i forgot to add the ticketid in the select statement. now when i hover over the links it displays the correct ticketid, but when i click on them it does not take me to all the data relating to that id. where about in my code do i echo the results?
this is the code i have:
<?php
include 'dbconnect.php';
$result = mysql_query("SELECT ticketid, created, fk_memberid2, subject FROM technicalproblems");
while($row = mysql_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['created']; ?></td>
<td><?php echo $row['fk_memberid2']; ?></td>
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row['subject']); ?></a></td>
</tr>
<?php }
?>
# 10 Re: displaying MySQL data
where about in my code do i echo the results?
So long as it is built into the table, as it is, it will show up in the outputted table. Notice the <?php echo...
# 11 Re: displaying MySQL data
yeh it does exist thats what im confused about. but it still doesnt take me to all that rows data. please take a look to see what im goin on about:
http://www.kumar.adsl24.co.uk/Storm%20Broadband/test.php
thanks again.
# 12 Re: displaying MySQL data
That would be because if(!isset($_GET['ticketid'])){...} statement isn't grabbing it correctly. Double-check that. If you can't figure it out, please post the source code again, since we have made modifications.
# 13 Re: displaying MySQL data
hi
yes it is definitely that. i dont know how to change it around. ive placed my original $_GET code at the top:
<table>
<tr>
<td>Date created</td>
<td>Member ID</td>
<td>Subject</td>
</tr>
<?php
include 'dbconnect.php';
if(!isset($_GET['ticketid']))
{
$query = "SELECT subject, message FROM technicalproblems WHERE ticketid=".$_GET['ticketid'];
$result = mysql_query($query) or die('Error : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$title = $row['ticketid'];
$content = $row['message'];
}
$result = mysql_query("SELECT ticketid, created, fk_memberid2, subject FROM technicalproblems");
while($row = mysql_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['created']; ?></td>
<td><?php echo $row['fk_memberid2']; ?></td>
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row['subject']); ?></a></td>
</tr>
<?php }
?>
</table>
# 14 Re: displaying MySQL data
You have confused your isset() statement. Notice that you have said "if ticketid is not set, then get the ticket id from the database." If you don't have the ticketid, you can't get it from the database.
Here is a much cleaner version of you code. Notice the mysql_real_escape_string converting $_GET['ticketid']. Without that, you are vulnerable to SQL injection.
<?php
// check for $_GET['ticketid']
// if it exists, show the article
if(isset($_GET['ticketid'])){
$query = "SELECT * FROM technicalproblems WHERE ticketid = " . mysql_real_escape_string($_GET['ticketid']);
$result = mysql_query($query) or die('Error : ' . mysql_error());
$row = mysql_fetch_assoc($result);
$title = $row['ticketid'];
$content = $row['message'];
// now you can echo it as you please
}
else{
// now if $_GET['ticketid'] is not set, list the articles
?>
<table>
<tr>
<td>Date created</td>
<td>Member ID</td>
<td>Subject</td>
</tr>
<?php
include 'dbconnect.php';
$result = mysql_query("SELECT ticketid, created, fk_memberid2, subject FROM technicalproblems");
while($row = mysql_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['created']; ?></td>
<td><?php echo $row['fk_memberid2']; ?></td>
<td><a href="<?php echo $_SERVER['PHP_SELF'] . '?ticketid=' . $row['ticketid']; ?>"><?php echo htmlentities($row['subject']); ?></a></td>
</tr>
<?php } /* end of while() */ ?>
</table>
<?php } /* end of isset() */ ?>
# 15 Re: displaying MySQL data
thank you. excellent reply.
appreciate the help. and thank you for adding the comments in the code. very beneficial!
# 16 Re: displaying MySQL data
You're welcome. Glad to help! Good luck with the rest. :wave:
