SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating tables on the fly from a DB query

    I thought I remembered from one of my PHP/MySQL classes that it’s possible to create HTML tables on the fly from DB queries. However a colleague whom I respect is saying that although the number of rows will obviously vary, the number of columns in the table must be fixed.

    As an example, I’d like to create a billing report – give users a form with 4 variables, thus having possible output table headers of “Date Billed”, “Therapist Name”, “Client Name” and “Amount”. But John User might not need the Therapist Name or the Client Name. I'd like to make it so that if he doesn't select those items that the table on his billing report would only have the column headers of “Date Billed” and “Amount”.

    Is that possible? I thought it was but my colleague says it's not. If it's possible, can you point me in the right direction so I can share the info with my colleague?

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all take in account that all php scripts on the Earth do the same thing: creating HTML tables on the fly

    For your case it is sure possible. No need to alter SQL query even. Just omit some output with simple condition like this
    PHP Code:
    <? if ($show['DateBilled']=='yes'): ?>
    <td><?=$row['DateBilled'?></td>
    <? endif ?>
    there is also thousands ways to do the thing

  3. #3
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rendering table rows involves iterating, or looping down the record set. You can also loop across each row of the record set to render different columns.

    Here's a proof of concept:
    PHP Code:
    <?php
    $db 
    = new Mysqli('localhost''user''password''dbname');

    $result $db->query("SELECT col1, col2, col3 FROM my_table LIMIT 30, 20");
    while(
    $row $result->fetch_assoc()) {

        
        
    //render table HEAD on first iteration only
        
    if(!isset($thead_done)) {
            
    $output "<table cellspacing='0' border='1'>\n" .
                        
    "<thead>\n" .
                        
    "<tr>\n";
                        
            foreach(
    $row as $col=>$value) {
                
    $output .= "<th>$col</th>\n";
            }
            
            
    $output .= "</tr>\n" .
                        
    "</thead>\n" .
                        
    "<tbody>\n";
                        
            
    $thead_done true;
        }
        
        
    //render each row
        
    $output .= "<tr>\n";
        foreach(
    $row as $value) {
            
    $output .= "<td>" htmlentities($valueENT_QUOTES) . "</td>\n";
        }
        
    $output .= "</tr>\n";
    }

    //close table if rows found
    if($result->num_rows 0) {
        
    $output .= "</tbody>\n" .
                    
    "</table>";
                    
        echo 
    $output;
    }

    else echo 
    "<p>No data found</p>";
    ?>
    That will work, whatever columns you select from the DB will be present in the HTML table.

    The problem will be knowing how to format different columns. For example numbers maybe should be right aligned, dates maybe should be run through the date function to display in a more friendly format, some items should be turned into hyperlinks etc.

    You could create formatting methods for each supported column, and pass the value through that, but it'd require a bit more thought.

    Hope that helps.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  4. #4
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem will be knowing how to format different columns.
    The method i presented above don't have such problem :P
    also, never use htmlentities. it is obsoleted function
    use htmlspecialchars instead

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why dont you give a try in phpclasses.org site

  6. #6
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I consider having an if statement for every possible column a readability problem.

    What makes you say that htmlentities is obsolete? The PHP manual doesn't mention this.
    entities converts a wider range of characters than htmlspecialchars. special chars could be used if you know your output will only contain the basic special characters.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  7. #7
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Template use will make it readable all right.

    Modern browsers support whole range of characters. No need to replace it with digits. It produces less readability
    The only case then you need it - two different encodings on one page. But in this case utf is your choice for sure. That's why I said it obsoleted.

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Modern browsers support whole range of characters. No need to replace it with digits. It produces less readability
    I don't understand. Having the raw characters in the HTML code is invalid, the page would fail validation and could cause problems to user agents. True, every web browser has had to make allowances for broken HTML for a long time and no doubt most will cope, but that's no excuse for producing invalid output.
    I don't see how character entities reduce readability, they are in the source, not the text users will see reading the page.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  9. #9
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having the raw characters in the HTML code is invalid
    You kidding.
    Please give an example of such invalid character? (not covered by htmlspecialchars of course)

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <html lang="en">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    
    <title>Test Page</title>
    </head>
    <body>
    
    <?php
    $text = '&#169; copyright';
    echo "<p>$text</p>";
    echo "<p>" . htmlspecialchars($text) . "</p>";
    echo "<p>" . htmlentities($text) . "</p>";
    ?>
    
    </body>
    </html>
    Fails validation. Remove the first two echo statements (leaving only the htmlentities line) and it works, and passes validation.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  11. #11
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm. Probably because that &copy; character is invalid for utf-8. It is 2-byte one. ©

  12. #12
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the responses. I'm wondering if, instead of trying to devise ways to have a variable number of columns in the output table, we need to be thinking of creating the SQL statement on the fly. For example, we give the user a form to collect the data and, depending on whether they select an item, we do or don't include that in the SQL statement. The output from the SQL then determines the number of cols and rows in the table. Does this make things easier or murkier?

  13. #13
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Every php script on the Earth create the SQL statement on the fly
    That's what our PHP scripts for. Half of topics here are on creating SQL queries dynamically.

    But mostly we dealing with rows, not columns.
    Why don't you describe your task some more? May be you don't need to pick columns to display at all?

  14. #14
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Hmmm. Probably because that &copy; character is invalid for utf-8. It is 2-byte one. ©
    &copy does not fail validation but © will fail validation when using utf-8
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  15. #15
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We're speaking about chars, not entities.
    Yes, this character invalid in utf-8. In utf-8 it must be 2-byte, like many other characters.

    But yes, for all you lazy latin1 speakers htmlentities is needed

  16. #16
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Every php script on the Earth create the SQL statement on the fly
    That's what our PHP scripts for. Half of topics here are on creating SQL queries dynamically.

    But mostly we dealing with rows, not columns.
    Why don't you describe your task some more? May be you don't need to pick columns to display at all?
    It's a medical billing application. What I want is to have a form where users input which fields they want included on statements they print out for their clients. The form might have these possible choices:

    Date of Service
    Type of Service
    Dx
    Fee
    Amt Allowed
    Pmt Date
    Payor
    Amt Paid
    Balance Due

    In some cases, you might need all of those fields on a statements, but in other cases (for example, for an employee to submit to their cafeteria plan at work), you might only want a handful - like maybe:

    Fee
    Pmt Date
    Amt Paid

    In the first instance, I'd want the underlying table to have 9 columns - one for each field selected. But in the second instance, the table would only need 3 columns. The other 6 shouldn't be printed at all.

  17. #17
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know how familiar you with programming, but usually it isn't question at all.
    Simple condition statement will do everything. There are thousands ways to do
    For example, if there is only two table types, only one condition needed:
    PHP Code:
    if ($short_form == 1) {
      
    $query="SELECT fee, pmtdate, amtpaid FROM TABLE ...";
      
    $table_template="narrow.html";
    } else {
      
    $query="SELECT date, type, dx, fee, pmtdate, amtpaid FROM TABLE ...";
      
    $table_template="wide.html";
    }
    $data=execute_query($query);
    out_data($table_template); 

  18. #18
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    I don't know how familiar you with programming, but usually it isn't question at all.
    Simple condition statement will do everything. There are thousands ways to do
    For example, if there is only two table types, only one condition needed:
    PHP Code:
    if ($short_form == 1) {
      
    $query="SELECT fee, pmtdate, amtpaid FROM TABLE ...";
      
    $table_template="narrow.html";
    } else {
      
    $query="SELECT date, type, dx, fee, pmtdate, amtpaid FROM TABLE ...";
      
    $table_template="wide.html";
    }
    $data=execute_query($query);
    out_data($table_template); 
    This is very helpful, Shrapnel. I couldn't have written it myself but I know enough programming to understand it which is what I needed. Thanks.


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
  •