I'm trying to create a page where I input a MYSQL query: SELECT column1,column2 FROM table;, and dynamically create an HTML table with column titles.
EDIT: removed most of my question, just left the question part. I have since created a script that does this and included it as an answer. I hope someone else makes use of it like I do (when I'm too lazy to log into phpmyadmin ... hahaha).
I would try mysql_fetch_assoc which gives back an associative array (map). Then you can use array_keys to get the column names.
mysqli_fetch_assoc. Though I've had to post code on at least one server that did not support mysqli_*, but that was over a year ago. The "mysql improved" methods are more secure, so use those for any new app.
mysqli_fetch_assoc. I'm very surprised I couldn't find a simply script like this online. I'm going to post my work here when I'm finished.This sounds like it might be related to testing where you sometimes just need to spit out the results to see them on a screen , so I'm going to put this here...
The php class dbug is an awesome tool for quickly getting a nicely formatted table out of a php array or even a MySQL result:
Well. Here's how I accomplished this, building on webjprgm's tip to use mysql_fetch_assoc:
php code to dynamically create a table with column titles, from PHP to mysql to html! :)
<head>
<title>mysql Table maker</title>
</head>
<body>
<center>
<?php
/* posted data sent to this page (from this page)*/
$pdatabase = htmlentities($_POST['database'], ENT_QUOTES); // database
$phost = htmlentities($_POST['host'], ENT_QUOTES); // host
$puser = htmlentities($_POST['user'], ENT_QUOTES); // user
$ppassword = htmlentities($_POST['password'], ENT_QUOTES); // password
$pcolumns = htmlentities($_POST['columns'], ENT_QUOTES); // comma seperated columns
$columns = explode(",",$pcolumns); // array of column names
$ptable = htmlentities($_POST['table'], ENT_QUOTES); // table
$pwhere = str_replace(array(";",'"'),array('',''),$_POST['where']); // WHERE clause
if (!empty($pwhere)) {$pwhere = "WHERE $pwhere";} // if not empty, prepend with "WHERE"
$porder = htmlentities($_POST['order'], ENT_QUOTES); // ORDER BY clause
$psort = htmlentities($_POST['sort'], ENT_QUOTES); // SORTING (asc or desc)
if (!empty($porder)) {$porder = "ORDER BY $porder $psort";} // if order is not empty, prepend with "ORDER BY"
$pgroup = htmlentities($_POST['group'], ENT_QUOTES); // GROUP BY clause
if (!empty($pgroup)) {$pgroup = "GROUP BY $pgroup";} // if not empty, prepend with "GROUP BY"
$plimit = htmlentities($_POST['limit'], ENT_QUOTES); // LIMIT clause
if (!empty($plimit)) {$plimit = "LIMIT $plimit";}
/* The finished product....or query...so to speak...if you will */
$query = "SELECT $pcolumns FROM $ptable $pwhere $pgroup $porder $plimit";
/* Safety precautions */
$query = str_replace(array("delete","drop","update","alter"),array('','','',''),$query);
// print_r($columns);
?>
<form action="mysql-table.php" method="POST">
<span style="position:fixed;top:0;left:0;width:100%;height:30px;background:#35AFE3">
host: <input name="host" type="text" value="<?php echo $phost;?>"/>
user: <input name="user" type="text" value="<?php echo $puser;?>"/>
password: <input name="password" type="password" value="<?php echo $ppassword;?>"/>
database: <input name="database" type="text" value="<?php echo $pdatabase;?>"/>
</span>
<hr/>
<span style="position:fixed;top:30px;left:0;width:100%;height:205px;background:#35FC39;">
SELECT
<input name="columns" type="text" value="<?php echo $pcolumns;?>"/><br/>
FROM
<input name="table" type="text" value="<?php echo $ptable; ?>"/><br/>
WHERE
<input name="where" type="text" value="<?php echo trim(str_replace("WHERE","",$pwhere)); ?>"/><br/>
ORDER BY
<input name="order" type="text" value="<?php echo trim(str_replace("ORDER BY","",$porder)); ?>"/><br/>
SORT
<select name="sort">
<option value=""></option>
<option value="ASC">ASC</option>
<option value="DESC">DESC</option>
</select><br/>
GROUP BY
<input name="group" type="text" value="<?php echo trim(str_replace("GROUP BY","",$pgroup)); ?>"/><br/>
LIMIT
<input name="limit" type="text" value="100"/><br/>
GO:
<input type="submit" value="submit" />
</span>
</form>
<span style="position:absolute;top:235px;left:0;width:100%;height:auto;background:#28c7d6;z-index:-1;">
<?php
if (!empty($_POST['columns']) && !empty($_POST['table'])) {
echo "<h3><b>Query:</b> <i>$query</i></h3><hr/>";
$mysqli = new mysqli($phost,$puser,$ppassword,$pdatabase); // Connect to DB
/* check connection */ // check for connection error
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($result = $mysqli->query($query)) {
echo "<table border='1' style='word-wrap:break-word'>"; // New table
/* Column Title */
echo "<tr>"; // New Row for the titles
foreach($columns as $c) { // For each column, create a column
echo "<td><b>$c</b></td>\r\n";
}
echo "</tr>"; // Close the titles' row
/* DATA RESULTS */
while ($row = $result->fetch_assoc()) { // for each set of rows:
echo "<tr>\r\n\r\n"; // create new row
foreach($columns as $c) { // for each column in the row:
// create a cell
echo "
<td style='max-width:400px;'>
<div style='max-height:300px;overflow-y:auto;'>
$row[$c]
</div>
</td>";
}
echo "</tr>"; // end of that row
} // end foreach results
echo "</table>"; // closing of the table
/* free result set */
$result->free();
} else {
echo "query failed.<hr/>";
}
/* close connection */
$mysqli->close();
} elseif (isset($_POST['columns']) || isset($_POST['table'])) { // end of !empty columns,table
echo "<b>MISSING IMPORTANT INFORMATION.<br/>
For column, you entered: <u> ".$_POST['columns']." </u><br/>
For table you entered: <u> ".$_POST['table']." </u>";
}
?>
</span>
</center>
</body>