To offer a web site search facility, the pattern matching function [ select * from table where title like %news%] is often used, only for a lack of knowledge of Mysqls’ natural language query feature. Like Google.com, the optimum is to provide your users with a search feature whereby users type anything, a single word, or a natural language phrase [e.g. iphone 3gs available in san francisco].  Here’s how to set it up in Mysql.

1. create your table
create table as always – example fields:  title varchar(80), description longtext.

2. add the full text index via SQL or using a tool like navicat
alter table add fulltext(title,longtext)

3. A tip: keep in mind, you can’t just add a single record and run a test search.  Several records must be present in the table for the keyword matching facility to work.

For more information, check out mysql.com reference documentation.

I’m working on 2 web projects, both with location-specific requirements.  Hesitant to pay for a solution, I searched for free solutions. Came up with a few free sites. One offered  a url query ip look up. Then  I fell into a site offering a free ip location database for download.  I thought the free database would work perfectly. So I got the table into mysql, built a php class,

IP Geolocatoin

iplocation, and tested here in the san francisco bay area. I had a buddy test in New Jersey. It worked at my place, but not in Jersey.

I finally decided to buy a database- I’m in the process of building the database, and modifying my php class. When I’m done, I’ll make the class available here. No charge, with hopes it’ll help someone with the same requirements.

#

mysql-php

It happens that I’m working on 3 websites simultaneously, so I need to work efficiently.  When it comes to writing php for data inserts and updates on mysql tables (big and small), time is consumed typing out all those variables, and single quotes.  My solution around this?  A class, xSql.  It makes use of the sql catalog feature.

I created 2 primary functions – an insert and update. All is required is to put field names, and values an array. Then the functions do the work, by deciding what the field type is, inserting the single quotes where necessary, and building the insert or update sql. Once the sql statement is constructed, mysql_query is executed.

There are 2 select functions as well. One that grabs all records based on a table name and where field, and a function that grabs specific fields from a table based on a list of fields, and a single or multiple where fields.
And that’s it!

It has afforded me great speed in assembling database operations in my php scripts.  Now on to automatic form generation and validation!

Part 2 of the web site checklist centers around Self-Hosting your web site.  Take a look at the check list and carefully consider each step. I’ll develop this into a parts list, as well as a task list in coming articles.

When I setup a web site, whether for business, or fun, keeping the budget low is key.  The checklist, the lead-off to self-hosting a web site article, gives you an idea of the things you’ll need to keep in mind to get your site going. 

Web Checklist – Self Hosting

0. Operating System - For client projects, I offer OS X (apple) or Linux.  In my view, if one wishes a stable, reliable web site, that stays up, and requires little operating system maintenance, choose OSX or Linux. 

1. Web Server hardware – start with an inexpensive computer – I generally buy a box from tigerdirect.com, or another low-priced seller. Keep the capital lay out low to start.


2. Web Server - Apache is the de facto standard in my view. It’s free, and quite powerful. And there are lots of tools, add-ons, and management software available.
For ease of configuration, and management, Apache GUI is a great tool.

3. Scripting Languages – PHP, Java, Javascript, ASP – my choice here is PHP, It’s free, very powerful, is object-oriented for real app building, and there are a lot of addons available.


4. Database – Mysql is everywhere. It’s free, easy to setup, easy to run, and the php library for mysql is very straightforward.


5. Web Site Design – Dreamweaver – lots of folks swear by hand-coding html. As a professional programmer with 20 years experience under my belt, using a tool like Dreamweaver reduces programming error, saves time (time=$$$), and its easy to use once you get the hang of it.


6. DNS Management (Domain Name Server) – this is always an issue if you have a dynamic IP – most do. Using a service like http://www.dyndns.com/ for your dynamic dns service works sometimes. I recommend getting a static ip address, it costs more, but its worth it. You want your site reliable. Contact your isp to get it setup.


7. Domain Name – I use cheap-domainregistration.com – It’s cheap. Bottom line.

8.  Co-Location Option – I’ve tried co-location many times. Have had mixed results. It’s costly, but frees you from dealing with all the issues of self-hosting.


9. ISP – get the company that offers the best deal for the fastest connection.

10. Server Backup / Power Backup - I usually have a 500GB or 1TB external backing up the server daily, with a battery backup unit on the server. I typically buy these items from costco.com.

Clients and friends often ask how to go about setting up a web site, whether for business or personal use. So I decided a short series is in order.  Here’s the first installment.

There are several steps in the list for setting up a web site. First there is a key decision to make that dictates the number of steps.
Hosting Company vs. Self-Hosting.  If you hire a company to host your web site, all you need focus on is:

1. your domain name – to register a domain name, I recommend cheap-domainregistration.com. You’ll see by the site (and most domain registrars) you simply type in a name you’d like to register, and it’ll let you know if its available, or what permutation of the name is.  Then you simply pay for the domain name, and finally setup custom DNS (domain name server) ip addresses telling your registrar and the DNS database where to find your domain name. More on this later.

2. To find a hosting company. While there are a slew of hosting companies around, charging anything from zero dollars and up, the key is that the company is reliable. Check out their reviews, ask around for a popular company. I have used many hosting companies over the years. My favorite is dreamhost.com .  They are relatively inexpensive, VERY reliable, and they have an unlimited storage and bandwidth option, along with different payment methods (monthly,quarterly,yearly), and fantastic support. You can choose shared server (there are other sites on the machine) or dedicated server (for your site).

3. As far as setup, you’ll need to go through the control panel and select /install what you’ll need for your web site. For example. An ecommerce web site would require PHP, MySql, a shopping cart (cool cart is one I frequently use), transaction gateway (a common one is authorize.net), a merchant account (so you can take credit cards), more on this later.

Now you’re ready to concentrate on your site. I recommend Dreamweaver. A lot of purists advocate hand-coding html.  Having been a programmer for over 20 years, starting with assembler  & C, I say, using a tool like Dreamweaver reduces the chances for error, and ensures coding standards, and offers integrated tools like Version Control, and above all, saves time.

To sum up, having a hosting company saves a lot of time. And there’s no operational headaches – no backing up servers, no worries about power issues, redundancy, routers, T1’s ( more on that later).  I’ve done self-hosting and hosting companies. My choice is hosting company, unless you’re a large company with IT, or have enough revenue to support a small data center.  

Next, the steps to self-host your web site.