Thursday, 30 August 2007

The SQL world outside MySQL - Well I never knew

I have been using MS SQL Server this week in my new job. I believe several things of this before I even touched it, some bias some guesses.
A. I do not like most MicroSoft work. Some times they have done something usable like Word 5 but then they add a lot of rubbish.
B. I have seen it lock up alot in a dev environment when I have not seen the same of MySQL.
C. I would rather have linux live servers not Windows.
D. I have not used it much.

I do not think MySQL is the best database but it is damn good with websites and PHP for my sites so far. It might not be what I'd choose for a bank for example. But straight data it is fine.

I have been using it to add stuff to a Intranet admin and to a website. Now the connection etc is happening in a library so I have not seen anything getting it to PHP it is just through SQL in the gui tool that I am learning.

Now the first thing is I do not seem to able to be lazy and edit the tables phpMyAdmin style. But I think I have seen that done before. I have liked that you can have lots of selects in the same window and running it brings a table for each. And highlighting it runs just that command. But then I came to date fields. It seems that the server returns the date column as "27 Aug 2007 10:20:22" and it does not have simple formating in the SQL to getting to return differently! Why is it returning words in the date format when I have not asked for it. There was a small extra bit of code that I was shown that you passed a single format id to to return a different format, not very nice though. So with a couple of functions it would return "27.08.2007 10:20:22.234" which is more what I would like.

The SQL is TRANSACT-SQL so I know I have to learn more syntax. That I do not mind so much. But when I asked if I could format the date I was told use PHP to do it. That seemed like a fair answer but it is a database dates are a key part. The thing that annoyed me about that was that feels like a Microsoft developer response, "It just is that way, use another tool to hack around with the result." I think it should work better.

I had something else too, just have to remember what.

I Remember GROUP BY in SQLServer you have to have all the fields in the SELECT also in the GROUP BY. Now on MySQL I can happy group by something and then pull out all other data in the select now limited.

I learn this from my manager and then on the podcast on the same day, that MySQL has a mode to behave more like a traditional DB and not so forgiving. This "MODE" or TRADITIONAL also then has the option to turn on all fields for a group by must be in the select. Then from that I read in the manual, from his links. SQL Mode in the Manual


Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.



Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.1.11, this mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

Also that in this traditional mode when inserting data for columns that are tool long will cause an out of range error, rather that truncating. This might be good for one insert but several on a non-transactional table could mean you only add half the data before the error.

So I learned something I did not know before about how data is treated on insert. I might not think the Group by is right and the Date rubbish but I learned something new.

Wednesday, 29 August 2007

Google Maps now list user results from MyMaps

I have just noticed that Google Maps is now listing the data added from My Maps (google users saved public maps).

I noticed this when I searched for the restaurant I just did a site on and the results include the cutom map I had made for their website, and my result is first for the user content.,+winchester&ie=UTF8&z=13&om=1

So at the bottom of the Search results for Local business matches there is a link for "See user-created content" and you get a list of all pins added by users that match the search.

So Red pins for Google and then click and see blue pins from users

This makes another great SEO/SEM option to get exactly the copy you want into the local restults and AT THE MOMENT the only things commonly listed are people giving driections to friends. I.e. Small coverage and not so specific, and as we all know that makes a good target to give good data.

At the moment you do have to click to this extra content, but there are few results and maybe other searches on google services mix the results on the same page?

So off to create map locations of all the sites I work on and key locations for their content....

Monday, 27 August 2007

People value listings

It has been so nice and easy to promote the restaurant site I have just done. There are hundreds of sites listing and talking about food, drink and listings on maps.

Most of the sites I have worked on are hard to find back links and communities for, they are magazines and media brands. They talk about other people but are only talked about by business. To work with a brand that offers a physical product is a nice change. Also a product that fits into listings just gets eaten up on the internet.

As I see it the main avenues are:
  • Listed in directory listings
  • Listed in guides listings on a subject
  • Reviewed in press
  • Reviewed by people
  • Enter awards for mentions
  • Write a blog around the restaurant or an aspect and get links to that.
  • Guest in other media or properties, like magazines or judging panels
  • Write a column on another site
  • Make some content that people can syndicate on there site.
So on to the searching an linking and back to the comments later.

Very Offline 2.0 or too much money

I got a nice summery gift card in the post on thursday wishing me good luck in my new job. I thought that was nice as none of my friends had yet. And it felt like it was from a friend. But it was from an employment agency. I have got my job with Click with Technology though an agency, but not this agency.

It was a nice card like for a birthday or some other event, not a mass printed company card, and not a postcard. It was even hand written to me. But why would the nice lady at Milk send me a card? They are not getting any money from me and Computer People were very nice and helped with the job but they did not send me a card. So why?

I think it is one of two things Offline2.0/Web2.0 or too much money. They have been sending lots of personal emails out with job positions and even a party to meet people and find work. So looks like they are going after lifetime contacts. I mean sending a card for 1 or 2 pounds for the chance of a referral or working with them again is probably alot cheaper than other methods. It is certainly more personal and interactive. That card has sat in my kitchen on a pile of papers for a few days, before being filed (I could not quite throw it away, it was nice), that has got to be alot more brand visibility than an email i got last week that i will never go back to with job offers.

So probably a good idea or one for a company throwing money around when dealing with one-to-one relationships not just a mail shot. I do not think I will use Milk as most of their jobs were not near me, but there is a good chance I'll talk about them, like here. I have been called by 20-30 or more agencies (or job site surfers doing match making) in 2 months so if you stick out I guess it helps.

Good luck to them

Wednesday, 22 August 2007

The Black Rat site is now live

I have just finished the website for my brother restaurant The Black Rat.
It was interested to make a site on using their controls. You do not get much you can customise but I got the domain to map and email set up for them on google apps. So seems to be a quick way of getting people an online presence while they work out what they want.

It would be nice if you could add more code to the pages as it stripped out the google map i added. So that seems to be where versions of the sites come in. Also you can not add your own google analytics as they use it so you get very basic stats. Also I am not sure if page rank is affected by being part of a hosted site. we will see.