An Easy Guide to MySQL Integration
MySQL is one of the most popular databases to use (mainly because it's free to download for personal uses). This guide will help you learn how to integrate MySQL with PHP.
This tutorial assumes that you have at least one database and at least one table set up. If you do not, phpmyadmin is a great (free) tool to use to help out. It can also be used to query the database instead of PHP. Check it out here. Download MySQL Database Server
Connection Functions
These functions return FALSE upon failure. mysql_connect($hostname, $username, $password) - opens a connection to the server at $hostname using the appropriate $username and $password. ex: $connection = mysql_connect($hostname, $username, $password); mysql_select_db($db) - opens a connection to the desired database. ex: mysql_select_db($db); mysql_ping($connection) - will ping the server to check for a connection. The server will attempt to reconnect if it fails. ex: mysql_ping($connection); mysql_close() - closes the connection to the server. ex: mysql_close();
Error Handling
mysql_error() - will return the error of the called function. ex: mysql_connect($hostname, $username, $password) or die('Cannot connect: ' . mysql_error()); mysql_errno() - a more discrete method for error handling. This function returns only the error number. ex: mysql_connect($hostname, $username, $password) or die('Cannot connect: ' . mysql_errno());
Date and Time Functions/Constants
For some versions the parentheses are not necessary. NOW() - will display the current date and time. ex: $sql = "UPDATE myTable SET time=NOW()"; TIMESTAMP() - will display the date and time of the query. CURDATE() - will display the current date. CURTIME() - will display the curernt time. DATE() - will create a date based on the inserted value. UTC_DATE() - will display the time according to the UTC standards. UTC_TIME() - will display the time according to the UTC standards. UTC_TIMESTAMP() - will display the date and time according to the UTC standards. YEAR() - will display the current year.
CURRENT_TIMESTAMP - another form of NOW(). Can be the default value of one field within the table.
SQL Queries
These statements are to be used in mysql_query($sql). Use * to include all fields. Mysql_query will return FALSE if it fails. INSERT INTO...VALUES - inserts values into a table. ex: $sql = "INSERT INTO myTable (field1, field2, field3, ...) VALUES('$val1', '$val2', '$val3', ...)"; UPDATE...SET - updates fields of a table. ex: $sql = "UPDATE myTable SET field1=$val1, field2='$val2'"; SELECT - selects values within a group of fields ex: $sql = "SELECT * FROM myTable"; ...ORDER BY..DESC/ASC - orders the query by a field in ascending or descending order. The default value is ASC. ex: $sql = "SELECT * FROM myTable ORDER BY id DESC"; ...WHERE - finds a row that meets the following critieria ex: $sql = "SELECT * FROM myTable WHERE id=1"; ex: $sql = "SELECT * FROM myTable WHERE id>5"; SELECT...LIMIT 1 - forces a limit on the number of rows to return. ex: $sql = "SELECT * FROM myTable LIMIT 1"; to select the last inserted entry: $sql = "SELECT * FROM myTable ORDER BY id DESC LIMIT 1";
SQL row functions
mysql_fetch_array($result) - returns the result in an array (associative or numerical) for use within the program. ex: $row = mysql_fetch_array($result); ... $id = $row[id]; ex: $row = mysql_fetch_array($result); ... $id = $row[0]; mysql_num_rows($result) - returns the number of rows found during a query. ex: $num = mysql_num_rows($result); ... echo "Number of rows found: $num"; mysql_num_fields($result) - returns the number of fields of the table; ex: $fieldno = mysql_num_fields($result); ... echo "There are $fieldno fields in this table";
Putting it all together
For this final example we will use this table: id firstName 1 John 2 Jane 3 Micah lastName Doe Doe Jenkins age 35 30 21
<?php $connection = mysql_connect($hostname, $username, $password) or
die('Cannot Connect:' . mysql_error()); mysql_select_db($database); $sql = SELECT * FROM myTable WHERE age>'21' ORDER BY id; $result = mysql_query($sql, $connection); or die('Query failed: ' . mysql_error()); $num = mysql_num_rows($result); echo "I found $num people that matched this query."; while($row = mysql_fetch_array($result)){ $output .= "$row[firstName] $row[lastName]<br />"; } ?> More here: PHP Manual
