Help on SQL to HTML Table [Archive]

sniperman

11-30-2010, 04:47 AM

Need some help with this PHP code. The code has been tested and already works to retrieve the contents of a MySQL table and display it as a table in HTML. The problem I encounter (highlighted in red) is that the script currently returns ALL the columns in the table, whereas I want the columns that are returned to be constrained explicitly to those called in the query_string.

<?php
$hostname='localhost';
$username='username';
$password='password';
$db='database';
$query_string="SELECT
value1,
value2,
value3,
value4
FROM `table1`";

$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
mysql_select_db($db, $global_dbh)
or die("Could not select database");
function display_db_query($query_string, $connection, $header_bool, $table_params) {
// perform the database query
$result_id = mysql_query($query_string, $connection)
or die("display_db_query:" . mysql_error());
// find out the number of columns in result
$column_count = mysql_num_fields($result_id)
or die("display_db_query:" . mysql_error());
// Here the table attributes from the $table_params variable are added
print("<TABLE $table_params >\n");
// optionally print a bold header at top of table
if($header_bool) {
print("<TR>");
for($column_num = 0; $column_num < $column_count; $column_num++) {
$field_name = mysql_field_name($result_id, $column_num);
print("<TH>$field_name</TH>");
}
print("</TR>\n");
}
// print the body of the table
while($row = mysql_fetch_row($result_id)) {
print("<TR ALIGN=LEFT VALIGN=TOP>");
for($column_num = 0; $column_num < $column_count; $column_num++) {
print("<TD>$row[$column_num]</TD>\n");
}
print("</TR>\n");
}
print("</TABLE>\n");
}

function display_db_table($tablename, $connection, $header_bool, $table_params) {
$query_string = "SELECT * FROM $tablename";
display_db_query($query_string, $connection,
$header_bool, $table_params);
}
?>
<HTML><HEAD><TITLE>Displaying a MySQL table</TITLE></HEAD>
<BODY>
<TABLE><TR><TD>
<?php
//In this example the table name to be displayed is static, but it could be taken from a form
$table = "`table1`";

display_db_table($table, $global_dbh,
TRUE, "border='2'");
?>
</TD></TR></TABLE></BODY></HTML>

alexjewell

12-03-2010, 03:44 AM

The problem is that you're defining $query_string again inside of display_db_table(); and telling it to SELECT * (all). Because the first declaration of $query_string is mentioned outside of the function's scope, which is local, your second definition is the ONLY definition it will use for $query_string. So, you must declare $query_string global, and try to not redefine it. Make sure you are keeping track of your variables so you don't mix them up, or try to name two separate variables the same thing. Here's the working code:

function display_db_table($tablename, $connection, $header_bool, $table_params) {
global $query_string;
display_db_query($query_string, $connection,
$header_bool, $table_params);
}

The only problem is that the table is predetermined in your first $query_string declaration. This means you'll have to do a little reconstruction to your functions in order to retrieve the table from a variable, such as $tablename or $table. You can always just move the variable $query_string down and put it IN the function display_db_table() itself, like:

function display_db_table($tablename, $connection, $header_bool, $table_params) {
$query_string="SELECT value1,value2,value3,value4 FROM `table1`";
display_db_query($query_string, $connection,
$header_bool, $table_params);
}

Hope this helps.