Skip to Content

Access Amazon DynamoDB Data from MySQL in PHP

Posted on October 26, 2022 by

Categories: AWS

Tags:

Without doing an ETL or caching data, you may access Amazon DynamoDB data from MySQL clients using the CData SQL Gateway and ODBC Driver for Amazon DynamoDB. Use PHP’s MySQL and PDO MySQL standard MySQL interfaces to connect to Amazon DynamoDB data in real-time by following the below instructions.

Join the Amazon DynamoDB Data Service

Provide values for the necessary connection attributes in the data source name if you haven’t previously (DSN). The DSN may be configured using the built-in Microsoft ODBC Data Source Administrator. Additionally, this completes the driver installation process. For instructions on constructing and setting up a DSN using the Microsoft ODBC Data Source Administrator, go to the “Getting Started” chapter in the help files.

Your AccessKey, SecretKey, and, if available, your Domain and Region are used to establish a connection with Amazon DynamoDB. On the security credentials page for your Amazon Web Services account, you can find your AccessKey and SecretKey. When you log into DynamoDB, your Region will be shown in the upper left-hand corner.

Set up the SQL Gateway.

To configure connectivity to Amazon DynamoDB data as a fictitious MySQL database, see the SQL Gateway Overview. You will set up a MySQL remote service that watches for client requests for MySQL. The SQL Gateway UI allows for the configuration of the service.

How to setup SQL Gateway to use MySQL as a Remote Service (Salesforce is shown)

PHP connection

The examples demonstrate how to connect to and run queries using object-oriented interfaces. To establish a connection to the virtual MySQL database, initialize the connection object with the following values:

  • Host: Enter the address of the remote host on which the service is executing. Since the service is now being utilized on a local machine, “localhost” is used as the remote host configuration.
  • Username: On the Users tab of the SQL Gateway, enter the username for a user that you have permission to use.
  • Enter the password for the account of the permitted user.
  • Database Name: The database name should be the system DSN.
  • Port: Indicate the port the service is currently using, in this case, port 3306.

MySQL

<?php
$mysqli new mysqli("localhost""user""password""CData AmazonDynamoDB Sys","3306");
?>

PDO

<?php
$pdo new PDO('mysql:host=localhost;dbname=CData AmazonDynamoDB Sys;port=3306''user''password');
?>

In PHP, a query

Once the connection is made, you may access the tables. The example is demonstrated in the following steps:

  1. Use Lead as an example to query the table. The $result object will have the results in the form of an associative array.
  2. Print out the data shown in the PHP page as you iterate over each row and column.
    Cut the line off.

MySQL

$result $mysqli->query("SELECT Industry, Revenue FROM Lead");
while($row $result->fetch_assoc()) {
foreach ($row as $k=>$v) {
echo "$k : $v";
echo "<br />";
}
}
$mysqli->close();

PDO

$result $pdo->query("SELECT Industry, Revenue FROM Lead");
while($row $result->fetch(PDO::FETCH_ASSOC)) {
foreach ($row as $k=>$v) {
echo "$k : $v";
echo "<br />";
}
}
$result = null;
$pdo = null;