Displaying data in website

[learning000]

Hi, i am new to SQL and databases but ive been building websites for ages now. I can display data from my databases in php on my website but can anyone help me to learn how to display it in a design. How it displays is pretty basic and i need it to fit into my designs. People tell me just to code HTML tables into it but it gives me errors.

To start with id jus like to learn how i can place the data from the database into a neat table in php if anyone can understand this and help me id be greatfull.

[jatar_k]

Welcome to WebmasterWorld learning000,

>> People tell me just to code HTML tables into it but it gives me errors

well, my wild guess is that you are just having some syntax problems with outputting text.

let us use a very basic example, hopefully I can cover all the points in it.

the first thing is to get the data we need from mysql, you seem to have that covered but I will give you this link here, just in case
Basics of extracting data from MySQL using PHP [webmasterworld.com]

let's say we have a table called contacts with 3 fields: name, address, phone

we can get all of the rows from this table using a very simple query
SELECT * FROM contacts

easy, this will grab all of the rows in our table. We don't know how many rows there might be and we will need to build our table to handle all of them.

thoughts
each row needs to be built into our table with its columns represented in each row of our table
we may want alternating styling for readability
we may want css or plain jane html

so we start simply, I will slap a little basic mysql stuff in here. You may, or may not, have it exactly the same, doesn't really matter.

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';
$connect = mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

$sql = 'SELECT * FROM contacts';
$result = mysql_query($sql);

alright so now we need to start building our loop. You have 2 choices at this point
1. read the results into an array and then have a seperate loop to display them
2. display the results right away

we will use option 2

we now need to think of our html output as well as our php syntax. We will use a 100% echo style as opposed to hopping in and out of php parsing.

we need table tags

echo '<table>';
echo '</table>';

I use single quoting here as I am not planning on putting any variables between the quotes. That allows me to use double quotes within those strings. Let's add some basic table stuff

echo '<table cellpadding="0" cellspacing="0" border="0" width="90%">';
echo '</table>';

alright, since we only need table tags once then I think our real loop should go in the middle of them

echo '<table cellpadding="0" cellspacing="0" border="0" width="90%">';
while ($row = mysql_fetch_array($result)) {
}
echo '</table>';

perfect. for each iteration (time the loop is executed) we need to output 1 row worth of data and all it's html code and styling. I usually start with just html.

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

or
echo '<tr><td></td><td></td><td></td></tr>';

that is my basic structure, which makes me think, I need a header row with my column names, since that needs to only be output once at the top of the table I think I will put that before my loop. Since it is also static, I will compress it all into on echo line

this

<tr> 
 <td>Name</td> 
 <td>Address</td> 
 <td>Phone #</td> 
</tr>

turns into this
echo '<tr><td>Name</td><td>Address</td><td>Phone #</td></tr>';

and it will go right after our echo for the table tag

we need to create a single row of output from our basic structure. Our array is called $row and name will be the first element, address the second and phone the third. This means a basic row would look like this

echo '<tr><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>';

if we pop that into our while loop now it will work as is but with no styling. In the line above, because I am using echo it is faster to use a comma between chunkss than to use . (concatenation operator). The reason for this is that when you concatenate in an echo statement it actually has to go put the whole thing together before it can echo it. Using the comma it just spits it out chunk by chunk. You don't necessarily have to do it all in one echo statement like I did. You can break it up into individual echo's for each cell if you like, that's just preference. I switch back and forth depending on how complex my table is.

so now our code looks like this

$dbhost = 'localhost'; 
$dbuser = 'username'; 
$dbpass = 'password'; 
$dbname = 'database'; 
$connect = mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 
$sql = 'SELECT * FROM contacts'; 
$result = mysql_query($sql); 
echo '<table cellpadding="0" cellspacing="0" border="0" width="90%">'; 
echo '<tr><td>Name</td><td>Address</td><td>Phone #</td></tr>'; 
while ($row = mysql_fetch_array($result)) { 
 echo '<tr><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>'; 
} 
echo '</table>';

works, displays really boring web pages ;)

let's bold the title row and let's say we have 2 classes for our rows, 1 light background one and one darker. That shouold help our users read the rows better. I also want to add a spacer line after every row.

so the title row will look like this

echo '<tr><td><b>Name</b></td><td><b>Address</b></td><td><b>Phone #</b></td></tr>';

our spacer row is just simple html

<tr><td colspan="3">&nbsp;</td></tr>

now we throw that into an echo line and we will add it after our data row echo line

echo '<tr><td colspan="3">&nbsp;</td></tr>';

and then adding our class to the rows is a little different. We need to switch every other row so we need an if statement. If one thing is true do this, else do that. We also need something to control the switch. Let's add a counter. The important thing about counters is to remember to increment them for every execution of the loop. So we initialize our counter above our loop and increment it at the very bottom inside of our loop

$counter = 0;

then the incrementing looks like this

$counter++;

so now we can test the value of our counter to see where we are. Since we only are building two possibilities we can use what is called a modulus. This returns the remainder. If we divide anumber by 2 then even numbers should have a remainder of 0 and odd ones will have a remainder of 1. That gives us 2 unique alternating cases to test.

if ($counter % 2) { 
 echo '<tr class="darkrow"><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>'; 
} else { 
 echo '<tr class="lightrow"><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>'; 
}

