iT邦幫忙

0

PHP網頁資料如何相同資料改成相同背景顏色

我想做到A欄位相同的值TR的背景色相同
舉下面為例
表格會變






我TABLE目前是類似下面

AAA BBB CCC DDD
001 123 456 789
001 456 789 123
002 123 456 123
003 456 123 123
003 123 123 123
003 789 789 789

上網找了很多但都是Excel的/images/emoticon/emoticon34.gif

<?php
 $name=$_REQUEST["name"];
 $dbhost = '';   
 $dbuser = '';   
 $dbpass = '';   
 $dbname = '';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL');	
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
	echo '
		<div id="test" class="www"><table border="1" width="1000" align="center">
			<thead><tr>
			<th>交易編號</th>
			<th>使用者帳號</th>
			<th>日期</th>
			<th>時間</th>
			<th>使用優惠</th>
			<th>餐點</th>
			<th>數量</th>
			<th>單價</th>
			<th>折扣金額</th>
			<th>折扣後金額</th>
			<th>訂單總額</th>
			</tr></thead>';  
  $sqi = "CREATE TEMPORARY TABLE tmpnav TYPE=HEAP AS SELECT nReservedata.Odnumber AS Odnumber,
		  nReservedata.UAccount AS UAccount,
		  nReservedata.tmealday AS tmealday,
		  nReservedata.tmealtime AS tmealtime,
		  SpecialOffersdata.OfferDetail AS OfferDetail,
		  Menudata.MealName AS MealName,
		  nReservedata.amount AS amount,
          Menudata.Price AS MPrice,
		  amount * Menudata.Price AS income,
		  nReservedata.Price AS Price,
		  nReservedata.Total AS Total
FROM nReservedata 
INNER JOIN Menudata
ON nReservedata.MealNumber=Menudata.MealNumber
INNER JOIN SpecialOffersdata
ON nReservedata.OfferID=SpecialOffersdata.OfferID
WHERE nReservedata.SNumber = '$number' ORDER BY `nReservedata`.`Odnumber` ASC";
  $sql1 ="SELECT 	Odnumber,
						UAccount,
						tmealday,
						tmealtime,
						OfferDetail,
						MealName,
						amount,
						income,
						Price,
						Total,
						MPrice
						FROM tmpnav
						ORDER BY Odnumber ASC";
		$result=mysql_query($sqi);
		$result=mysql_query($sql1);
  while($row = mysql_fetch_array($result))
  {
   	
	$Odnumber = $row[0];
	$UAccount = $row[1];
	$tmealday = $row[2];
	$tmealtime = $row[3];
	$OfferID = $row[4];
	$MealNumber = $row[5];
	$amount = $row[6];
	$income = $row[7];
	$Price = $row[8];
	$Total = $row[9];
	$MPrice = $row[10];
	$discount = $income - $Price;
	$newMPrice = number_format($MPrice);
	$newdiscount = number_format($discount);
	$newPrice = number_format($Price);
	$newTotal = number_format($Total);
		echo "<tr>";
		echo "<td>$Odnumber</td>";
		echo "<td>$UAccount</td>";
		echo "<td>$tmealday</td>";
		echo "<td>$tmealtime</td>";
		echo "<td>$OfferID</td>";
		echo "<td>$MealNumber</td>";
		echo "<td>$amount</td>";
		echo "<td>$newMPrice</td>";
		echo "<td>$newdiscount</td>";
		echo "<td>$newPrice</td>";
		echo "<td>$newTotal</td>";
   }
  		echo   '</tr></table></div>';
?>
看更多先前的討論...收起先前的討論...
小魚 iT邦高手 1 級 ‧ 2019-03-17 09:27:26 檢舉
可能要用style吧
先放上你的php code 吧,不然是要通靈之術嗎~
scjh8214 iT邦新手 5 級 ‧ 2019-03-17 17:32:13 檢舉
補上了
還是改用PDO吧,這年頭還有人在用MYSQL 物件撈資料?還有html不要全包在echo裡,這code還真是亂七八糟的
0
rogeryao
iT邦研究生 3 級 ‧ 2019-03-18 12:19:47
最佳解答

