2

In my recent SO addiction, I realize that a lot of people continue using MySQL original driver commands.

There is a lot of good tutorials on the net, but there is a sort of comparison table that can help in converting old projects?

fusion3k
  • 11,568
  • 4
  • 25
  • 47

1 Answers1

12

MySQL_ Original Driver to PDO Comparative Table

Introduction:

This table is not intended to be a tutorial or a guide to PDO, but to provide a correspondence between MySQL_ and PDO. So, some important PDO commands are missing, if they don't have a MySQL_ correspondence.

Among these omitted commands, there is a major PDO feature, the prepared statements. I strongly recommend to use it, also if only cited in this guide.

This is a very extended answer. I'm sorry for the low quality of spelling (I'm not english) and for the typos. I will try to correct and improve it in the next few days. Any suggestion or contribution is welcome.

Naming Conventions:

To avoid misunderstanding, in this answer I use:

In PDO, we have two major object types, the PDOObject (the connection between PHP and a database server) and the PDOStatement (a prepared statement and, after the statement is executed, an associated result set). In this answer, variable names are:

  • $dbh, refer to PDOobject;
  • $stmt, refer to PDOStatement.

Most used commands:

  • mysql_connect - Open a connection to a MySQL Server
  • PDO::__construct - Creates a PDO instance representing a connection to a database

MySQL_ way:

$con = mysql_connect( $dbHost, $dbUser, $dbPassword ) or die( mysql_error() );

PDO way:

$dsn = "mysql:host={$dbHost};dbname={$dbName}";
$dbh = new PDO( $dsn, $dbUser, $dbPassword );

In PDO we have to construct the Data Source Name string ($dsn) including in it the Host and the DataBase name, then we can open the connection using the defined DSN, User Name and User Password. With PDO we directly select the database at the connection, without need of _select_db.

To catch error(s) in PDO we need to use try/catch syntax:

try
{
    $dbh = new PDO( $dsn, $dbUser, $dbPassword );
}
catch( PDOException $exception )
{
    echo $exception->getMessage();
}
  • mysql_select_db - Select a MySQL database
  • PDO::__construct - Creates a PDO instance representing a connection to a database

MySQL_ way:

mysql_select_db( $dbName, [$con] ) or die( mysql_error() );

PDO way:

/*  See above, at mysql_connect vs PDO::__construct  */

In PDO we select DataBase in the connection command. To change it, we have to open a new connection.

  To perform queries in a different database without create a new connection, in PDO we can use this MySQL syntax: 

INSERT INTO databasename.tablename ...
SELECT FROM databasename.tablename ...
  • mysql_query - Send a MySQL query
  • PDO::query - Executes an SQL statement, returning a result set as a PDOStatement object

MySQL_ way:

$result = mysql_query( $query, [$dbh] ) or die( mysql_error() );

PDO way:

$stmt   = $dbh->query( $query ) or die( $dbh->errorInfo()[2] );

A PDO query return a PDOStatement object.

  With PDO we can also use prepared statements, an useful way to executed queries multiple times with the same or different parameters, auto-quoting parameters and prevent MySQL injections.

  • mysql_fetch_row - Get a result row as an enumerated array
  • mysql_fetch_array - Fetch a result row as an associative array, a numeric array, or both
  • mysql_fetch_assoc - Fetch a result row as an associative array
  • mysql_fetch_object - Fetch a result row as an object
  • PDOStatement::fetch - Fetches the next row from a result set
  • PDOStatement::fetchObject - Fetches the next row and returns it as an object.

MySQL_ way:

$row = mysql_fetch_row( $result );

PDO way:

$row = $stmt->fetch( PDO::FETCH_NUM );

To fetch row(s) PDO has fewer commands than MySQL_, but it has more and more options. By default, results are fetched both as enumerated than associative array (doubled result), but specifying a fetch mode (using a PDO Constant) we can set the result format.

PDO Fetch style constants:

Constant             Returned Format
···················  ·································································
PDO::FETCH_ASSOC     Associative array
PDO::FETCH_BOTH      Array indexed by both numeric and associative keys (default)
PDO::FETCH_BOUND     Boolean TRUE (and assigns columns values to variables to which  
                     they were bound with the PDOStatement::bindColumn() method)
PDO::FETCH_LAZY      combines PDO::FETCH_BOTH and PDO::FETCH_OBJ
PDO::FETCH_NAMED     same form as PDO::FETCH_ASSOC, but if there are multiple columns 
                     with same name, the value referred to by that key will be an 
                     array of all the values in the row that had that column name
PDO::FETCH_NUM       Enumerated array
PDO::FETCH_OBJ       Anonymous object with column names as property names

