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.
Don't have time to check my blog? Get a weekly email with all the new posts. This is my personal blog, so obviously it is 100% spam free.
This entry was posted on Sunday, September 16th, 2007 at 1:41 pm and is filed under Life, Tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.