How to Connect to AWS RDS MySQL Database to EC2 Instance With PHP By Using PDO

Mohammad Abu Mattar
Cloud Computing
07 Nov, 2022
08 Mins read

Introduction

In this post, we will learn how to connect to AWS RDS MySQL Database to EC2 Instance With PHP By Using PDO.

Prerequisites

AWS Account
EC2 Instance Amazon Linux 2
RDS MySQL Database
MySQL Workbench
SSH Client

Note: In previous posts, we have learned how to create a AWS RDS MySQL Database and connect to it using MySQL Workbench. If you have not read the previous posts, you can read them from the following links: How to Create a AWS RDS MySQL Database and Connect to it Using MySQL Workbench

Note: In previous posts, we have learned how to create a AWS EC2 Instance Amazon Linux 2. If you have not read the previous posts, you can read them from the following links: How To Create An AWS EC2 Instance Using AWS CLI

Create a PHP File structure

1
/var/www/html
2
├── connect.php
3
├── insert.php
4
├── index.php
5
└── insert-boats-form.php

Connect RDS MySQL Database to EC2 Instance With PHP By Using PDO

Create a connect.php file

We will create a connect.php file in the /var/www/html directory.

1
<?php
2
    $servername = "localhost";
3
    $username = "root";
4
    $password = "password";
5
    $dbname = "SAI";
6
    $port = "3306";
7

8
    $dsn = "mysql:host=$servername;port=$port;dbname=$dbname";
9

10
    try {
11
        $conn = new PDO($dsn, $username, $password);
12
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
13
        echo "Connected successfully";
14
    } catch(PDOException $e) {
15
        echo "Connection failed: " . $e->getMessage();
16
    }
17

18
?>

Explanation:

Click to expand the explanation

$servername - The server name of the RDS MySQL Database.
$username - The username of the RDS MySQL Database.
$password - The password of the RDS MySQL Database.
$dbname - The database name of the RDS MySQL Database.
$port - The port of the RDS MySQL Database.
$dsn - The Data Source Name, or DSN, contains the information required to connect to the database.
$conn - The connection object.
PDO::ATTR_ERRMODE - The error reporting attribute.
PDO::ERRMODE_EXCEPTION - Throw exceptions for errors.

Create a insert.php file

We will create a insert.php file in the /var/www/html directory.

1
<?php
2
    try {
3
        include_once 'connect.php';
4

5
        $bid = $_POST['bid'];
6
        $bname = $_POST['bname'];
7
        $color = $_POST['color'];
8

9
        $search = $conn->prepare(
10
            "INSERT INTO BOATS (BID, BName, BColor) VALUES (:bid, :bname, :color)"
11
        );
12

13
        $search->bindParam(':bid', $bid);
14
        $search->bindParam(':bname', $bname);
15
        $search->bindParam(':color', $color);
16

17
        $search->execute();
18

19
        $conn = null;
20
    } catch(PDOException $e) {
21
        echo "Connection failed: " . $e->getMessage();
22
        $conn = null;
23
    }
24
?>

Explanation:

Click to expand the explanation

include_once '../config/connect.php' - Include the connect.php file.
$_POST['bid'] - Get the bid value from the POST request.
$_POST['bname'] - Get the bname value from the POST request.
$_POST['color'] - Get the color value from the POST request.
$search = $conn->prepare() - Prepare the SQL statement.
$search->bindParam(':bid', $bid) - Bind the bid value to the :bid parameter.
$search->bindParam(':bname', $bname) - Bind the bname value to the :bname parameter.
$search->bindParam(':color', $color) - Bind the color value to the :color parameter.
$search->execute() - Execute the SQL statement.
$conn = null - Close the connection.
echo "Connection failed: " . $e->getMessage() - Print the error message.

Create a index.php file

We will create a index.php file in the /var/www/html directory. We will conneact to DB using connect.php file.

1
<?php
2
    include_once 'connect.php';
