It’s time to write our first example of inserting data in and deleting it from a MySQL table using PHP.
Save file it to your web development directory using the filename sqltest.php

It first checks for any inputs that may have been made and then either inserts new data into the table classics of the publications database or deletes a row from it, according to the input supplied. Regardless of whether there was input, the program then outputs all rows in the table to the browser. So, let’s see how it works. The first section of new code starts by using the isset function to check whether values for all the fields have been posted to the program. Upon confirmation, each line within the if statement calls the function get_post, which appears at the end of the program. This function has one small but critical job: fetching input from the browser.

For reasons of clarity and brevity, and to explain things as simply as
possible, many of the following examples omit certain very sensible
security precautions that would have made them longer and possibly detract from explaining their function in the clearest manner.
Therefore, it is important that you don’t skip past the section later
in this chapter on preventing your database from being hacked , in which you will learn about additional actions you can take with your code to secure it.

Fetching results one row at a time, code here

In this modified code, only one-fifth of the interrogations of the $result object are made (compared to the previous example), and only one seek into the object is made in each iteration of the loop, because each row is fetched in its entirety via the fetch_array method. This returns a single row of data as an array, which is then assigned to the array $row.

The fetch_array method can return three types of array according to the value passed to it:

MYSQLI_NUM
Numeric array. Each column appears in the array in the order in which you defined it when you created (or altered) the table. In our case, the zeroth element of the array contains the author column, element 1 contains the title column, and so on.

MYSQLI_ASSOC
Associative array. Each key is the name of a column. Because items of data are referenced by column name (rather than index number), use this option where possible in your code to make debugging easier and help other programmers better manage your code.

MYSQLI_BOTH
Associative and numeric array.
Associative arrays are usually more useful than numeric ones because you can refer to each column by name, such as $row[‘author’], instead of trying to remember where it is in the column order. This script uses an associative array, leading us to pass MYSQLI_ASSOC.

Closing a connection

PHP will eventually return the memory it has allocated for objects after you have finished with the script, so in small scripts, you don’t usually need to worry about releasing memory yourself. However, if you’re allocating a lot of result objects or fetching large amounts of data, it can be a good idea to free the memory you have been using to prevent problems later in your script.
This becomes particularly important on higher-traffic pages, because the amount of memory consumed in a session can rapidly grow. Therefore, note the calls to the close methods of the objects $result and $conn in the preceding scripts, as soon as each object is no longer needed, like this:
$result->close();
$conn->close();

Ideally, you should close each result object when you have finished
using it, and then close the connection object when your script will
not be accessing MySQL anymore. This best practice ensures that
resources are returned to the system as quickly as possible to keep
MySQL running optimally, and alleviates doubt over whether PHP
will return unused memory in time for when you next need it.

Once you have an object returned in $result, you can use it to extract the data you want, one item at a time, using the fetch_assoc method of the object. Type this script in and save it using the filename query-mysqli.php

Fetching results one cell at a time:
Link to code

Here, each time around the loop, we call the fetch_assoc method to retrieve the value stored in each cell, and output the result using echo statements.

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