Comparing row values

[mikeyk]

Hi. I have a MySQL database with 4 different fields. id, customer, case and status.

 

No problems of getting them to show up on my page but.. I would like to change the row bgcolor depending of field value on "customer". For example row color would be white until customer changes, then color yellow until customer changes again and back to white etc.. Hope you get the point.

 

Is this possible? Thanks.

[Adam]

You should store the previous customer in a variable and update it on each iteration if it's different. That will allow you to check when to change the colour. If you only have two colours in your cycle, you can just a simple IF statement:

 

$prev_customer = '';
$current_color = '';

while ($row = mysql_fetch_assoc($query))
{
    if ($prev_customer != $row['customer'])
    {
        $prev_customer = $row['customer'];
        $current_color = ($current_color == '#FFFFFF') ? '#FFFF00' : '#FFFFFF';
    }

    // ...
}

[nethnet]

If I understand correctly, your data might look something like the following:

 

+----+----------+------+--------+
| id | customer | case | status |
+----+----------+------+--------+
|  1 |  Mollie  |  c1  | Active |
|  2 |  Mollie  |  c2  | Closed |
|  3 |   Drew   |  c3  | Active |
|  4 |   Drew   |  c1  | Active |
|  5 |   Drew   |  c4  |  Pend  |
|  6 |    Ed    |  c2  | Active |
|  7 |   Sara   |  c1  | Closed |
+----+----------+------+--------+

 

I have no idea what `case` and `status` are, so I just gave them values for how I might picture them.  Correct me if I'm wrong but what you want to do is print out each record, and alternate the bgcolor of the rows for each NEW customer, and NOT for each new row.. right?  If so...

 

<?php

// Some variables to keep track of the bgcolor
$ticker = 0;
$lastcustomer = "";

// Begin the table
echo "<table>";

$query = ("SELECT * FROM `table` ORDER BY `customer`");
while ($row = mysql_fetch_assoc($query)) {
    if ($row['customer'] != $lastcustomer)
        $ticker++;
    $bgcolor = ($ticker % 2 == 1) ? "#FFFFFF" : "#CCCCCC";
    echo "<tr>";
    foreach ($row as $value)
        echo "<td style=\"background-color: $bgcolor; \">$value</td>";
    echo "</tr>";
    $lastcustomer = $row['customer'];
}

// End the table
echo "</table>";

?>

 

This is untested, so I may have forgotten a semicolon or something.

 

Edit: Looks like I was beaten to the punch.

[mikeyk]

Wow, that was fast! Got it working, thank you both very very much.

 

And to nethnet: You guessed it pretty close

 

Thanks!

[mikeyk]

Hi again. Now that the colors are working I thought of taking this a bit further. Same idea, customer matters.

 

This is what I'm after:

 

+----+----------+------+--------+
| # | customer | case | status |
+----+----------+------+--------+
|  1 |  Mollie  |  c1  | Active |
|  2 |  Mollie  |  c2  | Closed |
|  1 |   Drew   |  c3  | Active |
|  2 |   Drew   |  c1  | Active |
|  3 |   Drew   |  c4  |  Pend  |
|  1 |    Ed    |  c2  | Active |
|  1 |   Sara   |  c1  | Closed |
+----+----------+------+--------+

 

So first field would count numbers ascending order by customer. Been trying to solve this for past 3 hours with no luck.. Any ideas? Thanks in advance.

 

[ted_chou12]

So first field would count numbers ascending order by customer. Been trying to solve this for past 3 hours with no luck.. Any ideas? Thanks in advance.

Are you ordering by NUMBER or by CUSTOMER?

 

**http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

[mikeyk]

So first field would count numbers ascending order by customer. Been trying to solve this for past 3 hours with no luck.. Any ideas? Thanks in advance.

Are you ordering by NUMBER or by CUSTOMER?

 

