How to import a large SQL file into MySQL

9 11 2009

As a PHP/MySQL developer I’m regularly having to download backups of my databases, and occasionally I have to import them back into MySQL.

The problem is that phpMyAdmin has a file size limit for uploading an SQL file (usually 8MB, depending on your server configuration). Some MySQL backup files are in the hundreds of MegaBytes!

The solution? If you have access to the MySQL command line prompt:

  1. Put your SQL file in a simple path (such as “C:\database.sql” on Windows, or “/home/username/database.sql” on Linux)
  2. Login to MySQL
  3. Create the database (if not already created, or if the SQL doesn’t have a “Create Database” statement)
  4. Select the database (“USE databasename;”)
  5. Run the “Source” command, like so:

SOURCE C:/database.sql
or
SOURCE /home/username/database.sql

This should import the SQL file into the database, it can take a few minutes depending on the size of the file.

I post this here just in case anyone else had trouble importing large database backups into their MySQL databases. Hope this has helped a few people!





Gmail FINALLY removes ‘on behalf of’ from headers!

31 07 2009

Via the Official Gmail Blog

Until now, whenever you send an email from a custom address in Gmail, it would automatically add “From:” in the headers. This reveals your Gmail address to people who you may not wish to see it.

In Outlook and Hotmail, it shows your email as coming from:
myname@gmail.com on behalf of myname@mywebsite.com

Personally, this put me off consolidating my email addresses, because I didn’t want to give my Gmail address out to people, I’d rather they contact me via myname@mywebsite.com.

Well now, finally, Gmail has allowed us to send mail from a custom address without the “on behalf of”!

The way it works is instead of sending the mail out via the Gmail servers, it connects to your other SMTP server and sends it through there. This avoids any problems with spam filters thinking you’ve falsified your headers.

The way it worked before:
custom_from_old

The way it works now:
custom_from_new

For more info on how to enable this option, visit the Official Gmail Blog:
http://gmailblog.blogspot.com/2009/07/send-mail-from-another-address-without.html





Important Info Re: IT Crowd Series 3 DVD Easter Eggs

3 06 2009

I’m writing this as a personal favor to anyone working on decoding the IT Crowd Series 3 DVD Easter Eggs. There’s a lot of incorrect info going around, and although I have no interest in the hidden messages myself, I feel I should point out a few important things.

Firstly, my website www.reynholm.co.uk has NOTHING to do with the DVD. It’s a personal fansite, it’s been around for several years and I have no affiliation with Channel 4 or the IT Crowd. Please don’t email me asking for the password to the intranet, it’s easy to find. In fact I believe it’s on the Wikipedia page. And most of the sequences of numbers on the site (the password file, etc) have no meaning. Either way, nothing on my website will help you with the Series 3 DVD.

Secondly, and this is the most important one, all the IT Crowd sites are hosted on the same web server.

The IP address is 89.16.172.246

If you visit the IP address on it’s own, you get a blank placeholder site that says “The cake is a lie” and “Coming soon…”. http://89.16.172.246/

Here’s a list of the sites that I could find sitting on that server:
http://www.hokerspoker.com
http://www.itsveryblue.com
http://www.friendface.co.uk/
http://www.fiendface.com/ (Yes, FIENDface, without an ‘R’)
http://www.ilovewillies.com/
http://www.howlonghaveyougot.com/
http://www.bluffball.com/
http://www.bluffball.co.uk/
http://www.cuke.me/
http://www.cuke.co.uk/
http://www.reynholmindustries.co.uk/
http://www.ilovewillies.com/
http://www.itcrowd.com/

As with all these sites, if you go to a page that doesn’t exist you’ll get the standard “Coming soon…” and “Cake is a lie” error page. The same thing will happen if you replace “www” with something else. “Coming soon…” should be interpreted as actually meaning “Page Not Found”. I can pretty much guarantee that http://fakefakefake.bluffball.com/ is not just “coming soon”.

I hope this helps, it’s just basic knowledge to anyone who has ever run a web server or setup DNS :)





How to use the new Amazon AWS Signature in PHP

9 05 2009

I’ve just spent 3…no…4 hours messing about with this, until I finally got it to work.

The new Amazon Associates (or as it’s to be renamed, Amazon Product Advertising API) requires that by August all requests include a Signature.

Documentation here:

