Access database for php

post

Fully Remove Python and Install a Fresh Python in MacOS and Windows

The itertools and functools in Python

Developing R Packages using devtools

Share this post

Accessing Databases Using PHP

For accessing databases in a Web application, PHP provides two interfaces, PDO and MySQLi , to access a database in a web application. As indicated by its name, MySQLi works only with MySQL database.

PDO stands for PHP Data Objects which is a consistent object-oriented interface for accessing 12 different Databases. This means once we know how to program with PDO for accessing one Database type, the same code will work for most other database types too.

PDO API Extension

By default, PDO is enabled in PHP installations except with two extensions that must be installed: PDO and a driver for the database to be accessed.

All the current supporting PDO drivers are available at php.net-PDO Driver

To install MySQL driver, install php-mysql package along with a PHP installation.

Windows system with an xampp installation

The latest xampp (by March 2019) should include PHP 7.0. The extension, pdo_mysql , has been included in the installation.

To find whether PDO extension is enabled or not, open the PHP information page at http://locahost:80/dashboard/phpinfo.php. (You may need to replace 80 with your port number.)

On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have 15 matches for pdo.

The following blocks on the page confirms that two PDO drivers have been pre-installed for MySQL and SQLite and both drivers have been enabled.

If the PDO driver is not enabled, open the PHP initialization file, php/php.ini , at the xampp directory. Uncomment (remove the starting semicolon) the line for the Database type. The following shows two PDO drivers are enabled for MySQL and SQLight, respectively.

extension=php_mysqli.dll ;extension=php_oci8_12c.dll ; Use with Oracle Database 12c Instant Client ;extension=php_openssl.dll ;extension=php_pdo_firebird.dll extension=php_pdo_mysql.dll ;extension=php_pdo_oci.dll ;extension=php_pdo_odbc.dll ;extension=php_pdo_pgsql.dll extension=php_pdo_sqlite.dll

MacOS with AMPPS

To find whether PDO extension is enabled or not, open the PHP information page at http://localhost/cgi-bin/phpinfo.cgi. On the PHP information page, search (CTRL+F) the word ‘pdo’; if exists, it may have over 20 matches for pdo.

(AWS) EC2 instance running Ubuntu with Apache installation

You can install PHP 7.0 and PDO for MySQL by the command:

$ sudo apt-get install -y php7.0 libapache2-mod-php7.0 php7.0-mysql

If you want to install Apache, PHP and MySQL, all three one time, run the commands:

$ sudo apt-get install -y mysql-server mysql-client $ sudo mysql_secure_installation $ sudo apt-get install -y php7.0 apache2 libapache2-mod-php7.0 php7.0-mysql

To see what extensions available, run the command sudo apt search php7.0-* .

The following part shows a Web database in MySQL by using PDO.

Creating a MySQL database

In order to show the code for Web databases by using PDO, be sure to have your database service running and a database is ready.

If you need a sample database, follow two posts below to build a MySQL database counselor in a local MySQL server from xampp.

PDO will be used to view the counselor table in the sample database.

Script 1: login.php

1 2define('DB_DRIVER', 'mysql'); 3define('DB_HOST', "localhost"); 4define("DB_USER", "dbusername"); 5define("DB_PASSWORD", "dbpassword"); 6define("DB_DATABASE", "counselor"); 7?> 

Script 2: mysql_error.php

1 2function mysql_error() 3< 4 echo 'We are sorry.
Fatal error
Please click the back button or contact the administrator at admin@site.com'; 5> 6?> 