Also available (see Documentation for more info): PDO::FETCH_CLASS, PDO::FETCH_INTO

We can set the fetch method using constants as argument of ->fetch() command (as seen above), or setting a default fetch method directly in the original query:

$stmt   = $dbh->query( $query, PDO::FETCH_OBJ ) or die( $dbh->errorInfo()[2] );

Or using ->setFetchMode() method:

$stmt   = $dbh->query( $query ) or die( $dbh->errorInfo()[2] );
$stmt->setFetchMode( PDO::FETCH_OBJ );

MySQL_ / PDO Fetch Commands Comparative Table:

MySQL_                            PDO
································  ····················································
mysql_fetch_row                   $stmt->fetch( PDO::FETCH_NUM );
mysql_fetch_array                 $stmt->fetch( PDO::FETCH_BOTH );
mysql_fetch_assoc                 $stmt->fetch( PDO::FETCH_ASSOC );
mysql_fetch_object                $stmt->fetch( PDO::FETCH_OBJ );
                                  $stmt->fetchObject();

  In PDO you can also fetch all result rows at once using ->fetchAll() method: 

$rows = $stmt->fetchAll();                 /* Fetches all rows in FETCH_BOTH mode */
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);   /* Fetches all rows in FETCH_OBJ  mode */
  • mysql_num_rows - Get number of rows in result
  • mysql_affected_rows - Get number of affected rows in previous MySQL operation
  • PDOStatement::rowCount - Returns the number of rows affected by the last SQL statement

MySQL_ way:

$totRows = mysql_num_rows( $result );
$totRows = affected_rows( $result );

PDO way:

$totRows = $stmt->rowCount();

PDO use a single command to returning total rows number: when used after a INSERT/UPDATE query, it returns the affected rows number, when used after a SELECT query, it returns the rows number in the result set.

  • mysql_insert_id - Get the ID generated in the last query
  • PDO::lastInsertId - Returns the ID of the last inserted row or sequence value

MySQL_ way:

$id = mysql_insert_id()

PDO way:

$id = $dbh->lastInsertId();

MySQL_ way:

$row   = mysql_result( $result, $numRow );
$field = mysql_result( $result, $numRow, $numField );

PDO way:

$rows  = $stmt->fetchAll( FETCH_NUM );
$row   = $rows[ $numRow ];
$field = $rows[ $numRow ][ $numField ];

There is not a PDO equivalent for this MySQL_ function. The only way to emulate mysql_result is to pre-fetch all rows and then referring to results array. When we have a lot of rows in the results set, this is not a good idea.

  • mysql_escape_string - Escapes a string for use in a mysql_query
  • mysql_real_escape_string - Escapes special characters in a string for use in an SQL statement
  • PDO::quote - Quotes a string for use in a query.

MySQL_ way:

$string = mysql_escape_string( $string );

PDO way:

$string = $dbh->quote( $string );

The behaviour of PDO is different from the MySQL_ behavior:

Original String    MySQL_ Escaped String             PDO Quoted String
·················  ································  ································
Hello World        Hello World                       'Hello World'
'Hello World'      \'Hello World\'                   '\'Hello World\''
"Hello World"      \"Hello World\"                   '\"Hello World\"'

So, after quoting string, you have to use it in the query without quotes. e.i.:

$string = $dbh->quote( '"Hello World"' );
$stmt   = $dbh->query( "SELECT * FROM table WHERE field = $string" ) or die( $dbh->errorInfo()[2] );

  Instead of quoting strings, consider using prepared statements, an useful way to executed queries multiple times with the same or different parameters, auto-quoting parameters and prevent MySQL injections.

  • mysql_error - Returns the text of the error message from previous MySQL operation
  • PDO::errorInfo - Fetch extended error information associated with the last operation on the database handle
  • PDOStatement::errorInfo - Fetch extended error information associated with the last operation on the statement handle

MySQL_ way:

$errorMsg = mysql_error();

PDO way:

$error    = $dbh->errorInfo()[2];
$error    = $stmt->errorInfo()[2];

Both PDO and PDOStatement ->errorInfo() returns an array with this form:

Key     Value
······  ··············································································
0       SQLSTATE error code (a 5 characters identifier defined in the ANSI SQL std)
1       MySQL Driver specific error code
2       MySQL Driver specific error message
  • mysql_errno - Returns the numerical value of the error message from previous MySQL operation
  • PDO::errorCode - Fetch the SQLSTATE associated with the last operation on the database handle
  • PDOStatement::errorCode - Fetch the SQLSTATE associated with the last operation on the statement handle

MySQL_ way:

$errorNum = mysql_errno();

PDO way:

$errorNum = $dbh->errorCode();
$errorNum = $stmt->errorCode();
  • mysql_close - Close MySQL connection

