Using the php PDO extension is extremely easy. First make a connection and set some commonly used attribute values -
<?php
// db connection - you should put the connection code in an external .php file and require it when needed
$DB_HOST = ''; // fill in these values with your connection credentials
$DB_USER = '';
$DB_PASS = '';
$DB_NAME = '';
$DB_ENCODING = 'utf8'; // db character encoding - set to match your database tables
$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
Next, to convert any query to a prepared query, just do these steps -
- Replace each variable, single-quotes around the variable, any concatenation dots, and any {}, with a ? place-holder.
- Call the pdo prepare method. This returns a PDOStatement object.
- Call the PDOStatement execute method with an array consisting of the variable(s) that were removed in step #1.
- Fetch the data from the query using one of the PDOStatement fetch methods. Fetch() is used for a query that will match at most one row. FetchAll() is used for a query that will match a set of data. You can also use fetchAll() to get an array of values from a single column, rather than an array or rows. FetchColumn() is used when you only want a value from a single column, which is useful for things like COUNT() queries.
See the code at the end of this reply for an example - PHP Select from MYSQL with Variable
For your query/code, after you require the .php file that makes the connection, you would end up with this -
<?php
$query = " SELECT
g.name as `group`,
COUNT(ar.present) as attended
FROM
attendance_record ar
INNER JOIN
_person p
ON ar.personid = p.id
INNER JOIN
_person_group g
ON ar.groupid = g.id
-- WHERE
AND
YEAR(date) = ?
AND
ar.present = 1
";
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$query_data = $stmt->fetchAll();
For a query that doesn’t have any external/unknown data being put into it, instead of calling prepare()/execute(), you would just call the pdo query() method, which returns a PDOStatement object. You would then use any of the same PDOStatement fetch methods to retrieve the data into a php variable.
