Highlight table row in different colors based on values from database in php

Question

The code below has dynamic drop down list, that users chooses preferred brand and model, accordingly the table is generated in the webpage which results of brand, model and year . Now the code needs to group all the same year with different models and highlight them with set of different colors for each year accordingly. An expected results is attached as image.

// (___ & !!!) or (ALL & ___) or (ALL & ALL)
if ((!isset($_POST['model']) || ($_POST['brand'] == 'ALL' && empty($_POST['model']))) || ($_POST['brand'] == 'ALL' && $_POST['sip'] == 'ALL')) {
    $query1 = 'SELECT DISTINCT brand,model,year FROM `carlist` ORDER BY brand,model,year';
}
// (... & ...) or (... & ALL)
else if (!empty($_POST['brand']) && (empty($_POST['model']) || ($_POST['model'] == 'ALL'))) {
    $query1 = 'SELECT DISTINCT brand,model,year FROM `carlist` WHERE brand="'.$_POST['brand'].'" ORDER BY brand,model,year';
}
// (ALL & ...) or (ALL & notALL)
else if ($_POST['brand'] == 'ALL' && (!empty($_POST['model']) && $_POST['model'] != 'ALL')) {
    $query1 = 'SELECT DISTINCT brand,model,year FROM `carlist` WHERE model="'.$_POST['model'].'" ORDER BY brand,model,year';
}
else {
    $query1 = 'SELECT DISTINCT brand,model,year FROM `carlist` WHERE brand="'.$_POST['brand'].'" AND model="'.$_POST['model'].'" ORDER BY brand,model,year';
}
//echo $_POST['brand'].'<br />'.$_POST['model'];

echo '<table border=1><tr><th>BRAND</th><th>MODEL</th><th>YEAR</th></tr>';
$result1 = mysqli_query($con, $query1);


    while($row1 = mysqli_fetch_array($result1)){
    //echo'<tr><td>'.$row1['brand'].'</td><td>'.$row1['model'].'</td><td>'.$row1['year'].'</td></tr>';

        $query2= 'SELECT DISTINCT brand,model,year, count(*) `number` FROM `carlist` WHERE brand="'.$_POST['brand'].'" GROUP BY `year` HAVING count(*) > 1';
        $result2= mysqli_query($con, $query2);
        $count=0;


        while($row2 = mysqli_fetch_array($result2)){

            if ( $row2['year'] == $row1['year']) {
            $count=1;
            }

            //$intersect = array_intersect($row1,$row2);
            //echo $intersect[1];


        }

        $bgcolor = "#FF8C00"; 
        if($count==1){
            echo '<tr style="background-color: tomato;" ><td>'.$row1['brand'].'</td><td>'.$row1['model'].'</td><td>'.$row1['year'].'</td></tr>';

        }else {

            echo'<tr><td>'.$row1['brand'].'</td><td>'.$row1['model'].'</td><td>'.$row1['year'].'</td></tr>';
        } 

    }


echo '</table>';

Do you have certain colors for years or should colors be automatically generated? — Rene Korss, May 13 '15 at 06:33
Any colors would do, as long it differentiates each year with different colors . — Sasha28, May 13 '15 at 06:34
the problem is all rows are colored the same. The code if should check for duplicate year and color it accordingly. I hope I say it right. I am new to php and mysql — Sasha28, May 13 '15 at 06:37

Logan Wayne · Answer 1 · 2015-05-13T08:52:05.200

Method 1:

This won't need to do a nested loop. This first method will only work if your row is arranged by year.

/* DEFINE THIS VARIABLE FIRST BEFORE YOUR LOOP */
$yearstorage = ""; /* STORAGE FOR YOUR YEAR AND COMPARISON LATER */
$randomcolor = '#' . strtoupper(dechex(rand(256,16777215))); /* GENERATE RANDOM COLOR */

/* START OF YOUR LOOP */
while($row1 = mysqli_fetch_array($result1)){

  $res = mysqli_query($con,"SELECT * FROM carlist WHERE year=".$row1['year']."");
  if(mysqli_num_rows($res) > 1){ /* IF YEAR HAS ONLY ONE ROW */
    $randomcolor = "#ffffff";
  }

  if(empty($yearstorage)){ /* START OF FIRST ROW */
    ?>
      <tr style="background-color: <?php echo $randomcolor ?>;">
    <?php
    $storecolor = $randomcolor;
  }

  else if($yearstorage == $row1["year"]){ /* IF LAST YEAR ROW IS THE SAME AS THE CURRENT YEAR ROW */
    ?>
      <tr style="background-color: <?php echo $storecolor ?>;">
    <?php
  } 

  else { /* IF THE LAST YEAR ROW IS NOT THE SAME WITH THE CURRENT YEAR ROW */
    $randomcolor = '#' . strtoupper(dechex(rand(256,16777215))); /* GENERATE NEW RANDOM COLOR */
    ?>
      <tr style="background-color: <?php echo $randomcolor ?>;">
    <?php
    $storecolor = $randomcolor;
  }

  $yearstorage = $row1["year"]; /* STORE THE CURRENT YEAR FOR COMPARISON ON THE NEXT LOOP */

    ?>
        <td><?php echo $row1['brand']; ?></td>
        <td><?php echo $row1['model']; ?></td>
        <td><?php echo $row1['year']; ?></td>
      </tr>
    <?php

} /* END OF WHILE LOOP */

