3 methods to connect to MySQL with PHP with code examples
To start using the MySQL database, you must first understand how to connect from your custom PHP program (script) to this very MySQL database.
This article describes the following three methods, along with the corresponding PHP code examples, which explain how to connect to your database from PHP.
For all of the examples below, we will be connecting to an already existing MySQL database. Note: Everything explained here will also work with MariaDB, just as it does with MySQL.
1. Connecting to PHP using the mysqli extension
*mysqli means MySQL Improved
Create the following mysqli.php file
connect_error) ( die("Error: unable to connect: " . $conn->connect_error); ) echo "Connected to the database.
"; $result = $conn->query("SELECT id FROM goroda"); echo "Number of rows: $result->num_rows"; $result->close(); $conn->close(); ?> B the above code:
- mysqli - This function initiates a new connection using the mysqli extension. The function takes four arguments:
- localhost is the name of the host where the MySQL database is running
- name - the MySQL username to connect to
- pass - password for mysql user
- db - MySQL database to connect to.
- qvery is a MySQL query function. In this example, we select the id column from the cities database.
- Finally, we show the number of rows selected using the num_rows variable in the result. We also close both the result and the connection variable as shown above.
Connected to the base. Number of lines: 6 2. Connecting from PHP MySQL PDO Extension
*PDO stands for PHP Data Objects
The PDO_MYSQL driver implements the PDO interface provided by PHP to connect from your PHP script to a MySQL database.
Create the following mysql-pdo.php file:
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected to the database.
"; $sql = "SELECT id FROM goroda"; print "List id:
"; foreach ($conn->query($sql) as $row) ( print $row["id"] . "
"; ) $conn = null; ) catch(PDOException $err) ( echo "Error: unable to connect: " . $err->getMessage(); ) ?> In the above:
- new PDO - will create a new PDO object that will take the following three arguments:
- mysql connect string: it will be in the format "mysql:host=localhost;dbname=db". In the example above, db is running on localhost and we are connecting to the db database.
- MySQL username to connect to
- Password for mysql user
- $sql variable - create the sql query you want to execute. In this example, we select the id column from the cities table.
- query($sql). Here we are executing the sql query we just created.
- foreach. Here we iterate over the result from the query command above and store it in the $row variable and then echo it out.
- In MySQL PDO, to close a connection, simply set the $conn variable to null.
Connected to the base. List id: 1 2 3 4 5 6 3. Connecting from PHP using deprecated mysql functions
Use this method only if you are using an older version of PHP and for some reason cannot upgrade to the newer version. It is recommended to use Method #2 and Method #3 shown above instead of this method. I have included this method for reference only and not as a recommendation to use.
This particular extension has been deprecated as of PHP 5.5. But as of PHP 7.0 this won't even work as it has been removed. Since PHP 5.5, when you use these functions, it will generate an E_DEPRECATED error.
Create mysql.php file:
"; $result = mysql_query("SELECT id FROM goroda"); $row = mysql_fetch_row($result); echo "id 1: ", $row, "
\n"; mysql_close($conn); ?> In the above:
- The mysql_connect function takes three arguments:
- the name of the host where the MySQL database is running;
- MySQL username to connect to;
- password for mysql user. Here it connects to a MySQL database that is running on a local server using a username and password.
- mysql_select_db function. As the name suggests, it selects the database you want to connect to. Equivalent to the "use" command. In this example, we are connecting to the db database.
- mysql_query function - used to specify your MySQL query. In this example, we select the id column from the cities database. mysql_fetch_row. Use this function to extract rows from the SQL query we just created.
- Finally, close the connection using the mysql_close command as shown above.
Connected to the base. id 1: 1 This is how you can connect to MySQL. Again, it is better to use the first two methods; about
DBMS MySQL is one of the many databases supported by PHP. The MySQL system is distributed free of charge and has enough power to solve real problems.
A Brief Introduction to MySQL
SQL is an abbreviation for the words Structured Query Language, which stands for Structured Query Language. This language is the standard means for accessing various databases.
The MySQL system is a server to which users of remote computers can connect.
To work with databases, it is convenient to use the tool included in the Web developer kit: Denwer phpMyAdmin. Here you can create a new database, create a new table in the selected database, populate the table with data, and add, delete, and edit data.
MySQL defines three basic data types: numeric, datetime, and string. Each of these categories is subdivided into many types. The main ones are:
Each column after its data type contains other specifiers:
| Type of | Description |
|---|---|
| NOT NULL | All table rows must have a value in this attribute. If not specified, the field may be empty (NULL) |
| AUTO_INCREMENT | A special MySQL feature that can be used on numeric columns. If you leave this field blank when inserting rows into a table, MySQL automatically generates a unique identifier value. This value will be one more than the maximum value already existing in the column. Each table can have at most one such field. Columns with AUTO_INCREMENT must be indexed |
| PRIMARY KEY | The column is the primary key for the table. The data in this column must be unique. MySQL indexes this column automatically |
| UNSIGNED | After integer type means its value can be either positive or null |
| COMMENT | Table column name |
Creating a New MySQL Database CREATE DATABASE.
CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin
Create a new table performed using SQL command CREATE TABLE. For example, the books table for a bookstore would have five fields: ISBN, author, title, price, and number of copies:
CREATE TABLE books (ISBN CHAR(13) NOT NULL, PRIMARY KEY (ISBN), author VARCHAR(30), title VARCHAR(60), price FLOAT(4,2), quantity TINYINT UNSIGNED); To avoid an error message if the table already exists, you need to change the first line by adding the phrase "IF NOT EXISTS": CREATE TABLE IF NOT EXISTS books ...
For creating auto-updating field with the current date of type TIMESTAMP or DATETIME use the following construct:
CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Addendum data into this table is carried out using the SQL command INSERT. For example:
INSERT INTO books (ISBN, author, title, price, quantity) VALUES ("5-8459-0184-7", "Zandstra Mat", "Teach Yourself PHP4 in 24 Hours", "129", "5");
The operator is used to retrieve data from a table. SELECT. It retrieves data from the database by selecting rows that match the specified search criteria. The SELECT statement comes with a large number of options and use cases.
The * symbol means that all fields are required. For example:
SELECT * FROM books;
To access only a certain field, you must specify its name in the SELECT statement. For example:
SELECT author, title, price FROM books;
To access a subset of rows in a table, you must specify a selection criterion, which is set by the construct WHERE. For example, to select available inexpensive books about PHP, you would compose a query:
SELECT * FROM books WHERE price % Matches any number of characters, even zero
_ Matches exactly one character
In order for the rows retrieved by query to be listed in a certain order, the construction is used ORDER BY. For example:
SELECT * FROM books ORDER BY price;
Default order sorting goes in ascending order. You can reverse the sort order using the keyword DESC:
SELECT * FROM books ORDER BY price DESC;
Sort You can also use multiple columns. Instead of column names, you can use their serial numbers:
SELECT * FROM books ORDER BY 4, 2, 3;
To change the values previously recorded in the table, use the command UPDATE. For example, the price of all books increased by 10%:
UPDATE books SET price = price * 1.1;
The WHERE clause will restrict the UPDATE to certain rows. For example:
UPDATE books SET price = price * 1.05 WHERE price
To remove rows from a database, use the statement DELETE. Unnecessary rows are specified using the WHERE clause. For example, some books are sold:
DELETE FROM books WHERE quantity = 0;
If you want to delete all entries
TRUNCATE TABLE table_name
To completely delete a table, use:
DROP TABLE table_name
PHP communication with MySQL database
After working with phpMyAdmin to create a database, you can start connecting this database to the external Web interface.
To access a database from the Web using PHP, the following basic steps must be taken:
- Connecting to the MySQL server.
- Database selection.
- Executing a database query:
- addition;
- removal;
- change;
- Search;
- sorting.
- Getting the result of a query.
- Detaching from the database.
To connect to a database server in PHP there is a function mysql_connect(). Its arguments are computer name, username and password. These arguments can be omitted. By default computer name = localhost , then username and password are not required. If PHP is used in combination with the Apache server, then you can use the function mysql_pconnect(). In this case, the connection to the server does not disappear after the program ends or the function is called. mysql_close(). Functions mysql_connect() and mysql_pconnect() return the connection ID if everything went well. For example:
$link = mysql_pconnect(); if (!$link) die ("Unable to connect to MySQL");
After the connection to the MySQL server is established, you need to select a database. For this, the function is used mysql_select_db(). Its argument is the name of the database. The function returns true if the specified database exists and can be accessed. For example:
$db = "sample"; mysql_select_db($db) or die("Can't open $db");
Adding, deleting, modifying, and selecting data requires constructing and executing an SQL query. To do this, PHP has a function mysql_query(). Its argument is a query string. The function returns the request ID.
Example 1
Each time you run Example 1, a new record will be added to the table containing the same data. Of course, it makes sense to add the data entered by the user to the database.
Example 2-1 shows an HTML form for adding new books to the database.
Example 2.1
The results of filling out this form are transferred to insert_book.php.
Example 2.2
In example 2.2, the entered string data is processed by the function addslashes(). This function adds backslashes before single quotes ("), double quotes ("), backslashes (\), and null byte. The fact is that according to the requirements of the database query syntax, such characters must be enclosed in quotes.
To determine the number of records in the query result, use the function mysql_num_rows().
All query result records can be viewed in a loop. Before that, using the function mysql_fetch_ for each entry, an associative array is obtained.
Example 3.1 is an HTML form for searching for specific books in a database.
Example 3.1
The results of this form are passed to search_book.php.
Example 3.2
".($i+1). $row["title"]. "
Alternative option
".($i++) .$row["title"]."
"; echo "Author: ".$row["author"]."
"; echo "ISBN: ".$row["ISBN"]."
"; echo "Price: ".$row["price"]."
"; echo "Quantity: ".$row["quantity"]."
So how does the Web database architecture work:
- The user's web browser issues an HTTP request for a specific web page. For example, a user using an HTML form searches for all PHP books. The form processing page is called search_book.php.
- The web server receives a request for search_book.php, retrieves this file, and passes it to the PHP engine for processing.
- PHP connects to the MySQL server and sends the request.
- The server receives a database request, processes it, and sends the result (a list of books) back to the PHP engine.
- The PHP engine completes the script execution, formats the query result into HTML. The result is then returned as HTML to the Web server.
- The web server sends the HTML to the browser, and the user is able to view the requested list of books.
Using the transaction mechanism
Using the transaction mechanism as an example of how to transfer money from one person to another
If(mysql_query ("BEGIN") && mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = "Eve"") && mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = "Ida"") && mysql_query ("COMMIT"))( echo "Success"; )else( mysql_query ("ROLLBACK"); echo "Not successful"; )
SELECT … FOR UPDATE
If you run multiple processes that make a select query on the same table, they can select the same record at the same time.
To avoid the above situation, it is necessary to execute not just a SELECT query, but its extended version, which many people do not even suspect: SELECT ... FOR UPDATE.
Thus, when this query is executed, all affected records in the database will be locked until the session with the database is completed or until these records are updated. Another script will not be able to select locked records until one of the mentioned conditions occurs.
However, not all so simple. You need to fulfill a few more conditions. First, your table must be based on the InnoDB architecture. Otherwise, the blocking simply will not work. Secondly, before performing the fetch, you must disable the auto-commit of the query. Those. in other words, automatic execution of the request. After you specify an UPDATE request, you will need to contact the database again and commit the changes using the COMMIT command:
4.5KThe text uses the translation of official documentation made by the All-Russian Club of Webmasters.
All work with databases comes down to connecting to the server, selecting a database, sending a request, processing the request, and disconnecting from the databases. So, let's look at the whole thing point by point. For clarity of the entire text, let's imagine that the MySQL server is installed on the local machine. The port for working with it is standard. To connect, we will use the username "root" and the password "no_one".
Connection
Connecting to a database is done using the "mysql_connect()" function. It is passed three parameters: server name (or server name:port to connect), username and password. In case a second call to "mysql_connect()" is made with the same arguments, no new connection will be established - instead, the connection ID of an already open connection will be returned (i.e. work will continue with the same database). If you are working with only one database, then you do not need to create a connection ID. Once the script has finished executing, the connection to the server will close unless it was explicitly closed by an earlier call to "mysql_close()".
Example: $connect = mysql_connect('localhost', 'root', 'no_one'); In this case, the $connect variable is the connection identifier. If the work is carried out with only one database, then the code is written without an identifier: mysql_connect('localhost', 'root', 'no_one');
Database selection
"mysql_select_db" - selects a MySQL database. This means that the server may not have one database, but several. With this command, we will select the one that we need (for which we have rights). The parameter of this function is the name of the base. The base selected in this way becomes active and is associated with a specific identifier. If the connection identifier is not defined, then the last connection to the database is used.
Example: mysql_select_bd('test', $connect); - where test is the database name and $connect is the connection id. If the work is carried out with only one database, then the code is written without an identifier: mysql_select_bd('test');
Sending a request
mysql_query() sends a query to the database currently active on the server that is associated with the specified connection id. If no ID is specified, the last open link is used. The parameter of this function is a string with an sql query.
Example: $tmp=mysql_query("slect * from table", $connect); - this command will return the entire contents of the table table from the active database pointed to by the identifier $connect. If the work is carried out with only one database, then the code is written without an identifier: $tmp=mysql_query("slect * from table");
Processing request
There are several functions for handling requests. The choice of one or another method of processing requests depends on the style of programming, the task. It should also be borne in mind that different options “load” the server in different ways (some strongly, some not so much). Let's analyze a few of them.
mysql_fetch_object - Returns a php object as a result of processing. This method is good for those who are used to object programming
Example: while($result= mysql_fetch_object($tmp)) echo($result->name);
With php...
Creating a database connection in PHP in different ways:
1) old-fashioned way to connect to MySQL:
$conn=mysql_connect($db_hostname, $db_username, $db_password) or die ("No connection to server");
mysql_select_db($db_database,$conn) or die("Unable to connect to database");
Variable explanations below.
This uses the following functions:
- mysql_connect()- to connect to the server;
- mysql_select_db()- to connect to the database;
At the same time, we constantly check for errors in this way: or die ("Error such and such"); - translates as or die with such and such a mistake - to immediately find where the error is.
config.php
// variables for connecting to the database
$host = "localhost"; /host
$username = "root"; // password to connect to the database
$password = ""; // password for connecting to the database - on the local computer it can be empty.
$database_name = "my-dolgi"; // database name
// old way of connecting to the database
mysql_connect($host, $username, $password) or die("Can't connect to create connection");
// select database. If error - output
mysql_select_db($database_name) or die(mysql_error());
index.php
require_once "config.php";
$result = mysql_query("SELECT Name, Money FROM Dolg ORDER BY Money DESC LIMIT 5") or die(mysql_error());
";
while ($row = mysql_fetch_assoc($result)) (
";
}
mysql_free_result($result);
// Close the connection
mysql_close();
2) More progressive procedural style - connecting to the database using mysqli:
This way:
- more convenient;
- faster up to 40 times;
- increased security;
- there are new features and functions;
An example of connecting to a database in php with a selection from a table
config.php
// database connections
$link = mysqli_connect("localhost", "username", "password", "name-database"); // here we enter your data directly: username, password and database name, the first field is usually localhost
// output connection error
if (!$link) (
echo "Error connecting to database. Error code: " . mysqli_connect_error();
exit;
}
Please note - mysqli is used everywhere, not mysql !!!
index.php
require_once "config.php";
// Execute the request. If there is an error, output
if ($result = mysqli_query($link,"SELECT Name, Money FROM Dolg ORDER BY Money DESC LIMIT 5")) (
echo "To whom do I owe descending:
";
// Fetch query results
while ($row = mysqli_fetch_assoc($result)) (
echo $row["Name"] . " with debt " . $row["Money"] . "rubles.
";
}
// release used memory
mysqli_free_result($result);
// Close the connection
mysqli_close($link);
}
As you can see, some points have changed (highlighted in italics).
3) Object-oriented method of connecting to the MySQL database - using methods and classes:
Cons: More complex and less susceptible to errors.
Pros: brevity and convenience for programmers with experience.
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
if($conn->connect_errno)(
die($conn->connect_error);
) else (echo "Database connection successfully established";)
Here, in principle, everything is intuitive:
- $db_hostname is host(mostly localhost),
- $db_database- database name;
- $db_username and $db_password are username and password respectively!
An example of connecting to a database in php OOP style with a selection from a table
config.php
// database connections
$mysqli = new mysqli("localhost", "username", "password", "name-database"); // here we enter your data directly: username, password and database name, the first field is usually localhost
// output connection error
if ($mysqli->connect_error) (
die ("Error connecting to database: (" . $mysqli->connect_errno . ") " . mysqli_connect_error) ;
}
Please note - mysqli is used everywhere, not mysql !!! and unlike the previous method, "->" arrows appear, which indicate that this is OOP style.
index.php
require_once "config.php";
// Execute the request. If there is an error, output
if ($result = $ mysqli->query("SELECT Name, Money FROM Dolg ORDER BY Money DESC LIMIT 5")) (
echo "To whom do I owe descending:
";
// Fetch query results
while ($row = $result-> fetch_assoc()) {
echo $row["Name"] . " with debt " . $row["Money"] . "rubles.
";
}
// release used memory
$result->close();
// Close the connection
$mysqli->close();
}
Your task is to find the differences.
4) Communication with the database using PDO:
When connecting to a MySQL database, prepared statements are used (by the prepare method) and as a result, greater security and greatly increases performance.
config file from the previous method! - same
index.php
// PDO style to communicate with MySQL
if ($stmt = $mysqli->prepare("SELECT Name, Voney FROM Dolg ORDER BY Money< ? LIMIT 5")) {
$stmt->bind_param("i", $summa);
$sum = 100000;
// start execution
$stmt->execute();
// Declaration of variables for prepared values
$stmt->bind_result($col1, $col2);
echo "To whom do I owe descending:
";
// Fetch query results
while ($stmt->fetch()) (
echo $col1 . " with debt " . $col2 . "rubles.
";
}
// release used memory
$stmt->close();
// Close the connection
$mysqli->close();
As you can see, it is much more complicated here and you need to study PDO - this is a separate issue.
MySQL is one type of relational database. MySQL is a server to which different users can connect.
When you connect to the Internet, do you enter your login and password, as well as the name of the server you are connecting to? When working with MySQL, the same system is used.
One more point: what is a relational database? Relational means table-based. Microsoft's famous Excel spreadsheet editor is actually a relational database editor.
Connecting to a MySQL server
PHP uses the mysqli_connect() function to connect to a MySQL server. This function takes three arguments: server name, username, and password.
The mysqli_connect() function returns a connection ID, which is stored in a variable and then used to work with databases.
MySQL server connection code:
$link = mysqli_connect("localhost", "root", "");
In this case, I'm working locally on Denwere, so the hostname is localhost, the username is root, and there is no password.
The connection also needs to be closed when MySQL is finished. The mysqli_close() function is used to close the connection. Expanding the example:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_close($link);
Here we checked the connection identifier for truth, if something is wrong with our connection, then the program will not be executed, the die () function will stop its execution and display an error message in the browser.
Connection errors
The following functions are used to check the connection:
- mysqli_connect_errno() - Returns the error code of the last connection attempt. Returns zero if there are no errors.
- mysqli_connect_error() - Returns a description of the last error connecting to the MySQL server.
The mysqli_get_host_info() function returns a string containing the type of connection being used.
Also note that using the define command, I saved all connection parameters in constants. When you write large projects and there will be many files connecting to the MySQL server, it is convenient to store the connection parameters in a separate file and insert it using the include or require function.
Database selection
A MySQL server can have multiple databases. First of all, we need to select the base we need to work with. In PHP, there is one more parameter for this in the mysqli_connect() function - the name of the database.
I created on my computer via phpMyAdmin named tester. We connect to it:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); mysql_close($link);
So, we have chosen a database to work with. But as we know, a relational database consists of tables, and our database does not yet have tables. The database is created empty, without tables. Tables must be added to it separately. Let's add a table to it using PHP.
Create a table
In the name of the MySQL databases, the SQL part stands for Structured Query Language, which translates as a structured query language. In the SQL language, we will write queries and send them from the PHP program to the MySQL server.
To create a table, we just need to issue the CREATE TABLE command. Let's create a table called users whose columns will store logins (login column) and passwords (password column) of users.
$query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))";
In this code, we have assigned a string of text to the $query variable, which is an SQL query. We create a table named users which contains two columns login and password, both of data type VARCHAR(20). We will talk about data types later, for now I will only note that VARCHAR (20) is a string with a maximum length of 20 characters.
To send our query to the MySQL server we use the PHP function mysqli_query() . This function returns a positive number if the operation was successful and false if an error occurred (the syntax of the request is invalid or the program does not have permission to execute the request).
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; mysqli_query($query); mysqli_close($link);
The SQL query does not have to be written to a variable, it can be immediately written as an argument to the mysql_query() function. It just makes the code more readable.
This script has one drawback - it does not output anything to the browser. Let's add a message:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($query)) echo "The table has been created."; else echo "Table not created."; mysqli_close($link);
If we re-run this script for execution, we will see a message in the browser: "The table has not been created." The fact is that the table was created at the first start, and it is impossible to create a table with the same name again. We are faced with an error situation, so it's time to talk about error handling when working with MySQL.
Error processing
When debugging a program, we may need precise information about the error. When an error occurs in MySQL, the database server sets the error number and a line describing the error. PHP has special functions to access this data.
- mysqli_errno() - returns the error number.
- mysqli_error() - returns a string describing the error.
Now let's add the mysql_error() function to our script:
$link = mysql_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($query)) echo "The table has been created."; else echo "Table not created: ".mysqli_error(); mysqli_close($link);
Now our script will return the string to the browser: "Table not created: Table "users" already exists".
Deleting a table
So, we now have a table that we do not need. It's time to learn how to delete tables from the database.
To drop a table, use the DROP TABLE command followed by the table name.
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "DROP TABLE users"; if (!mysqli_query($query)) echo "Error dropping table: ".mysqli_error(); else echo "Table deleted."; mysqli_close($link);
Results
So, we have mastered the basics of MySQL. What we have learned to do:
- Connect to a MySQL database using the mysqli_connect() function.
- Close the connection to the MySQL server using the mysqli_close() function.
- Send SQL queries to the MySQL server using the mysqli_query() function.
- We have learned the SQL query to create a table: create table.
- We have learned the SQL query for dropping a table: drop table.
- We have learned how to handle errors using the mysqli_errno() and mysqli_error() functions.
Then we'll take a closer look at MySQL data types.
Read the following lesson:
