Replace Test urls in Wordpress to live using phpmyadmin

Just a quick help snippet for those of us who test wp websites on a test domain before moving them to a live url.

This issue arises when using a test domain for building a site and uploaded images, pdfs etc link to the test domain. When you make the website live the links still point to the old url in each and every post and page. Here is a quick SQL snippet to replace those old urls.

    UPDATE wp_posts
    SET guid =
    REPLACE(

        guid,
        "http://TestDomain/",
        "http://www.LiveDomain.com/"

    );

 UPDATE wp_posts
    SET post_content =
    REPLACE(

        post_content,
        "http://TestDomain/",
        "http://www.LiveDomain.com/"

    ); 


update wp_options
set option_value =
replace(
       option_value,
       "http://TestDomain/",
        "http://www.LiveDomain.com/"
); 

Joomla website offline login issue on IIS with sh404sef

QUICK how to fix sh404sef on IIS - just in case
1. open up webroot\components\com_sh404sef\shPageRewrite.php
2. Find and replace every instance of #__sh404SEF so it matches your db table. The default is lower case #__sh404sef. OMG WTF! it works on Linux and Apache, why not windows????

Venting and Explanations
Horray! another 3 hours learning about sh404sef. This time a site needed to use the joomla website offline login form. This particular site is hosted on win IIS over at Outstanda hosting, they actually had really responsive tech support which was nice.

Support was needed to figure out how to modify folder permissions in their control panel. For reference you have to change it in the FTP manager from the control panel.

As soon as the website offline was enabled the website was throwing a "service temporarily unavailable" which is INCREDIBLY annoying to figure out, esp because this server would not output error messages. I manually stepped through the files tracing it to the shPageRewrite.php file to the first line for the db call.

Zipcodes to go radius search MySQL table

Recently I have finished a customized zip radius search and used the one from Zipcodes to go. Unfortunately they only have csv, xls, and mdb formats for the database. To make use of their data you need to import it into mysql, but first you must create the table which phpmyadmin won't do automagically (please tell me im wrong by the way).

First you must create the table.

I tried to be as lazy as possible and tried to export the mdb to mssql then to mysql but it didn't really work and took waayy too long so i ended up having to write out the create table anyway.


SQL to create the gold table
Note: Remove the ID field until after you import all the data from the csv, otherwise the field lengths wont match!

CREATE TABLE IF NOT EXISTS `zipcodes_full` (
 `ID` int(12) unsigned NOT NULL AUTO_INCREMENT,
 `ZIP_CODE` varchar(255) NOT NULL DEFAULT '' ,
 `CITY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_ABBREV` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_POPULATION` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_HOUSING_UNITS` varchar(255) NOT NULL DEFAULT '' ,
 `LATITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `LONGITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `TIMEZONE` varchar(255) NOT NULL DEFAULT '' ,
 `DAYLIGHT_SAVINGS` varchar(255) NOT NULL DEFAULT '' ,
 `Returns` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG AGI` varchar(255) NOT NULL DEFAULT '' ,
 `Refunds` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG Refund` varchar(255) NOT NULL DEFAULT '' ,
 `Single` varchar(255) NOT NULL DEFAULT '' ,
 `Married Joint` varchar(255) NOT NULL DEFAULT '' ,
 `Head of Household` varchar(255) NOT NULL DEFAULT '' ,
 `<30` varchar(255) NOT NULL DEFAULT '' ,
 `30_44` varchar(255) NOT NULL DEFAULT '' ,
 `45_60` varchar(255) NOT NULL DEFAULT '' ,
 `>60` varchar(255) NOT NULL DEFAULT '' ,
 `TV_Designated Market Areas` varchar(255) NOT NULL DEFAULT '' ,
   PRIMARY KEY (`id`)
)  

Second:

Importing the data is a freaking pain through phpmyadmin. I ended up using the commandline on my local machine and now i am trying to use BigDump php to migrate it to the server.

Use the below script in the mysql commandline to import the csv into the db table. It worked wonderfully 4.6 some seconds. Great Now how do i get it to the server....

Note i dont have mysql mapped in my environment variables in windows so i needed to copy the csv file into the mysql bin directory.

load data local infile '5-DIGIT-ZIP-GOLD.csv' into table zipcodes_full fields terminated by ','  ENCLOSED BY '"' LINES TERMINATED BY '\r\n'