I have been teaching English in Japan, for 5 years. Before that I was living the life of Peter Gibbons from the movie Office Space. I've done some ColdFusion programming, but only recently got into Flash.I’m going to assume you can make files in Flash that can connect to a db via PHP, CF, ASP. I’m also going to assume you can use PHP-MyAdmin, but you don’t need to know anything about MS Excel. You could use any spreadsheet program such as OpenOffice Calc, but I’m going to talk about Excel for this article.
This article is a follow up to “Using MS Excel to quickly populate a text file”. You need to read that before doing this one since I don’t want to repeat some of the basics, and we are building on the spreadsheet from the first tutorial.
I had a corporate job where I had to use Excel to create reports based on other spreadsheets. I spent literally 4 days at about 8-10 hours a day compiling these reports until I realized that I was always taking numbers from the same sheets and the same cells. I created a wicked spreadsheet that checked if new files existed, opened them, copied various cells into my sheet, made a nice looking report with charts and graphs, and then printed everything at a certain time directly on my managers’ printers. At that point I learned how useful a spreadsheet is. I’ll try to pass along a fraction of what it can do.
Open up the spreadsheet and click on SHEET 2 at the bottom of the screen. 
We need to start over a bit to make it fit the MySQL format.
First off, in A1 put the number 1. Then in A2 put 2. Now click and drag so both are highlighted and then drag the magic box down to row 20. That will be our ID row.
Now in B1 type the word itself, which here is “run”, then in B2 it’s “sit” (no quotes) and so on down to “difficult” in B20. Of course these can be whatever you want them to be for your own needs.
In C1 type the correct answer and in D1 either type the wrong answer or the IF function mentioned in part 1. You should have something that looks like this:
The numbers in column A are centered only to make it easier to read.
Now it gets a bit funky.
In cell F1 we will now make the long SQL string to insert a line into the DB.
Type the following verbatim:
="INSERT INTO `verbORadj` VALUES ("
Hit enter and you should see:
INSERT INTO `verbORadj` VALUES (
I usually have to copy those backward `apostrophes` since I am on a Japanese keyboard and it causes problems. That is the first part of the static data. Most of the rest is dynamic.
From this point you just concatenate the cells together using & and “”. The “” go around strings (or things we just want to show) and the cells with values in them are just referred to by the letter and number of the cell. So the next part to add on to the above line is:
="INSERT INTO `verbORadj` VALUES ("&A1&",’"
Which will now show:
INSERT INTO `verbORadj` VALUES (1,’
Yes the apostrophe is dangling there on the end.
So we keep going. I HIGHLY suggest you do one segment at a time and check by hitting return. You either want to end with “ or a cell reference like B2. You can’t end with the ampersand since that means AND something. It would be like saying “I have been to
="INSERT INTO `verbORadj` VALUES ("&A1&",'"&B1
Will result in:
INSERT INTO `verbORadj` VALUES (1,’run
Now this was a little tricky and might be hard to see. After the value of cell A1, which is the number 1, we want a comma so we have to include that in the quotes. A1&”,” The next value has an apostrophe so let’s add that into the quotes with the comma. Now it looks like &A1&",'". Ampersand quote comma apostrophe quote.
To keep going we add another & and continue with B1 ampersand quote apostrophe comma apostrophe.
="INSERT INTO `verbORadj` VALUES ("&A1&",'"&B1&”’,’”
And you get
INSERT INTO `verbORadj` VALUES (1,’run’,’
At this point I just copy and paste and change the cell reference.
In the function bar select back to the & before the B1 and copy that. Then paste it at the end. Then change the 2nd B1 to C1. Do that again and change the last one to D1. We are almost done
Now the function looks like:
="INSERT INTO `verbORadj` VALUES ("&A1&","&"'"&B1&"','"&C1&"','"&D1&"'"
And shows:
INSERT INTO `verbORadj` VALUES (1,’run’,’verb’,’adj’
That last part is D1ampersand quote apostrophe quote.
Now the ending is simple. Just add this to the end:
&");" The ) and ; are in quotes because we simply want them to be shown.
The whole finished SQL line is now:
INSERT INTO `verbORadj` VALUES (1,’run’,’verb’,’adj’);
Now select F1, find the magic box and drag it down and once again Excel just saved you hours of typing. Once you get one correct then they are all correct. Now we get to paste this into the SQL query test box of phpMyAdmin.
Go to phpMyAdmin and select your database and table. If you haven’t created a table yet you can use this code:
CREATE TABLE `verbORadj` (
`id` int(11) NOT NULL auto_increment,
`word` varchar(25) NOT NULL default '',
`type` varchar(25) NOT NULL default '',
`not` varchar(25) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM ;
The way I got that was I went to another table that had a similar structure and exported the table structure. Then I exported one line of data so I knew I had the exact SQL line.
First I clicked the Export Tab, then the Export -> SQL option on the left. Then I unchecked Data and selected Structure. You can do both at once as well. The Structure option resulted in the above CREATE TABLE code, which I know is error free. I then changed the table name and data fields to fit my new table. Of course you can also manually create a table on the main page.
So back to the SQL query box. Select the SQL tab (mine is 3rd from the left. You should see something like this:
Erase the SELECT statement and paste in either the CREATE TABLE code, or the data from column F in the spreadsheet and click Go. 
For 20 rows this is not a big deal, but when you populate 500 – 1,000 in about 2 seconds, you’ll really understand the beauty of it all.
For the PHP/ASP/CF file you would just make something that queries the DB and then concatenates the ID with the variables Flash is looking for. I have used something like:
<?php
mysql_connect ("localhost", "username", "password");
mysql_select_db ("database_name");
$qResult = mysql_query ("SELECT * FROM table_name");
$nRows = mysql_num_rows($qResult);
for ($i=1; $i< $nRows+1; $i++){
$row = mysql_fetch_array($qResult);
$rString .="&word".$i."=".$row['word']."&".
"answer".$i."=".$row['answer']."&".
"wrong".$i."=".$row['wrong'];
}
echo $rString;
?>
Excel uses &, Flash uses +, and PHP uses a dot to add strings together. There are probably millions of PHP tutorials out there and I can’t explain it any better so I won’t try to decipher it line by line. I will say this script is using the variable “i” rather than the ID in the spreadsheet, both are ok.
Now you’re done, but I’ll show you a few things to make the spreadsheet even more useful.
Let’s say you are making 10 tables that are all similar. I have about 10-15 for my quizzes. I could put all my eggs in one basket, but I like to separate things. I think it’s better to have 100 people using ten tables rather than everyone querying just one. All the tables have the same structure, but a varying number of rows. The first thing I do is take the table name out of the static part of the string and put it in cell E1. Then I change the string to reference E1 where the table name goes. That way I only have to change one cell when I copy it to a new sheet.
Change:
="INSERT INTO `verbORadj` VALUES
To this:
="INSERT INTO `"&E$1&"` VALUES
Notice the $1. That locks it in place so when you click and drag down it doesn’t automatically change it to E1, E2, E3. Since it would only change the numbers (which are row numbers) I put $1. If I had something that would change columns too, I could use $E$1. That means always refer to only this cell, don’t let the magic box change it.
Another thing I do is put IF statements in each cell and copy them down about 50-100 rows depending how many rows I expect to have. These IF statements basically say “If there is nothing in a certain cell, then don’t show anything, but if there is something, then run this function.” Finally I put the opposite answer IF statement in the wrong answer column and copy that down.
Once I do that, I only have to add new words and new correct answers, everything else automatically appears. This is where the functions start getting heavy because they are nested. If there is a mistake, you have to pick through each little mark to see. Excel doesn’t give nice error messages. Most often you’ll receive something like #NAME? Which basically means “ehhhh…..say what?”.
First thing I do is put this in the next ID line, here it’s A21:
=IF(B21<>"",A20+1,"")
If B21 is not equal to “” (nothing) then add 1 to A20, otherwise leave it blank. Test it by typing anything in B21, you should see “21” in A21.
Then I add the wrong answer IF function from the first part to D21:
=IF(C21="verb","adj","verb")
Easy enough. Except, if there is nothing in B21 then there will be nothing in C21 and it will be blank, which is not equal to verb. So cell D21 (and any below it) will show “verb”. Little annoying, but not a big problem. Let’s fix it anyway.
=IF(B21<>"",IF(C21="verb","adj","verb"),"")
IF B21 is not blank then check if C21 = verb, but it B21 is blank, this cell stays blank too.
Next you need to copy that whole SQL string and paste it into the TRUE part of an IF statement in F21. That sentence was in English and it is still confusing.
First I drag the magic box of F20 down one so it automatically updates everything. Then I cut that cell’s function and start with the IF function. If you type straight through it’s easy.
=IF(B21<>””,
Now just hit CTRL-V. Next don’t do anything other than type ,””).
If you cut the formula and then just paste it there it’s almost ready. The only thing you need to do now is take off the = sign from =”INSERT. The = only goes at the beginning of the function. So it should look like this, but with no spaces (for readability):
=IF(B21<>"", "INSERT INTO `verbORadj` VALUES ("&A21&","&"'"&B21&"','"&C21&"','"&D21&"'"&");", "")
IF B21 is not blank, then “INSERT…..);”, otherwise stay blank.
So now test it by typing a word in B21 and you will see all the other lines just pop up.
I usually make spreadsheets that automatically create the text file string and the database string at the same time. Then I add all these IF statements and copy-paste them down about 500 rows. Finally I just sit and type in about 10-20 different words and answers whenever I have free time or think of new things. After a few days I have these huge lists that are preformatted. I just copy-paste them into text files and/or databases. Hope you enjoyed this and I hope Excel saves you hours.