3

4
    $query_SAILORS = "SELECT * FROM SAILORS";
5
    $query_BOATS = "SELECT * FROM BOATS";
6
    $query_RESERVES = "SELECT * FROM RESERVES";
7
    $query_1 = "SELECT * FROM SAILORS WHERE SID in (select SID from RESERVES WHERE BID = 101)";
8
    $query_2 = "SELECT SName FROM SAILORS WHERE SID in (select SID from RESERVES WHERE BID in (select BID from BOATS WHERE BColor = 'red')) order by SAge";
9
    $query_3 = "SELECT SName FROM SAILORS WHERE SID in (select SID from RESERVES)";
10
    $query_4 = "SELECT SID, SName FROM SAILORS WHERE SID in (select SID from RESERVES group by SID, Day having count(*) >= 2)";
11
    $query_5 = "SELECT SID FROM SAILORS WHERE SID in (select SID from RESERVES WHERE BID in (select BID from BOATS WHERE BColor = 'red')) or SID in (select SID from RESERVES WHERE BID in (select BID from BOATS WHERE BColor = 'green'))";
12

13
    $result_SAILORS = $conn->query($query_SAILORS);
14
    $result_BOATS = $conn->query($query_BOATS);
15
    $result_RESERVES = $conn->query($query_RESERVES);
16
    $result_1 = $conn->query($query_1);
17
    $result_2 = $conn->query($query_2);
18
    $result_3 = $conn->query($query_3);
19
    $result_4 = $conn->query($query_4);
20
    $result_5 = $conn->query($query_5);
21

22
    $SAILORS = $result_SAILORS->fetchAll();
23
    $BOATS = $result_BOATS->fetchAll();
24
    $RESERVES = $result_RESERVES->fetchAll();
25
    $result_1 = $result_1->fetchAll();
26
    $result_2 = $result_2->fetchAll();
27
    $result_3 = $result_3->fetchAll();
28
    $result_4 = $result_4->fetchAll();
29
    $result_5 = $result_5->fetchAll();
30

31
  $conn = null;
32
?>
33

34
<!DOCTYPE html>
35
<html>
36
<head>
37
    <title>SAI</title>
38
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tailwindcss/1.4.6/tailwind.min.css">
39
</head>
40
<body>
41
    <div class="container mx-auto">
42
        <div class="flex flex-wrap">
43
            <div class="w-full">
44
                <h1 class="text-4xl text-center">SAI</h1>
45
            </div>
46
        </div>
47
        <div class="flex flex-wrap">
48
            <div class="w-full">
49
                <h2 class="text-2xl text-center">SAILORS</h2>
50
            </div>
51
        </div>
52
        <div class="flex flex-wrap">
53
            <div class="w-full">
54
                <table class="table-auto">
55
                    <thead>
56
                        <tr>
57
                            <th class="px-4 py-2">SID</th>
58
                            <th class="px-4 py-2">SName</th>
59
                            <th class="px-4 py-2">SRating</th>
60
                            <th class="px-4 py-2">SAge</th>
61
                        </tr>
62
                    </thead>
63
                    <tbody>
64
                        <?php foreach ($SAILORS as $SAILOR): ?>
65
                            <tr>
66
                                <td class="border px-4 py-2"><?php echo $SAILOR['SID'] ?></td>
67
                                <td class="border px-4 py-2"><?php echo $SAILOR['SName'] ?></td>
68
                                <td class="border px-4 py-2"><?php echo $SAILOR['SRating'] ?></td>
69
                                <td class="border px-4 py-2"><?php echo $SAILOR['SAge'] ?></td>
70
                            </tr>
71
                        <?php endforeach; ?>
72
                    </tbody>
73
                </table>
74
            </div>
75
        </div>
76
        <div class="flex flex-wrap">
77
            <div class="w-full">
78
                <h2 class="text-2xl text-center">BOATS</h2>
79
            </div>
