PHP

The mysql API is deprecated. The new API, myslqi (improved), exists in 2 flavors:

The comparisons below might be useful when refactoring old code.

connect

local host — (port 3306 assumed)

$dbc=mysql_connect( 'localhost', 'acct', 'pw' ); /* mysql deprecated */
$dbc=mysqli_connect( 'localhost', 'acct', 'pw', 'database' ); /* mysqli procedural  */
$dbc=new mysqli( 'localhost', 'acct', 'pw', 'database' ); /* mysqli object-oriented  */ 

remote host — (port number specified)

$dbc=mysql_connect( 'host:port', 'acct', 'pw' );
$dbc=mysqli_connect( 'host', 'acct', 'pw', 'database', 'port' );
$dbc=new mysqli( 'host', 'acct', 'pw', 'database', 'port' ); 

In the code above, the first 2 lines use the procedural API: they create a "link_identifier," $dbc.

In the 3rd line, $dbc is a different animal. It's an object PHP creates based on the mysqli() class. About classes.

A "class" is like a blue print. You might build a house based on a blue print, but you can't live in the blue print. Likewise, PHP creates an object based on a class. Then you use the object, not the class.

Hence, you find $dbc->query() below, not mysqli->query().

PHP and Javascript differ on this point. Javascript uses a "prototype" model. Any function you write there is a usable object, and you can create other objects from it.

Redo this bit.

PHP does provide a "static" keyword. Static properties or methods can be used without instantiating the class.

Use the arrow operator -> for objects, the "scope resolution operator" :: for classes.
object->property
class::property
object->method()
class::method()

As the name "scope resolution operator" suggests, this notation helps PHP find the context of the property or method.

Oddness. A static property can be set equal to a string, integer, or array when created, but not to a variable, function return, or object. However, a method can set a static property to any of these.

function connect() {
	return mysqli_connect( 'host', 'user', 'pw', 'database' );
}
class db {
	public static $db = connect(); // error
	public static $db; 
	public static function set_db() {
		self::$db = connect(); // OK
	}
} 

query

link_identifier varies

$result=mysql_query( $sql ); /* link_id optional, last connection assumed  */
$result=mysqli_query( $sql, $dbc ); /* link_id required  */
$result = $dbc->query( $sql ); /* link_id is contained in $dbc object  */ 

Note: $dbc->query() returns an object (containing its own methods and properties). So in the 3rd line above, $result is also an object. It contains the result set, along with methods to fetch from it. Hence $result->fetch_assoc() below.

process

result set varies

$row = mysql_fetch_assoc( $result ) /* the function requires the result set as an argument  */
$row = mysqli_fetch_assoc( $result )
$row = $result->fetch_assoc() /* the object contains the result set—it knows what to fetch  */ 

In all cases, fetch_assoc() returns a 1 dimensional array, so $row becomes an array.

To make it 2-dimensional, use "$row[] =" . PHP will append a new element for each fetch.

A 1-dimensional array is rewritten with each fetch. A 2-dimensional array grows with each fetch.

1-dimensional reference

echo $row['name'] . ' ' . $row['uwnetid'];

2-dimensional reference

echo $row[0]['name'] . ' ' . $row[0]['uwnetid']; 
echo $row[1]['name'] . ' ' . $row[1]['uwnetid'];
echo $row[2]['name'] . ' ' . $row[2]['uwnetid'];
etc.

fetch_object() is slower, but consumes less memory.

To retrieve an object, instead of an associative array:

$result = $dbc->query( "select lname, uwnetid from tbl" );
$obj = $result->fetch_object();
echo $obj->lname;
echo $obj->uwnetid;

Above, $obj->lname is a reference to the $result object.

fetch_object() provides quick way to get a simple count.

$result = $dbc->query( "select count(*) as c from tbl" );
$count = $result->fetch_object()->c;

clean up

mysql_close(); /* link_id optional, last connection assumed  */
mysqli_close( $dbc ); /* link_id required  */
$dbc->close(); /* object contains link_id so knows what to close  */

The properties and methods of the mysqli class are described on php.net.

