Sending a query to MySQL from PHP is as simple as including the relevant SQL inthe query method of a connection object.
Querying a database with mysqli:
<?php
$query = “SELECT * FROM classics”;
$result= $conn->query($query);
if (!$result) die(“Fatal Error”);
?>

As you can see, the MySQL query looks just like what you would type directly at the command line, except that there is no trailing semicolon, as none is needed when you are accessing MySQL from PHP.

Here the variable $query is assigned a string containing the query to be made, and then passed to the query method of the $conn object, which returns a result that we place in the object $result. If $result is FALSE, there was a problem and the error

property of the connection object will contain the details, so the die function is called to display that error.
All the data returned by MySQL is now stored in an easily interrogable format in the $result object.

Now that you have saved the login.php file, you can include it in any PHP files that will need to access the database by using the require_once statement. This is preferable to an include statement, as it will generate a fatal error if the file is not found—
and believe me, not finding the file containing the login details to your database is a fatal error.
Also, using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses.

Connecting to a MySQL server with mysqli

<?php
require_once ‘login.php’;
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die(“Fatal Error”);
?>

This example creates a new object called $conn by calling a new instance of the mysqli method, passing all the values retrieved from the login.php file. We achieve error checking by referencing the $conn->connect_error property.

The -> operator indicates that the item on the right is a property or method of the object on the left. In this case, if connect_error has a value, there was an error, so we call the die function to terminate the program.
The $conn object is used in the following examples to access the MySQL database.

The die function is great when you are developing PHP code, but
of course you will want more user-friendly error messages on a
production server. In this case, you won’t abort your PHP program,
but will format a message that will be displayed when the program
exits normally—perhaps something like this:

function mysql_fatal_error()
{
echo <<< _END
We are sorry, but it was not possible to complete
the requested task. The error message we got was:
Fatal Error
Please click the back button on your browser
and try again. If you are still having problems,
please email our administrator. Thank you.
_END;
}

You should also never be tempted to output the contents of any
error message received from MySQL. Rather than helping your
users, you could give away sensitive information to hackers such as
login details. Instead, just guide the user with information on how
to overcome their difficulty based on what the error message
reports to your code.

Most websites developed with PHP contain multiple program files that will require access to MySQL and will thus need the login and password details. Therefore, it’s sensible to create a single file to store these and then include that file wherever it’s needed.

The login.php file:

<?php // login.php
$hn = ‘localhost’;
$db = ‘publications’;
$un = ‘username’;
$pw = ‘password’;
?>

Type the example, replacing username and password with the values you use for your MySQL database.

The hostname localhost should work as long as you’re using a MySQL database on your local system, and the database publications should work if you’re typing the examples I’ve used so far 🙂
The enclosing tags are especially important for the login.php file,

because they mean that the lines between can be interpreted only as
PHP code. If you were to leave them out and someone were to call up the file directly from your website, it would display as text and reveal your secrets. But, with the tags in place, all that person will see is a blank page. The file will correctly include your other PHP files.

The $hn variable will tell PHP which computer to use when connecting to a database. This is required because you can access MySQL databases on any computer connected to your PHP installation, and that potentially includes any host anywhere on the web. However, the examples in this chapter will be working on the local server.

The database we’ll be using, $db, is the one called publications that we created earlier.

Another benefit of keeping these login details in a single place is
that you can change your password as frequently as you like and
there will be only one file to update when you do, no matter how
many PHP files access MySQL.

The reason for using PHP as an interface to MySQL is to format the results of SQL queries in a form visible in a web page. As long as you can log into your MySQL installation using your username and password, you can also do so from PHP.
However, instead of using MySQLs command line to enter instructions and view output, you will create query strings that are passed to MySQL. When MySQL returns its response, it will come as a data structure that PHP can recognize instead of the formatted output you see when you work on the command line. Further PHP commands can retrieve the data and format it for the web page.

The process of using MySQL with PHP is as follows:

