CODEWITHSUNDEEP
phpmysqlpdo
Sudar
Sudar

Reputation: 20030

How to get the column names of a result returned by select query

I am building a reporting tool where an user can enter a SQL query and the tool will return the results in a CSV file. In addition to just writing to a CSV file, I also need to perform some additional logic here. So SELECT INTO OUTFILE will not work for me.

I know that executing arbitrary user provided SQL queries is bad, but this tool is going to be used only internally, so security shouldn't be a concern. Also I am limiting it to only select queries.

Now when I export the data in CSV format, I also want to output the column names of the query as the first row in the CSV file.

So my question is, is there a way to fetch the column names of a SQL query in PHP using PDO?

Mysql client tools like Sequel Pro are able to display the column names while displaying query results. So I am assuming that it should be possible, but I am not able to find it.

Upvotes: 1

Views: 1299

Answers (2)

Rohit Rasela
Rohit Rasela

Reputation: 445

Here I am not writing full PDO connection code. You can use below code/logic to get the return column name.

$stmt = $conn->query("SELECT COUNT(*), first_column, second_column FROM table_name");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$columns = array_keys($row);

print_r($columns); // array(0 => COUNT(*), 1 => first_column, 2 => second_column)

Upvotes: 1

Bobray
Bobray

Reputation: 97

The keys of the row result are the column names. You can display them like so:

$conn = mysqli_connect(YOUR_CONNECTION_INFO);
$result = mysqli_query($conn, YOUR_QUERY);
$row = mysqli_fetch_assoc($result);

foreach ($row as $key=>$value) {
    echo $key;
}

You said security wasn't an issue since the code would only be used internally, but what happens if you create a new database user with limited rights and connect to the database using that user.

That way you can set up the rights as you want from your database and won't have to worry about users dropping tables.

Upvotes: 1

Related Questions