if we plug that into our loop, replacing the original echo and add our counter we end with this

$dbhost = 'localhost'; 
$dbuser = 'username'; 
$dbpass = 'password'; 
$dbname = 'database'; 
$connect = mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 
$sql = 'SELECT * FROM contacts'; 
$result = mysql_query($sql); 
$counter = 0; 
echo '<table cellpadding="0" cellspacing="0" border="0" width="90%">'; 
echo '<tr><td><b>Name</b></td><td><b>Address</b></td><td><b>Phone #</b></td></tr>'; 
while ($row = mysql_fetch_array($result)) { 
 if ($counter % 2) { 
 echo '<tr class="darkrow"><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>'; 
 } else { 
 echo '<tr class="lightrow"><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>'; 
 } 
 echo '<tr><td colspan="3">&nbsp;</td></tr>'; 
 $counter++; 
} 
echo '</table>';

you can go on styling from there but those are the basics of how to plug your classes and html into your php

[learning000]

thanks alot jatar ima look through all this and attempt to do something simular and see how i get on.

[jatar_k]

no worries, you'll figure it out. It's just a matter of doing it a couple times.

[learning000]

hey sorry, also does it matter where the mysql_close($dbh); goes? i was told to put it after say

$username = "";
$password = "";
$hostname = "";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
$selected = mysql_select_db("dbstart",$dbh) or die("Unavailable");
$sql = 'SELECT * FROM Teamjc86 ORDER BY `Code` ASC ';
$result = mysql_query($sql);
mysql_close($dbh);

or does it go after everything at the bottom of all the code when its finished.

[grandpa]

I generally put all the close statements at the end of the code, unless it's explicitly needed elsewhere. jatak_k laid it out nicely.

I display three columns, with data from a different row for each column. Its important to initialize a counter that will be used inside the while loop. I use the same counter to check for column and row position in my table. A slightly different approach than using alternating rows. And, you still have all the creative ability of layout and css styling.

$counter = 1; // initialize loop counter to one
echo '<table>';
while ( do something ) {
.
.

if ($counter == '1') {
echo '<tr><td>start a new row</td>';
}
if ($counter == '2') {
echo '<td>center column</td>';
}
if ($counter == '3') {
echo '<td>end the row</td></tr>';
}

// check the counter inside the while loop
if ($counter == '3') {
$counter = 1;
}
else {
$counter ++;
}
} // end while
echo '</table>';

[learning000]

Hi, thanks for all the help, also is it possible to have a link in your database? so when its displayed in your website php it can display as a link to another page or open a new page in a new window?.

[jatar_k]

sure

if your store the fully qualified url in the database then you can adjust the above example. You could even have a column for the link text.

let's reformat our original example since the individual cells are getting longer

while ($row = mysql_fetch_array($result)) {  
 if ($counter % 2) {  
 echo '<tr class="darkrow">'; 
 echo '<td>',$row[0],'</td>'; 
 echo '<td>',$row[1],'</td>'; 
 echo '<td>',$row[2],'</td>'; 
 echo '</tr>';  
 } else {  
 echo '<tr class="lightrow">'; 
 echo '<td>',$row[0],'</td>'; 
 echo '<td>',$row[1],'</td>'; 
 echo '<td>',$row[2],'</td>'; 
 echo '</tr>';  
 }  
 echo '<tr><td colspan="3">&nbsp;</td></tr>';  
 $counter++;  
}

let's say that $row[2] is going to be our url and we add a 4th column located at $row[3] for our link text. We need to add our opening and closing a tag with the url ($row[2]) and then display $row[3] between them

we need to change this

echo '<td>',$row[2],'</td>';

into this

echo '<td><a href="',$row[2],'">',$row[3],'</a></td>';

our resulting loop then looks like this

while ($row = mysql_fetch_array($result)) {  
 if ($counter % 2) {  
 echo '<tr class="darkrow">'; 
 echo '<td>',$row[0],'</td>'; 
 echo '<td>',$row[1],'</td>'; 
 echo '<td><a href="',$row[2],'">',$row[3],'</a></td>'; 
 echo '</tr>';  
 } else {  
 echo '<tr class="lightrow">'; 
 echo '<td>',$row[0],'</td>'; 
 echo '<td>',$row[1],'</td>'; 
 echo '<td><a href="',$row[2],'">',$row[3],'</a></td>'; 
 echo '</tr>';  
 }  
 echo '<tr><td colspan="3">&nbsp;</td></tr>';  
 $counter++;  
}

make sense?

[learning000]

ok yes i think i understand, but how does this text link know what i want it to point to? like in html it's <a href="page.htm">link</a> so this is say $row2 but how do i get the link to say link to a page like info.php.

For example my database could be

Name: James
Age: 19
Job: Self employed

ok and i want the 'James' to link to a page like jamesinfo.htm or php whatever.

and then maybe like a simular thing but different name and so on linking to that persons info page. is this possible? if so then i dont see how what u described above does.

sorry for being such a noob :)

[learning000]

Sorry i understand now, so if $row2 is say James, and i want the link to be james i would link $row2 with $row2 , and create a folder james in my directory? and it will bring up the index of that folder?

[jatar_k]

kinda sorta

try it out, view source a lot and it will all become clear ;)