• Connect to MySQL and select the database to use.
• Prepare a query string.
• Perform the query.
• Retrieve the results and output them to a web page.
• Repeat steps 2 to 4 until all desired data has been retrieved.
• Disconnect from MySQL.

Well work through these steps in turn, but first its important to set up your login details in a secure manner so people snooping around on your system have trouble getting access to your database.

The golden rule to backing up is to do so as often as you find practical. The more valuable the data, the more often you should back it up, and the more copies you should make. If your database gets updated at least once a day, you should really back it up on a daily basis. If, on the other hand, it is not updated very often, you could probably get by with less frequent backups.

You should consider making multiple backups and storing them in
different locations. If you have several servers, it is a simple matter
to copy your backups between them. You would also be well
advised to make physical backups on removable hard disks, thumb
drives, and so on, and to keep these in separate locations—preferably somewhere like a fireproof safe. It’s important to test restoring a database once in a while, too, to make sure your backups are done correctly. You also want to be familiar with restoring a database because you may have to do so when you are stressed and in a hurry, such as after a power failure that takes down the website. You can restore a database to a private server and run a few SQL commands to make sure the data is as you think it should be.

As previously mentioned, the mysqldump program is very flexible and supports various types of output, such as the CSV format, which you might use to import data into a spreadsheet, among other purposes. Next example shows how you can dump the data from the classics and customers tables in the publications database to the files classics. In windows server use directory c:/temp. On macOS or Linux systems, you should modify the destination path to an existing folder.

Dumping data to CSV-format files:
mysqldump -u user -ppassword –no-create-info –tab=c:/temp
–fields-terminated-by=’,’ publications

To perform a restore from a file, call the mysql executable, passing it the file to restore from using the < symbol. So, to recover an entire database that you dumped using the –all-databases option.
Restoring an entire set of databases:

mysql -u user -p password < all_databases.sql or
mysql -u user -p < all_databases.sql

Restoring the publications database:

mysql -u user -p password -D publications < publications.sql or
mysql -u user -p -D publications < publications.sql

To restore a single table to a database, use a command such as that in next example where just the classics table is being restored to the publications database.

Restoring the classics table to the publications database:

mysql -u user -p password -D publications < classics.sql or
mysql -u user -p -D publications < classics.sql

If you want to back up all your MySQL databases at once (including the system databases such as mysql), you can use a command such as the next one, which would enable you to restore an entire MySQL database installation. Remember to use locking where required.

Use next command in terminal, do not use in mysql client.
Dumping all the MySQL databases to file:
Mariadb command is:
mysqldump -u user -p password -A > all_databases.sql
or mysql command is:
mysqldump -u user -p –all-databases > all_databases.sql

Of course, there’s a lot more than just a few lines of SQL code in
backed-up database files. I recommend that you take a few minutes
to examine a couple in order to familiarize yourself with the types
of commands that appear in backup files and how they work.

To back up only a single table from a database (such as the classics table from the publications database), you should first lock the table from within the MySQL command line, by issuing a command such as the following:

LOCK TABLES publications.classics READ;

This ensures that MySQL remains running for read purposes, but writes cannot be made. Then, while keeping the MySQL command line open, use another terminal window to issue the following command from the operating system command line:

mysqldump -u user -ppassword publications classics > classics.sql

You must now release the table lock by entering the following command from the MySQL command line in the first terminal window, which unlocks all tables that have been locked during the current session:

UNLOCK TABLES;

Now that you have mysqldump working, and have verified it outputs correctly to the screen, you can send the backup data directly to a file using the > redirect symbol.

mysqldump only works under terminal, not under mysql client.
Dumping the etunimitietokantadb database to a file:
mysqldump -u user –p password etunimitietokantadb > etunimitietokantadb.sql

The command in stores the backup file into the current directory. If you need it to be saved elsewhere, you should insert a file path before the filename. You must also ensure that the directory you are backing up to has the right permissions set to allow the file to be written.