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 corresponding PHP code examples that explain how to connect to your database from PHP.
For all the examples below, we will be connecting to an existing MySQL database. Note: Everything explained here will also work with MariaDB, just like 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(); ?> In 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 on which the MySQL database is running
- name - MySQL username to connect
- pass - password for the mysql user
- db - MySQL database to connect to.
- qvery is a MySQL query function. In this example, we select the id column from the city 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 database. Number of lines: 6 2. Connection 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 of 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 above example the db is running on localhost and we are connecting to the db database.
- MySQL username to connect
- Mysql user password
- $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 loop through the result from the above query command and store it in $row variable and then output it using echo.
- In MySQL PDO, to close a connection, simply set the $conn variable to null.
Connected to the database. List id: 1 2 3 4 5 6 3. Connection from PHP using outdated mysql functions
Use this method only if you are using an older version of PHP and for some reason cannot upgrade to the new 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 for use.
This particular extension has been deprecated since PHP 5.5. But as of PHP 7.0 this won't even work since it was removed. Since PHP 5.5, when you use these functions, it will generate an E_DEPRECATED error.
Create a 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;
- password for mysql user. Here it connects to the MySQL database which is running on the local server using the username and its 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 a db database.
- mysql_query function - used to specify your MySQL query. In this example, we select the id column from the city 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 database. id 1: 1 This is how you can connect to MySQL. I repeat, it is better to use the first two methods; O
Htmlbook.ru - Connecting to MySQL via PHP: Printable version Connecting to MySQL via PHP
It is convenient to centrally store almost all materials in one place, making it easy to access them and perform the necessary manipulations. A database (DB) acts as an information storage facility, so one of the main tasks required to write a website engine is working with MySQL.
Obtaining information through the database occurs in several stages.
A visitor requests a web page by entering its address (URL) into the browser. The web server (Apache in our case) determines that a PHP file is being requested and starts its interpreter. The PHP script contacts MySQL and requests the required information. The MySQL database returns the result of the query back to the PHP program. The script analyzes the received information and stores it in one or more variables. The text is then output using the echo function. The final HTML code generated by the program...
0 0
In this lesson we will talk about databases and consider such a pressing issue as connecting to MySQL from PHP. In this lesson we will learn how to connect to a database using real examples, and also learn how to handle errors when connecting to the server and database.
Connecting to MySQL
When connecting to MySQL, you must specify the server, the user and its password, as well as the database with which you want to work. The syntax for connecting to MySQL is as follows:
Let's take a closer look at this connection algorithm:
1. Connect to the MySQL server and get an identifier.
In order to connect to the database you must first connect to the MySQL server. For this purpose, there is a function “mysql_connect”, which indicates the location of this server, the user who has the right to work with the server and the password of this user. The result of the connection can be stored in a variable, which will be the MySQL connection identifier...
0 0
Connecting and working with mysql in php
To work with a database via php, you will first need to create a database. To do this, go to the database section of your hosting control panel. Create a new database there. Most often, when creating a database, in the same window you will have access to fields for creating a mysql user. Fill in the fields. If there are no fields for creating a user, then it will need to be created and associated with the database. You will need all the entered information when creating a connection to the database, so remember it or write it down.
Before executing the mysql_connect function, I recommend creating variables:
Usually I put these variables in a separate const.php file, and in the file I need I call it with the include construct.
Now let's move directly to the functions of working with the mysql database.
The first thing you need to do is connect to the database server, then connect our database. $myConnect =...
0 0
Lesson 17: Databases (DB)
A database is a collection of information/data organized to make it easier to access, administer and update. Databases make it possible to create dynamic websites with a large amount of information. For example, all HTML.net member data and everything...
0 0
Entering user data into the MySql database
In this lesson we will learn how to enter data entered by the user into a form into a MySql database. You will learn how to connect to a MySql database from web page code, and how to process and enter data into the database.
In the last lesson, I told you about how to install Denver on your computer, how to create your own database, a user for it, how to create a table in the database and we filled it with one record.
If you haven't read the first lesson, you can read it by following this link.
In this lesson we will create an html document for entering user information, as well as a php file that handles this information, which will process the data, connect to the MySql database and insert new records there.
Moving from words to action, let's get started.
Adding user data to the MySql database from a web page
First stage: create an html form for data entry
From the very beginning we need...
0 0
The requested page cannot be found. The requested page has been deleted, renamed, or is temporarily unavailable.
Try the following:
Make sure that the website address that appears in your browser's address bar is written correctly and does not contain any formatting errors. If you arrived at this page by clicking on a link, please contact the site administrator and alert them to the incorrectly formatted link. Click the button to check another link.
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)
Technical information (for support staff)
Search the Microsoft Support website for HTTP and 404 keywords. Review the topics titled Installing a Web Site, Solving Administrative Problems, and About Special Error Messages in IIS Help. IIS Help is available in IIS Manager...
0 0
From the author: data. We live in the age of information, so people have developed quite convenient technologies for storing it. Today I will show you how to create a database on hosting and why it is even necessary.
I already think that you yourself understand what a database is needed for - to store data. When installing any engine manually, you will be required to create it. Well, okay, but how to do this? There are at least 2 simple ways to do this.
Creating a database through the server control panel
Perhaps this is the simplest option. Any hosting provides you with CPanel or any other panel to manage your websites. There you can find the “Databases” item, where you can visually create a new database, a new user, and then link it to the database. It is not necessary to create a user if it has already been created. All rights must be set if this is an administrator profile.
Creating a database on hosting using the PhpMyAdmin utility
...0 0
PHP and MySQL
A big advantage of using a scripting language like PHP is
is the ability to generate dynamic content. However
it is important to consider the source of the latter. We have already seen how there can be
received input data from the user - from session memory and from flat
text files. Now we will learn how to use relational databases
us as a content source for a PHP-managed application.
Truly complex data-driven web applications across a range of
reasons why database management systems (DBMS) are used. First of all
out, using a structured query language (Structured Query Language,
SQL) a web programmer can delegate most storage tasks to
development and management of data on the database system. Secondly, databases
those who are better at managing large volumes of data than we are,
therefore, it is better to let them do what they do better -
Xia. Third,...
0 0
Using php...
Creating a database connection in PHP in different ways:
1) the old-fashioned way to connect to MySQL:
$conn=mysql_connect($db_hostname, $db_username, $db_password) or die ("No connection to the server");
mysql_select_db($db_database,$conn) or die ("No, it was not possible to connect to the database");
Explanations of the variables below.
The following functions are used:
- 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 (“The error is such and such”); - translated as or die with such and such an error - to immediately find where the error is.
config.php
// variables for connecting to the database
$host = "localhost"; /host
$username = "root"; // password for connecting 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 create connection");
// select the database. If there is an 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) A more progressive procedural style - connecting to the database using mysqli:
This method:
- convenient;
- up to 40 times faster;
- 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
// connections to the database
$link = mysqli_connect("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost
// output connection error
if (!$link) (
echo "Error connecting to the 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, we display it
if ($result = mysqli_query($link,"SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($row = mysqli_fetch_assoc($result)) (
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}
// freeing used memory
mysqli_free_result($result);
// Close the connection
mysqli_close($link);
}
As you can see, some points have changed (in italics).
3) Object-oriented method of connecting to a MySQL database - using methods and classes:
Cons: More complex and less susceptible to errors.
Pros: brevity and convenience for experienced programmers.
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
if($conn->connect_errno)(
die($conn->connect_error);
) else (echo "The connection to the database was successfully established";)
here, in principle, everything is intuitive:
- $db_hostname is host(mostly localhost),
- $db_database - db name;
- $db_username and $db_password - username and password respectively!
An example of connecting to a database in php OOP style with sampling from a table
config.php
// connections to the database
$mysqli = new mysqli("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost
// output connection error
if ($mysqli->connect_error) (
die ("DB connection error: (" . $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 an OOP style.
index.php
require_once "config.php";
// Execute the request. If there is an error, we display it
if ($result = $ mysqli->query("SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($row = $result-> fetch_assoc()) {
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}
// freeing 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 expressions are used (using the prepare method) and as a result, greater security and greatly increases performance.
config file from the previous method! - same
index.php
// PDO style for communication with MySQL
if ($stmt = $mysqli->prepare("SELECT Name, Voney FROM Dolg ORDER BY Money< ? LIMIT 5")) {
$stmt->bind_param("i", $summa);
$summa = 100000;
//start execution
$stmt->execute();
// Declaring variables for prepared values
$stmt->bind_result($col1, $col2);
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($stmt->fetch()) (
echo $col1 . "with debt". $col2 . " rubles.
";
}
// freeing used memory
$stmt->close();
// Close the connection
$mysqli->close();
As you can see, it’s much more complicated here and you need to study PDO - this is a separate topic.
Difficulty level: Easy
What you will need:
- Installed MySQL database (for example, from the Denwer kit).
- Apache+PHP combination or a program that works with a database - installed on a second computer.
- A local or Internet connection between a client computer and a server computer.
- Access to the "root" user (or anyone else with full rights) of the database.
1 step
To begin, sit down at a computer where you have MySQL installed. Go to the folder where you have it installed (most often it is a folder like “X:\usr\mysql”, where X is any letter of your hard drive or network drive), and there you will find the file “my.cnf”. If it has an icon that looks like a “computer with an arrow, like a shortcut” (see the image for the step), create a notepad shortcut in the folder (or any other text editor, for example – ), and to open the “my.cnf” file, drag the file to text editor shortcut; If the icon looks like “unknown file” or “text file”, open it with any text editor.
Step 2
Find the line with the “bind-address” parameter, and if there is no hash (#) at the beginning of the line, put it in. Don't forget to save the file after editing.
Step 3
Restart the database. Of course, everyone has their own methods - for example, you can “kill” the “mysqld.exe” process through the Task Manager (Ctrl+Alt+Delete), and then start the database again. With the new settings, MySQL will accept connections from other computers; However, data exchange will not work if, when connecting, you do not specify the name and password of a user who has the right to use the database from a remote computer.
Step 4
Connect as the “root” user (or any other user with full rights) to the database from the same computer where the database is located, and run the following command:
GRANT ALL ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION
Having previously replaced “user” with the name of the user who will be allowed to connect to the database from a remote computer, “password” with the password that the user should use.
You can also replace “*.*” with “db.*” - this will allow the user to work only with the “db” database; or on “db.table” - then the user will have even more limited rights and will only be able to work with the “table” table in the “db” database. By calling the GRANT command multiple times on the same user, but on the object you are granting rights to, you can specify exactly what the user can work with.
GRANT Command in MySQL 5.1 [English]
Step 5
You should now be able to connect to the database from the remote computer using the username and password you used in the GRANT command. In order to take away rights from a user (for example, take away the right to work with a specific database), use the REVOKE command:
REVOKE ALL , GRANT OPTION ON *.* FROM 'user'@'%' IDENTIFIED BY 'password'
The parameters are not exactly in the same order as in the previous step - but they are equivalent.
REVOKE command in MySQL 5.1 [English]
Step 6
You can delete a user (in case you need to delete a test user) using the DROP USER command:
DROP USER 'user'@'%' IDENTIFIED BY 'password'
Please note that DROP USER will not disconnect the users being removed if they are currently connected to the database; but if they disconnect, they will no longer be able to connect.
DROP USER Command in MySQL 5.1 [English]
- "%" in the described commands means that the user can connect from any computer. If you write "localhost" or "127.0.0.1" - only from the local one. Or you can specify a specific IP - for example, "192.168.0.1".
- With the GRANT command you can create, for example, two different users with the same usernames and passwords, but with different allowed IP addresses and different rights. Or two users with the same usernames but different passwords. Think about what exactly you need.
In this article today we’ll talk about creating a connection to a database and discuss which option is better to use procedural or object-oriented. First, let's look at what level we are at, if this is the level of a complete beginner, then my advice, without exception, is to start using the procedural style of connecting to the database. Previously, I wrote an article on this topic on my blog; for more information about the procedural style of connecting to a database, read the article: “How to connect to MySQL using PHP”. If you already have some experience working with a procedural style of connecting to a database, then you probably, like me, just took my projects and forced them to use an object-oriented approach.
One way or another, we will now look at the steps of constructing a class for creating a connection to a MySQL database in PHP. We will need two PHP files, in one file we will “put” the class for creating a connection to the database, and in the second we will work with this class.
Let's create two files:
- index.php;
- database.class.php;
I think we are not little children anymore and we know what we need to work with PHP files. The installed web server is Apache, PHP, MySQL DBMS and knows where to put these files - (for those who don’t know or have forgotten).
I put the file in which the class is stored in a separate file and name it in the format: class name.class.php and I know what is stored in this file. When there are many classes in a project, you can get lost, so I recommend naming files with classes in the format described above.
Database.class.php file:
Let's now look at what was created at this step. Using the keyword "class", the class name - DataBase and curly braces, we created the body of the class. In the created class, we created two properties, in $mConnect - where the result of connecting to the database is stored and $mSelectDB - where the result of selecting a database is stored. You may have noticed the keywords in each property - public and static. What are they talking about? Public means that the property is accessible from outside the class, and static makes it possible to access or call the property without creating an instance of the class, which is very often convenient in work.
Let's add the Connect() method to create a connection to the database:
".mysql_error()."
"; exit(); return false; ) // Return the result return self::$mConnect; ) ) ?>- $host - server IP address, on a local PC it is localhost;
- user - database user name;
- $pass - database user password;
- $name - the name of the database to which we connect;
The mysql_connect() function creates a connection to the database and stores the execution result in $mConnect. Next comes a check with the IF construct: If the connection was not successful, display an error message... Otherwise, PHP will ignore the IF block and continue to select the database. The mysql_select_db() function selects the database name, if the requested database does not exist in the database, in this case the program will inform the user of an error. If everything is successful, the database connection will return return.
Add the Close() method:
Sorry, we were unable to connect to the MySQL server
"; exit(); return false; ) // Try to select a database self::$mSelectDB = mysql_select_db($name, self::$mConnect); // If the database is not selected, display an error message.. if( !self::$mSelectDB) ( echo "".mysql_error()."
"; exit(); return false; ) // Return the result return self::$mConnect; ) // The method closes the connection to the database public static function Close() ( // Returns the result return mysql_close(self::$mConnect) ; ) ) ?>The next and last method in this class, Close(), closes the connection to the database; the mysql_close() function closes the connection to the MySQL server and returns the result.
Index.php file:
Using the define() function, we created constants to store database connection parameters. Require_once includes our DataBase class in the index.php file.
Let's remember the static keyword that was used in the DataBase class in the properties and methods of this class. This gives us the ability to access class properties and methods using "::" (two colons). The DataBase::Connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE) method takes 4 parameters - constants created by the define() function, which store information about the connection to the database. If the connection is successful, the Connect() method will return us a database connection. Next, we will be able to work and execute queries to the database. mysql_query() function - executes a query to the database. The mysql_fetch_assoc() function processes a series of query results and returns an associative array. The echo construct displays the MySQL server version. And finally, the DataBase::Close() method will close the connection to the database.
