insert query loop

ChipRivers

I am after a little help with a query to insert a large number of records into a mysql database that I have created. I know that I can probably write a simple script that will do the job for me but I can not get my head around loops and nesting loops etc!

What I am producing on my webpage is a grid 50 columns by 26 rows, each cell will contain a picture of a playing card. Each cell will also have a corresponding record in my database. The grid will appear on 9 different pages, each page being represented by a colour (red, orange, yellow, green, blue, purple, brown, grey and pink).

I have already created the database and it has the following fields:

gridID ..... key
colour ..... colours as above
row ..... letters through A to Z indicating which of the 26 rows
col ..... numbers 1 to 50 indicating which of the 50 columns
x ..... this represents the number of pixels from the left to poistition the card image
y ..... this represents the number of pixels from the top to position the card image
cardID ..... this will refer to a corresponding image record

Most entries to the above fields will be self explanatory but the x value will be in increments of 18 and the y in increments of 22.

Now if my maths are correct, I need 11700 entries into my database table, you can see why I don't want to do these manually!

So what I think I need to do is:

#1 Loop through an array of the colours

#2 Nest into the above loop a second loop to work through an array of the row letters A to Z

#3 Nest into the above loop a third loop to work through the column numbers 1 to 50

#4 Within the above loop, increment the values of x and y by the relevant values

#5 Within the above loop, write a query to insert the data into the table using the values created at each stage of the loops

NB the gridID value is an autoentry and the cardID value is to remain blank at this stage.

Any assistane with this would be very much appreciated, I am sure there are many people out there that would be able to create the required mini script without the blink of an eye.

tomhath

If you really want to do this, you don't need step 4, calculate y as part of step 2 and x as part of step 3, then insert one record as step 5. make sure you reset x at the start of every loop through step 2 and reset y every loop through step 1.

But I'm a bit confused by your table design. Why do you need all those rows if you don't have cardIDs for them? You really only need a row when there's a cardID assigned to that position, otherwise the cell is empty (no row present).

Php can calculate the arrays on the fly for you a lot faster than you can read that many rows from the dtabase, just read the rows for the page being displayed and set a default if the row isn't there.

ChipRivers

sorry, didn't fully explain! Each of the cells will contain a default image of the back of a playing card when the cardID field is empty. A process later on will allocate a cardID value which will then display a playing card face image in the cell rather than the default image. I there fore do require an entry for each cell, even though there is no entry for cardID.

Can anybody help me write the loops with the insert query, please!

Roger_Ramjet

You can generate all the data for insert in 1 query.

You do not need anything for x and y since this is presentation and should not be in the database.

Create a table called rows with 1 column and enter A - Z in it. Create another called cols and enter 1 - 50 in that. Create a third called pages and fill that with your page colours


$full_grid = "SELECT pages.page,cols.col, rows.row FROM pages, cols, rows";

That is going to return 11700 records, your full matrix of page/col/row.
Use it in an insert query to set up your data


$insert = "INSERT INTO grid (colour, row, col) SELECT  pages.page,cols.col, rows.row FROM pages, cols, rows";

This works because a join without a join-condition causes every row in 1 table to be joined to every row in the other: you get every possible combination of results.

ChipRivers

I can see what you are saying but I am not sure how I will display the required page layout without the x and y values in the database! I was planning on doing a query for each row by pulling records matching colour and required row, this resulting in 50 records for that row, I could then loop through these 50 records for each row to display the images across the page. I would use the x and y values from the database to position the images across the page.

Is there an alternative you can suggest?

Roger_Ramjet

Like I said, that is all about presentation and has nothing to do with the database. Ask another question about presentation if you don't know how to do it, but don't ask me to help you calculate or store the x, y s cos I don't do 'stupid'.

HINT: try just outputting the images without any positioning to see how the line up naturally. Then you can start to change that 'natural' position with CSS Positioning Properties , and the margins/borders/padding around each element.

ChipRivers

Sorry for troubling you! I am clearly not as experienced as you however I appreciate all the help that has been given. I now understand what you mean about leaving out the positioning and seeing how they line up! I will give that a go.

Thanks again and sorry for being 'stupid'!

Roger_Ramjet

Don't take it personal. I did not say you were stupid, I said that storing presentation in the db was stupid. Two entirely different things.

Yes, I mean quite literally generate the html just as a series of image tags with no presentation, layout or styling. Just put a <br /> at the end of each row.
See what you've got and then you can see what is the minimum CSS that will be needed to get what you want. My guess is that the layout will be pretty near what you are after to begin with, rows of thumbnails, just bunched up in the top left corner. You can then use margins and borders around the pix to get the spacing you need.