How to connect PHP script to MySQL database

In online form development, connecting PHP code with MySQL database is a common operation. User form data needs to be collected and added to the database. This article introduces two commonly used PHP and MySQL database connection methods.

PHP and MySQL database connection

To connect MySQL database to PHP, you need to install MySQL, database management tools and PHP on your computer. The two most commonly used connection methods are MySQLi and PDO.

First, we introduce the easier MySQLi to use.

First create a MySQL database, here we use TablePlus. TablePlus is a convenient database management tool that handles a variety of databases in a single interface. With its user-friendly interface, it takes only a few steps to create a database and add information. Open the application, click the database icon, and then click "New...", enter the database name and click "OK".

Create a MySQL connection

Next, use mysqli_connect to connect to the database. You need a MySQL database password. To manage credentials safely and conveniently, we use Secrets to store credentials.

Now we can connect to the MySQL database to PHP.

Open your commonly used PHP development tool and create a file named index.php. We use CodeRunner to write and edit code.

Here is the code for extending the connection using MySQLi:

 <?php $conn = mysqli_connect(
    "<database location>",
    "<mysql> ",
    "<mysql> ",
    "Connect"
);
if (!$conn) {
    echo 'Connection Error:' . mysqli_connect_error();
}
?></mysql></mysql>

Click the Run button at the top of CodeRunner to run the code and view the results. If there is no error, the PHP script successfully establishes the MySQL database connection.

Before running the code, make sure the system has PHP installed. If not, enter "brew install php" in the terminal.

After establishing a connection, you can perform operations on the database.

Query the database, just connect to the database as before and request the required information:

 <?php $conn = mysqli_connect(
    "<database location>",
    "<mysql> ",
    "<mysql> ",
    "Connect"
);
if (!$conn) {
    echo 'Connection Error:' . mysqli_connect_error();
}
$sql = 'SELECT id FROM connect_table';
$result = mysqli_query($conn, $sql);
$connect = mysqli_fetch_all($result, MYSQLI_ASSOC);
print_r($connect);
?></mysql></mysql>

We use the SELECT statement to find the data for the desired column.

How to insert a record

Next, demonstrate a PHP to MySQL connection example that inserts information into the database.

Using INSERT INTO… VALUES syntax:

The code snippet is as follows:

 <?php $conn = mysqli_connect(
    "<database location>",
    "<mysql> ",
    "<mysql> ",
    "Connect"
);
if (!$conn) {
    echo 'Connection Error:' . mysqli_connect_error();
}
$sql = 'INSERT INTO connect_table VALUES (5)';
if ($conn->query($sql) === TRUE) {
    echo "Record added!";
} else {
    echo "Error:" . $sql . "<br> " . $conn->error;
}
$conn->close();
?></mysql></mysql>

Add your own values ​​and run the code.

You can save the above code snippet for later use. We use the SnippetsLab application to save code snippets. It helps organize code snippets and avoids losing code examples.

How to update records in database from PHP script

To use mysqli to connect to PHP to update records in a MySQL database, you need to use the UPDATE … SET … WHERE syntax.

Specify the columns and rows to update and the value, and then run the code:

The code we use is as follows:

 <?php $conn = mysqli_connect(
    "<database location>",
    "<mysql> ",
    "<mysql> ",
    "Connect"
);
if (!$conn) {
    echo 'Connection Error:' . mysqli_connect_error();
}
$sql = 'UPDATE connect_table SET id = 66';
if ($conn->query($sql) === TRUE) {
    echo "Record updated!";
} else {
    echo "Error:" . $sql . "<br> " . $conn->error;
}
$conn->close();
?></mysql></mysql>

How to delete a query from a PHP script

Next, see how to quickly delete unwanted entries in the database.

The deletion syntax in MySQLi is DELETE FROM … WHERE …, let's try it in the code.

For example, if you want to remove the value 54 from the connect_table of the Connect MySQL database, you can use the following code:

The output "value has been deleted!" means that the operation is successful. We can recheck it in the TablePlus database view:

As you can see, the value 54 has been deleted from the id column.

Connect using PDO

Another common way to connect a PHP project to MySQL is PDO (PHP data object). This approach is more general because it can be used with multiple SQL databases, not just MySQL, which is different from MySQLi.

You can use the following code to establish a PDO MySQL connection:

The code we use is as follows:

 <?php $servername = "localhost";
$username = "<your database username>";
$password = "<your database password>";
try {
    $conn = new PDO("mysql:host=$servername;dbname=<your database name>", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connect to the server successfully!";
} catch (PDOException $e) {
    echo $e->getMessage();
}
?></your></your>

Once connected to the database, you can add PDO operations to your code, such as inserting, deleting, selecting, or updating.

Create a simple PHP form and submit your values ​​through it to test it.

in conclusion

Now you have learned about the two most popular methods of PHP and MySQL connections – MySQLi and PDO connecting to SQL databases.

PHP-MySQL Connection is a versatile tool that helps you retrieve data from a database, update the database, and collect user data and add it to the database.

If you are just starting to connect PHP to MySQL, it is recommended to try MySQLi. Once you're more familiar with the process, you can add PDO as it can be used with other databases, not just MySQL.

When writing code, you can use the CodeRunner code editor to write and execute code, use SnippetsLab to save code snippetsLab for later use, and use TablePlus to manage the database. As for the database's login credentials, it can be securely stored in Secrets, an application for storing passwords, credit cards, and bank account information.

Another tool you can try to help you use PHP is Whisk, which previews your pages in real time – it allows you to create and adjust in real time. So if you need to create a PHP form for your project, you can use this application to complete the task.

All of these applications are available through Setapp subscription. Setapp is a productivity tool service for Mac and iOS, dedicated to clearing daily tasks from your schedule and making room for new and exciting efforts. You can experience these and more daily task tools with a free 7-day trial.

The above is the detailed content of How to connect PHP script to MySQL database. For more information, please follow other related articles on the PHP Chinese website!