HTML Table from Database

  1. last week

    Dave S

    Oct 10 San Diego, California USA

    I have no issues with the basic concept of writing HTML while reading data from a database.
    However... I need to write it top to bottom,

    The easiest way while reading data in sorted order would be a table
    just write left to right, starting a new row after the Nth column.

    | A | B | C |
    | D | E | F |
    | G | H | I |

    but I want

    | A | D | G |
    | B | E | H |
    | C | F | I |
  2. Beatrix W

    Oct 10 Pre-Release Testers Europe (Germany)

    How is the data saved in your database? One value or multiple values? If you have one value you will need to parse the data into multiple values. Then you can transpose the matrix. See https://en.wikipedia.org/wiki/Transpose .

  3. Dave S

    Oct 10 San Diego, California USA

    N rows with one value
    A
    B
    C
    D
    E
    F
    G
    H
    I

  4. Emile S

    Oct 10 Europe (France, Strasbourg)
    Edited last week

    Step_Val = RowCount / Your Col Count

    and in a loop, you generate the html table (Loop_Idx is the loop indice)

    Generate one full Row in each loop:

    Cell_0 = RS.Field #1 = html table Cell #1
     Cell_1 = RS.Field #2 + Step_Val = html table Cell #2 + Step_Val
     Cell_0 = RS.Field #3 + Step_Val = html table Cell #3 + Step_Val
     Cell_0 = RS.Field #4 + Step_Val = html table Cell #4 + Step_Val
     etc.

    ?

  5. Dave S

    Oct 10 San Diego, California USA

    Sorry Emile.... not sure where you are going there....

  6. Emile S

    Oct 10 Europe (France, Strasbourg)

    In the html table:

    | A | D | G |
    | B | E | H |
    | C | F | I |

    row 1, cell 1 you store the Record A,
    row 1, cell 2: Record D
    row 1, cell 3: Record G

    same apply for all loops.

    The loop first line looks like:

    For Loop_Idx = 1 To (RS.RecordCount / 3) Step 3 // 3 is your column count example
    code lines
    Next

    code lines write one html Row at each for iteration: iteration 1: ADG, Iteration 2: BEH, and so on.

    This is programmation for the por. Maybe not clear, but it works.

    You have to think a bit more at what I wrote above because the idea above is incomplete (it suppose the data base have only one Field in each record): you have to adapt the idea to a db with more columns (probably).

  7. Dave S

    Oct 10 San Diego, California USA

    Emile.. I would LOVE to see a working example.... since looping thru the record count by steps serves no purpose as records are still read in "order"

    For Loop_Idx = 1 To (RS.RecordCount / 3) Step 3 // 3 is your column count example
    code lines
    Next

    would return A, B , C as loop_idx as no relation to the sequence order of the data in the table, remember this data is being retrieved via some SQL query

  8. Emile S

    Oct 10 Europe (France, Strasbourg)

    OK.

    I will try to sleep a bit and in the morning (mine), I will try to provide an example.

    How many Fields there are in your Data Base file ?

  9. Dave S

    Oct 10 San Diego, California USA

    one

  10. Emile S

    Oct 10 Europe (France, Strasbourg)

    @Dave S since looping thru the record count by steps serves no purpose as records are still read in "order"

    But you can read Record 1, 7 and 13 and write theColumn from these records in a Table Row.

    >One
    That is what Beatrix was asking (if my understanding is correct).

    So read the whole db contents in an array (if the number of entries is not too important), then read the array contents in a loop.

    I think I have the idea, I just need rest. Be back in 5 or 6 hours (my morning).

  11. Dave S

    Oct 10 San Diego, California USA

    @Emile S But you can read Record 1, 7 and 13 and write theColumn from these records in a Table Row.

    Uh..... no... the only way to read a database table by targeting a record number is if (big if) that the rowid is in sequence .... which is not a guarantee.

  12. Emile S

    Oct 10 Europe (France, Strasbourg)
    Edited last week

    Hi Dave,

    I was not able to sleep, so I fired Xojo and here I am:

    I put two TextAreas in a window. The left one is named TA_Source and holds 50 Names of songs Elvis Presley sings:

    Adam and Evil
    After Loving You
    Ain’t That Lovin’ You, Baby	
    All I Needed Was the Rain	
    All Shook Up	
    All That I Am
    Allá en el Rancho Grande
    Almost
    Almost Always True
    Almost in Love
    Baby Let’s Play House
    Baby What You Want Me to Do
    Baby, If You’ll Give Me All of Your Love
    Barefoot Ballad
    Beach Boy Blues
    Beach Shack
    Because of Love
    Beginner’s Luck
    Beyond the Bend	
    Beyond the Reef
    C’mon Everybody
    Columbus Stockade Blues
    Come Along
    Come What May
    Confidence
    Cotton Candy Land
    Cottonfields
    Could I Fall in Love
    Crawfish
    Crazy Arms
    Dainty Little Moonbeams
    Danny
    Danny Boy
    Dark Moon
    Datin’
    Didja’ Ever
    Dirty, Dirty Feeling
    Dixieland Rock
    Do Not Disturb
    Do the Clam
    Early Morning Rain
    Earth Angel
    Earth Boy
    Easy Come, Easy Go
    Echoes of Love
    Edge of Reality
    El Toro
    End of the Road
    An Evening Prayer
    Everybody Come Aboard

    The second TextArea is TA_Results and will display the <tr><td></td></tr> Rows.

    I do not create a db: you have one; I do not add the html prefix, table declaration / end and html suffix: you know how to.

    I only wrote code to generate the html rows how you wanted to. Look below:

     // Split the TA_Source contents in an array for speed
      Dim Source(-1) As String
      Dim Loop_Idx As Integer
      
      Source = Split(ReplaceLineEndings(TA_Source.Text,EndOfLine),EndOfLine)
      
      For Loop_Idx = 0 To 9
        // Place the song names in a html Row (5 cells)
        TA_Result.SelText = "<tr><td>" + Source(Loop_Idx) + "</td>" + EndOfLine
        TA_Result.SelText = "<td>" + Source(Loop_Idx + 10) + "</td>" + EndOfLine
        TA_Result.SelText = "<td>" + Source(Loop_Idx + 20) + "</td>" + EndOfLine
        TA_Result.SelText = "<td>" + Source(Loop_Idx + 30) + "</td>" + EndOfLine
        TA_Result.SelText = "<td>" + Source(Loop_Idx + 40) + "</td></tr>" + EndOfLine
        
        // Add an empty line for readability
        TA_Result.SelText = EndOfLine
        
        // To avoid infinite loop
        If UserCancelled Then Exit
      Next

    Et voilà the result for the first row:

    <tr><td>Adam and Evil</td>
    <td>Baby Let’s Play House</td>
    <td>C’mon Everybody</td>
    <td>Dainty Little Moonbeams</td>
    <td>Early Morning Rain</td></tr>

    More explanation:
    I had two problems:
    a. I was unable to wrote what I had in mind. In fact I was wrong on the implementation.
    b. With a wrong information, it is hard to understand: in this case, you know far better than me.

    This is an autodidact kind of work.

    Now if someone can write that better than what is above (more efficient, more academic), feel free to share it. I love to learn new things.

    To be totalled honest, I am a bit ashamed to show that code. Sometimes my code do the job but I think it is the result of a dirty programmer's job.

    Dave: I hope this is what you want to achieve.

  13. Gert V

    Oct 11 Leuven, Belgium

    You could also pivot a table with SQL, and then generate the HTML table from that.
    Check this: https://modern-sql.com/use-case/pivot

  14. Dave S

    Oct 11 San Diego, California USA

    @Gert VAssche You could also pivot a table with SQL, and then generate the HTML table from that.
    Check this: https://modern-sql.com/use-case/pivot

    Pivot swaps rows for colums... doesn't apply

    I have 1000 rows.... I don't want 1000 columns...

    I want column 1 to have rows 1 to 333, column 2 to have rows 334 to 666 and column 3 to have rows 667 to 999

  15. Sascha S

    Oct 11 Pre-Release Testers, Xojo Pro Germany/W'haven

    Can't you just readl all Rows from the DB and then go from 1 to 333 and do an AddRow, then do a "Cell(X, y) = " for Rows 334 to 666 and 667 to 999?

    Slow but a start...

  16. Dave S

    Oct 11 San Diego, California USA
    Edited last week

    the solutiion turned out to be quite simple, and relies on how HTML works

    Instead of one table, it is actually 4
    each column is in itself a table of one column

    thanks you all for playing

    Addrow applies to a LISTBOX.... nothing to do with writing an HTML table

  17. Sascha S

    Oct 11 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Dave S the solutiion turned out to be quite simple, and relies on how HTML works

    Instead of one table, it is actually 4
    each column is in itself a table of one column

    thanks you all for playing

    Addrow applies to a LISTBOX.... nothing to do with writing an HTML table

    Sorry. I saw a List and was instantly in "ListBox Mode" :(

  18. Sascha S

    Oct 11 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Dave S Instead of one table, it is actually 4
    each column is in itself a table of one column

    Why? I think i miss something here... :)

    I'd just start a <table> and then:

     <tr>
        <td>(Loop here from 1 to 333)</td>
        <td>(Loop here from 334 to the Number of the Beast)</td> 
        <td>(Loop here from 667 to 999)</td>
      </tr>

    and then close the </table> again. Or?

  19. Dave S

    Oct 11 San Diego, California USA

    perhaps because that creates a table that doesn't look like a table... but a mis-mash of data.
    each "cell" needs to be formatted, aligned, bordered.....

    again.... thanks...

  20. Newer ›

or Sign Up to reply!