Communication with MySQL databases. Creating a connection to a MySQL database in different ways Mysql connection with php

Using php...

Creating a connection to a database in PHP in different ways:

$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();

This method:

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 the host (mostly localhost),
• $db_database - database name;
• $db_username and $db_password - username and password respectively!

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.

When connecting to a MySQL database, prepared expressions are used (using the prepare method) and as a result there is 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.

MySQL is a type of relational database. MySQL is a server that can be connected to by various users.

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.

To connect to a MySQL server in PHP, use 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'm working on a local machine 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.

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 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.

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.

In the name of the MySQL databases, the SQL part stands for Structured Query Language, which translates as a structured query language. We will write queries in SQL and send them to the MySQL server from the PHP program.

To create a table we just need to issue the CREATE TABLE command. Let's create a table called users in the columns of which the logins (login column) and passwords (password column) of users will be stored.

$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 during the first launch, 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.

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.”

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);

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 queries 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:

I made a simple database of events for myself in PHP with a reminder about them via e-mail. It’s quite suitable as an example for working with a database in PHP.

The database of course is MySQL.

Tablets can be created manually through phpMyAdmin, as described in the example of creating a survey in PHP here, but here I will create tablets using queries.

File for creating tables create.php (run it once, then delete it after creating the tables).

User registration form add.php

Enter the username and password add1.php into the database

To come in

Registered.

Now the login and password entry form login.php

Event reminder in PHP

Remind password

After entering the correct login and password, we display a list of events see.php

Event reminder in PHP

Under the events table there is a form for entering a new event and an Add button; when you click on it, seeadd.php opens, the event is added and the plate with the new event and the input form are displayed again. In order for everything to be entered correctly, you need to pay attention to the date format and encoding.

Event reminder in PHP

In the table we have the Change button that opens change.php with a form for changing the previously entered entry.

Event reminder in PHP