由 MySQL 產生虛擬欄位 ItemNo 流水號, 再由 PHP 判斷 ItemNo 為奇數或偶數顯示不同底色
MySQL version 8.0

// 新增 ItemNo 欄位
$sql1 ="SELECT 	
DENSE_RANK() OVER (ORDER BY Odnumber ASC) AS ItemNo,
                        Odnumber,
						UAccount,
						tmealday,
						tmealtime,
						OfferDetail,
						MealName,
						amount,
						income,
						Price,
						Total,
						MPrice
						FROM tmpnav
						ORDER BY Odnumber ASC";
		$result=mysql_query($sqi);
		$result=mysql_query($sql1);
  while($row = mysql_fetch_array($result))
  {
    // 新增 ItemNo 欄位 , 調整順位
   	$ItemNo=$row[0];
	$Odnumber = $row[1];
	$UAccount = $row[2];
	$tmealday = $row[3];
	$tmealtime = $row[4];
	$OfferID = $row[5];
	$MealNumber = $row[6];
	$amount = $row[7];
	$income = $row[8];
	$Price = $row[9];
	$Total = $row[10];
	$MPrice = $row[11];
	$discount = $income - $Price;
	$newMPrice = number_format($MPrice);
	$newdiscount = number_format($discount);
	$newPrice = number_format($Price);
	$newTotal = number_format($Total);
		// 改為下式
        if ($ItemNo %2==0)
        {
          $bgcolor1="#00FFFF";
        }
        else
        {
          $bgcolor1="#00FF00";
        }             
        echo "<tr bgcolor=".$bgcolor1." >";    
        //
		echo "<td>$Odnumber</td>";
		echo "<td>$UAccount</td>";
		echo "<td>$tmealday</td>";
		echo "<td>$tmealtime</td>";
		echo "<td>$OfferID</td>";
		echo "<td>$MealNumber</td>";
		echo "<td>$amount</td>";
		echo "<td>$newMPrice</td>";
		echo "<td>$newdiscount</td>";
		echo "<td>$newPrice</td>";
		echo "<td>$newTotal</td>";
   }

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3d476561316ebfd5f1c901ac6f647c7b

MySQL 其它版本 SQL

select X.ItemNo,Y.Odnumber,Y.UAccount
from (
select count(*) as ItemNo,A.Odnumber
from (select distinct Odnumber
from tmpnav) as A,(select distinct Odnumber
from tmpnav) as B
where A.Odnumber>=B.Odnumber
group by A.Odnumber
order by A.Odnumber
) as X
left join (
select Odnumber,UAccount
from tmpnav
) as Y on Y.Odnumber=X.Odnumber
where 1=1

http://sqlfiddle.com/#!9/1b5888/1

結果如圖 :https://ithelp.ithome.com.tw/upload/images/20190319/20085021LHJTFGpxKo.png

排序法確實也是一招。只是也不確定他是否可以接受排序。

scjh8214 iT邦新手 5 級 ‧ 2019-03-20 20:37:23 檢舉

成功了~

0
純真的人
iT邦高手 1 級 ‧ 2019-03-17 07:34:38

在tr的位置設定css的背景色就可以了~

看更多先前的回應...收起先前的回應...
scjh8214 iT邦新手 5 級 ‧ 2019-03-17 14:04:29 檢舉

該怎麼判斷是相同資料呢??

scjh8214 iT邦新手 5 級 ‧ 2019-03-17 15:00:04 檢舉

我把我的問題寫得更詳細了/images/emoticon/emoticon56.gif

就你在MySql查詢資料的時候,多一個子查詢~
如果重複就回傳1
不重複就是0
那你在程式列出的時候,資料有1的都灰色就知道了~

scjh8214 iT邦新手 5 級 ‧ 2019-03-17 18:36:02 檢舉

看網路上有關子查詢的還是有點不懂/images/emoticon/emoticon02.gif
能不能示範一下/images/emoticon/emoticon41.gif

這個你若是以使用者帳號重複來顯示的話~
SQL應該是這樣~

