Stopping, starting and restarting a MySQL server

It's been a long time since I had the need to do this and had to look it up.

To stop a server use

service mysql stop (or could be service mysqld stop)

To start a server use

service mysql start

and to restart a server

service mysql restart

Easy!

Resolving the Missing Trailling Slash on a URL with Apache Server

What's the difference between

http://www.sjinfosystems.co.uk/blahblah

and

http://www.sjinfosystems.co.uk/blahblah/

Quite a bit actually. As far as an apache web server is concerned the first one is trying to access the file blahblah and the second one it trying to access the directory blahblah.

Obviously this can cause significant problems - like your punters being unable to access your content - if they miss off an essential slash. But this is all to easy to do, so how do we get the server to sort this out for us.

For Apache 1.3...

Create a .htaccess in the root of your site with the following

RewriteEngine on
RewriteRule ^(([^/]+/)*[^./]+)$ http://www.yourdomain.com/$1/ [R=301,L]

This simply adds the required slash on to any url that does not have one already or does not have a dot (.) in the final file part of the url i.e. for documents such as pdfs etc.

For Apache 2 (2.0.51 onwards) simply use the DirectorySlash directive.

Importing Spreadsheet Address Data in Google Maps

Recently I've been looking into schools in Monmouthshire for my two boys and wanted to put them onto map to get a rough idea of where they are. Putting little stickers on a paper map is so last millennium, but how do you take a list of addresses and quickly plot them onto Google Maps without having to manually look up each address?

The solution lies with this really great website http://www.batchgeocode.com/ which will take a load of tab delimited address data (plus any associated info, email addresses, urls etc) and plot them onto a google map for you. The instructions on the site are clear and simple. You arrange your data in the format defined by the site (it's an excel spreadsheet) and then just paste it into the textbox on the page. From there you can validate your data is good and adjust various settings to get the data parsed just right. Once done you end up with a google map with placemarks for your addresses. The placemarks contain your address data plus any other additional info you included. I put the telephone number, headteacher's name and links to the school's inspection reports for example

The only slight problem with this is that the info is contained within the batchgeocode system rather than the google maps system. http://www.batchgeocode.com/map/2c41429f9e6c62bb9267d4ef3df91559

But there is a way around this. Once completed in batchgeocode you can export the info as Google Earth KML data. This can then be imported into My Maps in Google as detailed here

http://www.askdavetaylor.com/how_to_import_xml_kml_data_file_google_maps.html

There you go, a load of addresses converted into placemarks on a google map.

http://maps.google.com/maps/ms?ie=UTF8&hl=en&msa=0&ll=51.703204,-2.71637&spn=0.815336,1.674042&z=10&msid=116029501273476060723.000480596d2e17acaf343

I don't make any guarantees as to the accuracy of this data as it's purely for my own use.

Heavy Goods

If you're a HGV driver, have a look at this site. http://www.drivercpcuk.co.uk

How to import an excel spreadsheet into a mysql table using phpmyadmin

This post assumes familiarity with the use of Microsoft Excel, PhpMyAdmin and copying and pasting. It's more about the process of juggling your data around to get it into an importable format than a step by step "press this next" guide.

Problem : Import a large multi-column spreadsheet into a mysql table.

Not as straightforward as you might think. MySQL can import a comma separated variable file, but what if your content has got commas in it?

The answer...

1. Using phpmyadmin create a table with corresponding fields to match the columns of your spreadsheet.
2. We now need to export the data in the spreadsheet into a .CSV format suitable for importing into MySQL.
3. If your data does not contain any commas load up your spreadsheet in excel and skip to step 11, skipping step 13.
4. If your data does contain commas we need to replace them all with some other combination of unusual symbols as Excel will only export with a comma delimeter and we do not want to confuse delimiting commas and language commas.
5. In Excel create a new (blank) spreadsheet.
6. Go back to the spreadsheet with the data in and 'Select All' by pressing Ctrl-A.
7. Copy the data by pressing Ctrl-C
8. Go to the blank spreadsheet and from the Edit menu select Paste Special.
9. In the Paste Special dialogue box select to only paste 'values'. All other entries can be left at their default settings. This will paste only the values into the new spreadsheet and leave out all formulas etc.
10. Now do a "Replace" by pressing Ctrl-H. Enter a comma in the 'Find' box and a double-caret ^^ in the replace box. Click "Replace All"
11. Now do a File, Save As and change the file type to .CSV and save the file in the folder of your choice.
12. Open the folder and load the .CSV into notepad or similar. Do a Replace (Ctrl-H) and in the find box put a comma and in the replace box put a semi-colon. Then click replace all. The file is now delimited by semi-colons.
13. Next another replace and in the find box put a double caret (^^) and the replace box put a comma. Then click replace all.
14. Then press Ctrl-S to save the file again. We now have a semi-colon delimited file with comma punctuation marks back where they were.
15. Now back to phpmyadmin. Browse to the correct table in your database and click on the import tab.
16. On the import tab, click the browse button on the File to import section and locate your CSV file.
17. Deselect the Partial Import check box
18. Now check the CSV using LOAD DATA check box. I find this works best. You may need to fiddle around with the other options to get it working
19. The default values shown should now work. These are Fields delimted by ';'; Fields enclosed by '"'; Fields escape by '\'; Lines terminated by 'auto'; column names blank and 'Use LOCAL keyword' checked.
20. Click 'Go'. Job done.

All quiet

Three proposals currently in with prospective clients. No word as yet. All hell will break loose if they all come back at the same time!

Update : Finally got a response beginning of January 2010! Still no content though. Luckily was busy elsewhere.

Stats

Working on compiling useful web statistics for my sites. Info such as page views, number of visits and referrers can give a real insight into the two crucial bits of information about your site. How your site is being used and how people are finding you.

Are you paying an expensive subscription to a business listing site? If so, you need to know exactly how much traffic they are driving to your site. If that's not much at all then you can question the value of their service to your business.