Introduction To Computational Media on the Web (ICM-W) : Week 10

MySQL and More

MySQL

MySQL is an open source database server that has grown very popular due to it's speed, feature set and suitability for web development (oh, yeah, it's cost as well).

A database, specifically a relational database is in concept similar to a series of spreadsheet tables which are linked together. Meaning that columns in one table, refer to columns in another table. In fact, that is exactly what a relational database is, a set of tables which may be linked in some way.

phpMyAdmin

phpMyAdmin is a free web based tool for use with MySQL. It can help with the setting up of a database, tables, defining queries, browsing and inserting data and a whole slew of other things.

SQL

SQL stands for the Structured Query Language. It is a standard that works across most database engines with slight variations.

The basic statements in SQL are as follows:

Select: Allows you to get data from the database according to the parmeters you specify:
select column_name1, column_name2 from table_name where column = whatever;
select first_name from names where id = 1;

Insert: Allows you to insert data into a database table
insert into table_name (column_name1, column_name2) values ('value1','value2);
insert into names columns (first_name, last_name) values ('shawn', 'van every');

Update: Allows you to update data in a table
update table_name set column_name = 'value' where column_name = 'whatever';
update names set middle_name = 'alan' where first_name = 'shawn' and last_name = 'van every';

Delete: Deletes rows from a table
delete from table_name where column_name = 'whatever';
delete from names where first_name = 'joe';

Notes:
Always end line with ";".
Separate multiple items to select with comma
Put strings in single quote
Single "=" for comparison
Use "and" and "or" instead of "||" and "&&"

A good friend is the Online MySQL Manual

PHP with MySQL

PHP and MySQL integrate very well together. A prime example of a PHP application working with the MySQL engine is phpMyAdmin.

Connecting to a pre-existing MySQL Database

<?        
	$hostname = "localhost";
	$dbname = "your_database_name";
	$username = "your_username";
	$password = "your_password";
                
	$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
	mysql_select_db($dbname, $mySql) or die(mysql_error());
?>

PHP MySQL Select

<?
        function sqlConnect() {
                # Configuration Variables
                $hostname = "localhost";
                $dbname = "yournetid";
                $username = "yournetid";
                $password = "yourpassword";

                $mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
                mysql_select_db($dbname, $mySql) or die(mysql_error());

                return $mySql;
        }

        $mysql = sqlConnect();
?>
<html>
        <head>
                <title>PHP/Mysql Select Example</title>
        </head>
        <body>
                <?
                        $sql = "select id, firstname, lastname from names";
                        $rs = mysql_query($sql,$mysql);

                        while ($row = mysql_fetch_array($rs))
                        {
                                echo($row['firstname'] . " " . $row['lastname'] . "<br />\n");
                        }
                ?>
        </body>
</html>

Try it: select.php

PHP MySQL Insert

<?        
		function sqlConnect() {
                # Configuration Variables
                $hostname = "localhost";
                $dbname = "yourdatabasename";
                $username = "yournetid";
                $password = "yourpassword";
                $mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
                mysql_select_db($dbname, $mySql) or die(mysql_error());

                return $mySql;
        }

        $mysql = sqlConnect();

        $inserted = false;
        if (isset($_GET['firstname']) && isset($_GET['lastname']))
        {
                $firstname = mysql_real_escape_string($_GET['firstname']);
                $lastname = mysql_real_escape_string($_GET['lastname']);

                $sql = "insert into names (firstname,lastname) values ('" . $firstname . "', '" . $lastname . "')";
                $rs = mysql_query($sql,$mysql);

                // Check for an error
                if ($rs === true)
                {
                        // It worked
                        $inserted = true;
                }
                else
                {
                        echo(mysql_error());
                        $inserted = false;
                }
        }
?>
<html>
        <head>
                <title>PHP/Mysql Insert Example</title>
        </head>
        <body>
                <?
                        if ($inserted === true)
                        {
                                echo("<b>Insert was successful</b><br /><br />");
                        }

                        $sql = "select id, firstname, lastname from names";
                        $rs = mysql_query($sql,$mysql);

                        while ($row = mysql_fetch_array($rs))
                        {
                                echo($row['firstname'] . " " . $row['lastname'] . "<br />\n");
                        }
                ?>
                <br /><br />
                Add a new row:<br />
                <form action="insert.php" method="GET">
                        Firstname: <input type="text" name="firstname" /><br />
                        Lastname: <input type="text" name="lastname" /><br />
                        <input type="submit" name="submit" value="Submit" />
                </form>
        </body>
</html>

Try it: insert.php