The mysql API is deprecated. The new API, myslqi (improved), exists in 2 flavors:
The comparisons below might be useful when refactoring old code.
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.
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.
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;
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.
function showrow( $name, $uwnetid ) { echo "\n<p>\"$name\" <$uwnetid@uw.edu></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
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\" <$uwnetid@uw.edu></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)bind_param( TYPES , VAR1 , VAR2, . . . ); TYPES : describes the type of column to which each var will be bound s - string, b - blob, i - int, d - double 1 letter per place holder in statement ( 'ss', $s1, $s2 ) // 2 data columns, both strings ( 'sid', $s, $int, $dbl ) // 1 string, 1 int, and 1 double VAR1, VAR2 : these are passed by reference — literal strings not allowed$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)
The code above needs it. See error handling for an overview.
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() |