PHP Code Walkthrough

Overview

This walkthrough explains how to perform a simple test of a SkySQL service using PHP code, as described in our Quickstart experience.

Done with this walkthrough? Return to Step 3 of the Quickstart

Dependency

  • PHP 7, 8

  • Choose one of PHP's MariaDB-compatible community-supported connectors:

    • mysqli library

    • PDO_MYSQL library

Install PHP Library

Use your OS package manager to install your choice of the mysqli library or the PDO_MYSQL library for PHP. For example, the virtual package php-mysqli can often be installed to get the real package installed, which may be named php7.4-mysql or something similar.

Prepare Code for mysqli

Using your editor of choice, create a file named app.php

Those that prefer to use PDO_MYSQL, jump ahead to the PDO_MYSQL section.

Connect

Copy the following code into app.php with the following modifications:

  • Change the host, user, and password for your service

  • Set the ssl_ca path to the full path where you saved the SkySQL certificate authority chain file.

<?php
# Basic connection settings
$dbHost = 'quickstart.mdbxxxxxxx.db.skysql.net';
$dbPort = 5001;
$sslPath = '/path/to/skysql_chain.pem';
$dbUser = 'DBXXXXXXXX';
$dbPassword = 'xxxxxxxxxx';

# Connect to the database using SSL
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$mysqli->ssl_set(NULL, NULL, $sslPath, NULL, NULL);
$mysqli->real_connect($dbHost, $dbUser, $dbPassword, NULL, $dbPort);
$mysqli->query('SET NAMES utf8');

Prepare the Table

Continue editing app.php using this code:

$mysqli->query("CREATE DATABASE IF NOT EXISTS quickstart");
$mysqli->query("USE quickstart");

$mysqli->query("
    CREATE TABLE programming_language (
      name VARCHAR(50) NOT NULL UNIQUE,
      rating INT
    )");

Data In

Continue to edit app.php using this code:

$rows = [
    ["C#", 6],
    ["C++", 8],
    ["Go", 5],
    ["Java", 9],
    ["JavaScript", 7],
    ["PHP", 3],
    ["Python", 10],
    ["Rust", 4],
];

$stmt = $mysqli->prepare("INSERT INTO programming_language VALUES (?, ?)");
$stmt->bind_param('si', $name, $rating);
foreach ($rows as $row) {
    list($name, $rating) = $row;
    $stmt->execute();
}

Data Out

Continue to edit app.php using this code:

$result = $mysqli->query("
    SELECT name, rating
    FROM programming_language
    ORDER BY rating DESC");

while ($row = $result->fetch_assoc()) {
    echo $row['rating'] . ' - ' . $row['name'] . "\n";
}

$result = $mysqli->query("
    SELECT name, rating
    FROM programming_language
    ORDER BY name
    LIMIT 1");

# All field values are returned as strings
echo "---\n";
$row = $result->fetch_assoc();
var_dump($row);

Jump ahead to run the test.

Prepare Code for PDO_MYSQL

Using your editor of choice, create a file named app.php

Those that prefer to use mysqli, jump back to the mysqli section.

Connect

Copy the following code into app.php with the following modifications:

  • Change the host, user, and password for your service

  • Set the ssl_ca path to the full path where you saved the SkySQL certificate authority chain file.

<?php
# Basic connection settings
$dbHost = 'quickstart.mdbxxxxxxx.db.skysql.net';
$dbPort = 5001;
$sslPath = '/path/to/skysql_chain.pem';
$dbUser = 'DBXXXXXXXX';
$dbPassword = 'xxxxxxxxxx';

$dsn = "mysql:host=$dbHost;port=$dbPort";

$options = [
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::MYSQL_ATTR_SSL_CA => $sslPath,
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
    # Thow exceptions instead of returning errors:
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

$pdo = new PDO($dsn, $dbUser, $dbPassword, $options);

Prepare the Table

Continue editing app.php using this code:

$pdo->query("CREATE DATABASE IF NOT EXISTS quickstart");
$pdo->query("USE quickstart");

$pdo->query("
    CREATE TABLE programming_language (
      name VARCHAR(50) NOT NULL UNIQUE,
      rating INT
    )");

Data In

Continue to edit app.php using this code:

$rows = [
    ["C#", 6],
    ["C++", 8],
    ["Go", 5],
    ["Java", 9],
    ["JavaScript", 7],
    ["PHP", 3],
    ["Python", 10],
    ["Rust", 4],
];

$stmt = $pdo->prepare("INSERT INTO programming_language VALUES (?, ?)");
foreach ($rows as $row) {
    $stmt->execute($row); # $row is an array of 2 values
}

Data Out

Continue to edit app.php using this code:

class Language {
    public $name;
    public $rating;
}

$rows = $pdo->query("
    SELECT name, rating
    FROM programming_language
    ORDER BY rating DESC
    ")->fetchAll(PDO::FETCH_CLASS, 'Language');

foreach ($rows as $row) {
    echo $row->rating . ' - ' . $row->name . "\n";
}

$rows = $pdo->query("
    SELECT name, rating
    FROM programming_language
    ORDER BY name
    LIMIT 1
    ")->fetchAll();

# All field values are returned as strings
echo "---\n";
var_dump($rows[0]);

Run the Test

Save your changes to app.php and start the application by running the following command (or equivalent option in your IDE):

$ php app.php

You should see the following output for mysqli:

10 - Python
9 - Java
8 - C++
7 - JavaScript
6 - C#
5 - Go
4 - Rust
3 - PHP
---
array(2) {
  ["name"]=>
  string(2) "C#"
  ["rating"]=>
  string(1) "6"
}

Or the following output for PDO_MYSQL:

10 - Python
9 - Java
8 - C++
7 - JavaScript
6 - C#
5 - Go
4 - Rust
3 - PHP
---
array(4) {
  ["name"]=>
  string(2) "C#"
  [0]=>
  string(2) "C#"
  ["rating"]=>
  string(1) "6"
  [1]=>
  string(1) "6"
}

Done with this walkthrough? Return to Step 3 of the Quickstart