Script 3: access_database.php

 1  2require_once('login.php'); 3require_once('mysql_error.php'); 4 5//$dsn = 'mysql:host=localhost:3306;dbname=counselor'; 6 $dsn = DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE; 7 8try < 9 // 1.Make a PDO object 10 $pdo = new PDO($dsn, DB_USER, DB_PASSWORD); 11 if(!$pdo) die('Fatal Error'); 12 13 // 2.Compose a SQL query 14 $tablename = 'counselor'; 15 $sql = "select * from $tablename"; 16 17 // 3.Run the query 18 $result = $pdo->query($sql); 19 20 // 4.Parse the query result 21 // PDO::FETCH_ASSOC: Return next row as an array indexed by column name 22 // 4.1 Get table column names 23 $colnames = array_keys($result->fetch(PDO::FETCH_ASSOC)); 24 // 4.2 Print each column name 25 foreach ($colnames as $key => $name) < 26 echo " $key: $name "; 27 > 28 // 4.3 Fetch all rows from $result 29 $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC); 30 // Print each row array 31 foreach ($rows as $key => $row) < 32 echo "
"; 33 foreach ($row as $key => $value) < 34 echo "
"; 35 echo htmlspecialchars($key)." : ".htmlspecialchars($value); 36 > 37 echo '
'; 38 > 39 // 5. Close a connection 40 $result->close(); 41 $pdo->close(); 42> catch (Exception $ex) < 43 echo($ex->getMessage()); 44 mysql_error(); 45> 

Specify a Data Source Name

Line 6 tspecifies a data source name in the variable $dsn . The resulting dsn string is

 mysql:host=localhost;dbname=counselor

Instantiate a new PDO Object

Line 10 creates a new PDO object for a database connection associated with a particular data source name and database user credential.

 $pdo = new PDO($dsn, DB_USER, DB_PASSWORD); 

Compose a SQL Statement

After a PDO object is available, lines 14 and 15 create a SQL statement in a string variable $sql.

 $tablename = 'counselor'; $sql = "select * from $tablename"; 

Execute the Statement

Line 18 shows the script that runs a SQL statement in $sql and stores the result in $result .

Fetch the Query Results

Lines 23 through 38 are the script for parsing $result to get the database table column names and print each row in the query result.

 $colnames = array_keys($result->fetch(PDO::FETCH_ASSOC)); 

$result->fetch(PDO::FETCH_ASSOC) fetches the first result row into an array; then extracts the keys of the array into $colnames . The array keys are the database table column names.

Fetch Style:

A call to the fetch method on $result with a PDO constant PDO::FETCH_ASSOC for a fetch style,

will fetch the next row into a PHP array, whose elements are indexed by the database table column names. Take the counselor table as an example, the array from fetching the first row in $result is:

Array ( [counselor_id] => 1 [first_name] => Jake [nick_name] => The Snake [last_name] => Roberts [telephone] => 412 565-5656 [email] => snake@ifpwafcad.com [member_since] => 2003-09-10 )

Refer to php.net for other fetch styles.

At the lines 25 through 27, a foreach statement scans each element in the array $colnames and prints both key and element in an HTML paragraph.

 foreach ($colnames as $key => $name) < echo " $key: $name "; > 

Print All Rows on the Page

The following scrip, at the lines 28 through 36, will print every row returned from running the query $sql, in an HTML paragraph with each column in one line.

 // 4.3 Fetch all rows from $result $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC); // Print each row array foreach ($rows as $key => $row) < echo "
"; foreach ($row as $key => $value) < echo "
"; echo htmlspecialchars($key)." : ".htmlspecialchars($value); > echo '
'; > 

Note: Everything is displayed in plain text without styles. Custom style sheets can be added to format the output.

For example, to custom each row with the following look,

Add the following embedded style sheets after the PHP block.

style> p < font-family: georgia, serif; font-size: 1.2em; padding: 1em; margin: 0 auto; width: 50%; border: 2px solid #ff9900; border-radius: 1em; background-color: snow; > p:hover < background-color: lightsalmon; > hr < color: #ff9900; height: 1px; > style>

Test the Page

Test the PHP page in a Web server that supports PHP.

© 2017-2021 jDataLab .
Under CC BY-NC 4.0
Powered by Hugo 0.63.0

Источник

PHP Connect to MySQL

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the «i» stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be «Whatever you like».

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

PDO Installation

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
echo «Connected successfully»;
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
echo «Connected successfully»;
?>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;

try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo «Connected successfully»;
> catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
>
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Источник