[go: up one dir, main page]
Scribd
Upload
2 views3 pages

Php Databases

The document serves as a PHP study guide focusing on databases, covering topics such as MySQLi, prepared statements, transactions, and PDO. It highlights the transition from the MySQL extension to MySQLi, the benefits of using prepared statements for efficiency and security, and introduces SQLite as a lightweight database solution. Additionally, it provides code examples for connecting to databases and executing queries using both MySQLi and PDO.

Uploaded by

coolakki20
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
2 views3 pages

Php Databases

The document serves as a PHP study guide focusing on databases, covering topics such as MySQLi, prepared statements, transactions, and PDO. It highlights the transition from the MySQL extension to MySQLi, the benefits of using prepared statements for efficiency and security, and introduces SQLite as a lightweight database solution. Additionally, it provides code examples for connecting to databases and executing queries using both MySQLi and PDO.

Uploaded by

coolakki20
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

Databases

PHP Study Guide


PHP Basics
Functions
Strings & Patterns
Arrays
I/O
Security
Databases
Object Oriented Programming
Data Formats & Types
Web Features
Design and Theory
Databases
Facts
Analyzing Queries
Prepared Statements
Mysqli
Transactions
PDO
SQLite
Facts
Recent versions of PHP no longer ship with the MySQL extension built in, you must
enable it at configure time.
We now have two versions of the MySQL extension available, MySQL and MySQLi
The MySQLi extension makes some of MySQL’s more recent functionality available,
things like prepared statements
Analyzing Queries
EXPLAIN EVERYTHING!
EXPLAIN SELECT * FROM 07_amazon_monitor_rank_results WHERE asin = '0672324547' ;
Prepared Statements
allow you to increase speed of repeated queries, and isolate data from command
First you Prepare the statement, then you bind parameters to it, then you execute
it
http://php.net/manual/en/mysqli.prepare.php
Bound parameters
The bound-parameter variant allows you to store a query on the MySQL server, with
only the iterative data being repeatedly sent to the server, and integrated into
the query for
execution.
Bound results
The bound-result variant allows you to use sometimes-unwieldy indexed or
associative arrays to pull values from result sets by binding PHP variables to
corresponding retrieved
fields, and then use those variables as necessary
After a query has been prepared and executed, you can bind variables to the
retrieved fields by using $stmt->bind_result.
1
2
3 <?php
boolean mysqli_stmt_bind_param (mysqli_stmt stmt, string types, mixed &var1 [,
mixed &varN)
class mysqli_stmt {
4
5
6 }
7
8
9
10
11
boolean bind_param (string types, mixed &var1 [, mixed &varN])
boolean mysqli_stmt_bind_result (mysqli_stmt stmt, mixed &var1 [, mixed &varN])
class mysqli_stmt {
boolean bind_result (mixed &var1 [, mixed &varN])
}
Mysqli
1
2
3
4 <?php
$link = mysqli_connect("localhost", "u", "p", "ex");
$city = "Montreal";
$stmt = mysqli_stmt_init($link);
5
6
7 if ($stmt = mysqli_stmt_prepare ($stmt, "SELECT Province FROM City WHERE
Name=?"))
{
mysqli_stmt_bind_param($stmt, "s", $city);
8
9
10
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $province);
mysqli_stmt_fetch($stmt);
http://php-guide.evercodelab.com/pages/databases.html
1/310/06/2017
Databases
11
12
13 printf("%s is in district %s\n", $city, $province);
mysqli_stmt_close($stmt);
}
14 mysqli_close($link);
Transactions
Allow you to merge multiple queries into one atomic operation, either they ALL
execute successfully, or none do
1
2
3
BEGIN TRANSACTION #name;
... queries here
COMMIT;
PDO
PHP Data Objects
consistent object oriented method to interact with various databases
database specific PDO driver must also be installed
1 <?php
2
3 $pdoConnection = new PDO('mysql:host=localhost;dbname=example', USERNAME,
PASSWORD);
foreach ($pdoConnection->query("SELECT * FROM users") AS $user) { echo $user['id'];
}
Prepared statements
1 <?php
2
3 $query = "SELECT * FROM posts WHERE topicID = :tid AND poster = :userid";
$statement = $pdoConnection->prepare($query, array(PDO::ATTR_CURSOR,
PDO::CURSOR_FWDONLY));
4 $statement->execute(array(':tid' => 100, ':userid' => 12));
5
6 $userAPosts = $statement->fetchAll();
$statement->execute(array(':tid' => 100, ':userid' => 13));
7 $userBPosts = $statement->fetchAll();
closing connection
1 <?php
2 $pdoConnection = null; //
1
2 <?php
PDOStatement->nextRowset()
array PDO::errorInfo ( void ) * 0 - SQLSTATE error code (a five characters
alphanumeric identifier defined in the ANSI SQL standard) * 1 - Driver-specific
error code. * 2 - Driver-
specific error message.
SQLite
is a database, without the database
rather than using a separate program to persistently maintain the database, SQLite
on the other hand requires the C libraries that comprise the DB to be built into
whatever program
would like to use them
was built into PHP by default as of PHP5, which is cool, some twits turn it off,
which isn’t
It’s fast, free, and has nice licensing terms
Apart from not needing to connect to a remote server or process, SQLite is no
different from other database systems
catagorizes data into textual and numeric
http://php-guide.evercodelab.com/pages/databases.html
2/310/06/2017
0 Comments
 Recommend
Databases
1

PHP study guide
Login
Sort by Best
⤤ Share
Start the discussion…
Be the first to comment.
✉ Subscribe d Add Disqus to your siteAdd DisqusAdd
🔒 Privacy
http://php-guide.evercodelab.com/pages/databases.html
3/3

You might also like