Categories
SQL / MySQL Tips and Tutorials

MySQL: Export Table to CSV Text Files for Excel

MySQL tables can be exported to SQL dump file which is basically a text file of SQL queries that can be used to import the table back into database. To export MySQL tables into other formats such as CSV, phpMyAdmin proves to be very handy if you have changed the execution timeout in seconds to zero (so it never times out) – or it won’t work with large tables. However, there’s another way to do this in native SQL query and it works until the end of a very large table:

SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

The INTO OUTFILE command in this MySQL query will store all rows selected from the table mytable into the text file c:/mytable.csv in the form:

"1","Anthony","24"
"2","Rachel","27"

Now you can open and use the CSV file in another application such as Excel and manipulate it any way you like.

If you need another format, just change the query accordingly.

Add column / field headers at the beginning of the CSV file

To add column names at the beginning of the CSV file so each field is identified, use this SQL snippet:

SELECT 'id', 'name', 'age' UNION
SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

That is, adding “SELECT ‘id’, ‘title’, ‘slug’ UNION”. And you will have:

"id","name","age"
"1","Anthony","24"
"2","Rachel","27"

The awkward thing is you have to manually add the field names one by one. For now, I know no option other than this that can add the column names before all the records when you are dumping CSV files from MySQL, except with phpMyAdmin.

Categories
Content / SEO Tips & Tutorials

A few SEO tips

findHaven’t come up with any solidly helpful posts recently so I thought I’d throw in this one. These are some of the things I learned the hard way in the past few years for ranking my websites in Google. Hopefully you would find something new in here.

I rely on SEO to get traffic – in most cases, it yields the best traffic across all possible sources. With good SEO (both on-site content optimization and off-site reputation / link building), it’s hard to not make money. Especially if you are an expert in Internet marketing (niche research, reputation building & management, consumer psychology, landing page tuning, blah blah blah…), it’s even harder to not be rich. Making good money is easy, you just need time.

Don’t use a host that’s POPULAR and CHEAP.

Really popular hosts like hostgator and dreamhost have millions of domains hosted with them. Because they are cheap, spammers like them and Google knows it. I frequently launch new sites and from my experience with dreamhost, after submitting the new site at here, without building any backlinks, it typically takes 1 week or more to get it indexed.

However with hawkhost and wiredtree, it’s totally different situation. Without any initial backlinks, new sites can be indexed in Google 1 day after submission, even when it’s just a blank site with an empty Apache index page. Sometimes I didn’t even have to manually submit the site and it magically and automatically got in Google’s index.

Sites hosted with hawkhost tend to be more stable in Google’s index. However, it’s hard to keep a new site (with merely any content) in Google’s index if it is hosted with dreamhost (and similarly very popular hosts with cheap shared plans). Google would soon get rid of your new site if you don’t keep working on it.

Have a 4 year old website.

Adsense is one of my favorite money makers and my most steady stream of Adsense income comes from a site I built in 2006. I created some nice content (very nice and very original) back then and I just left it there.

I made only $10 a month from the site in the first year and after some very frustrating ups and downs, it’s gradually climbing up. Now, 4 years later, it’s averaging $600 a month. To be honest, I never actually spent much time on it at all. No link building nor frequent content updating no nothing and it’s now making me 600 bucks a month. Not much, but still.

Not only is it receiving large amount of steady traffic, new content are generally very well positioned in front spots in search engine results. The older the site, the more authoritative it can get from search engine’s point of view.

Time is the ultimate distinguisher between builders and spammers. Spammers come and go, hit and run. They are always impatient, looking to make the quick buck with a spammy site. Once they find it’s not profitable, they’ll stop renewing the domain after the 1st year. Google knows this too well.

So most of your sites would not actually start performing in terms of search engine traffic until at least 1 year after domain registration. Yet most people are too obsessed with quick results and never wait that long. They kill their sites just before they can make them decent money.

Be natural.

Google is becoming smarter and smarter. I would never go against them by challenging their intelligence and capabilities to identify spam (or partial spam).

Sites I intentionally optimize in title, description and content keywords as well as off-site link anchor texts never seem to get anywhere substantial. It’s boring and it’s chores. It’s not worth it. I can spend the same time and dollar bill in creating content that’s useful and exciting. Best of all, it’s much more fun that will keep you going!

Duplicate content is a myth.

While being original is absolutely a must in ultra saturated / competitive niches, duplicate content isn’t that big a deal in most niches.

Forget SEO. Start making friends and never stop creating stuff.

I’ve been doing SEO for 4 years and I can finally say, this is the ultimate SEO tip.

Categories
Kavoir & Whatever

Argophilia – Eastern Europe Travel Portal

Phil was one of my best friends on the web and he has been very kind and helpful when I was just starting out. A few months back he invited me to work on a travel project that eventually landed as Argophilia. It’s a shame I was occupied by a lot of chores then and never actually contributed anything substantial to it.

Now you can see it’s becoming something real: http://argophilia.com/

And the news portal: http://www.argophilia.com/news/

They are simply awesome! Both the design and the content are exceptionally great. Hopefully they would take off soon as for now the only travel site targeting the Eastern Europe.