CODEWITHSUNDEEP
phphtmlsql
falcs
falcs

Reputation: 509

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

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.

Upvotes: 1

Views: 11143

Answers (6)

falcs
falcs

Reputation: 509

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>

Upvotes: 0

piddl0r
piddl0r

Reputation: 2449

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'.

Upvotes: 1

CAMason
CAMason

Reputation: 1122

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>';

Upvotes: 2

el Dude
el Dude

Reputation: 5183

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

and call it 

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

and don't forget bgcolor is deprecated =)

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 72961

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>";

Upvotes: 2

Abhishek Saha
Abhishek Saha

Reputation: 2564

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)
{

Upvotes: 5

Related Questions