MySQL“AS”字符串不输出到TXT文件[重复]

Question

This question already has an answer here:

• Include headers when using SELECT INTO OUTFILE? 18 answers

I have a PHP script that query's a count of specific rows per user in a table. I then have the SQL query use "AS" to output the count under each user's respective count; like so:

<?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);
?>

Now if I run this query at the MySQL command-line I get the following output:

+-----------------+--------------+-------------+--------------+---------------+--------------+---------------+--------------+--------------+---------------+---------------+
| 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 |
+-----------------+--------------+-------------+--------------+---------------+--------------+---------------+--------------+--------------+---------------+---------------+

However, when I check my output file "posts.txt" all it has is the numbers, like so:

0       0       0       0       1       0       0       0       0       2       0

I need to output this data to a text file, because I then cat the text file to an automated email I send to myself on a weekly basis.

Why are the names not outputting to the text file? Is there a way for me to output the "AS" strings?

I don't know if my automated mail bash script is causing the issue. In case it is, here's that script:

#!/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" [e-mail]
rm -f /var/lib/mysql-files/posts.txt
else
echo "No Stats Available." | mail -s "No Stats" [e-mail]
rm -f /var/lib/mysql-files/posts.txt
fi

Thank you in advance.

</div>

Answer 1

Unfortunately, the only way to include field names as headers in an outfile is to hard code the headers in the query (See this 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'";

In your context, it might be easiest to output the MySQL result from PHP to the text file, instead of MySQL creating the output file. An example of how to create such a file from an array result from a MySQL query can be found here.