How to Export One Table Structure in Mysql and Infobright

I have a database with tables in it which have over 300 fields each. I wanted to create a new table like the others but I didn’t have the table structure saved in a file. Due to the number of fields it was impractical to copy the screen after executing a “show create table tablename” command in that database, so I had to find another way.

I found that I could use the mysqldump command to export just the table structure, and this command would probably have worked if my database engine was regular mysql:

# /usr/local/infobright/bin/mysqldump -d -u adminUserName -p'adminPassword' dbName tableName > file.sql
mysqldump: Got error: 1031: Table storage engine for 'BRIGHTHOUSE' doesn't have this option when doing LOCK TABLES

So I looked up how to avoid the lock tables error and found the single transaction flag which made it work in Infobright (which is based on mysql but has some differences):

# /usr/local/infobright/bin/mysqldump -d -u adminUserName -p'adminPassword' --single-transaction dbName tableName > file.sql

How to Convert Excel Spreadsheets to SQL Statements

Last week I told a client he could send me a file in excel and I could import that into the mysql database directly. When I got the file I learned that it wasn’t quite that easy. I had planned to just save it as tab delimited text or CSV, but there was a “description” field which was copied from various webpages (by copying the rendered text in the browser window).

Within the description field there were trademark symbols, copyright symbols, slanted quotes, triple dots, non-standard dash marks, bullets, and even a cross symbol (or dagger) used for footnotes. In addition, there were several paragraphs (including multiple line breaks) in each description field.

The first challenge to overcome was getting the database to handle line breaks properly so I could import the data. I figured if I added a special character before each new record then I could cause the database to ignore the newline characters, but when I tried pipe (|) and three pipes (|||) neither of those worked (I only got one record imported each time). It finally worked for me when I added an equal sign (=) before each new record (after checking to make sure there were no equal signs in the text – if there had been it should have worked by escaping them with a backslash and telling mysql that is the escape character).

In order to insert the equal signs I first saved the excel sheet as tab delimited text, then opened it in notepad++ and used their regular expression search and replace tool to do the insertions quickly. Since I had numbers in the first field of my data, I searched for ^(\d) and replaced it with =\1. If I wanted to spend more time on it I could have made a perl script to go straight from the .txt file to sql statements, but I already had a php file to create sql statements so I wanted to use that.

Once I had the equal signs in place, I used a load data infile command (actually created by phpmyadmin, I just had to make the selections – the local keyword was necessary for permission requirements) like this:

LOAD DATA LOCAL INFILE 'path-to-text-file-here' INTO TABLE 'myTableName' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '=';

I was happy to get the lines imported, but the line breaks were lost and special characters showed up as question marks on the webpage where I displayed the data. So then I went back to my php script used for going from access to mysql from a few years ago and made a few modifications. I first tried using the php htmlentities() command to convert the symbols back to html code but for some reason that didn’t work for me. So through an iterative process I added replace commands for each symbol I found. I wasn’t trying to create all the symbols but to preserve the essence of the text. If you want to convert all the symbols you find you can look at a w2schools reference symbol page for a list – there is also an entity list at w3schools, if you don’t find your symbol in the first list. I also wanted to preserve the newlines so I used the php command nl2br and that worked great. Here is the final code I added for processing each field:

$data_value = trim($data_value);
$data_value = trim($data_value,'"');
if (empty($data_value)) continue;
$data_value = str_replace('™','™',$data_value);
$data_value = str_replace('®','®',$data_value);
$data_value = str_replace('’','\'',$data_value);
$data_value = str_replace('…','...',$data_value);
$data_value = str_replace('•','-',$data_value);
$data_value = str_replace('–','-',$data_value);
$data_value = str_replace('†','*',$data_value);
$data_value = str_replace('“','"',$data_value);
$data_value = str_replace('”','"',$data_value);
$data_value = str_replace('—','-',$data_value);
$data_value = nl2br($data_value);

I also couldn’t just copy the sql queries from the webpage because all the html code was converted back to symbols, so I added this line to write the queries directly to a local file:

file_put_contents("queries.txt",$myQueries);

Then I could open the queries.txt file and have a bunch of sql queries which I could run to populate my database table in mysql. My updated text to sql php script can be downloaded and used freely.

Update: I put the time into creating a perl script to make this easier, so take a look at that if the above process is too much work for you. 🙂

How to Add the Sidebar to WordPress Twenty Eleven Theme Single Posts

This is my first post since splitting off my technical posts into a new blog focused in that area. Part of the process was setting up a new wordpress blog, and of course I wanted to try a new theme, the Twenty Eleven theme. After I began importing a few posts I realized that there was no simple option to add the sidebar to the single post view. I tried messing with the CSS for a few minutes and then realized that there must be plenty of answers from people about this issue already.