example using object-oriented API

function showrow( $name, $uwnetid ) {
	echo "\n<p>\"$name\" &lt;$uwnetid@uw.edu&gt;</p>";
}

$dbc = new mysqli( 'host', 'acct', 'pw', 'database', port );// create $dbc object 

$sql="select concat(Last, ', ', First) as name, uwnetid from people order by name limit 25";
$result = $dbc->query( $sql );// create $result object

while ( $row[] = $result->fetch_assoc() );// fetch query results

$max = count( $row ) -1;// find size of $row array

for ( $i=0; $i<$max; ++$i ) {// ascending list
	showrow( $row[$i]['name'], $row[$i]['uwnetid'] );
}

for ( $i=$max-1; $i>=0; --$i) {// descending list
	showrow( $row[$i]['name'], $row[$i]['uwnetid'] );
}

$dbc->close();// close connection
unset( $max, $row, $result, $sql, $dbc );// remove variables

example using prepared statements

Idea is to separate data from SQL statements.

Insert ? place-holders in statement, then bind variables to them.

Variables are bound in a separate step. MySQL sanitizes their data before inserting them in statement (similar to mysql_real_escape_string but managed by server).

This requires 3 steps to execute the query, and 1 or 2 steps to fetch the results:

function showrow( $name, $uwnetid ) {
	echo "\n<p>\"$name\" &lt;$uwnetid@uw.edu&gt;</p>";
}

$dbc = new mysqli( 'host', 'acct', 'pw', 'database', port );// create $dbc object

$sql = "select concat(Last, ', ', First) as name, uwnetid from people 
	WHERE left( last, 1 ) = ? and typeid = ? order by last, first"; // 2 place holders in WHERE

if ( $stmt = $dbc->prepare( $sql ) ) { // create prepared statement object 
	// Define the vars that will replace the ?s in WHERE condition.
	$initial = 'W'; // first letter of last name
	$type = 50; // typeID
	$stmt->bind_param( 'si', $initial, $type ); // bind vars to the query (more)

	$stmt->execute(); // Execute the query.

	// Fetch results: GET_RESULT or BIND_RESULT.

	// get_result() — Good for multiple rows.
	$result = $stmt->get_result(); // Get entire result set.
	while( $row = $result->fetch_assoc() ) {
		showrow( $row['name'], $row['uwnetid'] );
	}

	// bind_result() — Good for single rows.
	$stmt->bind_result( $name, $uwnetid ); // Bind vars to each expected column (* not allowed).
	while ( $stmt->fetch() ) { // Extract values, one row per fetch.
		showrow( $name, $uwnetid ); // NB: these are strings, not arrays.
	}

	// Familiar properties, such as "num_rows" are initialized with yet-another function.

	$stmt->store_result();  // Required to initialize 'num_rows'.
	$row_max = $stmt->num_rows;

	$stmt->close();
}

(Good overview: websitebeaver)

error checking

The code above needs it. See error handling for an overview.

common conversions

mysql (procedural) mysqli (object)
mysql_connect(host:port, user, pw) $dbc=new mysqli(host, user, pw, db, port)
NOTE: PORT can't be specified w/out DB
mysql_select_db( db ) $dbc->select_db( db )
mysql_error() $dbc->connect_error
get current database
if non selected, no error.
$dbc->query("select database()")
$var=mysql_real_escape_string($var) $var=$dbc->real_escape_string($var)
$r=mysql_query($sql) $r=$dbc->query($sql)
mysql_error() $dbc->error
mysql_errno() $dbc->errno
$row=mysql_fetch_assoc($r) $row=$r->fetch_assoc()
$row=mysql_fetch_array($r) $row=$r->fetch_array()
$var=mysql_result($r,0) $row=$r->fetch_row()
$var=$row[0]
mysql_num_rows($r) $r->num_rows
mysql_affected_rows() $dbc->affected_rows
mysql_insert_id() $dbc->insert_id
$max=mysql_field_len($r,n) $finfo=$r->fetch_field_direct(n)
$max=$finfo[0]
mysql_close() $dbc->close()