SELECT 	Odnumber,
UAccount,
tmealday,
tmealtime,
OfferDetail,
MealName,
amount,
income,
Price,
Total,
MPrice,
(select count(*) from tmpnav as b where a.UAccount = b.UAccount) as Num
FROM tmpnav as a
ORDER BY Odnumber ASC

就可以判斷Num欄位的資料,是否大於1
大於1就是重複~剛好1就不是重複~
當然~我不知道你實際資料的重複是以什麼作判斷標準..

scjh8214 iT邦新手 5 級 ‧ 2019-03-17 19:18:20 檢舉

我是用Odnumber
但我跑出來結果顯示Error

SELECT 	Odnumber,
UAccount,
tmealday,
tmealtime,
OfferDetail,
MealName,
amount,
income,
Price,
Total,
MPrice,
(select count(*) from tmpnav as b where a.Odnumber = b.Odnumber) as Num
FROM tmpnav as a
ORDER BY Odnumber ASC

應該是成php程式錯誤吧...?
你檢查你帶出來的語法是否錯誤?
我用線上資料庫測試~沒有問題..

http://sqlfiddle.com/#!9/161e1b/1

https://ithelp.ithome.com.tw/upload/images/20190317/200613694iS0uBYfhA.png

scjh8214 iT邦新手 5 級 ‧ 2019-03-17 21:52:34 檢舉

我執行後
只顯示出 Can't reopen table: 'a'

<?php
 $dbhost = '';   
 $dbuser = '';   
 $dbpass = '';   
 $dbname = '';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL');
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
	echo '
		<div id="test" class="www"><table border="1" width="1000" align="center">
			<thead><tr>
			<th>交易編號</th>
			<th>使用者帳號</th>
			<th>日期</th>
			<th>時間</th>
			<th>使用優惠</th>
			<th>餐點</th>
			<th>數量</th>
			<th>單價</th>
			<th>折扣金額</th>
			<th>折扣後金額</th>
			<th>訂單總額</th>
			<th>NUM</th>
			</tr></thead>';  
  $sqi = "CREATE TEMPORARY TABLE tmpnav TYPE=HEAP AS SELECT nReservedata.Odnumber AS Odnumber,
		  nReservedata.UAccount AS UAccount,
		  nReservedata.tmealday AS tmealday,
		  nReservedata.tmealtime AS tmealtime,
		  SpecialOffersdata.OfferDetail AS OfferDetail,
		  Menudata.MealName AS MealName,
		  nReservedata.amount AS amount,
          Menudata.Price AS MPrice,
		  amount * Menudata.Price AS income,
		  nReservedata.Price AS Price,
		  nReservedata.Total AS Total
FROM nReservedata 
INNER JOIN Menudata
ON nReservedata.MealNumber=Menudata.MealNumber
INNER JOIN SpecialOffersdata
ON nReservedata.OfferID=SpecialOffersdata.OfferID
WHERE nReservedata.SNumber = 'A001' ORDER BY `nReservedata`.`Odnumber` ASC";
  $sql1 ="SELECT 	Odnumber,
UAccount,
tmealday,
tmealtime,
OfferDetail,
MealName,
amount,
income,
Price,
Total,
MPrice,
(select count(*) from tmpnav as b where a.Odnumber = b.Odnumber) as Num
FROM tmpnav as a
ORDER BY Odnumber ASC";
		$result=mysql_query($sqi);
		$result=mysql_query($sql1);
  while($row = mysql_fetch_array($result))
  {
   	
	$Odnumber = $row[0];
	$UAccount = $row[1];
	$tmealday = $row[2];
	$tmealtime = $row[3];
	$OfferID = $row[4];
	$MealNumber = $row[5];
	$amount = $row[6];
	$income = $row[7];
	$Price = $row[8];
	$Total = $row[9];
	$MPrice = $row[10];
	$num=$row[11];
	$discount = $income - $Price;
	$newMPrice = number_format($MPrice);
	$newdiscount = number_format($discount);
	$newPrice = number_format($Price);
	$newTotal = number_format($Total);
		echo "<tr>";
		echo "<td>$Odnumber</td>";
		echo "<td>$UAccount</td>";
		echo "<td>$tmealday</td>";
		echo "<td>$tmealtime</td>";
		echo "<td>$OfferID</td>";
		echo "<td>$MealNumber</td>";
		echo "<td>$amount</td>";
		echo "<td>$newMPrice</td>";
		echo "<td>$newdiscount</td>";
		echo "<td>$newPrice</td>";
		echo "<td>$newTotal</td>";
		echo "<td>$num</td>";
   }
  		echo   '</tr></table></div>';
		echo mysql_error();
