MySQL "AS" strings don't output to TXT file

Question

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

Thank you in advance.

Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. You're using both styles here inconsistently. — tadman, May 10 '19 at 19:25

score 0 · Answer 1 · answered May 10 '19 at 19:43

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.

MySQL "AS" strings don't output to TXT file

1 Answers1