By customer because number isn't a field in database. I maybe expressed myself badly. What I'm trying to achieve is to count 1,2,3,4.... for each entry by same customer. And to do that automatically so if I add/delete entry it shows right. Got the pointT

[ted_chou12]

I am not very sure, try this if i interpretted it right:

<?php

// Some variables to keep track of the bgcolor
$ticker = 0;
$lastcustomer = "";

// Begin the table
echo "<table>";

$query = ("SELECT * FROM `table` ORDER BY customer, id");
while ($row = mysql_fetch_assoc($query)) {
    if ($row['customer'] != $lastcustomer)
        $ticker++;
    $bgcolor = ($ticker % 2 == 1) ? "#FFFFFF" : "#CCCCCC";
    echo "<tr>";
    foreach ($row as $value)
        echo "<td style=\"background-color: $bgcolor; \">$value</td>";
    echo "</tr>";
    $lastcustomer = $row['customer'];
}

// End the table
echo "</table>";

?>

This sorts the customer name before the id, so entries of the same customer will appear NEXT TO each other.

Make sure id is the actual col name that you call it though. (eg order or number wtv)

Ted

[mikeyk]

Thanks for your reply. But I'm not sure how I could achieve my goal with that.. The problem isn't ordering the rows, problem is counting the rows by customer. So that every customer's first row is #1, second #2 and so on.. If that can be done by your code then I just can't figure it out...

 

#1 Customer1

#2 Customer1

#3 Customer1

#1 Customer2

#1 Customer3

#2 Customer3

....

[ted_chou12]

Thanks for your reply. But I'm not sure how I could achieve my goal with that.. The problem isn't ordering the rows, problem is counting the rows by customer. So that every customer's first row is #1, second #2 and so on.. If that can be done by your code then I just can't figure it out...

 

#1 Customer1

#2 Customer1

#3 Customer1

#1 Customer2

#1 Customer3

#2 Customer3

....

So you want a number as a output?

Eg "Customer 1 has 5 entries?"

[mikeyk]

Yes, I want a number as a output. But not like total number of entries by customer. More like counting each row asc order if the customer is same. So that first record of Customer1 would show up #1 and sixth record #6 and 2,3,4,5 between those.

[ted_chou12]

Yes, I want a number as a output. But not like total number of entries by customer. More like counting each row asc order if the customer is same. So that first record of Customer1 would show up #1 and sixth record #6 and 2,3,4,5 between those.

Cool!

$query = ("SELECT * FROM `table` WHERE customer='customer1' ORDER BY id'");
while ($row = mysql_fetch_assoc($query)) {
    if ($row['customer'] != $lastcustomer)
        $ticker++;
    $bgcolor = ($ticker % 2 == 1) ? "#FFFFFF" : "#CCCCCC";
    echo "<tr>";
    foreach ($row as $value)
        echo "<td style=\"background-color: $bgcolor; \">$value</td>";
    echo "</tr>";
    $lastcustomer = $row['customer'];
}

This will only show up the entries for customer 1

Ted

[nethnet]

Try this:

 

<?php

// Some variables to keep track of the bgcolor
$ticker = 0;
$lastcustomer = "";

// Begin the table
echo "<table>";

$query = ("SELECT * FROM `table` ORDER BY `customer`, `id`");
while ($row = mysql_fetch_assoc($query)) {
    if ($row['customer'] != $lastcustomer) {
        $ticker++;
        $counter = 1;
    }
    $bgcolor = ($ticker % 2 == 1) ? "#FFFFFF" : "#CCCCCC";
    echo "<tr>";
    foreach ($row as $key => $value)
        if ($key == "id")
            $value = $counter;
        echo "<td style=\"background-color: $bgcolor; \">$value</td>";
    echo "</tr>";
    $lastcustomer = $row['customer'];
    $counter++;
}

// End the table
echo "</table>";

?>

 

Change `table` to your table name, obviously

[mikeyk]

Try this:

 

Exactly what I was looking for.  Thanks - again!