?>
scjh8214 iT邦新手 5 級 ‧ 2019-03-17 22:22:38 檢舉

我成功顯示出Num但有的比數正確有的是錯的
我有附圖片
只有前面三筆是Odnumber有重複的
其他都是只有一筆資料

<?php
 $dbhost = '';   
 $dbuser = '';   
 $dbpass = '';   
 $dbname = '';   
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL');
  mysql_query("SET NAMES 'utf8'");
  mysql_select_db($dbname);
	echo '
		<div id="test" class="www"><table border="1" width="1000" align="center">
			<thead><tr>
			<th>交易編號</th>
			<th>使用者帳號</th>
			<th>日期</th>
			<th>時間</th>
			<th>使用優惠</th>
			<th>餐點</th>
			<th>數量</th>
			<th>單價</th>
			<th>折扣金額</th>
			<th>折扣後金額</th>
			<th>訂單總額</th>
			<th>NUM</th>
			</tr></thead>';  
  $sqi = "CREATE TEMPORARY TABLE tmpnav TYPE=HEAP AS SELECT a.Odnumber AS Odnumber,
		  a.UAccount AS UAccount,
		  a.tmealday AS tmealday,
		  a.tmealtime AS tmealtime,
		  SpecialOffersdata.OfferDetail AS OfferDetail,
		  Menudata.MealName AS MealName,
		  a.amount AS amount,
          Menudata.Price AS MPrice,
		  amount * Menudata.Price AS income,
		  a.Price AS Price,
		  a.Total AS Total,(select count(*) from nReservedata as b where a.Odnumber = b.Odnumber) as Num
FROM nReservedata AS a
INNER JOIN Menudata
ON a.MealNumber=Menudata.MealNumber
INNER JOIN SpecialOffersdata
ON a.OfferID=SpecialOffersdata.OfferID
WHERE a.SNumber = 'A001' ORDER BY `a`.`Odnumber` ASC";
  $sql1 ="SELECT 	Odnumber,
						UAccount,
						tmealday,
						tmealtime,
						OfferDetail,
						MealName,
						amount,
						income,
						Price,
						Total,
						MPrice,
						Num
						FROM tmpnav
						ORDER BY Odnumber ASC";
		$result=mysql_query($sqi);
		$result=mysql_query($sql1);
  while($row = mysql_fetch_array($result))
  {
   	
	$Odnumber = $row[0];
	$UAccount = $row[1];
	$tmealday = $row[2];
	$tmealtime = $row[3];
	$OfferID = $row[4];
	$MealNumber = $row[5];
	$amount = $row[6];
	$income = $row[7];
	$Price = $row[8];
	$Total = $row[9];
	$MPrice = $row[10];
	$num=$row[11];
	$discount = $income - $Price;
	$newMPrice = number_format($MPrice);
	$newdiscount = number_format($discount);
	$newPrice = number_format($Price);
	$newTotal = number_format($Total);
		echo "<tr>";
		echo "<td>$Odnumber</td>";
		echo "<td>$UAccount</td>";
		echo "<td>$tmealday</td>";
		echo "<td>$tmealtime</td>";
		echo "<td>$OfferID</td>";
		echo "<td>$MealNumber</td>";
		echo "<td>$amount</td>";
		echo "<td>$newMPrice</td>";
		echo "<td>$newdiscount</td>";
		echo "<td>$newPrice</td>";
		echo "<td>$newTotal</td>";
		echo "<td>$num</td>";
   }
  		echo   '</tr></table></div>';
		echo mysql_error();
?>

https://ithelp.ithome.com.tw/upload/images/20190317/20114578ELSWL5EYgl.jpg

因為你的這句只有比對一個資料表呀~(nReservedata)

select count(*) from nReservedata as b where a.Odnumber = b.Odnumber

不像底下還有條件篩選掉不顯示的~

FROM nReservedata AS a
INNER JOIN Menudata
ON a.MealNumber=Menudata.MealNumber
INNER JOIN SpecialOffersdata
ON a.OfferID=SpecialOffersdata.OfferID
WHERE a.SNumber = 'A001'

所以你的子查詢應該也要比照辦理..

select count(*) from
FROM nReservedata AS b
INNER JOIN Menudata
ON b.MealNumber=Menudata.MealNumber
INNER JOIN SpecialOffersdata
ON b.OfferID=SpecialOffersdata.OfferID
WHERE b.SNumber = 'A001'
and a.Odnumber = b.Odnumber
scjh8214 iT邦新手 5 級 ‧ 2019-03-18 20:45:03 檢舉

後來我有用出來但會有重複顏色的問題
假如資料表是連續的重複Odnumber
顏色就會是相同的

你可以多設幾個背景顏色應變呢~
假設有10個背景顏色就輪流顯示應該就夠了~

scjh8214 iT邦新手 5 級 ‧ 2019-03-19 01:29:13 檢舉

如果我沒有理解錯的話
那假如有兩個不同編號的NUM都剛好是3
要怎麼做顏色應變呢?
要怎麼做輪流顯示呢?/images/emoticon/emoticon19.gif

你可以設定一個i變數
每次有訂單重複i就加1(當有不同訂單編號才i+1)
如果i超過10的時候~就取餘數~
也就是i % 10 範圍0~9在輪流取色囉~

1
浩瀚星空
iT邦高手 1 級 ‧ 2019-03-18 11:59:18

這邊告訴你我以前用的實際案例。我取名叫做分色法。
運用md5的特殊處理來做分段區分。

首先,先決定最多多少顏色分色。
我之前是只設定了10色。輪替使用。
這邊我先用三色。你可以自行增加要替換的顏色。
然後將參數先行做初始化處理。
要放到回圈之前。

$p_color=Array('#000','#111','#222');//決定好要分色的色碼,可自行增加
$max_color_num = count($p_color); //這是取得你給的色碼數量初始化
$set_md5_color = Array();    //依md5區分對應色碼初始化
$color_index = 0;            //設定啟始色碼索引對應

然後之後再將你其下獲得的資料
將你這些程式碼

    $Odnumber = $row[0];
	$UAccount = $row[1];
	$tmealday = $row[2];
	$tmealtime = $row[3];
	$OfferID = $row[4];
	$MealNumber = $row[5];
	$amount = $row[6];
	$income = $row[7];
	$Price = $row[8];
	$Total = $row[9];
	$MPrice = $row[10];
	$num=$row[11];
	$discount = $income - $Price;
	$newMPrice = number_format($MPrice);
	$newdiscount = number_format($discount);
	$newPrice = number_format($Price);
	$newTotal = number_format($Total);

在其下增加如下的程式
這邊先假設只抓Price跟Total為資料依據。
另用陣列來記錄。(你也可以只用你想要區分的記錄做陣列值就好。)
這是為了後續做md5處理方便使用

    $data = Array();
    $data['Price'] = $Price;
	$data['Total'] = $Total;	

最後在你要輸出的

echo "<tr>";

改成如下的程式碼對應

$_md5_data = md5(json_encode($data));//這就是將資料md5化處理。這是在確保資料唯一性處理
if(!isset($set_md5_color[$_md5_data])){
   $set_md5_color[$_md5_data] = $color_index;//給與現在的分色代碼
   $color_index++;//分色代碼推下一個
   if($color_index >= $max_color_num){//這邊處理當超過可分色代碼時,重新來。
       $color_index = 0;
   } 
}
$tr_color = $p_color[$set_md5_color[$_md5_data]];//取得現在的分色
echo "<tr style='background-color:". $tr_color."'>";

照以上的方式條改的話。你的程式會依據Price及Total的不同。會有其不同的顏色。
不過色碼不多的情況下,很可能會有多重資料同色的情況。所以想要完全區分的話。最好色碼設定多一些。

