# PHP backend

The following examples are based on the database from the course PHP-Laravel.
Only the tables genres and recordswill be used.

# Database connection

The file connection.php only contains the variables to connect to the database.
All other pages in this chapter use the same connection.
By keeping the connection in a separate file, you only have to maintain the code in one place.

<?php

// Database connection
$DB_HOST = 'localhost';
$DB_USERNAME = 'root';
$DB_PASSWORD = 'password';
$DB_DATABASE = 'databaseName';

// Set response headers
header('Access-Control-Allow-Origin: *');       // Enable CORS
header('Content-Type: application/json');
http_response_code(200);                        // response code 200: OK

// Open connection to database
try {
	$conn  = new PDO("mysql:host=$DB_HOST;dbname=$DB_DATABASE;charset=utf8", $DB_USERNAME, $DB_PASSWORD);
	$conn ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$conn ->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch(PDOException $error) {
	http_response_code(400);
	echo json_encode([
		"error" => 400,
		"message" => $error->getMessage()
	]);
	die();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Lines 4 - 8:
Before you can access data in the MySQL database, you have to connect to the server.
On https://panel.sinners.be/database you will find all the data for your own hosting.
Line 10: the page is also accessible outside your own domain (CORS)
Line 11: the MIME media type for JSON text is application/json
Line 12: the HTTP 200 OK status code indicates that the request was successful.
Line 22: A 400 Bad Request Error indicates that the request could not be processed by the server.
A 400 error ends up in the fail() methode of jQuery's $.getJSON().
Line 17: specifies how PDO displays error messages.
Line 18: specifies that PDO displays the result as an associative array.

# Examples

# Get all records

Get all records from the table records and display the result as an array of JSON objects.
[{...},{...},{...}]
Live preview: https://pverhaert.sinners.be/cordova/master_1.php

<?php

include_once('connection.php');

// get all `records`
$query = "SELECT * FROM records";

try {
	$records = $conn->prepare($query);
	$records->execute();
	$result = $records->fetchAll();
} catch(PDOException $error) {
	http_response_code(400);
	$result = [
		"error" => 400,
		"message" => $error->getMessage()
	];
}

echo json_encode($result);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Line 3: import (paste) the code from connection.php here.
Line 6: place the query you are going to execute here.
Line 20: transform the array into a JSON object.
(You can also include additional parameters such as JSON_PRETTY_PRINT or JSON_THROW_ON_ERROR.)

# Get all records (ordered by artist)

Live preview: https://pverhaert.sinners.be/cordova/master_2.php

<?php

include_once('connection.php');

// get all `records` ordered by `artist`
$query = "SELECT id, artist, title, price 
		  FROM records 
		  ORDER BY artist";

try {
	$records = $conn->prepare($query);
	$records->execute();
	$result = $records->fetchAll();
} catch(PDOException $error) {
  	http_response_code(400);
	echo json_encode([
		"error" => 400,
		"message" => $error->getMessage()
	]);
}

echo json_encode($result, JSON_PRETTY_PRINT);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Get all records with genre (ordered by artist)

Live preview: https://pverhaert.sinners.be/cordova/master_3.php

<?php

include_once('connection.php');

// get alle `records` with `genre`, ordered by `artist`
$query = "SELECT records.id, artist, title, genres.name AS genre 
	      FROM records 
	      JOIN genres ON records.genre_id = genres.id
	      ORDER BY artist";

try {
	$records = $conn->prepare($query);
	$records->execute();
	$result = $records->fetchAll();

} catch(PDOException $error) {
  	http_response_code(400);
	echo json_encode([
		"error" => 400,
		"message" => $error->getMessage()
	]);
}

echo json_encode($result);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Get all genres with the associated records

Live preview: https://pverhaert.sinners.be/cordova/master_4.php

<?php

include_once('connection.php');

// get all `genres` with the associated `records`
$query = "SELECT genres.name, records.id AS record_id, artist, title 
          FROM genres
          JOIN records ON records.genre_id = genres.id";

try {
	$genres = $conn->prepare($query);
	$genres->execute();
	$result = $genres->fetchAll(PDO::FETCH_GROUP);      // group by `genre`
} catch(PDOException $error) {
  	http_response_code(400);
	echo json_encode([
		"error" => 400,
		"message" => $error->getMessage()
	]);
}

echo json_encode($result);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Select one record with id as parameter

Live preview:

<?php

include_once('connection.php');

// selecteer één `record`
$record_id = $_GET['id'] ?? 999999;

$query = "SELECT * FROM records WHERE records.id = $record_id";

try {
	$record = $conn->prepare($query);
	$record->execute();
	$result = $record->fetch();     // use fetch() instead of fetchAll() => object instead of array with 1 object
} catch(PDOException $error) {
  	http_response_code(400);
	echo json_encode([
		"error" => 400,
		"message" => $error->getMessage()
	]);
}

echo json_encode($result);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Bonus

Download all examples from this chapter.
Open index.html in a browser.
Open ìndex.html in PhpStorm and study how to build the page with jQuery's $.getJSON().

# More info

PHP JSON Complete Tutorial