http://docs.amazonwebservices.com/AWSECommerceService/latest/DG/rest-signature.html

Here’s how I implemented it in PHP (I had to make some changes to support multiple keywords, such as ‘dan aykroyd’. The space was causing huge problems after being urlencoded and decoded, parsed, hashed, etc).

define('AMAZON_ID','YOUR AWS DEVELOPER CODE');
define('SECRET_KEY','YOUR AWS SECRET KEY');
$keyword = "steve martin"; // The keywords you're searching for
$aws = array();
$keyword = urlencode($keyword);
$url = "http://webservices.amazon.com/onca/xml?Service=AWSECommerceService";
$url .= "&AWSAccessKeyId=".AMAZON_ID;
$url .= "&Operation=ItemSearch";
$url .= "&SearchIndex=DVD";
$url .= "&Keywords=".$keyword."";
$url .= "&ResponseGroup=Small,OfferFull,Images,Reviews,ItemAttributes,SalesRank";
$url .= "&Timestamp=".gmdate('Y-m-d\TH:i:s\Z');
$url = str_replace("+",urlencode("+"),$url);
$url_a = parse_url($url);
$url_a['query'] = str_replace(',',urlencode(','),$url_a['query']);
$url_a['query'] = str_replace(';',urlencode(':'),$url_a['query']);
parse_str($url_a['query'],$params);
uksort($params, 'strnatcmp');
$qstr = '';
foreach ($params as $key => $val) {
$qstr .= "=".rawurlencode($val);
}
$qstr = substr($qstr, 1);
$qstr = str_replace('%20',urlencode('+'),$qstr);
$qstr = str_replace(',',urlencode(','),$qstr);
$qstr = str_replace(';',urlencode(':'),$qstr);
$sig = "GET\n"
. "webservices.amazon.com\n"
. "/onca/xml\n"
. $qstr;
$sig = base64_encode(hash_hmac('sha256', $sig, SECRET_KEY, true));
$sig = str_replace('+','%2B',$sig);
$sig = str_replace('=','%3D',$sig);
$params['Signature'] = $sig;
$p = array();
foreach($params as $k=>$v) {
$p[]=$k."=".$v;
}
$qstr = implode("&",$p);
$rebuilt_url = $url_a['scheme']."://".$url_a['host'].$url_a['path']."?".$qstr;
$aws['url'] = $url."&Signature=".$sig;
$xml = file_get_contents($aws['url']);
echo '';
var_dump($xml);
echo '';

I know what you’re going to say, why didn’t I do it ‘this way’ or ‘that way’?

The problem is that you don’t just encrypt the query string and tack it onto the end of the URL as a Signature. You need to split the query string up, re-sort them, and then join them back together again first. And if the query doesn’t come out looking exactly the same as the original, then the Signature won’t validate. I tried dozens of other ways and in the end I had to go for the most longwinded and seemingly illogical method.

I *could* probably waste time finding a way to condense this and make it simpler, but I’ve already lost my entire night because of this. And at least I know it works.

Update: In the end I used a modified version of Ulrich Mierendorff’s code from here:
http://mierendo.com/software/aws_signed_query/
It works with all the queries I can throw at it, including searches with single quotes.
View it in action here:
http://www.bluesbrotherscentral.com/profiles/willie-big-eyes-smith/
(The Amazon box at the bottom of the page)
It looks like I was on the right track, if I had the time I would have fixed the bugs myself (but why reinvent the wheel?)





Amazon.com can be so hopeless sometimes…

8 05 2009

For the record, I love Amazon.com. I order dozens of CDs and DVDs through them all the time.

On my site I wrote a function to use Amazon Web Services to download product data for actors. For example, you visit Dan Aykroyd’s page and all his DVDs appear at the bottom. Simple enough.

Then today I get this confusing email from Amazon:

In addition to the new name, signatures will be necessary to authenticate each call to the Product Advertising API. This requirement will be phased in starting May 11, 2009, and by August 15, 2009, all calls to the Product Advertising API must be authenticated or they will not be processed. For pointers on how you can easily authenticate requests to the Product Advertising API, please refer to the developer guide, available here.

Sounds fair enough to me, I figure I just have to add a line of code to my script or something.

So I read the documentation:

http://docs.amazonwebservices.com/AWSECommerceService/latest/DG/rest-signature.html

