I want to execute a SELECT query but I don't how many columns to select.
Like:
select name, family from persons;
How can I know which columns to select?
"I am currently designing a site for the execute query by users. So when the user executes this query, I won't know which columns selected. But when I want to show the results and draw a table for the user I should know which columns selected."
Use mysql_query() and execute this query:
SHOW COLUMNS FROM table
Example:
<?php
$result = mysql_query("SHOW COLUMNS FROM sometable");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
}
?>
use DESC table or
Example
SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='your_table';
output
column1,column2,column3
First, understand exactly what data you want to retrieve. Then look at the database schema to find out which tables the database contains, and which columns the tables contain.
The following query returns a result set of every column of every table in the database:
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS;
In this sqlfiddle, it returns the following result set (truncated here for brevity):
TABLE_NAME COLUMN_NAME
-----------------------
CHARACTER_SETS CHARACTER_SET_NAME
CHARACTER_SETS DEFAULT_COLLATE_NAME
CHARACTER_SETS DESCRIPTION
CHARACTER_SETS MAXLEN
COLLATIONS COLLATION_NAME
COLLATIONS CHARACTER_SET_NAME
COLLATIONS ID
COLLATIONS IS_DEFAULT
COLLATIONS IS_COMPILED
COLLATIONS SORTLEN
Now I know that I can select the column CHARACTER_SET_NAME from the table CHARACTER_SETS like this:
SELECT CHARACTER_SET_NAME
FROM CHARACTER_SETS;
Use mysqli::query to execute these queries.
If I understand what you are asking, you probably want to use MySQLIi and the the fetch_fields method on the result set:
http://us3.php.net/manual/en/mysqli-result.fetch-fields.php
See the examples on that page.
For unknown query fields, you can just use this code.
It gives you every row fields name=>data. You can even change the key to '' to get ordered array columns by num following the columns' order in the database.
$data = array();
while($row = mysql_fetch_assoc($query))
{
foreach($row as $key => $value) {
$data[$row['id']][$key] = $value;
}
}
print_r($data);
If you want to get column names for any query in all cases it's not so easy.
In case at least one row is returned you can get columns directly from this row.
But when you want to get column names when there is no result to display table/export to CSV, you need to use PDO functions that are not 100% reliable.
// sample query - it might contain joins etc.
$query = 'select person.name, person.family, user.id from persons LEFT JOIN users ON persons.id = user.person_id';
$statement = $pdo->query($query);
$data = $statement->fetchAll(PDO::FETCH_CLASS);
if (isset($data[0])) {
// there is at least one row - we can grab columns from it
$columns = array_keys((array)$data[0]);
}
else {
// there are no results - no need to use PDO functions
$nr = $statement->columnCount();
for ($i = 0; $i < $nr; ++$i) {
$columns[] = $statement->getColumnMeta($i)['name'];
}
}