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.
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.
gridmaster
gridmasterid - int PK
gridname - varchar
gridrowmaster
gridrowmasterid - INT PK
gridmasterid - INT (from gridmaster table)
rowname - varchar
gridcolumnmaster
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.
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 )
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?
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…
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
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.
)
