Work with databases in php

PHP MySQL Database

With PHP, you can connect to and manipulate databases.

MySQL is the most popular database system used with PHP.

What is MySQL?

• MySQL is a database system used on the web
• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by Oracle Corporation
• MySQL is named after co-founder Monty Widenius’s daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

Databases are useful for storing information categorically. A company may have a database with the following tables:

PHP + MySQL Database System

• PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

Database Queries

A query is a question or a request.

We can query a database for specific information and have a recordset returned.

Look at the following query (using standard SQL):

The query above selects all the data in the «LastName» column from the «Employees» table.

To learn more about SQL, please visit our SQL tutorial.

Download MySQL Database

If you don’t have a PHP server with a MySQL Database, you can download it for free here: http://www.mysql.com

Facts About MySQL Database

MySQL is the de-facto standard database system for web sites with HUGE volumes of both data and end-users (like Facebook, Twitter, and Wikipedia).

Another great thing about MySQL is that it can be scaled down to support embedded database applications.

Источник

Get Started With CRUD Operations in PHP MySQL Databases

Sajal Soni Last updated Oct 19, 2022

In this article, we’re going to explore how you could use a MySQL database to perform CRUD (create, read, update, and delete) operations with PHP. If you want to get your hands dirty with database connectivity in PHP, this article is a great starting point.

If you are just getting started with PHP, you probably realize that database connectivity is an essential feature that you’ll need to get familiar with sooner or later. In most cases, a database is the backbone of any web application and holds the data of the application. So, as a PHP developer, you’ll need to know how to deal with database operations.

In this article, we’ll keep things simple and explore how to use the core mysqli functions. In upcoming articles of this series, we’ll explore a couple of other ways to handle database connectivity.

Today, we’ll go through the basics of database connectivity in PHP, and we’ll use the MySQL database as our database back-end. Let’s have a quick look at what we’ll cover in this article:

• how to set up a database connection
• how to select a database
• how to insert and update records
• how to fetch records
• how to delete records

I assume that you have a working installation of PHP and MySQL and that you’re aware of the basics of MySQL. Also, you’ll need to make sure that the mysqli extension is enabled in your PHP installation, since we’ll use it to do database operations with the MySQL database.

If you are not sure about the mysqli extension, you can check it using the phpinfo() function. In the phpinfo() output, you can check if there’s a section titled mysqli. You should also see the Mysqli Support | enabled header if the mysqli extension is enabled.

There’s also an easy way to check it using the CLI interface. You can run the following command to list all extensions that are enabled with your PHP setup.

It should print a list of extensions, and if it contains the mysqli keyword, the mysqli extension is enabled.

Now that we’re set up, let’s start creating a database connection.

How to Set Up a Database Connection

In this section, we’ll discuss how you can establish a database connection using the mysqli extension.

There are two ways you can use the mysqli extension, procedural and object-oriented, but we’ll use the procedural way in this post to keep things simple. If you’re curious about the object-oriented syntax, let me know your questions in the comment section and I’ll be happy to answer them.

Firstly, let’s go through the syntax of the mysqli_connect function, which is used to set up a connection with the MySQL back-end.

$connection_obj = mysqli_connect("MYSQL_HOSTNAME>", "MYSQL_USERNAME>", "MYSQL_PASSWORD>", "MYSQL_DATABASE>"); 

The mysqli_connect function takes four arguments and returns the connection object upon successful connection. Let’s go through each argument:

• : This should be replaced with the MySQL server’s host-name or IP address. If you’re working with the MySQL server in your local system, you can either use localhost or 127.0.0.1 .
• : The username of your MySQL user. Username is root by default.
• : The password of your MySQL user. Password is blank (i.e. no password) by default.
• : The database that you want to connect to.

Upon successful connection, the $connection_obj contains the connection object. The following image shows the various properties of the $connection_obj object.

With this, you’re ready to run queries against the database which was provided to the argument.

On the other hand, if the connection is not successful, we can debug it as shown in the following snippet:

$connection_obj = mysqli_connect("MYSQL_HOSTNAME>", "MYSQL_USERNAME>", "MYSQL_PASSWORD>", "MYSQL_DATABASE>"); 

Источник

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:

Источник