80
        </div>
81
        <div class="flex flex-wrap">
82
            <div class="w-full">
83
                <table class="table-auto">
84
                    <thead>
85
                        <tr>
86
                            <th class="px-4 py-2">BID</th>
87
                            <th class="px-4 py-2">BName</th>
88
                            <th class="px-4 py-2">BColor</th>
89
                        </tr>
90
                    </thead>
91
                    <tbody>
92
                        <?php foreach ($BOATS as $BOAT): ?>
93
                            <tr>
94
                                <td class="border px-4 py-2"><?php echo $BOAT['BID'] ?></td>
95
                                <td class="border px-4 py-2"><?php echo $BOAT['BName'] ?></td>
96
                                <td class="border px-4 py-2"><?php echo $BOAT['BColor'] ?></td>
97
                            </tr>
98
                        <?php endforeach; ?>
99
                    </tbody>
100
                </table>
101
            </div>
102
        </div>
103
        <div class="flex flex-wrap">
104
            <div class="w-full">
105
                <h2 class="text-2xl text-center">RESERVES</h2>
106
            </div>
107
        </div>
108
        <div class="flex flex-wrap">
109
            <div class="w-full">
110
                <table class="table-auto">
111
                    <thead>
112
                        <tr>
113
                            <th class="px-4 py-2">SID</th>
114
                            <th class="px-4 py-2">BID</th>
115
                            <th class="px-4 py-2">Day</th>
116
                        </tr>
117
                    </thead>
118
                    <tbody>
119
                        <?php foreach ($RESERVES as $RESERVE): ?>
120
                            <tr>
121
                                <td class="border px-4 py-2"><?php echo $RESERVE['SID'] ?></td>
122
                                <td class="border px-4 py-2"><?php echo $RESERVE['BID'] ?></td>
123
                                <td class="border px-4 py-2"><?php echo $RESERVE['Day'] ?></td>
124
                            </tr>
125
                        <?php endforeach; ?>
126
                    </tbody>
127
                </table>
128
            </div>
129
        </div>
130
        <div class="flex flex-wrap">
131
            <div class="w-full">
132
                <h2 class="text-2xl text-center">Find all information of sailors who have reserved boat number 101.</h2>
133
            </div>
134
        </div>
135
        <div class="flex flex-wrap">
136
            <div class="w-full">
137
                <table class="table-auto">
138
                    <thead>
139
                        <tr>
140
                            <th class="px-4 py-2">SID</th>
141
                            <th class="px-4 py-2">SName</th>
142
                            <th class="px-4 py-2">SRating</th>
143
                            <th class="px-4 py-2">SAge</th>
144
                        </tr>
145
                    </thead>
146
                    <tbody>
147
                        <?php foreach ($result_1 as $row): ?>
148
                            <tr>
149
                                <td class="border px-4 py-2"><?php echo $row['SID'] ?></td>
150
                                <td class="border px-4 py-2"><?php echo $row['SName'] ?></td>
151
                                <td class="border px-4 py-2"><?php echo $row['SRating'] ?></td>
152
                                <td class="border px-4 py-2"><?php echo $row['SAge'] ?></td>
153
                            </tr>
154
                        <?php endforeach; ?>
155
                    </tbody>
156
                </table>
157
            </div>
158
        </div>
159
        <div class="flex flex-wrap">
160
            <div class="w-full">
161
                <h2 class="text-2xl text-center">Find the names of sailors who have reserved a red boat, and list in the order of age.</h2>
162
            </div>
163
        </div>
164
        <div class="flex flex-wrap">
165
            <div class="w-full">
166
                <table class="table-auto">
167
                    <thead>
168
                        <tr>
169
                            <th class="px-4 py-2">SName</th>
170
                        </tr>
171
                    </thead>
172
                    <tbody>
173
                        <?php foreach ($result_2 as $row): ?>
174
                            <tr>