TEN Extra Steps!!!

I’ve now spent an entire hour trying to update my code. It works fine without the Signature (and it will continue to do so until August), but as soon as I add the Signature it gives me a 400 Bad Request.

According to the documentation this is the crucial line:

base64_encode(hash_hmac('sha1', $sig, "1234567890", true)) 

Anyway I’ve had enough for now, I’m going to drink something.

Update: Well it turns out that the part of the documentation they don’t include is that I need to get  a Secret Key, they didn’t mention that “1234567890″ is a fake number.

Now I have to see if using my Secret Key will fix it…





Blues Brothers Central v2.0 – 97% Finished!

12 03 2009

I’m now about 97% finished rebuilding Blues Brothers Central (www.bluesbrotherscentral.com)

It’s a massive overhaul I began in Feb 2006. The site I’m currently running has existed since 2003, and although I’ve constantly added new content/features and redesigned it several times, it’s essentially the same framework.

The new site is a complete rebuild, everything has been changed, from the logo to the color scheme. Even the URL format has gone from the ugly:
/album.php?album_id=7
to the much more friendly:
/albums/made-in-america/

So, what’s left to do and why haven’t I finished it yet?
Well there’s a couple of incredibly boring data entry tasks I have to finish. One of them was adding all the hundreds of gigs the band has played since the 1970’s, I’ve almost finished these but there are many I won’t bother adding. On the current site people have submitted gigs with useless vague information, like “I think they played in Belgium in 1990, or it could have been 1991″. Because I obviously have the time to waste hours trying to track down when it happened…
Over time more people will submit gigs and this time they’ll be able to be more specific, so if there are any missing then they can be filled in later on anyway.

There’s a MASSIVE picture gallery containing around 1000 photos, these will need to be tagged into the correct categories, however this is something I can ask my members to help with.

I’ve got plenty of products to add to the Blues Brothers Store, and a couple of bootleg albums to add. Plenty of multimedia needs to be uploaded and tagged.