I found one that looked promising at http://tricksmommy.com/2011/07/08/add-sidebar-in-single-post-pages-of-twenty-eleven-wordpress-theme/. Unfortunately after following the detailed instructions there the single post page had the sidebar added but the main index page was shifted off to the right. I couldn’t figure out the exact reason why, but the instructions mentioned copying css from another view so I figured it must be a different setup or something. I chose the left sidebar on my site whereas they used the default with the sidebar on the right, so that might be why it didn’t work for me. After removing those changes I came up with a simpler solution (step 1 is the same as the site mentioned above, step 2 is where we differ):

  1. Go the the Theme Editor and select the Single Posts(single.php). Search for the following line: 
    <?php get_footer(); ?>

    Just above it place the following code:

    <?php get_sidebar(); ?>
  2. In the same file, look for the following code: 
    <?php get_template_part( 'content', 'single' ); ?>
					
<?php comments_template( '', true ); ?>

    You’ll want to add another div around the above two lines and give it a new class name so you can set the positioning, like this:

    <div class="singlePostWrapper">
<?php get_template_part( 'content', 'single' ); ?>
					
<?php comments_template( '', true ); ?>
</div>
  3. Next you’ll open the style.css and add a simple definition for the new css class you created above as a wrapper around the post and comments on the single post pages. You can add this at the end of the file: 
    /*Allow room for sidebar in single posts*/
.singlePostWrapper {
  position:relative;
  left:25%;
  width:75%;
}

    Note that the above class definition works well for the sidebar on the left, but if you chose to put the sidebar on the right in the theme settings you can change the word ‘left’ above to ‘right’ and it should work fine.

How to add special shipping by product in Zen Cart

originally posted at http://www.goodfeelingplace.com/how-to-add-special-shipping-by-product-in-zen-cart
I am in the process of migrating a static site (peacemonger.org) to a database driven site “powered by” zen cart. One of the features requested was the ability to add special shipping charges for odd shaped items, like posters.

After doing some research I discovered a page with instructions at http://tutorials.zen-cart.com/index.php?article=190, but it took me awhile to figure out how to implement them for the specific situation I was targeting. After reading the instructions a few times I realized that I had to really create an extra read-only text attribute to make my “special shipping” attribute be selected (as a single radio button) by default. Here’s what it looks like in the admin panel:

zencart attribute controller legend
zencart attribute controller, showing the options to add a required attribute to a product

And here’s what it looks like on the product page (the radio button is selected by default now):

zencart shipping example

Though I just realized that the order is the opposite of what I want, even though I specifically set the order as noted above. Does anyone know why I can’t set the order in this way?

Update: I found a thread which helped me get the attributes sorted at http://www.zen-cart.com/forum/showthread.php?t=87470. The specific change I made which worked was to set the sort order for the attribute names, under Catalog -> Option Name Manager (they were all set to sort order 0 on that page before).

The Essence of AJAX – How I learned it in an hour

originally posted at http://www.goodfeelingplace.com/the-essence-of-ajax-how-i-learned-it-in-an-hour
I was a bit resistant to learning ajax for a couple years because I didn’t see a need for it in the programs I was developing.  However, one day I learned it without knowing it.  I was working with code written by someone else which called a javascript function that invoked a url, then called another function to handle the results of the url.  An example that looks very similar to the code I was working with is at http://www.captain.at/howto-ajax-form-post-request.php (unfortunately that page no longer exists, but I’ve made my own example that you can see at http://www.wilycode.com/simple-ajax-auto-complete-example/ – the details below refer to the not found page but the concepts are the same).

If you look at that code, there are 3 javascript functions used – get (this is called from clicking on a button in the form on the page), makePostRequest (this is the function which calls the url and sends parameters to it via the post method), and alertContents (this function is called from the makePostRequest function when the response is received from the url).

The get function just creates a string with parameters to pass, defines an url, and then passes them to the makePostRequest function.  Here are the main lines of code in the makePostRequest function and what they mean:

  • http_request.onreadystatechange = alertContents; – this says when the http_request.readyState changes, then call the alertContents function.
  • http_request.open(‘POST’, url, true); – this defines what url is going to be called and what method is going to be used
  • http_request.send(parameters); – this sends the parameters specified in the post array to the url requested

There are a few other lines specifying a the header of the request, and a bunch of lines in the beginning that setup the http_request object to work for different browsers.

Then in the alertContents function (which is actually called several times, whenever the readyState changes), once the readyState of 4 is reached (you can read more about readyStates at http://www.yaldex.com/wAjax/DiggingdeeperintoHTTPreadystates.html) the http status is checked.  If the status is 200 (and not 404, 501, 301, etc. – see http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html for a full list with explanations) then the result of that http request (like what you would get if you put that url into a browser address bar and sent the appropriate post parameters in the header – done through a script, not a browser – then checked the page source) is stored into a javascript variable and you can do whatever you want with it.

In that example they just stick it into a span element on the existing page, but you can do many other things.  If you invoke an url which takes as parameters the values of certain form elements on the page, you could return a list of values to populate another dropdown with, etc.  So you can take the example and modify the parameters passed, what the invoked url does, and what is done with the results – then you will have your own ajax function!