Communication with MySQL databases. Sending queries to a database in PHP Connecting to a php form database
3 methods to connect to MySQL with PHP with code examples
To start using the database MySQL data, you first need to 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. Connect 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 more old version PHP and for some reason you can't update it to 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 local server using the 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 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
MySQL is a type of relational database. MySQL is a server to which various users can connect.
When you connect to the Internet, do you enter your username 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 thing: what is a relational database? Relational means based on tables. Microsoft's famous spreadsheet editor Excel is actually a relational database editor.
Connecting to MySQL server
To connect to MySQL server PHP uses the mysqli_connect() function. This function takes three arguments: server name, username, and password.
The mysqli_connect() function returns the connection identifier, it is stored in a variable and later used to work with databases.
MySQL server connection code:
$link = mysqli_connect("localhost", "root", "");
In this case I am working for local computer on Denwere, so the hostname is localhost, the username is root, and there is no password.
The connection also needs to be closed after finishing working with MySQL. The mysqli_close() function is used to close the connection. Let's expand the example:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_close($link);
Here we checked the connection identifier for truth; if there is something 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. If there are no errors, returns zero.
- mysqli_connect_error() - returns a description of the last connection error 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 as constants. When you write large projects and there will be a lot of files connecting to the MySQL server, it is convenient to store connection parameters in separate file and insert it using the include or require function.
Selecting a Database
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 another parameter for this in the mysqli_connect() function - the database name.
I created it on my computer via phpMyAdmin with the name tester. Let's 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. Now 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 SQL language we will write queries from PHP programs send them 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 the $query variable a string of text that represents an SQL query. We create a table called users that contains two columns login and password, both of VARCHAR(20) data type. We'll talk about data types later, for now I'll just 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 request syntax is incorrect 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 need to be written into a variable; it can be written directly 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 run this script again, we will see a message in the browser: “The table has not been created.” The fact is that the table was created the first time it was launched, but 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 Handling
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 with its description. 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 line to the browser: “Table not created: Table “users” already exists.”
Delete a table
So, now we have a table that we don’t need. It's time to learn how to drop tables from a 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 while deleting table: ".mysqli_error(); else echo "Table deleted."; mysqli_close($link);
Results
So, we have mastered the basics of MySQL. What we 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 query s to the MySQL server using the mysqli_query() function.
- We learned the SQL query for creating a table: create table.
- We learned the SQL query for deleting a table: drop table.
- We 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 next lesson:
We learned how to connect to the MySQL server, select a database to work with, learned the PHP function of sending queries to the MySQL server, learned two simple queries (creating and deleting a table), and learned how to close the connection.
Now we will study more deeply MySQL queries. So let's get started!
Creating a table - CREATE TABLE
Now we have an empty database, there are no tables in it. So first we'll create a table. We already know how to do this from the first part.
Here is the script code that will create the plate we need:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($link, $query)) echo "The table has been created."; else echo "Table not created: ".mysqli_error(); mysqli_close($link);
Our table has only two fields: login and password. For now we don’t need any more, let’s not complicate the process.
So, the table has been created.
Adding rows (records) to a table - INSERT
You can add a new row to the table by SQL help insert commands Here's an example:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "INSERT INTO users (login, password) VALUE ("zeus", "pass123")"; if (mysqli_query($link, $query)) echo "User added."; else echo "User not added: " . mysqli_error(); mysqli_close($link);
An SQL query consists of the INSERT INTO command, the database name users, then the field names in parentheses, then the word VALUE, followed by the values to be added in parentheses. Values are enclosed in quotation marks.
The request syntax looks like this:
INSERT INTO table_name (column1, column2) VALUE ("x1", "x2")
Quotes in the second parentheses are required.
In place of values there can be variables. Here's an example:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $login = "zeus"; $password = "pass123"; $query = "INSERT INTO users (login, password) VALUE ("$login", "$password")"; if (mysqli_query($link, $query)) echo "User added."; else echo "User not added: " . mysqli_error(); mysqli_close($link);
Of course, this example makes little sense. It may be useful for beginners to hear that this is how logins and passwords that users provide during registration are recorded in the database. This data is stored in variables and then, after verification, written to the database.
Exists quick way inserting multiple rows with one INSERT query:
INSERT INTO users (login, password) VALUE ("bob", "eee333"), ("Rooki", "12345"), ("magy", "olol88e8")
As you can see, the listed data is simply separated by commas.
So, using the INSERT command, we learned how to add records to a table. Let's move on.
View Table: SELECT Command
Now we have a users table that has rows. The previous script can be run several times, and each time it will add a row to the table. Now we may not know how many rows we have in the table. And I want to know what we have written in it.
To retrieve data from a table, use the SELECT SQL command. The * sign means that we are requesting all data, then after the word FROM we write the name of the table from which we want to obtain data.
Let's query all the data from the users table:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "An error occurred: " . mysqli_error(); else echo "Data received"; mysqli_close($link);
The mysqli_query() function returned the query result identifier to us - we put it in a variable and will later work with it using other PHP functions.
Number of records in request
Let's determine how many lines are in our query? I ran the script for adding a record to the table, I don’t remember how many times and now I don’t know how many rows are in my table.
To determine the number of rows in the result of a query, use the mysqli_num_rows() function. This function is passed the identifier of the query result, and it returns the number of records.
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "An error occurred: " . mysqli_error(); else echo "Data received"; $count = mysqli_num_rows($result); echo "Total rows in the table: $count."; mysqli_close($link);
If we need to find out the number of records in the table, then the above method is not the most suitable. Here we found out the number of records found in the query, but the number of records in the table is searched differently.
Number of records in table SELECT COUNT(*)
To find out the number of records in a table, you can use the SELECT COUNT(*) FROM table_name command.
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "An error occurred: " . mysqli_error(); else echo "Data received."; $count = mysqli_fetch_row($result); echo "Total rows in the table: $count."; mysqli_close($link);
Please note that here we used a new one PHP function mysqli_fetch_row() to fetch data. This function returns a row of the query result in the form of a simple array; in our case, there is one field in the row and it has index 0.
Viewing the result of a query in a loop
After executing an SQL query with a SELECT command and obtaining the query result ID, PHP creates an internal pointer in the result recordset. This pointer automatically moves to the next record after accessing the current record. This mechanism makes it very convenient to loop through the result set of a SELECT query.
PHP has several functions with which you can get an array consisting of its fields for each line of the resulting query. For example, let's take the mysqli_fetch_row() function. This function is passed the request identifier and returns an array. So, in a loop, the entire query result is viewed, and when the end of the query result is reached, the function will return false .
So, we query all the data from the users table (SELECT * FROM users).
"; while ($row = mysqli_fetch_row($result)) ( echo "Login: $row. Password: $row.
"; ) mysqli_close($link);
The mysqli_fetch_row() function returns a simple array. In each iteration of the loop, we will receive an array with a row from the table, the fields of which we can access by specifying a numeric index.
The same can be done using the mysql_fetch_assoc() function, it returns an associative array.
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); $result = mysqli_query($link, "SELECT * FROM users"); if (!$result) echo "An error occurred: " . mysqli_error(); else echo "Data received.
"; while ($row = mysqli_fetch_assoc($result)) ( echo "Login: $row. Password: $row.
"; ) mysqli_close($link);
There are also functions mysqli_fetch_array() - returns any array type, and mysqli_fetch_object() - returns an object.
SELECT DISTINCT query - unique field values
Let's create a new table:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); // delete the existing table mysqli_query($link, "DROP TABLE users"); // create a new table $query = "CREATE TABLE users(name VARCHAR(20), surname VARCHAR(20), age TINYINT UNSIGNED)"; if (mysqli_query($link, $query)) echo "The table has been created.
"; else echo "Table not created: " . mysqli_error(); // function to add records to the table function add_new_line($link, $query) ( if (!mysqli_query($link, $query)) echo "User not added : " . mysqli_error(); ) // add records add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Max", "Jayson", "33")"); add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Bob", "Freeman", "26")"); add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Sara", "Lopes", "65")"); add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Serg", "Pupin", "29")"); add_new_line($link, " INSERT INTO users (name, surname, age) VALUE ("Serg", "Borman", "43")"); add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Max", " Lopes", "21")"); // display the contents of the table in the browser $result = mysqli_query($link, "SELECT * FROM users"); if (!$result) echo "An error occurred: " . else echo "Data received.
"; while ($row = mysqli_fetch_assoc($result)) ( echo "First name: $row. Last name: $row. Age: $row.
"; ) mysqli_close($link);
So, we have a new, more complex table with unique records. Now let's see how many names we have in the database.
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); $result = mysqli_query($link, "SELECT DISTINCT name FROM users"); echo "Total names: " . mysqli_num_rows($result)."
"; echo "List of names:
"; while ($name = mysqli_fetch_row($result)) ( echo "$name
"; ) mysqli_close($link);
The SQL query "SELECT DISTINCT name FROM users" returned a result with all the unique names in our table. Each unique name in a new row of the query result.
Sorting the result - ORDER BY
By adding the ORDER BY command to the SQL query, we sort the query result in ascending order (numbers and letters in alphabetical order). Here is an example in which you can compare a regular query and one sorted by age (age field).
"; ) echo "Sort by age:
"; $result = mysqli_query($link, "SELECT * FROM users ORDER BY age"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) mysqli_close($link);
You can replace the age field in the ORDER BY command with the name field and see the result.
To sort the query result in reverse order, use the ORDER BY age DESC command.
Matching condition - WHERE
By adding the WHERE command to the SQL query, we will query only those records that meet the condition. For example, let's make a request for people under 30 years old.
To do this, we use the SQL query "SELECT * FROM users WHERE age
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "People under 30:
"; $result = mysqli_query($link, "SELECT * FROM users WHERE age<30");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
"; ) mysqli_close($link);
We can also immediately sort the result in ascending age order:
" SELECT * FROM users WHERE age<30 ORDER BY age ".
If we make a query " SELECT name FROM users WHERE age<30 ORDER BY age ", то в результате нам вернут только значения поля "name", но они также будут отсортированы по age.
We can query the values of two fields: " SELECT name, age FROM users WHERE age
Now let's request all users with the name "Max".
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "All Maxes:
"; $result = mysqli_query($link, "SELECT * FROM users WHERE name="Max""); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) mysqli_close($link);
And another example of a query - it will select only names from the users table, everything except Max.
SELECT name FROM users WHERE name!="Max"
That's all for the WHERE query.
Limiting entries - LIMIT
By adding the LIMIT command to the SQL query, we will limit the size of the result.
The query that returns the first three entries is: " SELECT * FROM users LIMIT 3 ". Let's see how it works:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "Table contents:
"; $result = mysqli_query($link, "SELECT * FROM users"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) echo "
First three entries:
"; $result = mysqli_query($link, "SELECT * FROM users LIMIT 3"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) echo "
Second three entries:
"; $result = mysqli_query($link, "SELECT * FROM users LIMIT 3, 3"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line .
"; ) mysqli_close($link);
Also here we used the query: "SELECT * FROM users LIMIT 3, 3". The second triple indicates the offset in the query result.
Match pattern - LIKE
The SQL language supports simple templates. To do this, use the LIKE command and specify the pattern using the % symbol.
Here is an example query that will return all records with names starting with the letter S.
SELECT * FROM users WHERE name LIKE "S%"
I'm testing the request:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "Table contents:
"; $result = mysqli_query($link, "SELECT * FROM users"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) echo "
Names starting with S:
"; $result = mysqli_query($link, "SELECT * FROM users WHERE name LIKE "S%""); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age : $line.
"; ) mysqli_close($link);
Here is an example query that will return all records with last names ending with the letter s.
SELECT * FROM users WHERE name LIKE "%s"
Condition met - IN
This query using the IN command will return only those rows that strictly match the condition.
For example, we are interested in people aged 21, 26 and 33 years.
SELECT * FROM users WHERE age IN (21,26,33)
I'm testing the request:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "Table contents:
"; $result = mysqli_query($link, "SELECT * FROM users"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $line. Age: $line.
"; ) echo "
People with the required ages (21, 26, 33):
"; $result = mysqli_query($link, "SELECT * FROM users WHERE age IN (21, 26, 33)"); while ($line = mysqli_fetch_row($result)) ( echo "First name: $line. Last name: $ line. Age: $line.
"; ) mysqli_close($link);
Maximum and minimum value in a column
Selects the maximum age value in the users table.
SELECT max(age) FROM users
The following query selects data from the users table using the name and age fields, where age takes the minimum value.
SELECT name, min(age) FROM users
Updating a record - UPDATE
Let's set Max Lopes age to 15 years. This is done with a MySQL query:
UPDATE users SET age="15" WHERE name="Max" AND surname="Lopes"
Please note the new AND command (and means “and” in English) in the query. If we do not specify the last name, then the age of 15 years will be set for all Maxes in the table.
You can update two or more fields in one row with one query. This is done as follows:
UPDATE users SET age = "18", surname = "Coocker" WHERE id = "3"
Our table doesn't have an id field, so this query won't work on it. But we will definitely learn this field containing unique line numbers.
Delete an entry - DELETE
MySQL database query to delete a record:
DELETE FROM users WHERE id = "10"
Again, our table does not have an id field. But we can remove from it all people under 18 years of age.
DELETE FROM users WHERE age< "18"
Delete a table - DROP TABLE
MySQL database query that deletes the entire users table:
DROP TABLE users
Delete a column - ALTER TABLE ... DROP ...
Sometimes you may need to remove a column from a table, let's for example remove the age column from users:
ALTER TABLE users DROP age
This MySQL query deleted the column permanently and permanently.
Add a column - ALTER TABLE ... ADD ...
Sometimes you may need to add a column to an existing table, let's for example add the age column back to the users table:
ALTER TABLE users ADD age TINYINT UNSIGNED
Renaming a column - ALTER TABLE ... CHANGE ...
Sometimes you may need to rename a column, for example, rename the age column to vozrast. We do it like this:
ALTER TABLE users CHANGE age age TINYINT UNSIGNED
This MySQL query renamed the column age to vozrast with data type TINYINT UNSIGNED.
Renaming a table - RENAME TABLE ... TO ...
Sometimes you may need to rename the table:
RENAME TABLE users TO peoples
Removing a database - DROP DATABASE
This query can delete the database named tester:
DROP DATABASE tester
Creating a database - CREATE DATABASE
This query creates a database named tester:
CREATE DATABASE tester
This request works for me in Denver, but on hosting it may not work if the database user does not have rights to perform deletion.
Results
So, in this part we got acquainted with queries to MySQL. Many of the queries we examined are not often useful to us in the process of work, but we need to know them, since they will definitely come in handy in the process of developing scripts.
Some requests are usually made only from phpMyAdmin (creating and deleting databases for example).
When working on websites, you usually need to add a record to a table, edit a record, or delete a record from a table.
The next step is to learn about MySQL data types.
Using php...
Creating a connection 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:
- more 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! - the 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.