MySQL_ way:

mysql_close( $con );

PDO way:

unset( $dbh );

There is not a PDO equivalent for this MySQL_ function. The more diffuse practice is to do nothing (php itself close the connection at the end of the script). Alternatively, we can use unset().

Others commands:

Following commands are compared but not explained. The explanation can be added in future.

Please do not refer to this section until this alert is not removed:
Equivalence of following commands is not yet tested and intended as draft.

  • mysql_client_encoding - Returns the name of the character set

MySQL_ way:

$charSet = mysql_client_encoding();

PDO way:

$stmt = $dbh->query( "SELECT COLLATION('foo')" );

Solution from “Get charset with mysql PDO”

  • mysql_create_db - Create a MySQL database

MySQL_ way:

$result = mysql_create_db( $databaseName );

PDO way:

$result = $dbh->exec( "Create database {$databaseName}" );
  • mysql_data_seek - Move internal result pointer [NT]

MySQL_ way:

mysql_data_seek( $result, $rowNumber );
$row = mysql_fetch_assoc( $result );

PDO way:

$row = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $rowNumber );

Solution from “mysql_data_seek PDO equivalent”

  • mysql_db_name - Retrieves database name from the call to mysql_list_dbs

MySQL_ way:

$result = mysql_list_dbs();
$dbName = mysql_db_name( $result, $numRow );

PDO way:

$stmt   = $dbh->query( "SHOW databases" );
$dbName = $stmt->fetchAll( PDO::FETCH_NUM )[$numRow][0];
  • mysql_drop_db - Drop (delete) a MySQL database

MySQL_ way:

mysql_drop_db( $dbName ) or die( mysql_error() );

PDO way:

$dbh->query( "DROP DATABASE $dbName" ) or die( $dbh->errorInfo()[2] );
  • mysql_fetch_field - Get column information from a result and return as an object
  • PDOStatement::getColumnMeta - Returns metadata for a column in a result set

MySQL_ way:

$meta = mysql_fetch_field( $result, $columnNum );

PDO way:

$meta = (object) $stmt->getColumnMeta( $columnNum );

PDO result is an associative array instead of MySQL_ returned Object. Also returned values are different:

MySQL_          PDO             Description
name            name            Column name
table           table           Name of the table the column belongs to (alias if one)
max_length      len             Maximum length of the column
not_null        (use flags)     1 if the column cannot be NULL
primary_key     (use flags)     1 if the column is a primary key
unique_key      (use flags)     1 if the column is a unique key
multiple_key    (use flags)     1 if the column is a non-unique key
numeric         (use flags)     1 if the column is numeric
blob            (use flags)     1 if the column is a BLOB
type            native_type     Column type / native_type: PHP native type
                pdo_type        Column tyoe as PDO::PARAM_* constants
unsigned        (use flags)     1 if the column is unsigned
zerofill        (use flags)     1 if the column is zero-filled
(various)       flags           Any flags set for this column
(no)            precision       Numeric precision of this column
  • mysql_fetch_lengths - Get the length of each output in a result

MySQL_ way:

$fieldLengths = mysql_fetch_lengths( $result );

PDO way:

$row          = $stmt->fetch( PDO::FETCH_NUM );
$fieldLengths = array_map( 'strlen', $row );
  • mysql_field_flags - Get the flags associated with the specified field in a result

MySQL_ way:

$fieldFlags = mysql_field_flags( $result, $columnNum );

PDO way:

$fieldFlags = implode( ' ', $stmt->getColumnMeta( $columnNum )['flags'] );

Note: from my test, PDO does not return “auto_increment” flag (to be verified).

  • mysql_field_len - Returns the length of the specified field

MySQL_ way:

$fieldLen = mysql_field_len( $result, $columnNum );

PDO way:

$fieldFlags = $stmt->getColumnMeta( $columnNum )['len'];
  • mysql_field_name - Get the name of the specified field in a result

MySQL_ way:

$fieldName = mysql_field_name( $result, $columnNum );

PDO way:

$fieldName = $stmt->getColumnMeta( $columnNum )['name'];
  • mysql_field_seek - Set result pointer to a specified field offset

MySQL_ way:

mysql_field_seek( $result, $columnNum );

PDO way:

...
  • mysql_field_table - Get name of the table the specified field is in

MySQL_ way:

$tableName = mysql_field_table( $result, $columnNum );

PDO way:

$tableName = $stmt->getColumnMeta( $columnNum )['table'];
  • mysql_field_type - Get the type of the specified field in a result

MySQL_ way:

$fieldType = mysql_field_type( $result, $columnNum );

PDO way:

$fieldType = $stmt->getColumnMeta( $columnNum )['table'];

Note: PDO has different syntax from MySQL_ (i.e. ‘INT24’ vs ‘int’, ‘VAR_STRING’ vs. ‘string’).

  • mysql_free_result - Free result memory
  • PDOStatement::closeCursor - Closes the cursor, enabling the statement to be executed again.

MySQL_ way:

mysql_free_result( $result );

PDO way:

$stmt->closeCursor();

Note: temporary associated in this answer, but the two commands act in a different way.

  • mysql_get_client_info - Get MySQL client info
  • PDO::getAttribute - Retrieve a database connection attribute

MySQL_ way:

$clientInfo = mysql_get_client_info();

PDO way:

$clientInfo = $dbh->getAttribute( PDO::ATTR_CLIENT_VERSION );
  • mysql_get_host_info - Get MySQL host info
  • PDO::getAttribute - Retrieve a database connection attribute

MySQL_ way:

$hostInfo = mysql_get_host_info();

PDO way:

$hostInfo = $dbh->getAttribute( PDO::ATTR_CONNECTION_STATUS );
  • mysql_get_proto_info - Get MySQL protocol info

MySQL_ way:

$protocolInfo = mysql_get_proto_info();

PDO way:

...
  • mysql_get_server_info - Get MySQL server info
  • PDO::getAttribute - Retrieve a database connection attribute

MySQL_ way:

$serverVersion = mysql_get_server_info();

PDO way:

$serverVersion = $dbh->getAttribute( PDO::ATTR_SERVER_VERSION );
  • mysql_info - Get information about the most recent query

MySQL_ way:

$lastQueryInfo = mysql_info();

PDO way:

...
  • mysql_list_dbs - List databases available on a MySQL server

MySQL_ way:

$result = mysql_list_dbs();

PDO way:

$stmt   = $dbh->query( "SHOW databases" );
$array  = array_column( $stmt->fetchAll( PDO::FETCH_NUM ), 0 );
  • mysql_list_fields - List MySQL table fields

MySQL_ way:

$listFields = mysql_list_fields( $dbName, $tableName );

PDO way:

...
  • mysql_list_processes - List MySQL processes

MySQL_ way:

$processes = mysql_list_processes();

PDO way:

...
  • mysql_list_tables - List tables in a MySQL database

MySQL_ way:

$tableList = mysql_list_tables( $sbName );

PDO way:

$tableList = $dbh->query( "SHOW TABLES FROM $dbName" );
  • mysql_num_fields - Get number of fields in result
  • PDOStatement::columnCount - Returns the number of columns in the result set

MySQL_ way:

$columnCount = mysql_num_fields( $result );

PDO way:

$columnCount = $dbh->columnCount();
  • mysql_pconnect - Open a persistent connection to a MySQL server

MySQL_ way:

$con = mysql_pconnect( $dbHost, $dbUser, $dbPassword ) or die( mysql_error() );

PDO way:

...
  • mysql_ping - Ping a server connection or reconnect if there is no connection

MySQL_ way:

mysql_ping() or die( 'Lost MySQL Connection' );

PDO way:

...
  • mysql_set_charset - Sets the client character set

MySQL_ way:

mysql_set_charset( $charSet );

PDO way:

/* At Connection Start: */
$dsn = "mysql:host={$dbHost};dbname={$dbName};charset={$charSet}";
$dbh = new PDO( $dsn, $dbUser, $dbPassword );
  • mysql_stat - Get current system status
  • PDO::getAttribute - Retrieve a database connection attribute

MySQL_ way:

$serverInfo = mysql_stat();

PDO way:

$serverInfo = $dbh->getAttribute( PDO::ATTR_SERVER_INFO );
  • mysql_tablename - Get table name of field

MySQL_ way:

$result    = mysql_list_tables( $dbName );
$tableName = mysql_tablename( $result, $tableNum );

PDO way:

$stmt      = $dbh->query( "SHOW TABLES FROM test" )->fetchAll(PDO::FETCH_NUM);
$tableName = $stmt[1][0];
  • mysql_thread_id - Return the current thread ID

MySQL_ way:

$threadId = mysql_thread_id();

PDO way:

$threadId = $dbh->query( "SELECT CONNECTION_ID()" )->fetch()[0];

Solution from “Get PDO connection ID”

  • mysql_unbuffered_query - Send an SQL query to MySQL without fetching and buffering the result rows
  • PDO::setAttribute - Set a statement attribute

MySQL_ way:

$result = mysql_unbuffered_query( $query );

PDO way:

$dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, False );

Other PDO / PDOStatement commands:

Community
  • 1
  • 1
fusion3k
  • 11,568
  • 4
  • 25
  • 47