如何在mysql表中添加最后一列 [英] How to Add last column in mysql table

问题描述

这是我的脚本代码

$sql ="SELECT     t.Contact_Email,
                  e1.Contact_Email email1,
                  e2.Contact_Email email2,
                  e3.Contact_Email email3,
                  e4.Contact_Email email4
         FROM     (
                  SELECT e.Contact_Email FROM email1 e
                  UNION ALL 
                  SELECT e.Contact_Email FROM email2 e
                  UNION ALL 
                  SELECT e.Contact_Email FROM email3 e
                  UNION ALL 
                  SELECT e.Contact_Email FROM email4 e
                  ) t
        LEFT JOIN email1 e1
              ON  t.Contact_Email = e1.Contact_Email
        LEFT JOIN email2 e2
              ON  t.Contact_Email = e2.Contact_Email
        LEFT JOIN email3 e3
              ON  t.Contact_Email = e3.Contact_Email
        LEFT JOIN email4 e4
              ON  t.Contact_Email = e4.Contact_Email";

echo '<table><thead><tr><th>Email</th>';
for ($i=1;$i<5; $i++){
    echo "<th>email $i</th>";
}
echo '</tr></thead><tbody>';

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    echo '<tr><td>'.$row['Contact_Email'].'</td>';
    for ($i=1;$i<5; $i++){
        echo '<td>'.(empty($row['email'.$i])?'no':'yes').'</td>';
    }
    echo '</tr>';
}
echo '</tbody></table>';

输出为:

该表的奇数列表示打开"动作，其偶数列表示单击"动作. 现在，我想在最右边添加一个总计"列.

The table's odd columns represent "open" actions, and its even columns represent "click" actions. Now I want to add a 'Total' column at the far right.

因此在图片中，标题为电子邮件1，电子邮件3，电子邮件5 ... ，这些列表示打开"操作，并在其上显示 电子邮件2，电子邮件4，电子邮件6 ... ，它代表点击操作.

So in picture, where the header says email 1, email 3, email 5... the columns represent "open" actions and where it says email 2, email 4, email 6... it represents click actions.

现在，总计应计算如下:

Now, the total should be calculated as follows:

未完成操作值得2分，而点击值得5分.因此，如果打开操作有2个是"值，而有3个是"值则是 点击操作，则总数应为 2x2 + 3x5 = 19 .

Open actions are worth 2 points, and clicks are worth 5 points. So if there are 2 "yes" values for open actions and 3 "yes" values for click actions, then the total should be 2x2 + 3x5 = 19.

这是我想要的输出.我该怎么办?

That is the output I would like to have. How can I do this?

推荐答案

给出您发布的代码，这就是我的处理方式.

Given the code you've posted, here's how I'd handle this.

首先，我将创建一个关联的查找数组，其键是列名，其值是对应的点值；看起来像这样:

First, I'd create an associative lookup array whose keys are the column names and whose values are the corresponding point values; it would look something like this:

$pointVals = array('email1' => 2, 'email2' => 5, 'email3' => 2, ... );

您可以根据需要生成此数组；我可能会从数据库中的查找表中读取它.请注意，此数组仅包含与计算相关的列.

You can generate this array however you wish; I'd probably read it from a lookup table in the database. Note that this array contains only the columns for which the calculation is relevant.

接下来，我将在while循环中的内部 中包含以下代码. $row是您对mysql_fetch_array()的调用返回的记录数组.

Next, I would include the following code inside your while loop. $row is the record array returned by your call to mysql_fetch_array().

while ($row = mysql_fetch_array($result)) {
  ... // existing code goes here
  $rowSum = 0;
  foreach($pointVals as $colName => $val)
  {
    if(isset($row[$colName]) && !empty($row[$colName]))
      $rowSum += $val;
  }
  ... // do stuff with $rowSum here
}

话虽如此，我强烈建议您将 mysql 驱动程序转换为 mysqli 或 PDO ，以 mysql 已被弃用，并已从PHP 7中删除.此外，我强烈怀疑您的查询可以进行简化和改进，但是我需要查看基表以提出任何建议.

Having said that, I would strongly encourage you to convert from the mysql driver to mysqli or PDO, as mysql is deprecated and has been removed from PHP 7. Also, I strongly suspect your query could be simplified and improved, but I'd need to see the base table to suggest anything.

这篇关于如何在mysql表中添加最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！