Results 1 to 2 of 2

Thread: Help on SQL to HTML Table

  1. #1
    Join Date
    Apr 2009
    Location
    Sydney, Australia
    Posts
    118
    Thanks
    16
    Thanked 1 Time in 1 Post

    Default Help on SQL to HTML Table

    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.


    Code:
    <?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>

  2. #2
    Join Date
    Mar 2006
    Location
    Cleveland, Ohio
    Posts
    574
    Thanks
    6
    Thanked 5 Times in 5 Posts

    Default

    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:

    PHP 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:

    PHP Code:
    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.
    Thou com'st in such a questionable shape
    Hamlet, Act 1, Scene 4

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •