Tag: select

Once you have a Mysql database setup with a table and some data in that table you are ready to display the data through PHP. This is very easy to do not only efficiently but also with a clean design in mind.

So let’s say this is your MySQL table:

Categories
Id, Title, Description, Timestamp

Once you have a Mysql database setup with a table and some data in that table you are ready to display the data through PHP. This is very easy to do not only efficiently but also with a clean design in mind.

Here is a basic Mysql command to grab all the data in our table “categories”:

[codesyntax lang=”php”]
<?php

$query = “SELECT * FROM categories ORDER BY id DESC”;
$sql = mysql_query($query) or die(mysql_error());
$count = mysql_num_rows($sql);

?>
[/codesyntax]

This will grab all the data out of the table “categories” in descending order by the ID. Now let’s say we want to display this in a table with headers and organization. Here is an easy example of how to do this:

[codesyntax lang=”php”]
<?php

if($count > 0){
// rows were returned by the query
// set up the table opener and headers
echo ‘<table width=”100%” align=”center” border=”0″ cellpadding=”2″ cellspacing=”0″ class=”data_table”>’;
echo ‘<tr>’;
// headers
echo ‘<th align=”left”>Title</th>’;
echo ‘<th align=”left”>Description</th>’;
echo ‘<th align=”left”>Posted Date</th>’;
echo ‘</tr>’;
for($i=0;$i<=$count;$i++){
while($row = mysql_fetch_array($sql)){
// go through all the returned rows and print the fields
echo ‘<tr>’;
if($i%2==0){
// apply row styles
echo ‘<td align=”left” class=”td_alt2″>’.$row[‘title’].'</td>’;
echo ‘<td align=”left” class=”td_alt2″>’.substr($row[‘description’],0,200).”;
// shorten to 200 characters
if(strlen($row[‘description’]) > 200){
echo ‘…’;
}
echo ‘</td>’;
echo ‘<td align=”left” class=”td_alt2″>’.date(“m/d/Y”,$row[‘timestamp’]).'</td>’;
} else {
echo ‘<td align=”left” class=”td_alt1″>’.$row[‘title’].'</td>’;
echo ‘<td align=”left” class=”td_alt1″>’.substr($row[‘description’],0,200).”;
// shorten to 200 characters
if(strlen($row[‘description’]) > 200){
echo ‘…’;
}
echo ‘</td>’;
echo ‘<td align=”left” class=”td_alt1″>’.date(“m/d/Y”,$row[‘timestamp’]).'</td>’;
}
echo ‘</tr>’;
}
}
echo ‘</table>’;
} else {
echo ‘No entries found, please try again.’;
}

?>[/codesyntax]

This will output something like so:

Title	Description	Posted Date
Data 1 Title	Data 1 Description	Data 1 Date
Data 2 Title	Data 2 Description	Data 2 Date
Data 3 Title	Data 3 Description	Data 3 Date
Data 4 Title	Data 4 Description	Data 4 Date
Data 5 Title	Data 5 Description	Data 5 Date