php - MySQL "AS"字符串不输出到 TXT 文件

Question

这个问题已经有答案了:

Include headers when using SELECT INTO OUTFILE? (23 个回答)

已关闭 4 年前。

我有一个 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'";

在您的上下文中，将 PHP 的 MySQL 结果输出到文本文件可能是最简单的，而不是 MySQL 创建输出文件。有关如何从 MySQL 查询的数组结果创建此类文件的示例，请参见 here .