PDA

View Full Version : displaying mysql data

Tjobbe
November 30th, 2004, 15:24
I'm in the middle of writing a simple login /logout script to allow access to a special area of my site for members.

I have the login script and register page where it writes the following fields to the table:

username
password
firstname
lastname
email
address1
address2
postcode
county
telephone

I can get the login script to display this no problems.

What I want to do is create an extar page, in a seperate paswword protected are for admin purposes, where all the details are displayed, except for the passwords, so we can see who is registerred on our database.

I'd like a simple html table to display the output in an easy to read format like this:

|username | firstname | lastname | address1 | address2 | postcode | county | email | telephone |

with the data then displayed from the mysql table like that.

How would I do this in an easy manner? obviously i need the database connect variables at the op, with an echo of somekind but I dont know how to get it displayed as a table.

can anyone help me out with this?
Tjobbe
November 30th, 2004, 16:10
i came accorss this:

I'm using a localhost set up so no username and password is needed but I know i need something like this:


<html>
<head><title>(Title Here)</title></head>
<body>
<?php
$db="userlist";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM users" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
print "<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=arial size=1/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
mysql_close($link);
?>
</body>
</html>



my db is called userlist, and my field is called users.


this displays EVERYTHING, including the encrypted password, which isnt what I need! I need eveything but thta, if possible, as it will just confuse the user.
sonicgroup
November 30th, 2004, 16:37
[syntax:51dfb32273="php"]
<html>
<head><title>(Title Here)</title></head>
<body>
<?php
$db="userlist";
$link = mysql_connect("localhost");
if (! $link) die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM users" ) or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
echo "There are $num_rows records.<P>";
echo "<table width=200 border=1>\n<tr>\n";
while ($row = mysql_fetch_object($result)){
echo "\t<td>$row->field1</td><td>$row->field2</td><td>$row->field3</td>\n";
}
print "</tr>\n</table>\n";
mysql_close($link);
?>
</body>
</html>
[/syntax:51dfb32273]

Replace $row->field1 with $row->yourFieldName, etc.
Tjobbe
November 30th, 2004, 16:53
thanks very much, Thats now a lot clearer, but I have a slight problem with that as it does not format the table properly, if i have mor ethan 1 record. I'm just unsure of what to tell php to start a new table row rather than a continuos one. currently it looks like this:

<html>
<head><title>(Title Here)</title></head>
<body>
There are 2 records.<P> <table width='100%' border='0' cellpadding='4' cellspacing='1' bgcolor='#000000'>
<tr bgcolor='#FFFFFF'>
<td>Username</td>
<td>First Name </td>
<td>Last Name </td>
<td>Email</td>
<td>Phone Number </td>
<td>Address 1 </td>
<td>Address2</td>
<td>Postcode</td>
<td>County</td>
</tr>

<tr>

<td bgcolor='#FFFFFF'>dad</td><td bgcolor='#FFFFFF'>terry</td><td bgcolor='#FFFFFF'>andrews</td><td bgcolor='#FFFFFF'>terry@bagnboxman.co.uk</td><td bgcolor='#FFFFFF'>01295788522</td><td bgcolor='#FFFFFF'>unit 1, west street</td><td bgcolor='#FFFFFF'>shutford, banbury</td><td bgcolor='#FFFFFF'>ox156ph</td><td bgcolor='#FFFFFF'>Oxon</td>
<td bgcolor='#FFFFFF'>tjobbe</td><td bgcolor='#FFFFFF'>tjobbe</td><td bgcolor='#FFFFFF'>andrews</td><td bgcolor='#FFFFFF'>tjobbe@gmail.com</td><td bgcolor='#FFFFFF'>01295730112</td><td bgcolor='#FFFFFF'>green garden cottage, main street</td><td bgcolor='#FFFFFF'>north newington, banbury</td><td bgcolor='#FFFFFF'>ox156aj</td><td bgcolor='#FFFFFF'>oxfordshire</td>
</tr>
</table>

</body>

thanks in advance
sonicgroup
November 30th, 2004, 17:00
Oops. Hehe. It's a simple matter of moving the <tr> tags into the while loop.

[syntax:cd56bbd745="php"]
<html>
<head><title>(Title Here)</title></head>
<body>
<?php
$db="userlist";
$link = mysql_connect("localhost");
if (! $link) die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM users" ) or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
echo "There are $num_rows records.<P>";
echo "<table width=200 border=1>\n";
while ($row = mysql_fetch_object($result)){
echo "<tr>\n\t<td>$row->field1</td><td>$row->field2</td><td>$row->field3</td>\n</tr>\n";
}
echo "</table>\n";
mysql_close($link);
?>
</body>
</html>
[/syntax:cd56bbd745]
Tjobbe
November 30th, 2004, 17:04
ah i see! thanks very much!
Tjobbe
November 30th, 2004, 17:06
no seriously, thanks very very much, ive learnt quite a bit today, brilliant!

10 points for MR sonicgroup i believe..
sonicgroup
November 30th, 2004, 17:40
You're certainly welcome. You could be programming in something really, really, really, bad, like ASP. ;)
Tjobbe
November 30th, 2004, 17:43
heh heh, now thats something maybe for the future.