Populating a table with a grid structure

I know I can do this programmatically in php but would like to do it in a single mysql query if that is possible. I have a structure that is a grid in nature, think spreadsheet. So there is one table with the grid info called gridmaster. There is a table called gridrowmaster that keeps all the row information and another called gridcolumnmaster that keeps all the column info.

So for grid #1 I would have one entry in the master table. And let’s say 6 rows in the row table and 14 columns in the column table. The row and column tables are keyed on unique row and column numbers that are sequential and each record contains a gridmasterid to associate it to the appropriate grid.

What I want to do is dynamically populate a table that has all the grid cells in it with the key rowno and colno since row numbers and column numbers are unique. Columns and Rows are not reused in the row and column tables.

So in this example the rows would be #1-6 and the columns would be #1-14 with all of those records having an entry for gridmasterid = 1. If grid #2 were the same size the rows would be 7-12 and the columns 15-28 and gridmasterid - 2 and so on.

So my new table would be keyed on 1-1, 1-2, 1-3… and in the example of the two grids above would have 168 entries (6 x 14 x 2). (And before anyone asks, yes I do need this structure because I keep information at all 4 levels uniquely to that level, i.e. grid, row, col, gridsquare)

As I said, in php I would process through the grid, row and col structures and insert the new records that way but I have no clue how to do this in MySQL or even if it can be done. Although I have a suspicion it can be done and believe that is the right way to go about this if in fact that is true.

Thanks for any input.

1 Like

I suspect it is possible…Not sure so much about if it is the best approach… and I think that I’ll need to read this posts 3-4 times to understand what you want.

I know I’d have to.

TBH it would be easier for me to read a small sample of what the resulting HTML would look like.

Let me try to simplify.

3 tables

gridmasterid - int PK
gridname - varchar

gridrowmasterid - INT PK
gridmasterid - INT (from gridmaster table)
rowname - varchar

gridcolumnid - INT PK
gridmasterid - INT (from gridmaster table)
colname - varchar

from these 3 tables I want to generate a 4th table with the following structure

gridrowid - INT (from gridrowmaster table)
gridcolumnid - INT (from gridcolumnmaster)
display - char(1)

So programmatically I would go through each gridmaster id and select the rows. For each row I would go through all the columns and add an entry to the table with the key row#-col#, like 1-1, 1-2 for row 1 col 1, row 1 col 2, etc.

Does that help?

1 Like

lol that shows how confused I was. I thought you were talking about using data to generate a “grid”

I don’t know what term would be best, but shouldn’t the fourth table have a “cell id”

I think the fourth table is a junction table with two primary keys, gridrowid and gridcolumnid, which together make a unique record. So maybe cellid is not needed as the unique primary key, and the database query would use JOINs on the first three tables to get the data for the fourth table. (But not being an expert on JOINS, maybe someone can jump in here with a suggested query :blush: )


[quote=“bostboy, post:4, topic:214146, full:true”]
from these 3 tables I want to generate a 4th table with the following structure[/quote]

you only need to use two of them, because apparently based on your desired output, you don’t want any gridnames

SELECT gr.gridrowmasterid AS gridrowid , gc.gridcolumnid , 1 AS display FROM gridrowmaster AS gr CROSS JOIN gridcolumnmaster AS gc


1 Like

You always make it seem so easy, @r937!

1 Like

Close, but that joins every row with every column. I only want them paired for a gridmaster. So gridmaster 1 should cross join rows 1-6 with columns 1-14 and gridmaster 2 should cross join rows 7-12 with columns 15-28.

The row and column tables do carry the appropriate gridmaster with their rows and columns. Make sense?

If I add this to the end it works. Genius!!

where gr.gridmasterid = gc.gridmasterid

1 Like

It works great with the where clause so I would like to get one more thing. And without discussing why I would like to get it because I know you’ll tell me I shouldn’t… :smile:

I would like to add a CONCAT to the SELECT statement to CONCAT the gridrowid and gridcolid with a ‘-’ in the middle but when I do the first row of the query returns

rowid = 1, colid = 1, CONCAT = 312d31, display = 1
rowid = 2, colid = 1, CONCAT = 322d31, display = 1

I would expect it to return 1-1

Here is my exact query that is running

SELECT gr.gridrowid
, gc.gridcolumnid
, CONCAT(gridrowid, ‘-’, gridcolumnid)
, 1 AS display
FROM gridrowmaster AS gr
CROSS JOIN gridcolumnmaster AS gc
WHERE gr.gridmasterid = gc.gridmasterid

just a note, I can use CONCAT(gr.gridrowid, ‘-’, gc.gridcolumnid) and get the same result

Also, this is running against the actual tables and returns 6,091 rows which is the correct number overall.

Strange (to me at least) but when I use this same statement to actually execute the insert statement it inserts the CONCAT correctly as ‘1-1’, ‘1-2’, etc.

Anyhow it is working great. Thanks again for your help!! Truly appreciated.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.