Connecting PHP and MYSQL: One Mark Questions, Book Back Answers and Notes

About Connecting PHP and MYSQL

Medium ~45 min study

Almost every useful website stores data: student records, orders, user accounts. That data lives in a database such as MySQL, while the page itself is produced by PHP. On its own, PHP cannot see the tables, and MySQL cannot build a web page. This chapter shows how the two are joined so that a script can read records from a table and display them.

PHP does this through the MySQLi extension, a set of built-in functions whose names start with mysqli_. You use one function to open the connection with the server name, user name, password and database name, another to send an SQL statement, others to read the rows that come back, and one more to close the connection. Once you see the order in which these calls happen, the example programs in the textbook become easy to follow.

Exam questions from this chapter focus on the functions themselves: what arguments mysqli_connect() takes and in what order, what mysqli_query() and mysqli_close() need, and how to report a failed connection. Book back MCQs often show several versions of a function call and ask which one has the correct syntax, so learn each call with its arguments exactly as the textbook writes them.

What you'll learn

• Describe the steps a PHP script follows to work with a MySQL database.
• Open a database connection with mysqli_connect() using the correct arguments in the correct order.
• Check for a failed connection and print the error message.
• Run SQL statements from PHP using mysqli_query().
• Display the rows of a query result on a web page using mysqli_fetch_array() in a loop.
• Close a database connection with mysqli_close().

Before you start

• PHP variables, data types and basic syntax.
• SQL statements such as SELECT, INSERT, UPDATE and DELETE.
• What localhost means and what a database user name and password are.

Connecting PHP and MYSQL explained

Connecting a PHP Script to a MySQL Database

Why PHP Needs a Database Connection

A database-driven page follows a simple pattern. The PHP script connects to the MySQL server, sends an SQL statement, receives the result and turns it into HTML for the browser. PHP provides the MySQLi extension for this, where the i stands for improved. It was introduced in PHP 5.0.0 to work with newer MySQL servers. With these functions a script can insert a registration record, update a student's marks or list all the rows of a table. To practise on your own computer you need a web server, PHP and MySQL installed together, which packages such as XAMPP or WAMP provide.

Opening the Connection and Checking for Errors

The connection is opened with mysqli_connect(). The textbook syntax is mysqli_connect("Server Name", "User Name", "Password", "DB Name");, for example $con = mysqli_connect("localhost", "my_user", "my_password", "Student_DB");. For a database on the same machine, the server name is usually localhost. The function returns a connection object, which you store in a variable and pass to the other functions. If the connection fails, the script should say so and stop, instead of trying to run queries on a connection that does not exist. The textbook checks this with if (mysqli_connect_errno()) and prints the reason using mysqli_connect_error().

Running Queries and Reading the Result

Once connected, SQL statements are sent with mysqli_query(). Its syntax is mysqli_query("Connection Object", "SQL Query"), so the connection comes first and the SQL string second, as in mysqli_query($con, $sql);. For a SELECT statement it returns a result set holding the matching rows. mysqli_num_rows($result) tells you how many rows came back. To display them, loop through the result with while ($row = mysqli_fetch_array($result)). Each call returns the next row as an array, and the loop ends when no rows are left. Inside the loop you print the fields, often as rows of an HTML table.

Closing the Connection

After the queries are finished, close the connection with mysqli_close(). It takes one argument, the connection object: mysqli_close($con);. Closing releases the memory and the connection slot held on the MySQL server. PHP does close connections when a script ends, but closing them yourself is good practice. A MySQL server allows only a limited number of connections at a time, and on a busy site unclosed connections can use up that limit and make new visitors wait or fail to connect.

Common mistakes to avoid

• Leaving out the database name in mysqli_connect(). The connection may open, but queries fail because no database is selected. Give all four values in order: server name, user name, password and database name.
• Running queries without checking whether the connection worked. Test with mysqli_connect_errno() and print mysqli_connect_error() so you know why it failed.
• Swapping the arguments of mysqli_query(). The connection object comes first and the SQL string second; writing mysqli_query($sql, $con) is wrong.
• Leaving connections open at the end of the script. Call mysqli_close() with the connection object when the work is done.
• Calling mysqli_close() before the while loop that fetches the rows. Once the connection is closed the script can no longer run queries on it, so close it only after all the output is printed.

Test yourself on these with the practice test, then check the worked reasoning in the solved MCQs.

Frequently asked questions

How do I connect a PHP script to a MySQL database?

Call mysqli_connect() with the server name, user name, password and database name, for example $con = mysqli_connect("localhost", "my_user", "my_password", "Student_DB"). It returns a connection object that you pass to the other mysqli functions.

What arguments does mysqli_connect() take?

As used in the textbook, it takes four values in this order: the server name (usually localhost), the user name, the password and the database name. PHP lets you leave some of them out, but for exam answers always write all four in this order.

How do I check if the database connection failed?

After calling mysqli_connect(), test mysqli_connect_errno() in an if statement. If it returns a non-zero value, the connection failed, and mysqli_connect_error() gives the message, for example echo "Failed to connect to MySQL: " . mysqli_connect_error();

What is the purpose of mysqli_query()?

mysqli_query() sends an SQL statement to the MySQL server and runs it. It takes two arguments, the connection object and the SQL query string. For SELECT it returns a result set; for statements like INSERT or UPDATE it returns true or false.

Why should database connections be closed?

Each open connection uses memory and one of the limited connection slots on the MySQL server. Calling mysqli_close() frees these as soon as your work is done, so other users of the site can connect without delay.

How do I read the records from a query result?

Pass the result to mysqli_fetch_array() inside a while loop: while ($row = mysqli_fetch_array($result)). Each call returns the next row as an array, so you can print its fields, and the loop stops when there are no more rows.

How is mysqli_close() different from mysqli_connect()?

mysqli_connect() opens a connection and needs the server name, user name, password and database name. mysqli_close() ends an existing connection and needs only one argument, the connection object.