看更多先前的回應...收起先前的回應...
scjh8214 iT邦新手 5 級 ‧ 2019-03-18 20:46:51 檢舉

我依照你的方法但背景全部顯示空白的/images/emoticon/emoticon02.gif

  while($row = mysql_fetch_array($result))
  {
   	$data = Array();
	$data['Odnumber'] = $row[0];
	$data['UAccount'] = $row[1];
	$data['tmealday'] = $row[2];
	$data['tmealtime'] = $row[3];
	$data['OfferID'] = $row[4];
	$data['MealNumber'] = $row[5];
	$data['amount'] = $row[6];
	$data['income'] = $row[7];
	$data['Price'] = $row[8];
	$data['Total'] = $row[9];
	$data['MPrice'] = $row[10];
	$data['discount'] = $row[7] - $row[8];
	$data['newMPrice'] = number_format($row[10]);
	$data['newdiscount'] = number_format($row[7] - $row[8]);
	$data['newPrice'] = number_format($row[8]);
	$data['newTotal'] = number_format($row[9]);
	$p_color=Array('#ffffff','#ccff99','#66ffff');
	$max_color_num = count($p_color);
	$set_md5_color = Array();    //依md5區分對應色碼。預設值先是空的
	$color_index = 0;            //設定啟始色碼對應值

		$_md5_data = md5(json_encode($data));//這就是將資料md5化處理。這是在確保資料唯一性處理
		if(!isset($set_md5_color[$_md5_data]))
			{
			   $set_md5_color[$_md5_data] = $color_index;//給與現在的分色代碼
			   $color_index++;//分色代碼推下一個
				   if($color_index > $max_color_num)
					{//這邊處理當超過可分色代碼時,重新來。
					   $color_index = 0;
					} 
			}
		$tr_color = $p_color[$set_md5_color[$_md5_data]];//取得現在的分色
		echo "<tr style='background-color:". $tr_color."'>";
		echo "<td>".$data['Odnumber']."</td>";
		echo "<td>".$data['UAccount']."</td>";
		echo "<td>".$data['tmealday']."</td>";
		echo "<td>".$data['tmealtime']."</td>";
		echo "<td>".$data['OfferID']."</td>";
		echo "<td>".$data['MealNumber']."</td>";
		echo "<td>".$data['amount']."</td>";
		echo "<td>".$data['newMPrice']."</td>";
		echo "<td>".$data['newdiscount']."</td>";
		echo "<td>".$data['newPrice']."</td>";
		echo "<td>".$data['newTotal']."</td>";
   }
  		echo   '</tr></table></div>';

汗!!!!

$p_color=Array('#ffffff','#ccff99','#66ffff');
	$max_color_num = count($p_color);
	$set_md5_color = Array();    //依md5區分對應色碼。預設值先是空的
	$color_index = 0;            //設定啟始色碼對應值

這段是得要放到一開頭的地方。
我不是寫的很清楚了。
你放在迴圈運行不是沒用處了。
一直被初始化有用嗎??
難怪只會取到白色的。

我講的明白一點好了。

$p_color=Array('#ffffff','#ccff99','#66ffff');
$max_color_num = count($p_color);
$set_md5_color = Array();    //依md5區分對應色碼。預設值先是空的
$color_index = 0;            //設定啟始色碼對應值

這一段的程式碼用意叫做「設定初始化處理」。
所以它一定要放在你的while之前的任何地方才行。
你不能放到while裏面讓他跑。因為這樣就會一直被初始化。
你就一直得不到任何值。

我只是給你一個範例點,你要懂得去理解其程式的用處。不要只會照copy。還copy錯地方了。
這會讓人很無奈

這邊其實我有注意到一件事。你將資料全部都做陣列化了。
我想可能我上面沒有說的很清楚。
將資料陣列化的目前是為了讓你做資料重覆性的判斷依據。
所以你只要將需要判斷的資料做陣列化就行了。

如果你連訂單序號也列入了唯一性資料的判斷依據。那不就所有筆數都是不一樣的數據了?

等等我再將我上面的說明再寫清楚一些好了。你再重看一次學習吧。

我要發表回答

立即登入回答