MySQL "AS“字符串不能输出到TXT文件

Question

我有一个PHP脚本，查询的是表中每个用户的特定行数。然后，我让SQL查询使用"AS“来输出每个用户各自的计数；如下所示：

<?php
$servername = "localhost";
$username = "name";
$password = "mypassword";
$dbname = "mydatabase";

//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//Check connection
if($conn->connect_error) {
        die("Connection failed:" . $conn->connect_error);
}

$sql = "SELECT SUM(answer LIKE '%Author: Charlotte Augustine%') AS Charlotte_Posts,SUM(answer LIKE '%Author: Jason Biskie%') AS Biskie_Posts,
     SUM(answer LIKE '%Author: Chris Borie%') AS Borie_Posts,
     SUM(answer LIKE '%Author: Jason Burton%') AS Burton_Posts,
     SUM(answer LIKE '%Author: Marcus Jackman%') AS Jackman_Posts,
     SUM(answer LIKE '%Author: Karl Kreder%') AS Kreder_Posts,
     SUM(answer LIKE '%Author: Quezada%') AS Quezada_Posts,
     SUM(answer LIKE '%Author: Chris Roland%') AS Roland_Posts,
     SUM(answer LIKE '%Author: Patrick Shafer%') AS Shafer_Posts,
     SUM(answer LIKE '%Author: Patrick Tokeshi%') AS Tokeshi_Posts,
     SUM(answer LIKE '%Author: Jonathan Vasquez%') AS Vasquez_Posts
  FROM `ost_faq` WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY into outfile '/var/lib/mysql-files/posts.txt'";

if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
} else {
        echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

现在，如果我在MySQL命令行运行这个查询，我会得到以下输出：

+-----------------+--------------+-------------+--------------+---------------+--------------+---------------+--------------+--------------+---------------+---------------+
| Charlotte_Posts | Biskie_Posts | Borie_Posts | Burton_Posts | Jackman_Posts | Kreder_Posts | Quezada_Posts | Roland_Posts | Shafer_Posts | Tokeshi_Posts | Vasquez_Posts |
+-----------------+--------------+-------------+--------------+---------------+--------------+---------------+--------------+--------------+---------------+---------------+
|               0 |            0 |           0 |            0 |             1 |            0 |             0 |            0 |            0 |             2 |             0 |
+-----------------+--------------+-------------+--------------+---------------+--------------+---------------+--------------+--------------+---------------+---------------+

但是，当我检查我的输出文件"posts.txt“时，它只有数字，如下所示：

0       0       0       0       1       0       0       0       0       2       0

我需要将这些数据输出到文本文件中，因为我会将文本文件转换为每周自动发送给自己的电子邮件。

为什么名称不能输出到文本文件？有没有办法输出"AS“字符串？

我不知道是不是我的自动邮件bash脚本导致了这个问题。如果是，下面是脚本：

#!/bin/bash
echo "Weekly Stats" > posts.txt
echo "=============================" >> posts.txt
cat "/var/lib/mysql-files/posts.txt" >> posts.txt
if [ -s /var/lib/mysql-files/posts.txt ]
then
cat "posts.txt" | mail -s "Stats" me@domain.com
rm -f /var/lib/mysql-files/posts.txt
else
echo "No Stats Available." | mail -s "No Stats" me@domain.com
rm -f /var/lib/mysql-files/posts.txt
fi

提前谢谢你。

Answer 1

不幸的是，在outfile中包含字段名作为标头的唯一方法是在查询中硬编码标头(请参阅此question/answer)：

$sql = "
  SELECT  'Charlotte_Posts',
          'Biskie_Posts'   ,
          'Borie_Posts'    ,
          'Burton_Posts'   ,
          'Jackman_Posts'  ,
          'Kreder_Posts'   ,
          'Quezada_Posts'  ,
          'Roland_Posts'   ,
          'Shafer_Posts'   ,
          'Tokeshi_Posts'  ,
          'Vasquez_Posts'
  UNION ALL 
  SELECT  SUM(answer LIKE '%Author: Charlotte Augustine%') AS Charlotte_Posts,
          SUM(answer LIKE '%Author: Jason Biskie%') AS Biskie_Posts,
          SUM(answer LIKE '%Author: Chris Borie%') AS Borie_Posts,
          SUM(answer LIKE '%Author: Jason Burton%') AS Burton_Posts,
          SUM(answer LIKE '%Author: Marcus Jackman%') AS Jackman_Posts,
          SUM(answer LIKE '%Author: Karl Kreder%') AS Kreder_Posts,
          SUM(answer LIKE '%Author: Quezada%') AS Quezada_Posts,
          SUM(answer LIKE '%Author: Chris Roland%') AS Roland_Posts,
          SUM(answer LIKE '%Author: Patrick Shafer%') AS Shafer_Posts,
          SUM(answer LIKE '%Author: Patrick Tokeshi%') AS Tokeshi_Posts,
          SUM(answer LIKE '%Author: Jonathan Vasquez%') AS Vasquez_Posts
  FROM `ost_faq` WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY 
  INTO OUTFILE '/var/lib/mysql-files/posts.txt'";

在您的上下文中，与MySQL创建输出文件相比，将MySQL结果从PHP输出到文本文件可能是最简单的。可以在here中找到如何从MySQL查询的数组结果创建这样一个文件的示例。