I would have written a guide on how I did this, but the blog post over on If Not True Then False covers it perfectly, so just go over there and follow their guide.
Profiling SQL queries with EXPLAIN
If you have a complex SQL query, you might find that performance isn’t exactly ideal. Worse still, you don’t actually know which part of the query it is that is actually taking so long.
Luckily, MySQL comes to the rescue with the ability to explain.
All you have to do is start your query with the keyword EXPLAIN and MySQL will, rather than returning you a recordset of results, will instead provide a break down of everything it has done, including how it made the table joins and what order it did everything in.
EXPLAIN SELECT a INNER JOIN b ON a.col1 = b.col2 WHERE a.col1 > 1 AND b.col2 > 2;
Speeding up inserts with INSERT DELAYED
If your insert statements are not time critical, you can use insert delayed in your SQL to speed things up. The syntax is as follows.
INSERT DELAYED table (col1, col2) VALUES ('a', 'b');
Insert delayed can be used with MyISAM and Memory, but cannot be used with InnoDB.
The advantage of using insert delayed is that the MySQL server returns a success message straight away so the script can keep going, without it actually having to do the insert. This allows the MySQL server to carry it out when it isn’t busy, and do several at the same time.
It’s appropriate for tables such as logs tables where it doesn’t matter too much if they don’t go in straight away.
Updating views in MySQL
Yes, you can update views in MySQL! A lot of people seem to be under the impression that you are unable to, but this simply isn’t the case. There are a lot of restrictive rules, which can be found in the MySQL manual, but as a rule of thumb, you can update them, but only one table.
For example, lets say you have a view which links table A and table B.
You can run an update on this view, but only if you are only updating the columns from one table. So you could run a query that updates a number of columns from table A, or you could run a query which updates columns from table B – but you can’t run a query which updated columns in both table A and table B.
If you need to accomplish this, you need to use a join instead.
SQL UPDATE command
The SQL update command allows you to make specific changes to one or more rows. You could in theory do this by deleting the old one and inserting a new one but this means you have to deal with all the data and can only do one row of data. Using UPDATE solves both of these problems.
Basic syntax
As with everything there is a basic command structure to allow you to sort out what you want and run the various different parts of the commands. The syntax for using UPDATE is as follows:
UPDATE table SET field1 = 'value1', field2 = somenumber WHERE field3 = 'value3'
There are three parts to this. The first is the table to update. The second is the values which you are setting. The third is the validation of which rows in the table should have the update run.
Sample table
To make this easier to do I am going to create a sample table of data from which we can work from. The table is going to be called friends and will have several bits of data in it.
Name email age favourite colour Jim jim@mail.com 24 blue Alex alex@operamail.com 23 black Josh happy@killer.com 30 grey Mike mikebob@avril.com 27 blue
Ok first lets say that Josh tells me that his email has changed from happy@killer.com to josh@wiggam.com. So we need to run an update command to change the email address from the old one to the new one.
UPDATE friends SET email = 'josh@wiggam.com' WHERE name = 'Josh'
In this code, all rows where the name is Josh, will have the current email changed to match josh@wiggam.com. This would be a problem if I had two Josh’s but I don’t so only one is updated. If I did have two Josh’s I could change the statement to say:
WHERE email = 'happy@killer.com'
Or I could also try
WHERE name = 'Josh' AND age = 30
Updating multiple values
Next let’s pretend that it’s Alex’s birthday – he turns 24 and decides that his favourite colour is now green. So we need to update two values in the table at once. Luckily this is pretty simple to do.
UPDATE friends SET age = 24, favourite colour = 'green' WHERE name = 'Alex'
First thing I must stress – you should never have a column name with a space it – don’t do it. But seen as this is just an example it’s not a problem. If you really needed a name like that then using FAVOURITE_COLOUR or a dash, etc to space them out.
In the above update statement both age and favourite colour are updated in every row where the name is Alex. Also there are no hyphens around age as it’s a number and so does not require them. You will get an error if you try to put them round a number when using a number column, as you will do for not using them in a text column.
Updating multiple rows
Finally I am going to look at how to update two records at a time. Though we have really already covered it so this is more of a confirmation to make sure you have it right. Take a look at this code:
UPDATE friends SET age = 60 WHERE favourite colour = 'blue'
I decide that everyone who likes blue is an old foogy so I am going to update everyone’s age to 60 who said their favourite colour was blue. As usual the script looks for everyone who is ok by the WHERE validation. This time it finds both Jim and Mike and changes both their ages to 60.
Conclusion
The UPDATE command is fairly simple – you just specify the table to update, the values to set and the conditions a row must meet for the update to be run on that row. One final thought though – take another look at Mike’s email address and try and guess what music I was listening to when I wrote that one 
.
Yep, it was of course Feeder 
.
SQL SELECT command
Whether you connect to MySQL, MS Access, SQL or more databases and whether you do it via PHP, ASP or even more you still use the same basic commands of SQL to extract information from the database.
Below is a sample database table called “members”
Username Name Email Posts Jim Jim Harris jim@ntlworld.com 24 Mod Toby Hunter t.hunter@btopenworld.com 4 Happy Simon Gates theman2003@aol.com 12
Seen as your database connections and how you phrase the code depends on what language you are using I won’t cover that. Chances are you will have some variable such as db = “your SQL commands here”
First of all, you may want to extract all the data. A basic command would be:
SELECT * FROM members
The SQL commands such as SELECT and FROM are always in capitals. SELECT is the first thing you put whenever you are reading or extracting data. It tells you what to take out. For instance you may only want certain columns.
SELECT name FROM members
Or
SELECT name,email FROM members
* tells the script to take all the columns from the database.
FROM tells you what table in the database to take the data from. So for instance if you had a table called “stats” you would use:
SELECT * FROM stats
Next you may want to add conditionals onto the data to only take certain rows:
SELECT * FROM members WHERE name = "Jim Harris"
This would give you the result:
Jim Jim Harris jim@ntlworld.com 24
You can also add several conditional values on:
SELECT * FROM members WHERE username = "jim" and name = "Jim Harris"
That would produce:
Jim Jim Harris jim@ntlworld.com 24
As with most mathematical type equations you don’t have to use the = sign all the time. For instance:
SELECT * FROM members WHERE posts > 10
This would produce
Jim Jim Harris jim@ntlworld.com 24 Happy Simon Gates theman2003@aol.com 12
The guy with the username “mod” would be missed out as his has not made more than 10 posts.
Finally you can also order the rows. For instance if you wanted to order then by the number of posts they made:
SELECT * FROM members ORDER BY posts DESC
This would produce:
Jim Jim Harris jim@ntlworld.com 24 Happy Simon Gates theman2003@aol.com 12 Mod Toby Hunter t.hunter@btopenworld.com 4
By contrast:
SELECT * FROM members ORDER BY posts ASC
Would produce:
Mod Toby Hunter t.hunter@btopenworld.com 4 Happy Simon Gates theman2003@aol.com 12 Jim Jim Harris jim@ntlworld.com 24
ASC sorted them lowest first or alphabetically and DESC sorts them highest first or reverse alphabetically.
Accessing MySQL with ASP
Being an active server page coder by nature, all my sites are in ASP. So eventually I hit a problem – two sites want to use the same Access database but they are on different hosts. Therefore I decided it would be best to use a MySQL database that they could share by making remote connections.
Why use MySQL?
The main advantage is as I have already mentioned, having a central MySQL database allows you to connect all your sites directly to it and access the information. There are ways to do this in Microsoft Access but they are patchy at best. The other option would be to use MS SQL server although this doesn’t come cheap from any host.
Changing the code
Let’s say for example I have a MySQL database on a linux hosting account at www.examplelinux.com. And I have a website at www.examplewindows.com that I want to connect with. Luckily connecting to MySQL is similar to any other connection except we just need to make a few changes.
The first is obvious; the connection script needs to be changed as we are accessing MySQL rather than an Access database. The second are certain changes in the scripts to make sure we don’t end up with any MySQL errors.
A typical DNS less connection would be something like:
Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath("/Conndb/springer1.mdb")
Whereas a connection to MySQL would look more like:
Driver={MySQL ODBC 3.51 Driver}; Server=examplelinux.com; Database=dbname; UID=username; PASS=password
To do this your server needs to have the MySQL driver on it. You can download it from http://www.mysql.com/downloads/index.html. You can also use a DNS connection if you have one which makes things considerably simpler:
DSN=linuxexampledns
All you need to do is replace the connection line you currently use with the new one modifying the username, password and name of the database you are going to use on your target server.
Further modifications
To make the ASP code work with MySQL there are a few other modifications you need to make with your code. The problem arises from the use of the ‘ character. Where Access is ok with it, MySQL will bring up a nice big error message.
Luckily a bit of PHP style code can come to the rescue. If you are familiar with PHP, you will know you can remove the significance of a character using \ before it. Therefore a quick replace function does the trick
Replace(string, "'", "\'")
So for instance a select command would go from:
SELECT * FROM hobbies WHERE firstname = '" + (users.Fields.Item("firstname").Value) + "' ORDER BY name ASC
And changed to:
SELECT * FROM hobbies WHERE firstname = '" + Replace((users.Fields.Item("firstname").Value) ,"'","\'") + "' ORDER BY name ASC
Setting up the server
Once the modifications to the code have been made you can get on with setting up the server to accept the requests. First of all you need to allow the scripts to access the database.
In CPanel goto MySQL Database and scroll down to access hosts: then add in the IP address of www.windowsexample.com. If you can’t find it out, simply run the scripts and it will bring you up an access denied message containing the IP address you are trying to access the database from.
In Direct Admin goto MySQL Databases, click on the database and then you can enter the IP address in to the access hosts form near the bottom. If you can access the privileges section in phpMyAdmin directly you can also do it from there as you can set for each user what access host the user can use.
The only other problem is getting the contents of your Access database to your MySQL server. The easiest way I found to do this way to set up a DNS connection to the MySQL server on my local machine, though you may be able to do this directly to the server providing you grant yourself access, and then going into Access and Exporting it.
File > Export then open the drop down menu at the bottom and and scroll to the bottom of the list until you see ODBC Databases (). This will close the export window and prompt you to enter the name of what you want to call it when you export it. Most likely it will be best to leave the default here.
Then it will come up with a box asking you to select a data source. Select the machine data source tab and find your DNS connection on the list then click ok and your done.
Conclusion
Switching your database from Access to MySQL is a hassle – you need a Linux server, code changes and connection scripts. However if your sites need to share a database its a far cheaper option that using MS SQL.