Rapidly Import Database Column Names as Variables with Spreadsheet Formulas
- November 7th, 2011
- By كارما
- Write comment
Many times when processing form data for a database I find myself using table column names as the variable names for their corresponding datum. You can save yourself a lot of time by importing your table column names into a spreadsheet then applying the SUBSTITUTE() function to nest their names in repetitive code. For this example we will be processing some form data in a PHP script for insertion into a MySQL database. Although it is possible to take your column list from almost any source we will only cover phpMyAdmin and the CLI interface in this example.
mysql> show columns from users; +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ ... | d_address1 | varchar(255) | NO | | NULL | | | d_address2 | varchar(255) | NO | | NULL | | | d_city | varchar(255) | NO | | NULL | | | d_prov | varchar(40) | NO | | NULL | | | d_postal | varchar(20) | NO | | NULL | | | d_pone1 | varchar(40) | NO | | NULL | | | d_phone2 | varchar(40) | NO | | NULL | | | d_fax | varchar(40) | NO | | NULL | | | d_email | varchar(255) | NO | | NULL | | | d_type | varchar(255) | NO | | NULL | | | d_usedinv | varchar(255) | NO | | NULL | | | d_purchasepref | text | NO | | NULL | | | d_frannum | varchar(255) | NO | | NULL | | | d_legalname | varchar(255) | NO | | NULL | | | d_hst | varchar(255) | NO | | NULL | | | d_license | varchar(255) | NO | | NULL | | ... +--------------------+--------------+------+-----+---------+----------------+ 101 rows in set (0.00 sec)
LibreOffice crashed when I tried to do a straight-up formatted paste from Firefox and even when it does work it tends to take a long time so I recommend first dumping it into a plain text editor then copying from there to your spreadsheet application. LibreOffice automatically detected that I was importing a table and opened the Text Import dialogue box; depending on your spreadsheet software you may have to first save the data in a text file (perhaps .csv) and open it with the correct delimiter. For imports from phpMyAdmin use Tab, for imports from the CLI use the pipe (|) character.
We are probably going to end up with some white-space around the values in our names column so it is important to TRIM() the data when plugging it into a formula. Insert a column next to the names column to accommodate the results. The first step in our PHP script will be to run our variables through a function called clean() which might escape characters and perform some level of input validation. Assuming our first name is in cell A1 this is the formula we will put into cell B1:
=SUBSTITUTE("$sub = clean($_POST['sub']);", "sub", TRIM(A1))
This is effectively the same as:
$b1 = str_replace("\$sub = clean(\$_POST['sub']);", 'sub', trim($a1));
Now drag the formula using the drag icon in the lower right hand corner of cell B1 (or wherever your formula resides) and drag it down the length of the result column. The formula will be inserted into each cell, with calls to A1 automatically incremented to A2, A3, etc. Observe:
Now it is simply a matter of copying the result column into your source code, updating the formula and re-applying it to the lower cells in the column to complete the operation. Changing the formula to wrap the column name in backticks, then in single quotes with a $tring symbol will construct your mysql insert query; changing it to reflect `sub` = ‘$sub’, will construct the body of an update query.
