HTML Table from Database

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 |

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 .

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

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.

?

Sorry Emile… not sure where you are going there…

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).

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

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 ?

one

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).

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.

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:

[code] // 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 = “” + Source(Loop_Idx) + “” + EndOfLine
TA_Result.SelText = “” + Source(Loop_Idx + 10) + “” + EndOfLine
TA_Result.SelText = “” + Source(Loop_Idx + 20) + “” + EndOfLine
TA_Result.SelText = “” + Source(Loop_Idx + 30) + “” + EndOfLine
TA_Result.SelText = “” + Source(Loop_Idx + 40) + “” + EndOfLine

// Add an empty line for readability
TA_Result.SelText = EndOfLine

// To avoid infinite loop
If UserCancelled Then Exit

Next[/code]

Et voilà the result for the first row:

[code]Adam and Evil

Baby Let’s Play House C’mon Everybody Dainty Little Moonbeams Early Morning Rain[/code]

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.

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

[quote=409407:@Gert Van Assche]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[/quote]
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

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…

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

[quote=409426:@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[/quote]

Sorry. I saw a List and was instantly in “ListBox Mode” :frowning:

[quote=409426:@Dave S]Instead of one table, it is actually 4
each column is in itself a table of one column[/quote]

Why? I think i miss something here… :slight_smile:

I’d just start a and then:

[code]




[/code]

and then close the

(Loop here from 1 to 333) (Loop here from 334 to the Number of the Beast) (Loop here from 667 to 999)
again. Or?

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…

I’m glad you found a working solution. Thank you for your time & patience, Dave. :slight_smile: