Upgrading from PHP 4

[gerkintrigg]

Oh my oh my. The mysqli protocols confuse me. I know php4 inside out but I’ve been out of the game for many years and need to work out how the hell mysqli works. 

All I need is a simple syntax to grab a record from a table and output it using an echo command. Can anyone help me? All the websites I’ve visited seem to get too deep into it too quickly and I end up getting lost. 

[mac_gyver]

forget about the mysqli extension when updating old code, especially when there's external/unknown data being put into the sql query statement. use the much simpler and more consistent php PDO extension instead.

after you make the connection using the PDO extension, in $pdo, the following are some usage examples -

a query that doesn't have any external/unknown data being put into it 

// form the sql query statement
$sql = "your sql query statement here...";
// execute the query
$stmt = $pdo->query($sql);

// for SELECT queries, fetch the data into an appropiately named php variable
// for a query that will match at most one row
$some_descriptive_var = $stmt->fetch();
// for a query that will match a set of zero or more row(s)
$some_descriptive_var = $stmt->fetchAll();

// use the variable holding the data at the appropiate place in your html document. if the query didn't match any data, the variable will be a boolean false/empty() value.

 

for a query that does have external/unknown data being put into it, you need to use a prepared query, with a ? place-holder in the sql query statement for each data value, then supply the data when you execute the query.

// form the sql query statement
$sql = "your sql query statement here...";
// prepare and execute the query
$stmt = $pdo->prepare($sql);
$stmt->execute(supply an array of the variable(s) holding the data values here...);

// the code dealing with the result from a SELECT query is the same as above

when converting an old query that has variable(s) in the sql query statement, you would remove the variable, any single-quotes, {}, and concatenation dots associated with the variable from the sql query statement, and replace each variable with just a ? place-holder. the variable(s) that were removed would be supplied as an array to the ->execute(...) method call. you would also remove, rather than convert, any ..._escape_string() functions in old code.

when you make the connection, set emulated prepared queries to false (you want to run TRUE prepared queries when possible), error mode to exceptions, and set the default fetch mode to assoc (so that you don't have to specify it in every fetch statement, but can override it when necessary.) here is some typical connection code -

$DB_HOST = ''; // db hostname or ip address
$DB_USER = ''; // db username
$DB_PASS = ''; // db password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match the character encoding of your db tables

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc

by using exceptions to handle database statement errors (the connection always uses an exception for an error, the above setting applies to query, prepare, and execute statements), you can eliminate any existing error handling logic, rather than to convert it. in most cases you should just let php catch any exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. the only time you would need to have a try/catch block in your code to catch and handle a database exception is when inserting/updating duplicate data. this is a recoverable error. your catch logic would detect if a duplicate key error occurred (requires setting the database table column(s) as a unique index, if they are not already), and setup a user error message indicating what was wrong with the data that was submitted. if the error is not for a duplicate key, you would re-throw the exception and let php catch and handle it.

Edited February 25, 2019 by mac_gyver

[gerkintrigg]

Thanks for the very comprehensive reply. I've tried your code and seem to be getting a problem with this line:

 

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);

 

[Barand]

Congratulations on your decision to upgrade from v4 (only 12 years late)

Quote

Support for PHP 4 has been discontinued since 2007-12-31. Please consider upgrading to PHP 5.

I love guessing games. (When is the closing date?)

My entry for the "Guess gerkintrigg's problem" is

• Have you enabled the PDO extension in your php.ini file? (phpinf() will tell you)

[mac_gyver]

11 hours ago, gerkintrigg said:

a problem

i'm guessing the answer is 42?