Method 2:

This second method will work even if your row is not arrange in year. Even if arranged by different column name which I would refer more to you.

You have to create a loop to get all the year distinctly, and store the color and year in an array, and compare and get them on your main loop.

No need to do a nested loop also. But two separated loop.

As you will notice, this code is also shorter than the first method.

If a year contains one row only, it will have a white background.

$counter = 0; /* DETERMINER OF THE COLOR TO BE USED LATER */
$res = mysqli_query($con,"SELECT DISTINCT year FROM carlist");
while($row = mysqli_fetch_array($res)){
  $randomcolor = '#' . strtoupper(dechex(rand(256,16777215))); /* GENERATE NEW RANDOM COLOR */
  /* STORE THE COLOR AND YEAR IN AN ARRAY */

  $res2 = mysqli_query($con,"SELECT * FROM carlist WHERE year = ".$row['year']."");
  if(mysqli_num_rows($res2) > 1){ /* IF YEAR IS USED MORE THAN ONCE */
    $colorstorage[$counter] = $randomcolor;
  }
  else { /* ELSE, IT WILL HAVE A WHITE BACKGROUND */
    $colorstorage[$counter] = "#ffffff";
  }
  $yearstorage[$counter] = $row['year'];
  $counter = $counter + 1; /* INCREMENT COUNTER */
} /* END OF LOOP THAT STORES THE COLOR AND YEAR IN AN ARRAY */

/* START OF YOUR MAIN LOOP */
while($row1 = mysqli_fetch_array($result1)){

  $key = array_search($row1["year"],$yearstorage);
  ?>
    <tr style="background-color: <?php echo $colorstorage[$key]; ?>;">
      <td><?php echo $row1['brand']; ?></td>
      <td><?php echo $row1['model']; ?></td>
      <td><?php echo $row1['year']; ?></td>
    </tr>
  <?php

} /* END OF YOUR MAIN LOOP */

This code able to color the rows, but it also colors the year that occur only once — Sasha28, May 13 '15 at 08:23
@Sasha28 - updated the second method. Try it. Year that occured once won't have a colored background. — Logan Wayne, May 13 '15 at 08:29
@Sasha28 - Both method are now able to color the row white if the year is used once. — Logan Wayne, May 13 '15 at 08:56
@Sasha28 Check the number of line error. And tell me what line that is. — Logan Wayne, May 13 '15 at 12:35

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Create function what generates random color

function getColor($year){
  return '#'.substr(md5($year), 0, 6);
}

In your while loop

// Get color for current year
// Same years will be colored with same color
$bgColor = getColor($row1['year']);

// Print table row
echo '<tr style="background-color: '.$bgColor.';"><td>'.$row1['brand'].'</td><td>'.$row1['model'].'</td><td>'.$row1['year'].'</td></tr>';

So your while loop will be

while($row1 = mysqli_fetch_array($result1)){

    // Delete extra while and if condition

    $bgColor = getColor($row1['year']);
    echo '<tr style="background-color: '.$bgColor.';"><td>'.$row1['brand'].'</td><td>'.$row1['model'].'</td><td>'.$row1['year'].'</td></tr>';
}

NOTE: Your queries are vulnerable to SQL injection. Please see this answer on how to prevent it.

NOTE 2: Since your background colors are generated automatically, it may happen that text is not easily readable.

edited May 23 '17 at 11:43

Community

1
1

answered May 13 '15 at 06:42

Rene Korss

5,414
3
31
38

Erm , this code doesnt work. Plus, i cant delete extra while and if condition, they are to compare the row1 and row2 to find the duplicate year – Sasha28 May 13 '15 at 07:17
Why you have to compare them if `getColor` function gives same color if it's same year? And how it dosen't work? Output is something different or some errors? – Rene Korss May 13 '15 at 07:20
Actually, the code is working but it displays almost similar shades of colors thus hard to read or look for values. And it also highlight a value which don't have duplicate in the tale. Can you set only 30 colors ? And can explain what does "return '#'.substr(md5($year), 0, 6);" do ? – Sasha28 May 13 '15 at 07:48
@Sasha28 - `return` means to return this value in place where you call the function. And for its value, it generates a random hash number, based also on the year number, that would resemble an HTML color. – Logan Wayne May 13 '15 at 07:54
This calculates [md5](http://php.net/md5) hash of `$year` value. And then takes first 6 characters to create color based on `$year` value. This means that every `string` has unique color. Okay. Maybe I misunderstand you. You want to highlight rows by year, what occur more than once? Yeah, it can be little hard to read the values. Another option woulde be to generate `rgba` so it jas `opacity`. Therefore would be much more readable. – Rene Korss May 13 '15 at 07:58
@ReneKorss yes yes only want to highlight rows by year which occurs more than once. how do you do that with this code ? – Sasha28 May 13 '15 at 08:13
@ReneKorss can you make the table to group the same year together and display them in the table without coloring them ? – Sasha28 May 14 '15 at 02:20
@Sasha28 What do you mean by grouping? You could order by `year` and all same year items will be together. – Rene Korss May 22 '15 at 06:16

Highlight table row in different colors based on values from database in php

2 Answers2

Method 1:

Method 2: