mikeyk Posted April 8, 2011 Share Posted April 8, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/ Share on other sites More sharing options...
Adam Posted April 8, 2011 Share Posted April 8, 2011 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'; } // ... } Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1198662 Share on other sites More sharing options...
nethnet Posted April 8, 2011 Share Posted April 8, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1198667 Share on other sites More sharing options...
mikeyk Posted April 8, 2011 Author Share Posted April 8, 2011 Wow, that was fast! Got it working, thank you both very very much. And to nethnet: You guessed it pretty close Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1198875 Share on other sites More sharing options...
mikeyk Posted April 9, 2011 Author Share Posted April 9, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199196 Share on other sites More sharing options...
ted_chou12 Posted April 9, 2011 Share Posted April 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199200 Share on other sites More sharing options...
mikeyk Posted April 9, 2011 Author Share Posted April 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199202 Share on other sites More sharing options...
ted_chou12 Posted April 9, 2011 Share Posted April 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199204 Share on other sites More sharing options...
mikeyk Posted April 9, 2011 Author Share Posted April 9, 2011 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 .... Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199214 Share on other sites More sharing options...
ted_chou12 Posted April 9, 2011 Share Posted April 9, 2011 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?" Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199225 Share on other sites More sharing options...
mikeyk Posted April 9, 2011 Author Share Posted April 9, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199230 Share on other sites More sharing options...
ted_chou12 Posted April 9, 2011 Share Posted April 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199236 Share on other sites More sharing options...
nethnet Posted April 9, 2011 Share Posted April 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1199286 Share on other sites More sharing options...
mikeyk Posted April 12, 2011 Author Share Posted April 12, 2011 Try this: Exactly what I was looking for. Thanks - again! Quote Link to comment https://forums.phpfreaks.com/topic/233071-comparing-row-values/#findComment-1200652 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.