Transcript Chapter 4

Chapter 26
PHP
Part IV
Outline
• Introduction to MySQL
• PHP MySQL Functions
Introduction to MySQL
• MySQL: Multi-user and multi-threaded
RDBMS server
• Uses SQL to interact with and manipulate
data
• Handle large databases
• Basic queries: CREATE, SELECT, DELETE,
INSERT, UPDATE
Creating a MySQL Database
• starting MySQL on eagle:
– mysql [-h host] [-u username]
[database_name] [-p]
– all parameters are optional
• creating a database:
e.g. mysql> create database cars;
mysql> use cars;
Creating a Table
• mysql> create table tableName (
col1_name data_type constraints,
col2_name data_type constraints,
…
col3_name data_type constraints);
• data types include char( ), int, int unsigned, float
• constaints include:
– NOT NULL - values in the column cannot be left empty
– PRIMARY KEY - values in the column are unique over all rows
• e.g. mysql> create table stock (make char(20), model
char(20), year int, mileage int);
Creating a Table
• To get description of a table: describe tableName
e.g. mysql> describe stock;
+---------+----------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| make | char(20) | YES |
| NULL |
|
| model | char(20) | YES |
| NULL |
|
| year
| int(11) | YES |
| NULL |
|
| mileage | int(11) | YES |
| NULL |
|
+---------+----------+------+-----+---------+-------+
Inserting Data into a Table
• mysql> insert into tablename (col_name1,
•
col_name2 … col_nameN)
values (val_1, val2, …valN);
Example:
– mysql> insert into stock (make, model, year, mileage)
values("mazda", "protege", 1993, 230000);
– mysql> insert into stock (make, model, year, mileage)
values("toyota", "rav", 1999, 45000);
Displaying Records and Columns of a Table
• To show the contents of all rows and all
columns:
mysql> select * from tablename;
e.g. mysql> select * from stock;
+--------+---------+------+---------+
| make | model | year | mileage |
+--------+---------+------+---------+
| mazda | protege | 1993 | 230000 |
| toyota | rav
| 1999 | 45000 |
| ford | escort | 1997 | 156000 |
+--------+---------+------+---------+
Displaying Records and Columns of a Table
• To display selected columns:
e.g. mysql> select make, model from stock;
+--------+---------+
| make | model |
+--------+---------+
| mazda | protege |
| toyota | rav
|
| ford | escort |
+--------+---------+
Displaying Records and Columns of a Table
• To display selected rows:
e.g. mysql> select * from stock where mileage >
100000;
+-------+---------+------+---------+
| make | model | year | mileage |
+-------+---------+------+---------+
| mazda | protege | 1993 | 230000 |
| ford | escort | 1997 | 156000 |
+-------+---------+------+---------+
Deleting Selected Rows
• mysql> delete from tableName
•
where columnName=value;
Example:
mysql> delete from stock
where make = "mazda" and year < 2000;
mysql> select * from stock;
+--------+--------+------+---------+
| make | model | year | mileage |
+--------+--------+------+---------+
| toyota | rav | 1999 | 45000 |
| ford | escort | 1997 | 156000 |
+--------+--------+------+---------+
Updating Selected Rows
• mysql> update tableName
•
set col_name1 = value_1, col_name2 = value_2, …
where columnName=value;
Example:
mysql> update stock set mileage = mileage - 1000;
+--------+--------+------+---------+
| make | model | year | mileage |
+--------+--------+------+---------+
| toyota | rav | 1999 | 44000 |
| ford | escort | 1997 | 155000 |
+--------+--------+------+---------+
Delete Table and Database
• To Delete a Table from a Database
mysql> drop table stock;
• To Delete a Database from MySQL
mysql> drop database cars;
• Resources:
http://dev.mysql.com/
PHP MySQL Functions
• A nice feature of PHP is that MySQL
support is built-in.
• PHP contains a complete set of the PHP
MySQL functions needed to communicate
with MySQL
Connecting To a Database
• mysql_connect(serverName, username, password );
–
–
–
–
–
–
connects a script to a MySQL server
arguments are optional, depending on the system
default serverName = "localhost"
default username = user name in which the process is running
default password = ""; works if the password is not required
returns a reference to the connection or false if connection fails
• e.g.
$db = mysql_connect( );
if( $db == false ) {
die ("Unable to connect to MySQL");
}
Selecting a Database
• mysql_select_db(databaseName, connection );
– specifies the database to work with
– connection: reference returned from mysql_connect().
If not supplied the last opened connection is used.
• e.g.
mysql_select_db("cars", $db );
if (mysql_select_db("cars", $db ) == false ) {
die ("Unbale to open the database");
}
Making a Query
• mysql_query(queryString, connection );
– make a select query
– query is a string for the MySQL query
– Don't end the query with a semi-colon
– returns the results of the query
• e.g. $query = "select * from stock"
$result = mysql_query($query, $db);
Making a Query
• mysql_num_rows($result);
– returns the number of rows in the result
• Example:
– $numRows = mysql_num_rows($result);
Making a Query
• mysql_fetch_array($result);
– returns row information as both an associative array and an indexed
array
– returns false if no more rows
– the array is indexed with numbers or the column names (e.g. make,
model, year, mileage)
– we can iterate through all rows of the query results
• Example:
for ($r = 1; $r <= $numRows; $r++) {
$row = mysql_fetch_array($result); // returns next row as array
print " make: " . $row["make"];
// or $row[0]
print " model: " . $row["model"]; // or $row[1]
}
Inserting Data
• mysql_query(queryString, connection );
• Example:
// this will insert a record into the table stock
$result = mysql_query("insert into stock (make,
model) values ('nissan', 'altima')");
if ($result == false) {
print "Insert record failed";
}
Deleting Data
• mysql_query(queryString, connection );
• Example:
// this will delete records from the table stock
$result = mysql_query("delete from stock where
make='nissan'");
if ($result == false) {
print "Deletion failed";
}