Database fundamentals

Designing a database with tables

In the above diagram, part of the free software available from mysql.com, there are several key terms that you should understand before trying to use an SQL database server.

Database - the main container for your data. On community, your database name is the same as your username for logging into shell.mfaca.sva.edu (or will be once you establish a database with Levent).
Table - a container that is like a folder inside of the database.
Column - a type of data that is in the table.
Row - one record from the database.
Query - a search or insert or update sent to the database server.
Auto index - a numerical id that changes with each new row inserted
Null - a non-value, empty. "Not null" means that the field has to have some kind of value, even if it is an empty string.
Unsigned - the value is never negative (like -43)

Examples of queries that search through an imaginary table named "test":
SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';
These are examples of SQL (Structured Query Language). To use this language we either must be entering the directly into a piece of client software that speaks to the MySQL server OR we can use another language like PHP that is capable of contacting the MySQL server.
With PHP, we send the above examples to the server (one by one!) with the mysql_query() function. It then returns a variable that can be used to investigate what happened. If there is no error, we can then use mysql_fetch_row() for as many rows as were returned for our query.

PHP is usually compiled with access to MySQL database server. Functions like mysql_query(), mysql_connect(), mysql_error() are called an API (application programmer's interface) for MySQL. After specifying details like the username, password, and database name, a $conn variable is received from the function mysql_connect(). This permits us to send SQL queries to the server with code like:

<?php
  $sql = "select name from artist where id = '$artistID';";
  $result = mysql_query( $sql, $conn ) or die ( mysql_error() );
  $artist = mysql_fetch_array( $result );
  $artistName = $artist[name];
?>