Change color of table based on values from SQL Query database using PHP

Question

I have some code to produce a table from an SQL query. I would like the background color of the cell to represent the value of "rel.cat", which can be an integer between 1-8.

<!DOCTYPE HTML>
<html>
<head>
</head>
<body>
<?php
// Connect to the database server
$dbcnx = mysql_connect("xxxxx",xxxxx,xxxxx);
if (!$dbcnx) {
  echo( "<P>Database Connection Failed</P>" );
  exit();
}
// Select the matrix databse database
  if ( !@mysql_select_db("sustaina_matrix") ) {
    echo( "<P>Not Connected to Matrix Database</P>" );
    exit();
  }
// Assign the query
$query = "SELECT rel.id, rel.cat colourcode FROM rel";
// Execute the query
$result = mysql_query($query);
if (!$result){
    die ("Could not query the database: <br />". mysql_error());
}
?>
<table>
    <tr>
        <th>Relationship ID</th>
        <th>Colour ID</th>
</tr>
<?php
// Change colours
function getcolour()
{
    if ($catc = "1")
        return '#000000';
    elseif($catc = "2")
        return '#C0C0C0';
    elseif($catc = "3")
        return '#00FF00';
    elseif($catc = "4")
        return '#0000FF';
    elseif($catc = "5")
        return '#FF99FF';
    elseif($catc = "6")
        return '#FF9900';
    elseif($catc = "7")
        return '#FF0000';
    else
        return '#FFFFFF';
}
// Fetch and display the results
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $id = $row["id"];
    $catc = $row["colourcode"];
    echo "<tr>";
    echo "<td>$id</td>";
    echo "<td bgcolor='getcolour()'>$catc</td>";
    echo "</tr>";
}
?>
</table>
</body>
</html>

Currently all the cells are red, and I don't know why.

Please don't use the mysql_* functions as they are in the deprecation process. Be a better PHP Developer. — Jason McCreary
– Jason McCreary, Commented Oct 22, 2012 at 15:37

Abhishek Saha · Accepted Answer · 2012-10-22 15:41:19Z

5

your if statements should have a double "==".

Instead of

if ($catc = "1")

it should be

if ($catc == "1")

Assign == everywhere in all your if conditions.

And also assign the parameter to the function.

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $id = $row["id"];
    $catc = $row["colourcode"];
    echo "<tr>";
    echo "<td>$id</td>";
    echo "<td bgcolor='getcolour(\"$catc\")'>$catc</td>";
    echo "</tr>";
}

Get the parameter in the function as well.

function getcolour($catc)
{

answered Oct 22, 2012 at 15:41

Abhishek Saha

2,5641 gold badge20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason McCreary Over a year ago

Note that getcolour is a PHP function. As such, your echo statement is incorrect.

Jason McCreary · Accepted Answer · 2012-10-22 15:42:11Z

2

You have several problems.

Pass and accept $catc to the function getcolour().
Use logical operator (==) not assignment (=) in your conditions.
Properly output the function results.

Code:

function getcolour($catc) {
    // ... existing code
}

echo "<td bgcolor='" . getcolour($catc) . "'>$catc</td>";

answered Oct 22, 2012 at 15:42

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

1 Comment

falcs Over a year ago

Several of these answers helped but echo "<td bgcolor='" . getcolour($catc) . "'>$catc</td>"; is the correct form

CAMason · Accepted Answer · 2012-10-22 15:49:21Z

Supply the variable $catc to your function. i.e.

function getcolour($catc) {
    // ... existing code
}

echo "<td bgcolor='getcolour($catc)'>$catc</td>";

You might find it more readable to use a switch() instead

function getcolour($catc) {
    switch($catc)
    {
        case 1:
            return '#000000';
            break;
        case 2:
            return '#C0C0C0';
            break;
        case 3:
            return '#00FF00';
            break;
        case 4:
            return '#0000FF';
            break;
        case 5:
            return '#FF99FF';
            break;
        case 6:
            return '#FF9900';
            break;
        case 7:
            return '#FF0000';
            break;
        default:
            return '#FFFFFF';
            break;
    }
}

echo '<td bgcolor="' . getcolour($catc) . '">' . $catc . '</td>';

piddl0r · Accepted Answer · 2012-10-22 15:52:18Z

1

You're not passing a value to the getcolour function. That function will need to be changed too, at the moment the if statement assigns a value rather than comparing values, so the first if statement will always be true. Change each one to '==' rather than '='.

change this :

echo "<td bgcolor='getcolour()'>$catc</td>";

to this :

echo "<td bgcolor='".getcolour($row['catc'])."'>.$row['catc']</td>";

This assumes that catc is returned from the query as 'catc'.

edited Oct 22, 2012 at 15:52

answered Oct 22, 2012 at 15:39

piddl0r

2,4492 gold badges23 silver badges36 bronze badges

2 Comments

piddl0r Over a year ago

@AbhishekSaha : Cheers, didn't spot that :)

Abhishek Saha Over a year ago

Very few people reply to their mistakes. Liked you. :-)

el Dude · Accepted Answer · 2012-10-22 15:46:17Z

0

 function getcolour($catc){
..........etc

and call it

<td bgcolor='",getcolour($catc),"'>$catc</td>

and don't forget bgcolor is deprecated =)

answered Oct 22, 2012 at 15:46

el Dude

5,1935 gold badges31 silver badges40 bronze badges

Comments

falcs · Accepted Answer · 2012-10-23 10:47:37Z

Based on all the awnsers given here is the final working code:

<!DOCTYPE HTML>
<html>
<head>
<meta content="en-gb" http-equiv="Content-Language">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<title>Relationships</title>
<link href="mainstyles.css" rel="stylesheet" type="text/css">
</head>
<body>
<?php
// Connect to database
require($DOCUMENT_ROOT . "connect.php");
// Assign the query
$query = "SELECT rel.id, rel.cat colourcode FROM rel";
// Execute the query
$result = mysqli_query($link ,$query);
if (!$result){
    die ("Could not query the database: <br />". mysqli_error());
}
?>
<table>
    <tr>
        <th>Relationship ID</th>
        <th>Colour ID</th>
</tr>
<?php
// Change colours
function getcolour($catc) { 
    switch($catc) 
    { 
        case 1: 
            return '#000000'; 
            break; 
        case 2: 
            return '#C0C0C0'; 
            break; 
        case 3: 
            return '#00FF00'; 
            break; 
        case 4: 
            return '#0000FF'; 
            break; 
        case 5: 
            return '#FF99FF'; 
            break; 
        case 6: 
            return '#FF9900'; 
            break; 
        case 7: 
            return '#FF0000'; 
            break; 
        default: 
            return '#FFFFFF'; 
            break; 
    } 
} 
// Fetch and display the results
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
    $id = $row["id"];
    $catc = $row["colourcode"];
    echo "<tr>";
    echo "<td>$id</td>";
    echo "<td bgcolor='" . getcolour($catc) . "'>$catc</td>";
    echo "</tr>";
}
?>
</table>
</body>
</html>

Collectives™ on Stack Overflow

Change color of table based on values from SQL Query database using PHP

6 Answers 6

1 Comment

Code:

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Code:

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related