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.

CMS

Got a prospective new client with a requirement for some self management. Looking at drupal again as a possibility for a CMS due to its ease of use.

Exporting a psd to pdf for print

Been updating the business card design for Sarah Bowden at smart-temp. I created the original photoshop psd as portrait document 6.1cm by 9.1cm standard business card size. To get the required printing resolution that needs to be at 944.882 pixels per cm (or 2400 pixels per inch in old money).

In photoshop cs2 I then selected Print with preview and selected the following options.

Then click page setup and then printer. In the printer box choose adobe pdf as the printer tne then click properties.

In the Adobe PDF settings tab choose High Quality Print as the default settings. And away you go.

(Images for this to follow soon!)

Feedback

Still waiting on feedback on my latest sites before I can proceed. In the meantime, enjoy some Duffy

Good clean fun!

I've been working on another project for a Newport based accountancy firm. I've gone for a very clean design on a white background. Still a work in progress so some of the content doesn't match (I copied it from this site) but you can get the idea. http://root.sjinfosystems.co.uk/oneonone

This is amazing!

A glimpse of the future? Apparently Microsoft have this sort of interface up and running in their labs. http://ohhello.tv/index.php/work/view/microsoft_sustainability/

Newport City Truck Stop

Next project. Newport City Truck Stop. Have already done a holding page for this one http://www.newportcitytruckstop.co.uk but need to crack on with the site proper. One of the main considerations for this is that the site should be viewable on a mobile phone. Interesting. To me.

Going Live!

Sarah pleased with the work, so the site has gone live http://www.smart-temp.co.uk/. Sarah's feature should appear in the Western Mail today http://www.walesonline.co.uk/ so will be interesting to see if it impacts the number of visits the site gets.

Update @ 2:30pm : Feature not published today. Still, at least the site is ready.

Footer crisis

Got into a right mess with footers on the new smart-temp website. No matter what I did with the positioning the footer ended up either right at the top of the screen or halfway down it.

After a good hour of struggling with it I finally realised that the footer was set to appear below the last item on the page, but in the actual html was below a fixed size absolutely positioned item. So it was always in the same position on the page. Moved the absolutely positioned item elsewhere in the code so that the footer was at the end of the html after the main content. All working fine now.

Passed back to Sarah for checking. Waiting for response now.

Destroying a session completely in php

Interesting snippet of code. To completely destroy a session and all associated data including any cookies

session_start();

//Clear out all session data
$_SESSION = array();

//if a cookie is set set the cookie expiry date to yesterday
if (isset($_COOKIE[session_name()])) {
setcookie(session_name(), '', time()-86400, '/');
}

//destroy the session
session_destroy();

Smart Temp v2.0

Here we go with the redevelopment of the smart-temp site. Quite a bit of reworking required on the graphical side. New background fade and new buttons. Using a different technique with the buttons using css to apply the graphical highlight rather than a succession of javscript image hovers. Much neater and simpler. The test site can be seen here http://root.sjinfosystems.co.uk/smarttemp2

After liaising with Sarah it seems that I wasn't keeping close enough to the new brief. My imagination was running away with me. So back to basics. No background fade, so we're back to plain white. No gradient across any of the major graphical elements (the flowers and site title) so back to a plain dark purple there. Despite my reservations, it's quite striking. I think I like it!

There is a problem though. Lime green. Hmmmn.

Offline

A faulty internet connection chez Jones this weekend stopped most design work in its tracks. Smart-Temp updates must be done today really.

Crisis Averted!

Urgent phone call from the wife. Morgan Denton Jones is a limited liability partnership (LLP) not an LLB which is something completely different altogether. Fixed it now. All is well. Lawyers eh?

It's live!

New website went live today. Very pleased with it and have had some very positive feedback so far. Admittedly that was from my wife, but nevertheless very satisfying!

Next in the pipeline is a major update for Sarah Bowden @ http://www.smart-temp.co.uk ready for a feature on her that will appear in the Western Mail (http://www.walesonline.co.uk) this coming Wednesday. Guess that's my weekend sorted!