mysql
readings
Severance, C. (2014). Elizabeth Fong: Creating the SQL Database Standards. Computer, 47(8), 7-8.
Axmark, D., & Widenius, M. (1999). MySQL introduction. Linux Journal, 1999(67es), 5.
general
A database, hosted on a web server
Serves client requests for data from the database
databases
relational
We're talking about "relational" databases here, that is, a database organized into tables
Each table is a relation
Each row is a tuple
Each column is an attribute
queries
there is a pretty standard query language at this point for relational databases, called SQL
for structured query language
relationship to MySQL
MySQL is a web-based toolkit for creating relational databases accessible via the web
MySQL supports queries through SQL
administration
phpMyAdmin is a special client written for managing MySQL installs on the server
Note: SQL commands are not case sensitive, while SQL parameters are
Managing databases
SHOW DATABASES: shows all the databases in the installed server
Some servers install predefined databases to track user defined databases. Don't touch those.
DROP DATABASE <name>: deletes the named database
CREATE DATABASE <name>: creates the named database
Defining databases
CREATE TABLE <name> (<attributes>) <flags>: creates a table in your database with the name.
attributes
A comma separated list of attributes
Each attribute should have the format <name> <type> <flags>
name: provide a name for the attribute/column
type: provide a type to the values in the column. These are predefined.
flags: let you set things such as
whether a value is required
whether the value should be autoincremented (e.g. an ID)
whether the value is a key (a unique ID for the tuple)
others...
flags
Lets you further configure the database, including
what character set to use. For web, usually utf8
which engine (database "format") to use: default is myISAM, but InnoDB is better
SHOW TABLES: shows the tables inside the current database
DESCRIBE <name>: shows the attribute details for the named database
DROP TABLE <name>: deletes the named table
Adding data to databases
INSERT INTO <tablename> SET
<column1name> = <column1value>,
<column2name> = <column2value>...
Getting data from databases
SELECT <columnname>, <columnname>... FROM <tablename>
this returns these column values from all rows
add WITH to limit the rows returned
... WHERE <columnname> = <value>
or any other comparison operator
can string together multiple criteria with AND or OR
Updating databases
UPDATE <tablename> SET <columnname> = <value>,... WHERE ....
Removing data from databases
DELETE FROM <tablename> WHERE ....
Make sure you create user accounts that only have rights to the databases referenced in served pages
Using accounts with more rights than these is a security concern
mysql and the web
steps in answering a script/mysql page request
client requests page
server sees there is script involved, so fires up script interpreter
some of the script references the server's mysql database
mysql answers the script calls back to script
script takes results, inserts them into the html it builds
script delivers output html to web server
html delivered to client
mysql and php
connecting to mysql from web page php
use the php data objects extension
create a new connection
new PDO('mysql:host=<hostname>;dbname=<databasename>','<username>','<password>')
if this doesn't work, you will have to catch the exception to respond
try {
$pdo = new PDO(...;
} catch {
$output = 'couldn't connect to database!';
/* output variable */
exit();
}
configuring connections
To ensure that connections throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
To set the character encoding
$pdo->exec('SET NAMES "utf8"');
exec sends a query to the database
disconnecting
$pdo = null;
and, all connections are closed when script finishes
doing stuff with/to the database
exec command executes sql queries
returns the number of affected rows
for SELECT queries, use the query command
$result = $pd9->query('SELECT mycolumn FROM mytable');
this returns a PDOStatement object
it contains all returned rows
to process each row, use fetch
$row = $result->fetch()
to access a field in the row, use this syntax
$onefield = $row['mycolumn'];