MySQL Database Management System
Using either DreamCoder or PHPMyAdmin, create a database named test. Create a project folder called MySQL Project. Create an images folder inside.
Create Table
Make a simple HTML page with a CSS file attached. Save the file as create.php. Make sure the file extension is .php. Use the server, username and password that you set up on your local server. Change them in the code if necessary. These values must match!
Repeat these steps for each of the following pages using the corresponding name.
In create.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Create a MySQL table in the selected database
mysql_query("CREATE TABLE family(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
position VARCHAR(30),
age VARCHAR(30))")
or die(mysql_error());
echo "Table family Created!";
?>
(You can only run this once.)
Insert Values
In insert.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Insert a row of information into the table "example"
mysql_query("INSERT INTO example
(name, age) VALUES('Timmy Mellowman', '23' ) ")
or die(mysql_error());
mysql_query("INSERT INTO example
(name, age) VALUES('Sandy Smith', '21' ) ")
or die(mysql_error());
mysql_query("INSERT INTO example
(name, age) VALUES('Bobby Wallace', '15' ) ")
or die(mysql_error());
echo "Data Inserted!";
?>
Minimum Value
In min.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * INTO OUTFILE 'result.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM example")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>id</th> <th>name</th> <th>type</th> <th>price</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['type'];
echo "</td><td>";
echo $row['price'];
echo "</td></tr>";
}
echo "</table>";
$query = "SELECT type, MIN(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo $row['type']. " - $". $row['MIN(price)'];
echo "<br />";
}
?>
Try max.php next.
Table Values
In table.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>Name</th> <th>Age</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";
?>
Update Values
Create a new table named example withid (int) name (varchar) and age (int) columns. Add 5 people with ages ranging from 20 through 30. Make sure at least one has age 21.
In update.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get Sandy's record from the "example" table
$result = mysql_query("UPDATE example SET age='22' WHERE age='21'")
or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example WHERE age='22'")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>Name</th> <th>Age</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";
?>
Insert Values
In insert.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
mysql_query("INSERT INTO example
(name, age) VALUES('Timmy Mellowman', '23' ) ")
or die(mysql_error());
mysql_query("INSERT INTO example
(name, age) VALUES('Sandy Smith', '21' ) ")
or die(mysql_error());
mysql_query("INSERT INTO example
(name, age) VALUES('Bobby Wallace', '15' ) ")
or die(mysql_error());
echo "Data Inserted!";
?>
Order Values
In order.php, add the following code in the body:
<?
$username="root";
$password="jcrdb";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$result = mysql_query("SELECT * FROM example ORDER BY age")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>Name</th> <th>Age</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";
?>
Count Values
Create the products table:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
DROP TABLE IF EXISTS `test`.`products`;
CREATE TABLE `test`.`products` (
`id` varchar(30) NOT NULL default '',
`name` varchar(60) NOT NULL default '',
`type` varchar(30) NOT NULL default '',
`price` decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Add the values:
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123451','Park''s Great Hits','Music',19.99);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123452','Silly Puddy','Toy',3.99);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123453','Playstation','Toy',89.95);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123454','Men''s T-Shirt','Clothing',32.5);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123455','Blouse','Clothing',34.97);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123456','Electronica 2002','Music',3.99);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123457','Country Tunes','Music',21.55);
INSERT INTO PRODUCTS
(id,name,type,price)
VALUES('123458','Watermelon','Food',8.73);
In count.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get all the data from the "products" table
$result = mysql_query("SELECT * FROM products")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>id</th> <th>name</th> <th>type</th> <th>price</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['type'];
echo "</td><td>";
echo $row['price'];
echo "</td></tr>";
}
echo "</table>";
$query = "SELECT type, COUNT(name) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
echo "<br />";
}
?>
Average Values
In average.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get all the data from the "products" table
$result = mysql_query("SELECT * FROM products")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>id</th> <th>name</th> <th>type</th> <th>price</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['type'];
echo "</td><td>";
echo $row['price'];
echo "</td></tr>";
}
echo "</table>";
$query = "SELECT type, AVG(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "The average price of ". $row['type']. " is $".$row['AVG(price)'];
echo "<br />";
}
?>
Sum Values
In sum.php, add the following code in the body:
<?
$username="root";
$password="";
$database="test";
mysql_connect("localhost", $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
// Get all the data from the "products" table
$result = mysql_query("SELECT * FROM products")
or die(mysql_error());
echo "<table border='2'>";
echo "<tr> <th>id</th> <th>name</th> <th>type</th> <th>price</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['type'];
echo "</td><td>";
echo $row['price'];
echo "</td></tr>";
}
echo "</table>";
$query = "SELECT type, SUM(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['type']. " = $". $row['SUM(price)'];
echo "<br />";
}
?>
