The MySQL Database and Rules for Using SQL Queries

Because of its versatility, the MySQL^R database server is the most popular database for partnering with PHP in web applications. MySQL can be used for small business sites as well as high-traffic websites. But how do you communicate with the MySQL server? You will use the SQL computer language and, like PHP, you can put the SQL code within the HTML code for your webpages.

SQL stands for Structured Query Language and the sentences you use to talk to the database server are called SQL queries. Much like HTML, the SQL language uses English words and therefore is easy to understand and use. Let's take a look at an example query that will select the names of all the members from a membership table that live in the state of Ohio. It will then put these names in alphabetical order.

SELECT memberName FROM member_table
WHERE state = 'OH'
ORDER BY memberName

The first word in a SQL query (SELECT in the example) is the query name and tells the server what task to perform. In the example above SELECT is used to select the names of certain members.
The rest to the query tells the server how to perform the task, that is, what criteria to use to select the data from the database table.
There are a few rules to remember when writing SQL queries.
1. Query names are not case sensitive and therefore Select is equal to SELECT. Although the database server does not care if you put the query names in all capitals letters, all lowercase letters or a mixture of uppercase and lowercase, it is common practice to put the query name in all capital letters. This makes it easier to read the code. So in the example above, the word SELECT is in all uppercase letters. It is also easier to read the code if you use all uppercase letters for the main words in the rest of the query such as the words FROM, WHERE and ORDER BY in the example above.
2. Table names (member_table), column names (state) and variable names (memberName) are case sensitive and therefore memberName is not equal to MemberName. This is especially important when working on Linux and Unix.
3. Much like HTML, you can spread a SQL query over several lines, as in the example above. But you can also put the query all on one line. Just remember to separate each word in the query with at least on space.
4. Quotation marks are used to enclose character strings but you do not use quotation marks for number values. In the example above, the OH is a character string and therefore is enclosed within single quotation marks.
  
  WHERE state = 'OH'
  
  However if we were to sort the memberName by zipcode instead, you would not need the quotation marks because the zipcode is a number.
  
  WHERE zipcode = 55555
5. It is customary to use single quotation marks in SQL statements. However, you can use either single or double quotation marks when using MySQL. Therefore the lines below will work just a well as the lines in the example for the previous rule.
  
  WHERE state = "OH"
  WHERE zipcode = 55555
6. If you are using quotation marks within a character string, you will need to escape these quotation marks. For example, for the character string Nancy said "Hello", we will need to escape the quotation marks surrounding the word Hello when we place this character string within quotation marks.
  
  $variable = "Nancy said \"Hello\" ";