How to create a MySQL db @WPI:

http://www.wpi.edu/Academics/CCC/Services/Databases/MySQL.html
This page has forms to create/drop databases, add/remove users from a database.

MySQL basics

keywords (SELECT, DELETE, FROM, WHERE) are case-insensitive
database, table, & column names are case-sensitive.

Basic table commands:

CREATE/DROP
USE
SHOW TABLES
DESCRIBE <tablename>

data types:

INT, REAL
CHAR(length), VARCHAR(length), TEXT(length)
DATE, TIME (also DATETIME)

VARCHAR & TEXT require length fields as maximum length allowed. CHAR is always length characters long.
also binary types & other

Modify an attribute/column type:

ALTER TABLE <table> MODIFY <column> <newtype>

Standard SQL:

INSERT INTO <table> (column1, column2, ...) VALUES (val1, val2, ...)
don't need column names IF you specify a value for every column as spec'd in the table's CREATE call

AUTO_INCREMENT - good for numeric keys: 

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
			name VARCHAR(100),
			pop INT,
			founded DATE);

to take advantage of auto-incrementing, need to either leave out that column when updating/inserting, or specify NULL attribute

UPDATE <table>
SET <column> = <value>, <columnA> = <valueA>
WHERE ...

DELETE FROM <table>
WHERE ...

SELECT <column(s)>
FROM <table(s)>
WHERE ...

Join:

WHERE author.id = book.author

Aliasing:

SELECT blah_name_long AS myfield
FROM <table>
WHERE myfield = 'Joe'
or
SELECT people.names, tests.score
FROM tests, really_long_people_table_name AS people

Ordering:

SELECT ...
FROM ...
ORDER BY <val1>, <val2>, etc.
or ORDER BY <val1> DESC (descending order)

Grouping

mysql> SELECT name, rank, salary FROM people;
+--------------+----------+--------+
| name         | rank     | salary |
+--------------+----------+--------+
| Jack Smith   | Private  |  23000 |
| Jane Walker  | General  | 125000 |
| June Sanders | Private  |  22000 |
| John Barker  | Sergeant |  45000 |
| Jim Castle   | Sergeant |  38000 |
+--------------+----------+--------+

mysql> SELECT rank, AVG(salary) FROM people GROUP BY rank;
+----------+-------------+
| rank     | AVG(salary) |
+----------+-------------+
| General  | 125000.0000 |
| Private  |  22500.0000 |
| Sergeant |  41500.0000 |
+----------+-------------+

Operators:

Arithmetic: +, -, *, /
Comparison: =, <> or !=, <, >, <=, >=
Boolean: AND, OR, NOT

Testing for NULL:

SELECT ..
FROM ...
WHERE author IS NULL
(or IS NOT NULL)
Don't use "WHERE author = NULL"! it will always return an empty set!

Pattern Matching: LIKE and REGEXP

LIKE looks for an exact match (ex. LIKE 'Java' vs LIKE 'Java%')
Example:
LIKE 'Java' will match the string "Java", but no others.
LIKE 'Java%' will match "Java", "Java for Dummies" - anything that starts with "Java"

REGEXP looks for the experession anywhere within the value being compared
So REGEXP 'Java' will match "Java", "Java for Dummies", and "Beginners Guide to Java"

PHP basics

See http://www.php.net/manual/en/tutorial.php for more PHP help.

The names of your php files need to end in *.php.
The first line of each file must be: #!/usr/local/bin/php

Example:
<html> ...(more html stuff)
<?php echo ("Hello World!\n"); ?>

Allowable syntax: From http://www.php.net/manual/en/language.basic-syntax.php 

<?php echo("print stuff - good for XHTML\n"); ?>
<? echo ("this also works\n"); ?>
<?= expression ?>	- shortcut for <? echo expression ?>
<script language="php">
	echo ("some people use this syntax");
</script>

More advanced example: You can open & close php tags within an expression (such as an if statement or function block) 

<?php
if ($expression) { 
    ?>
    <strong>This is true.</strong>
    <?php 
} else { 
    ?>
    <strong>This is false.</strong>
    <?php 
}
?>
Another example (with a function definition) 
<?
function foo () {
   echo "Enter foo...";
?>Some HTML inside foo...<?
   echo "Leave foo.";
}
?>
 <HTML><BODY>
  <? foo(); ?>
 </BODY></HTML>
produces:
Enter foo...Some HTML inside foo...Leave foo.

Globals & reserved variables

$_SERVER, phpinfo();

Example
<?php echo $_SERVER["HTTP_USER_AGENT"]; ?>

Getting data from forms:

$_POST or $_GET

Form has: 

<form action="action.php" method="POST">
Your age: <input type=text name="age">
<input type=submit>
</form>
Action.php contains:
You are: <?php echo $_POST["age"]"; ?> years old.

You can also use $_REQUEST if you don't care whether the data was received from a POST or a GET request.

PHP and MySQL:

See http://www.php.net/manual/en/ref.mysql.php for more help.

Example: 

<?php
    /* Connecting, selecting database */
    $link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
        or die("Could not connect");
    print "Connected successfully";
    mysql_select_db("my_database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);
?>

Functions:

Connection: mysql_connect, mysql_close, mysql_select_db
Queries: mysql_query
   mysql_fetch_row, mysql_fetch_associative, mysql_fetch_array(MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH)
   mysql_num_rows, mysql_num_fields
Other: mysql_affected_rows