[SOLVED] Displaying a table records from a database

PRodgers4284 · February 7, 2008

I want to display a the contents of a table from a mysql database that displays a list of users that have registered to my website, can anyone help me or point me in the right dirrection of how to do this?

Thanks

Phil

haku · February 7, 2008

$users = mysql_query("SELECT username FROM users);
while($user = mysql_fetch_array($users))
{
    echo $user['username']. "<br />";
}

PHPoracle · February 7, 2008

Smarter if you used SELECT * FROM

aschk · February 7, 2008

no NOT smarter at all. And from someone who has PHPOracle in their username I would expect you to know this.

Specifying by column name is smarter (because you know what columns are coming back and don't leave it to chance) and in large tables (in excess of 50 columns) specifying each column name individually can speed up queries.

PHPoracle · February 7, 2008

no NOT smarter at all. And from someone who has PHPOracle in their username I would expect you to know this.

Specifying by column name is smarter (because you know what columns are coming back and don't leave it to chance) and in large tables (in excess of 50 columns) specifying each column name individually can speed up queries.

Unless you have a really crappy server, or a large script which I doubt in this case, it is SMARTER to use the *. Maybe the query is faster for one column, but by what? 0.00000000000000000000000001?

haku · February 7, 2008

I'm going to have to agree with aschk on this. Its definitely not smarter to pull all the fields out of a database. You should never do anything more than you need to in programming, its inefficient. For small sites it may not matter, but for any site with a significant amount of users, those little bits add up.

On top of this, we are speaking of a table with usernames in it. That most likely means that there are also passwords saved in the database. Using * pulls the passwords out of the database and lowers your level of security. Once passwords are in the database, they should stay there.

PRodgers4284 · February 7, 2008

$users = mysql_query("SELECT username FROM users);
while($user = mysql_fetch_array($users))
{
    echo $user['username']. "<br />";
}

I not sure if im doin this correct or not, i have the following code below and i have indictated where i need the table to go ///database table here///, do i put the sql statement here?

my code

<?php 
/* Include Files *********************/
session_start(); 
include("database.php");
include("login.php");
/*************************************/
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Jobs4U</title>


<!--CSS Stylesheet  -->
<link rel="stylesheet" type="text/css" href="./css/main.css" />
</head>

<body style="margin-left:1px;margin-right:1px;margin-bottom:0px;margin-top:0px;">

<!--Main Container  -->	
<div id="container">

<!--Main Header  -->
<div id="header" style="width: 988px; height: 154px">
	 
</div>


<!-- Navigation Menu -->
<div id="nav_menu">
	<div class="link_container" style="width: 852px; height: 32px">
		<a href="page.htm" class="main_nav">home</a><span class="link_separator">::</span>
		<a href="page.htm" class="main_nav">products</a><span class="link_separator">::</span>
		<a href="page.htm" class="main_nav">services</a><span class="link_separator">::</span>
		<a href="page.htm" class="main_nav">about us</a><span class="link_separator">::</span>
		<a href="page.htm" class="main_nav">contact</a>                
		<input type="text" name="search0" maxlength="30" size="30" >  
		<a href="page.htm" class="main_nav">Quick Search>>></a></div>
</div>

<!-- Container (left) -->	
<div id="body">
	<div id="body-left-panel" style="width: 654px; height: 811px">
		<div style="padding:10px;">

<!-- Left container 1(Top) -->
			<div class="box_upper_partviewdetails">
				<img src="img/box_up-left_edge.png" alt="" class="align-left"/>
				<img src="img/box_up-right_edge.png" alt="" class="align-right"/>
				<span class="box_header">:: Account Details</span></div>

<!--(Middle)  -->
			<div class="box_middle_partviewdetails">
			<div class="pad_15tah">
                    	 <table border="1" width="80%" id="table14">
						<tr>
							<td>

						/////Database table here//////

							</td>
						</tr>
					</table>




					</p>
						</div>
			</div>

<!-- (bottom) --> 			
		<div class="box_lower_part">
			<img src="img/box_low-left_edge.png" alt="" class="align-left"/>
			<img src="img/box_low-right_edge.png" alt="" class="align-right"/>
		</div>

<!--Left Container 2 (Top)  -->
		</div>
		</div>
	<p>

<!--Right Panel 1  -->
	</p>
	<div id="body-right-panel" style="width: 333px; height: 811px">
		<div style="padding:10px;">


<!--Top  -->
			<div class="box_upper_part">
				<img src="img/box_up-left_edge_dark.png" alt="" class="align-left"/>
				<img src="img/box_up-right_edge_dark.png" alt="" class="align-right"/>
				<span class="box_header">Member Login</span></div>


<!--Middle  -->
			<div class="box_middle_part">
				<div style="padding:5px;">
				<ul class="img_red">


		<!--Login -->						
		<?php displayLogin(); ?>
			<p><br /></p>
		</div>
		</div>


<!-- (bottom)  -->		
			<div class="box_lower_part">
			<img src="img/box_low-left_edge_dark.png" alt="" class="align-left"/>
			<img src="img/box_low-right_edge_dark.png" alt="" class="align-right"/>
			</div>


<!--Right Panel 2 (top)  -->
	<div class="box_upper_part" style="width: 313px; height: 29px">
		<img src="img/box_up-left_edge_dark_nodot.png" alt="" class="align-left"/>
		<img src="img/box_up-right_edge_dark.png" alt="" class="align-right"/>
		<span class="box_header">:: Resource Centre</span>
	</div>


<!--Middle  -->
	<div class="box_middle_part">
	<div style="padding:10px;">
	<div align="center">
			<table border="0" width="65%" id="table12" cellspacing="0" cellpadding="0" bgcolor="#E7E6E4">
				<tr>
					<td>
					<img border="0" src="images/cv.gif" width="221" height="23"></td>
				</tr>
				<tr>
					<td><p> </p>
						<p><span class="navyboldtxt">CV Advice</span>. </p>
						<p> - Advice on how to create a CV </p>
						<p> - Create your own CV</p>
						<p> </p>
					</td>
				</tr>
			</table>
			<table border="0" width="65%" id="table13" cellspacing="0" cellpadding="0" bgcolor="#E7E6E4">
				<tr>
					<td>
						<img border="0" src="images/interview.gif" width="221" height="23">
					</td>
				</tr>
				<tr>
					<td> <p><span class="navyboldtxt">
						Interview Advice</span>. </p>
						<p> -  How to approach an interview </p>
						<p> - Interview Techniques</p><p> </td>
				</tr>
			</table>
	</div>
	</div>
	</div>


<!-- (bottom)  -->
	<div class="box_lower_part" style="width: 313px; height: 22px">
		<img src="img/box_low-left_edge_dark.png" alt="" class="align-left"/>
		<img src="img/box_low-right_edge_dark.png" alt="" class="align-right"/>
	</div>
	</div>
	</div>
	</div>


<!-- Footer  -->
	<div id="footer">		
	<div id="footer_designby" style="width: 987px; height: 30px">
		<div class="designedby">Designed by 
			<a href="">Philip Rodgers</a></div>
	</div>
	</div>
	</div>	

</body>
</html>

Phil

PHPoracle · February 7, 2008

You can use mysql_free_result() to free more memory, and make the script run faster I think.

haku · February 7, 2008

Sorry, I help fix code and point people in the right direction if they are having conceptual problems on where to go. I charge money to write code though.

You can use mysql_free_result() to free more memory, and make the script run faster I think.

Your point being?

aschk · February 7, 2008

Let me provide an example of why PHPOracle's method is not the correct one (instead of relying on "crappy" statements).

example 1:

$sql = "SELECT * FROM users";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
  echo $row['username'];
}

example 2:

$sql = "SELECT username as 'user', password as 'pass', website as 'site', im as 'imessenger' FROM users";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
  echo $row['user'];
}

In example 2 we can see what columns we're using, and also what aliases they are. So now, if we change add in another column to our table we're not suddenly pulling MORE information out than we need. In example 1 we are merely guessing column names (because they're not specified, they're invisible) and when you hand over the application to another developer (as is the real world) then they ALSO know what columns they're interacting with immediately and aren't using guess work. A change in column name will also be relatively straight forward and transparent. Column name for "username" changes to "firstname" we can immediately update our SQL statement and see transparently that it'll immediately work with our PHP without issue (due to the use of alias). This is especially effective is your sql is abstracted away in stored procedures.

Let me also say that by pulling a larger dataset than you need you are performing unnecessary memory allocation for columns that are not in use. Pull 1 column out requires (~almost, depending on field type) 4 times less space than pulling 4 columns out. Thus slowing down your application.

Also, debugging when you don't actually know what size your dataset/column count is adds an unknown quantative variable into your solution.

Now if PHPOracle would like to provide some example reasoning behind his method i'm sure we'll all be enlightened further.

aschk · February 7, 2008

do i put the sql statement here?

You can put your SQL statement + relevant PHP code ANYWHERE providing it occurs before or AT this point in your script.

I would however recommend trying to keep as much PHP at the top of your script. It helps to keep a clear segmentation between logic and presentation. (Google "MVC" for additional learning).

How? I hear you cry.

Well, do you SQL statement at the top, and instead of echo'ing out the usernames just create an array of usernames, then later on in your presentation (html) loop your array and output all those names.

e.g.

<?
// Initialise usernames array.
$usernames = array();

// Initialise SQL statement.
$sql = "SELECT username 'username' FROM users";
$query = mysql_query($sql);

// Build our usernames array.
while($row = mysql_fetch_assoc($query)){
  $usernames[] = $row['username'];
}
?>


<!--- HTML HTML HTML --->

<?foreach($usernames as $username):?>
<tr><td><?=$username?></td></tr>
<?endforeach;?>

<!--- more html ---->

Hopefully that will help.

phpSensei · February 7, 2008

Ive Read Somewhere where using "<? ?>" ment get off your ass and change them to "<?php ?>"

aschk · February 7, 2008

Thank you for that Sensei, another helpful comment.

You could at least have been more informative and pointed people to a decent tag syntax convention http://uk.php.net/manual/en/language.basic-syntax.php where it clearly states that <? ?> tags are perfectly valid although not XHTML/XML compliant. Also saying that short tags need to be turned on would have helped to clarify this for anyone reading...

phpSensei · February 7, 2008

Thank you for that Sensei, another helpful comment.

You could at least have been more informative and pointed people to a decent tag syntax convention http://uk.php.net/manual/en/language.basic-syntax.php where it clearly states that <? ?> tags are perfectly valid although not XHTML/XML compliant. Also saying that short tags need to be turned on would have helped to clarify this for anyone reading...

My pleasure.

I don't copy paste my info from php.net or google them all the time. I was talking to you when I said "<? ?>" and I thought you could have told the user to change it, instead of me being an ass and pointing it out for you.

aschk · February 7, 2008

Yes, that's right it's all about the points, because i really want more points... not perhaps the fact that the information i've provided thus far has been informative/helpful and backed up with factual information. I forget these forums are actually about flaming other people, and not helping those that need it...

I think you have also helpfully shown who really is the point scavenger here with your last post. ::)

phpSensei · February 7, 2008

Yes, that's right it's all about the points, because i really want more points... not perhaps the fact that the information i've provided thus far has been informative/helpful and backed up with factual information. I forget these forums are actually about flaming other people, and not helping those that need it...

I think you have also helpfully shown who really is the point scavenger here with your last post.

I won't take this any further with you, next time instead of being such a jerk to PHPoracle like that also, you could you have said it nicely to him. I am sure you don't like it when someone proves a point but does it in a rude manner. Made PHPoracle look stupid, and embrassed him...

These forums aren't about Flaming, I have been here longer then you have, I think I know what its about.

Here are a few "things" you could have said to php oracle that isnt copy pasted from anywhere:

Yes phporacle, your methods may work but it is not Smarter, so here is the way to do it:

End of story.

aschk · February 7, 2008

Funny you should say that

Yes phporacle, your methods may work but it is not Smarter, so here is the way to do it:

because that's exactly what I did...

His 1 line statement was NOT correct, and not factually backed up. He should be embarassed for such a flagrant use of incorrect information. I then provided a much elaborated piece of information to corroborate what I said.

And it's interesting that you should

(referring to these forums)

know what its about.

yet seem to blindly ignore the moral rules you seem to "adhere" to.

And just because you've "been here longer" doesn't give you any seniority in the matter.

If you feel i've been unhelpful, spiteful, or misleading in any way then by all means feel free to contact me by message.

phpSensei · February 7, 2008

Edit: You know what, No further spamming this thread, I would be happy to discuss this with you, but you will just block yourself from every thing I say to you.

phpSensei · February 7, 2008

When Somebody is new and asks a question, or makes a mistake like PHPoracle did, you don't target him but rather share the information as if your talking to everyone, and teaching them a lesson. Not "Relying on Crappy Statements", and "Let me proove PHPoracle Wrong"...

haku · February 7, 2008

To be fair, phporacle started the snotty attitude first.

PRodgers4284 · February 7, 2008

I have written some basic code to display the records from the tabel, but i can only get it to display the first record in the table, can anyone help, i think i need the code to do a loop to get the other records. I really appreciate the help everyone has provided.

PRodgers4284 · February 7, 2008

My code is

<?php

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM users")
		or die(mysql_error());  

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
?>
<table>
<tr>
<td>Username</td>
</tr>
<tr>
<td><?php echo " ".$row['username']; ?></td> 
</tr> 

<tr>
<td>Password</td>
</tr>
<tr>
<td><?php echo " ".$row['password']; ?></td> 
</tr>
<tr>
<td>forename</td>
</tr>
<tr>
<td><?php echo " ".$row['forename']; ?></td> 
</tr>

<?php 
    ?> 
</table>

phpSensei · February 7, 2008

My code is

<?php

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM users")
		or die(mysql_error());  

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
?>
<table>
<tr>
<td>Username</td>
</tr>
<tr>
<td><?php echo " ".$row['username']; ?></td> 
</tr> 

<tr>
<td>Password</td>
</tr>
<tr>
<td><?php echo " ".$row['password']; ?></td> 
</tr>
<tr>
<td>forename</td>
</tr>
<tr>
<td><?php echo " ".$row['forename']; ?></td> 
</tr>

<?php 
    ?> 
</table>

You are Correct, You need to loop through the data using the WHILE LOOP...

Examples:

$result = mysql_query("SELECT * FROM users")
		or die(mysql_error());  

while($row = mysql_fetch_array( $result )){


echo $row['username'] . "<br>";

}

Smarter if you used SELECT * FROM

no NOT smarter at all. And from someone who has PHPOracle in their username I would expect you to know this. Specifying by column name is smarter (because you know what columns are coming back and don't leave it to chance) and in large tables (in excess of 50 columns) specifying each column name individually can speed up queries.

It doesnt matter what your username is...

I can be called GOD, would that make me any different?

craygo · February 7, 2008

Getting back to the question...

<!--(Middle)  -->
			<div class="box_middle_partviewdetails">
			<div class="pad_15tah">
                    	 <table border="1" width="80%" id="table14">
<tr>
<td>Username</td>
<td>Password</td>
<td>Forename</td>
</tr>
<?php
$sql = "SELECT * FROM users";
$res = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($res)){
echo "<tr>
<td>".$row['username']."</td>
<td>".$row['password']."</td>
<td>".$row['forename']."</td>
</tr>\n";
}
?>
					</table>

ray

PRodgers4284 · February 7, 2008

Thanks guys for the help, really appreciate it

Sign In

[SOLVED] Displaying a table records from a database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information