Interacting with databases

Dr Andres Baravalle

Workshop 5: Interacting with databases

• Interacting with databases

Databases

A database is an organized collection of data. The data is typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies).

Wikipedia

Databases are for long-term storage

A database typically is used to provide long-term storage for your information. If your web application or web server failes, a database will not loose your information.

Your information will be lost in case of disk failure - that's why redundancy is important.

SQL statements

To actually work with databases and tables, you use SQL statements.

Common statements include:

• SELECT: Retrieves data from one or more tables
• INSERT: Inserts data into a table
• UPDATE: Updates data in a table
• DELETE: Deletes data from a table

SQL statements (2)

In the next slides we will look at the most common SQL statements. We'll see a simplified overview of SQL statements; for the full documentation refer to the MySQL web site.

SELECT

SELECT select_expr 
	[FROM table_references
	[WHERE where_condition]
	[ORDER BY {col_name | expr | position}
	[LIMIT {[offset,] row_count]

Example:

SELECT * FROM t1

INSERT

INSERT 
	[INTO] tbl_name [(col_name,...)]
	{VALUES | VALUE}

Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
            

UPDATE

UPDATE table_reference 
	SET col_name1={expr1|DEFAULT}
	[WHERE where_condition]

Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
            

DELETE

DELETE FROM tbl_name
    [WHERE where_condition]

Using phpMyAdmin

phpMyAdmin can be used to perform the most common database operations, including:

• creating users, databases, tables
• running queries
• exporting data

Using phpMyAdmin (2)

You shouldn't normally write your SQL code directly into your PHP files. Test the code in phpMyAdmin first!

Querying a database

<?php
// create a mysqli object, for the connection
$mysqli = new mysqli("localhost", "my_username", "my_password", "my_database");
/* check connection */
if ($mysqli->connect_errno) {
   printf("Connect failed: %s\n", $mysqli->connect_error); 
 }
else {
	// run a query and fetch the result set
	if ($result = $mysqli->query("SELECT * FROM City") {

		while($row = $result->fetch_array())
		{
			$row = $result->fetch_array();
			var_dump($row);
		}
   	
		$result->close();
   }
	$mysqli->close();
}
?>

Inserting data into a database

<?php
// create a mysqli object, for the connection
$mysqli = new mysqli("localhost", "my_username", "my_password", "my_database");
/* check connection */
if ($mysqli->connect_errno) {
   printf("Connect failed: %s\n", $mysqli->connect_error); 
 }
else {
	// run your command
	$mysqli->query("INSERT INTO cities VALUES (1, 'London');";
	$mysqli->close();
}
?>