I need to also finish:

  • The Blues Brothers Revival page
  • Blues Brothers 25th Anniversary page
  • Movie Cars (If I don’t finish this I’ll get flooded with annoying emails

I’m also working on a new section that I’m not ready to reveal, mainly because so far I’ve only done the artwork and some basic designs. The programming is only about 5% finished at this stage.

And finally there’s a secret project I’m working on to go with this site, it’s codenamed “Project B12″, it has it’s own separate website. I may launch that along with BB Central v2.0, or I may hold off until the site has stabilized.

A decision I made a while ago was that I won’t be running any banner rotations on Blues Brothers Central. The main reason was that it slowed down the pages, it was another piece of software I had to keep updated, and it wasn’t really getting enough interest. It’s a shame that I won’t be able to promote the websites of my friends and members, but in the end I have to think of the bigger picture. I’ll only be running Google Adsense and Amazon Associates advertising, along with some small promotions for other affiliates I’m involved with.

Finally, here’s some artwork I created but ultimately never ended up using, it was to promote the new site:
New Site Coming Soon

That’s a photo I took myself while in Chicago in 2005, I’ve just modified it heavily and added some new signs. I hated it, but it was enough to inspire me to design something 100 times better (and completely different).
Here’s the original:





Will you crazy people leave me alone?

3 03 2009

So as you all know I built www.lcarsdeveloper.com back in 2002, when I used to be a bit of a Star Trek fan (something I lost interest in very quickly).

At the time I was teaching myself Flash, and I wanted to take on a big project to test my Flash skills and improve on them, so I started messing around with the computer interface from Star Trek, which is known as “LCARS”.

In 2005 my site merged with another site called STEngineer.com, a site that was trying to build an operating system called “LOSS” (LCARS Operating System Shell). I gave control over to the STEngineer owners, but after writing their name all over the site and redirecting their domain name to mine (as well as breaking half the pages) they left and never returned.

Eventually I closed down the forum on the site because it was being overrun by spammers and I couldn’t be bothered moderating it. I had offers from people who wanted to take over the forum, I was even prepared to give them all the posts that were made, but nobody had any serious offers (just amateurs who would have given up 2 weeks later).

In the end I just stopped touching the site, uploaded all the source code (Flash FLA files) and said “do what you want with them”.

A lot of people took me up on that offer, the best example is www.lcars.org.uk, which has essentially expanded on my work and built another site out of it. I’m glad to see there’s a decent credit in the footer of the site, although to just say “thanks for designing the menu” is a little bit of an understatement when he’s kept the original logo and all the animations too. I’m embarrassed to see he’s even posted my hideously inaccurate animation here, the one I only made as a placeholder and never got around to finish.  And these:
http://www.lcars.org.uk/lcars_m.htm
http://www.lcars.org.uk/lcars_o.htm
http://www.lcars.org.uk/lcars_p.htm
http://www.lcars.org.uk/lcars_r.htm
http://www.lcars.org.uk/lcars_x.htm (this one he finished)
http://www.lcars.org.uk/lcars_n.htm
http://www.lcars.org.uk/lcars_s.htm (I hated this one)
http://www.lcars.org.uk/lcars_aas.htm (not sure if this is based on mine)
http://www.lcars.org.uk/lcars_u.htm
http://www.lcars.org.uk/lcars_zh.htm (From my LOSS8 demo…I think?)
http://www.lcars.org.uk/lcars_w.htm (Never finished this one)
http://www.lcars.org.uk/lcars_t.htm
http://www.lcars.org.uk/Enterprise.htm (The big animation half way down with the scrolling text, although he finished it)

Don’t get me wrong, I’m thrilled that this guy has gotten so much out of my work, and it’s obvious from the hundreds of animations he’s made on his own that he has his own talent, he’s just used my work as a shortcut for his own. I think that’s fantastic, it’s exactly what I posted the files online for!

But I’m getting very tired of getting emails from…lets face it…morons who think I somehow stole his website.

Like this gem:

Why do you own ADGE’S lcars web site if you are not going to do anything with it.
Some of my friends wanted a new screen save of save trek. but so far there are no new changes. if you are not going to comtrubute to the star Trek then sale the web site to someone who will do something with it.

I mean come on, I don’t OWN his website! I have nothing to do with, aside from the fact that his menu system is 90% my work.
And since when did I make screensavers? I’ve NEVER made a screensaver, and I never even tried to!
If I’m not going to contribute to star trek I should sell my website to someone who will? Well I’ve already given the whole thing away for free, who would possibly want to pay for it when they can do what Adge did and click “DOWNLOAD”?

Anyway that’s my rant for the day. Stop emailing me.





Drunk Programming

26 02 2009

Programming is more fun when you’re drunk. I’m actually enjoying it again for the first time in months! And all it took was 2 glasses of bourbon and 2 glasses of Irish Whiskey! :)

This function I just wrote is so awesome, seriously.

Now I have to fix someone else’s website before I can go back to working on my own. Stupid hosting company has messed up something. Now when you get a UNIX_TIMESTAMP() from the MySQL database it gives you the wrong timezone, so when I convert it back into a date it comes out as 1 day before I wanted!

So it goes into the database table as 2008-10-13, it comes out as 2008-10-14!

They probably changed the timezone on the servers or something stupid, so now I have to rewrite all the functions.

This is why I host my own sites in Texas instead of Australia…

 

Man…Firefly was one hell of a great show. I make sure to watch the whole series and movie at least once a year, half way through it again right now. There should be more sci-fi.





How to ‘Google’ Your Life!

9 02 2009

This is something I’ve been thinking about for quite a while, how you can use Google and their associated products to get more out of your life online!

http://www.bluesbrotherscentral.com/forum/computers-video-games-technology/7452-how-google-your-life.html





Friendface.co.uk Text Deciphered!

23 12 2008

If anyone was watching The IT Crowd last week, you would remember the whole plot revolved around a Facebook style website called “Friendface”.

Well there is a real official website at www.friendface.co.uk!

Except when you load the site you get a Reynholm Industries error message (I had nothing to do with this, I assure you!)

Well I’ve had nothing better to do tonight, so I  deciphered the blurry text on the page for everyone who was curious what it says. This is genuine, I did this by matching the symbols to where they appeared in the rest of the paragraph. The little upside down V with a dot above it is an ‘A’, and so on.

Here you go:

The text from www.friendface.co.uk, deciphered!

The text from www.friendface.co.uk, deciphered!

Update: I just found out that someone else beat me by 4 hours, it’s on Graham Linehan’s blog

Still, we’re both happy that someone else could confirm we got it right :)