Assigning Styles in PHP/MySQL Table

Imagine a table with three rows, representing the U.S., Canada and Mexico. I want to use CSS to give each row a distinct background color and font color.

Someone told me I have to include the CSS in my original CSS table. So I’m trying to figure out how to do that.

To make my table, I first made a plain text file (CSV), then imported it into MySQL. The plain text file might look something like this. (I’m using commas to indicate separate cells:

Nation , Capital , Population
U.S. , D.C. , 300 million
Canada , Ottawa , 50 million
Mexico , Mexico City , 175 million

Now, here’s how my styles would look if this was a regular html table:

<tr class=“na” id=“us”>
<td class=“name” id=“us”>U.S.</td>
<td class=“capital”>D.C.</td>
<td class=“pop”>300 million</td>
</tr>

If I want this same style in my MySQL/PHP table, how should I change my CSV table - something like this?:

<tr class=“na” id=“us”><td class="name id=“us”>Nation</td> , <td class="capital>Capital</td> , <td class=“pop”>Population</td>
U.S. , D.C. , 300 million

Thanks.

First off, you’re not going to actually insert the HTML & CSS formatting into your MySQL database. That’s what PHP does; extracts the data from MySQL and formats it in any manner you wish via the HTML you output. If you start doing that, your MySQL db will be useless other than retrieving and displaying to a webpage. Trying to search / update the records would be a nightmare.

You have to realize something here, you have 4 different components at work:

  • MySQL - RDBMS, allows multiple methods to store and query data
  • PHP - scripting language - can connect with MySQL and retrieve / manipulate data
  • HTML - language that will be the structure of your webpage that PHP outputs
  • CSS - styles to apply to your HTML

All these are mutually exclusive and offer a seperate piece of the puzzle. Getting them to work together is simple enough, but they still should be kept seperate and called on when needed.

OK - then where do I put the CSS styles?

I can’t insert them in the MySQL database, and there are now table rows or cells visible on my target page until I preview it in my browser.

The whole idea of using PHP is to create a dynamic webpage from your data. You create the HTML / CSS structure in the PHP script itself, just as if you were typing it into a standalone HTML page. You can also create a seperate CSS page and import it into the HTML just as you would any other page. Perhaps some examples would help.

First example - static HTML & CSS


<html>
<head>
  <title>Static webpage</title>
  <style type="text/css">
        body { color: #000; background: #fff; font-family: sans-serif; }
        p { color: #191976; background: #faebd7; text-align: justify; }
  </style>
</head>
<body>
<h1>Example of a Static webpage</h1>
<hr>
<p>
Here is some content on my page.
Unfortunately, since this is a static page, it can only be changed when I update the HTML by hand.
</p>
</body>
</html>

Ok so obviously this is just a plain old HTML document that you can create in any browser. Not much good unless you plan on keeping the content the same day after day after day…ad infinitum.

Example Dynamic PHP script:


<?php
// dynamic_php.php
// 2004-02-07

// some script settings
$page_title = "Dynamic webpage";
// get the $id value from GET string
$id = intval($_GET['id']);

/*
  escape to HTML
  notice we use almost the exact same HTML  and CSS as before,
  changed slightly to allow for PHP dynamic content
*/
?>

<html>
<head>
  <title><?php echo $page_title; ?></title>
  <style type="text/css">
        body { color: #000; background: #fff; font-family: sans-serif; }
        p { color: #191976; background: #faebd7; text-align: justify; }
  </style>
</head>
<body>

<?php
// back to PHP mode

// connect to MySQL and get the heading and content
mysql_connect("server", "user", "pass");
mysql_select_db("test");
$result = mysql_query("SELECT heading, content FROM dynamic_table WHERE id = '$id'");

// retrieve the information from the DB
// you could also use mysql_fetch_array() here and get it all in one statement.
$heading = mysql_result($result, 0, 'heading');
$content = mysql_result($result, 0, 'content');


// finish the page
echo '<h1>'. $heading .'</h1>';
echo '<hr>';
echo '<p>'. $content .'</p>';
// escape to HTML again and we're done!
?>
</body>
</html>

So you see, in the second example we can dynamically change the page heading and content based on whats in the database. You can still format the page in exactly the same way, with the exact same HTML and CSS, but you interweave PHP code to create a dynamic page that will change based on your user input, or based on the date, etc.

Hope this helps!

My table kind of blew up in my face, but I’ll make a new MySQL from scratch and play with the code you gave me.

Thanks for the tip!

After re-reading your post a little more carefully, I understand your problem a little better. Here’s a couple of examples of using CSS to change the styles in your table rows.

First one, simply applying a CSS rule to a table row:


/*
    rest of your HTML / PHP goes here
    let's say you have a class called 't_row' defined like

    .t_row { color: #fff; background: #000; }
*/

// assumes mysql connection, db select

// query
$result = mysql_query("SELECT name, email FROM sometable");

// start the table
echo '<table border=1 cellpadding=1 width="90%">';

// fetch the data into an array and loop through each row
while ( $row = mysql_fetch_assoc($result) )
{
    // now we apply our class to the entire table row
    echo '<tr class="t_row">';
    echo '<td>' .$row['name']. '</td>';
    echo '<td>' .$row['email']. '</td>';
    echo '</tr>';
}
// close the table
echo '</table>';

Ok so in that simple example we apply our CSS class rule to the table row <tr> tag at the start of each row. You don’t have to assign a different class to each table cell <td> tag.

Second example - same thing, but three different CSS rules to apply to specific rows, based on the DB output. In this case, you have some sort of returning value from the SQL query that defines the type, causing you to want a different row color for each type.


/*
    again, the rest of your HTML / PHP goes here
    now we have three CSS classes based on 'type'

    .type_CAR { color: #fff; background: #000; }
    .type_TRUCK { color: #191976; background: #f0ffff; }
    .type_SUV {color: #000; background: #ccc; }

    hey - I said they would be different, not pretty. :-)
*/

// assumes mysql connection, db select

// query
$result = mysql_query("SELECT type, make, model FROM sometable");

// start the table
echo '<table border=1 cellpadding=1 width="90%">';

// fetch the data into an array and loop through each row
while ( $row = mysql_fetch_assoc($result) )
{
    // this switch() statement determines the row color
    switch( $row['type'] )
    {
        // assumes 'type' column is one of CAR | TRUCK | SUV
        case("CAR"):
            $class = 'type_CAR';
            break;
        case("TRUCK"):
            $class = 'type_TRUCK';
            break;
        case("SUV"):
            $class = 'type_SUV';
            break;
        default:
            $class = 'type_CAR';
            break;
    }

    // start the rows, with the color based on the switch statement
    echo '<tr class='.$class.'>';
    echo '<td>' .$row['type']. '</td>';
    echo '<td>' .$row['make']. '</td>';
    echo '<td>' .$row['model']. '</td>';
    echo '</tr>';
}

// close the table
echo '</table>';

The switch() statement determines which type for each record, and makes the row color based on that. You still have your CSS definitions at the top of the page or on an external CSS page that you call into the document <head>. This should give you a much clearer example of how to change the row colors for US / Canada / Mexico. I would also recommend changing your database table structure to allow for a ‘country’ ID, something like this:
(alphabetical order here, people)
Canada = 1
Mexico = 2
US = 3

This would help your script logic, just use the numeric value of the country ID in the switch() statement rather than the exact string “US” or “Mexico”, etc.

Hope this helps, good luck! :wink:

1 Like