175
                                <td class="border px-4 py-2"><?php echo $row['SName'] ?></td>
176
                            </tr>
177
                        <?php endforeach; ?>
178
                    </tbody>
179
                </table>
180
            </div>
181
        </div>
182
        <div class="flex flex-wrap">
183
            <div class="w-full">
184
                <h2 class="text-2xl text-center">Find the names of sailors who have reserved at least one boat. </h2>
185
            </div>
186
        </div>
187
        <div class="flex flex-wrap">
188
            <div class="w-full">
189
                <table class="table-auto">
190
                    <thead>
191
                        <tr>
192
                            <th class="px-4 py-2">SName</th>
193
                        </tr>
194
                    </thead>
195
                    <tbody>
196
                        <?php foreach ($result_3 as $row): ?>
197
                            <tr>
198
                                <td class="border px-4 py-2"><?php echo $row['SName'] ?></td>
199
                            </tr>
200
                        <?php endforeach; ?>
201
                    </tbody>
202
                </table>
203
            </div>
204
        </div>
205
        <div class="flex flex-wrap">
206
            <div class="w-full">
207
                <h2 class="text-2xl text-center">Find the ids and names of sailors who have reserved two different boats on the same day.</h2>
208
            </div>
209
        </div>
210
        <div class="flex flex-wrap">
211
            <div class="w-full">
212
                <table class="table-auto">
213
                    <thead>
214
                        <tr>
215
                            <th class="px-4 py-2">SID</th>
216
                            <th class="px-4 py-2">SName</th>
217
                        </tr>
218
                    </thead>
219
                    <tbody>
220
                        <?php foreach ($result_4 as $row): ?>
221
                            <tr>
222
                                <td class="border px-4 py-2"><?php echo $row['SID'] ?></td>
223
                                <td class="border px-4 py-2"><?php echo $row['SName'] ?></td>
224
                            </tr>
225
                        <?php endforeach; ?>
226
                    </tbody>
227
                </table>
228
            </div>
229
        </div>
230
        <div class="flex flex-wrap">
231
            <div class="w-full">
232
                <h2 class="text-2xl text-center">Find the ids of sailors who have reserved a red boat or a green boat.</h2>
233
            </div>
234
        </div>
235
        <div class="flex flex-wrap">
236
            <div class="w-full">
237
                <table class="table-auto">
238
                    <thead>
239
                        <tr>
240
                            <th class="px-4 py-2">SID</th>
241
                        </tr>
242
                    </thead>
243
                    <tbody>
244
                        <?php foreach ($result_5 as $row): ?>
245
                            <tr>
246
                                <td class="border px-4 py-2"><?php echo $row['SID'] ?></td>
247
                            </tr>
248
                        <?php endforeach; ?>
249
                    </tbody>
250
                </table>
251
            </div>
252
        </div>
253
    </div>
254
</body>
255
</html>

Click to expand the explanation

Select all columns from the SAILORS table

1
SELECT * FROM SAILORS

Select all columns from the BOATS table

1
SELECT * FROM BOATS

Select all columns from the RESERVES table

1
SELECT * FROM RESERVES

Select all columns from the SAILORS table where the SID is in the SID column of the RESERVES table where the BID is equal to 101

1
select * from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
    WHERE BID = 101
5
);

Select the SName column from the SAILORS table where the SID is in the SID column of the RESERVES table where the BID is in the BID column of the BOATS table where the BColor is equal to 'red' and order the results by the SAge column

1
select SName from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
    WHERE BID in (
5
        select BID from `SAI`.`BOATS`
6
        WHERE BColor = 'red'
7
    )
8
)
9
order by SAge;

Select the SName column from the SAILORS table where the SID is in the SID column of the RESERVES table

1
select SName from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
);

Select the SID and SName columns from the SAILORS table where the SID is in the SID column of the RESERVES table grouped by the SID and Day columns having a count of * greater than or equal to 2

1
select SID, SName from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
    group by SID, Day
5
    having count(*) >= 2
6
);

Select the SID column from the SAILORS table where the SID is in the SID column of the RESERVES table where the BID is in the BID column of the BOATS table where the BColor is equal to 'red' or the SID is in the SID column of the RESERVES table where the BID is in the BID column of the BOATS table where the BColor is equal to 'green'

1
select SID from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
    WHERE BID in (
5
        select BID from `SAI`.`BOATS`
6
        WHERE BColor = 'red'
7
    )
8
)
9
or SID in (
10
    select SID from `SAI`.`RESERVES`
11
    WHERE BID in (
12
        select BID from `SAI`.`BOATS`
13
        WHERE BColor = 'green'
14
    )
15
);

$result =$conn->query($query) executes the query and returns the result as a mysqlPDOStatement object which can be used to fetch the results as an associative array using the fetchAll() method
$result->fetchAll(); fetches all the results as an associative array and returns it
<?php foreach ($results as $row): ?> loops through the results and assigns each row to the $row variable
<?php echo $row['column_name'] ?> prints the value of the column with the name column_name in the current row
<?php endforeach; ?> ends the loop

Create a form to insert a new row into the `BOATS` table

We will create a new file insert-boats-form.php in the /var/www/html directory and add the following code to it.

1
<!DOCTYPE html>
2
<html>
3
<head>
4
    <title>SAI</title>
5
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tailwindcss/1.4.6/tailwind.min.css">
6
</head>
7
<body>
8
    <div class="container mx-auto">
9
        <div class="flex flex-wrap">
10
            <div class="w-full">
11
                <h1 class="text-4xl text-center">SAI</h1>
12
            </div>
13
        </div>
14
        <div class="flex flex-wrap">
15
            <div class="w-full">
16
                <h2 class="text-2xl text-center">Insert data into database</h2>
17
            </div>
18
        </div>
19
        <div class="flex flex-wrap">
20
            <div class="w-full">
21
                <form action="insert.boats.php" method="post">
22
                    <div class="flex flex-wrap">
23
                        <div class="w-full">
24
                            <label class="block text-gray-700 text-sm font-bold mb-2" for="bid">
25
                                bid
26
                            </label>
27
                            <input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline" id="bid" name="bid" type="text" placeholder="bid">
28
                        </div>
29
                    </div>
30
                    <div class="flex flex-wrap">
31
                        <div class="w-full">
32
                            <label class="block text-gray-700 text-sm font-bold mb-2" for="bname">
33
                                bname
34
                            </label>
35
                            <input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline" id="bname" name="bname" type="text" placeholder="bname">
36
                        </div>
37
                    </div>
38
                    <div class="flex flex-wrap">
39
                        <div class="w-full">
40
                            <label class="block text-gray-700 text-sm font-bold mb-2" for="color">
41
                                color
42
                            </label>
43
                            <input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline" id="color" name="color" type="text" placeholder="color">
44
                        </div>
45
                    </div>
46
                    <div class="flex flex-wrap">
47
                        <div class="w-full">
48
                            <button class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline w-full" type="submit">
49
                                Submit
50
                            </button>
51
                        </div>
52
                    </div>
53
                </form>
54
            </div>
55
        </div>
56
    </div>
57
</body>
58
</html>

Explanation:

Click to expand the explanation

The action attribute of the form is set to insert-boats.php which is the file that will handle the form submission
The method attribute of the form is set to post which means that the form data will be sent to the server using the POST method
The id attribute of the input elements are set to the column names of the BOATS table
The name attribute of the input elements are set to the column names of the BOATS table
The type attribute of the input elements are set to text which means that the input will be a text input
The placeholder attribute of the input elements are set to the column names of the BOATS table
The type attribute of the submit button is set to submit which means that the button will submit the form

Conclusion

In this tutorial, How to connect to a MySQL database using PHP was explained. We also learned how to create a form to insert data into a MySQL database using PHP.