what is mysqli? since the mid-90s, mysql extension has served as the major bridge between php and...
Post on 31-Dec-2015
216 Views
Preview:
TRANSCRIPT
What is MySQLi?Since the mid-90s, Mysql extension has
served as the major bridge between PHP and MySQL.
Although it has performed its duty quite well, situation has changed since the introduction of PHP 5 and MySQL 4.1
What is MySQLi?To correct the issues of MySQL extension, a
new extenstion has been created for PHP5It is called MySQLiIt supports all the latest features in MySQL
server 4.1 or higherThe ‘i’ stands for any one of: improved,
interface, ingenious, incompatible or incomplete.
Major FeaturesProcedural InterfaceAn object-oriented interface Support for the new MySQL binary
protocol that was introduced in MySQL 4.1.
Support for the full feature set of the MySQL C client library
Let’s see the code!/* Connect to a MySQL Server */ $mysqli = new mysqli('hostname','username','password','database');
if ( mysqli_connect_errno() ) { echo "Connection error. Errorcode: ".mysqli_connect_error(); exit;
}
/* Close the connection */ $mysqli->close();
How to Run a Queryif ($result = $mysqli->query('SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {
/* Fetch the results of the query */ while( $row = $result->fetch_assoc() ){
echo $row['Name'] ." (". $row['Population'] .")\n"; }
/* Destroy the result set and free the memory used for it */ $result->close();
} else { echo $mysqli->error;
}
Prepared StatementsOne of the new features of MySQLiUsing this feature, it is possible to create
queries that are:More secureHave better performanceMore convenient to write
Two types of Prepared Statements:Bound ParameterBound Result
Bound Parameter Prepared StatementsA Query template is created and sent to the
MySQL serverMySQL server validates it, stores it and
returns a special handle for future useWhen a query needs to be executed, data to
fill in the template is sent to the serverA complete query is formed and then
executed
AdvantagesThe body of the query is sent only once, later
only data to fill in are sentMost of the work required to validate and parse
the query only needs to be done a single time, instead of each time the query is executed.
The data for the query does not need to be passed through a function like mysql_real_escape_string() to ensure that no SQL injection attacks occur. Instead, the sent data is handled safely by server when it is combined with the prepared statement.
Query StructureThe '?' placeholders can be used in most places
that could have literal data, e.g. a query could be transformed fromSELECT Population FROM City WHERE Name = 'Dhaka';
toSELECT Population FROM City WHERE Name = ?;
Let’s see a complete example of bound parameter prepared statement
Using Bound Parameter Prepared Statementsif( $stmt = $mysqli->prepare("INSERT INTO CountryLanguage
VALUES (?, ?, ?, ?)") ){ $stmt->bind_param('sssd', $code, $language,
$official, $percent); $code = 'BAN'; $language = 'Bangla'; $official = 'F'; $percent = 77.8; /* execute prepared statement */ $stmt->execute(); echo $stmt->affected_rows. " Row inserted.\n"; /* close statement and connection */ $stmt->close();
}
The Format StringThe following table shows the bound varaible
types and when to use them:
BIND Type COLUMN Type
i All INT types
d DOUBLE and FLOAT
b BLOBs
s All other types
Bound Result Prepared StatementsAllow the value of variables in a PHP script to
be tied to the value of fields of data in a query result set.
Create a queryPrepare the queryAsk the MySQL server to execute the queryBind PHP variables to columns in the query
resultRequest that a new row of data be loaded into
the bound variables.
Using Bound Result Prepared Statements if( $stmt = $mysqli->prepare("SELECT Code, Name FROM
Country ORDER BY Name LIMIT 5") ){$stmt->execute(); /* bind variables to prepared statement */ $stmt->bind_result($col1, $col2); /* fetch values */ while ($stmt->fetch()) { echo $col1 ." ". $col2 ."\n";
}/* close statement */ $stmt->close();
}
Using Bound Parameters and Bound Results TogetherIt is possible to use bound parameters and
bound results together in a single prepared statement.
Lets see a more complete example that uses both of these
Using Bound Parameters and Bound Results Togetherif ( $stmt = $mysqli->prepare("SELECT Code, Name FROM
Country WHERE Code LIKE ? LIMIT 5") ) { $stmt->bind_param("s", $code); $code = "B%"; $stmt->execute(); /* bind variables to prepared statement */ $stmt->bind_result($col1, $col2); /* fetch values */ while ($stmt->fetch()) {
echo $col1 ." ". $col2 ."\n"; } /* close statement */ $stmt->close();
}
top related