如何改进这个SQL查询（SQLite和PHP）

Question

I am rendering an array based on data from multiple db tables in SQLite.

Tables:

• staff - Contains all users/staff members that can have certificates
• certificates --- Contains all available certificates that a user can have
• rel__staff_certificate --- Contains certificates connected (relation) to a user

TABLE staff

`staff_id`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name`  TEXT NOT NULL,

TABLE certificates

`cert_id`   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`caption`   TEXT NOT NULL,
`description`   TEXT

TABLE rel__staff_certificate

`rel_id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`staff_id`  INTEGER,
`cert_id`   INTEGER,
`cert_date` TEXT NOT NULL,
`comments`  TEXT

.

.

So here is the deal:

// Get all available certificates
$sql_get_cert = "SELECT * FROM certificates ORDER BY cert_id ASC";

// Get all certificates for staff user
$sql_get_staff_cert_rel = "SELECT * FROM rel__staff_certificate WHERE staff_id = :staff_id AND cert_id = :cert_id LIMIT 1";

// Prepare SQL queries...
$get_cert_rel = $PDODB->prepare($sql_get_staff_cert_rel);
$get_cert = $PDODB->prepare($sql_get_cert);

// Get all certificates to array $certdata
$get_cert->execute();
$certdata = $get_cert->fetchAll();

// Create the array where I want to push data to:
$staffdata[$i] = array(
    $staff['staff_id'], // $staff() is populated earlier and works just fine
    $staff['name'],
    $ccdata['region'],  // $ccdata() is populated earlier and works just fine
    $ccdata['cc_code']
);

// Loop through all available certificates in table ´certificates´
foreach ($certdata as $cert) {
    // Bind values for the relation SQL now when we have them all
    $get_cert_rel->bindValue(':staff_id', $staff['staff_id']);
    $get_cert_rel->bindValue(':cert_id', $cert['cert_id']);
    $get_cert_rel->execute();

    // Get the certification relation data to array $certreldata()
    $certreldata = $get_cert_rel->fetch();

    // If a certificate date exists, then use that for our array which tells us that this staff has this certificate and was certified on this date
    if (!empty($certreldata['cert_date'])) { 
        array_push($staffdata[$i], $certreldata['cert_date']);
    }
    // If no certificate date exsits, then just add value "N/A" as not available
    else {
        array_push($staffdata[$i], 'N/A');
    }
}

OK so thats it! It works, but as you can see I am doing a lot of SQL execution to the SQL server in the foreach() loop and I do not believe this is very good as it will consume a lot of unnecessary time.

Can anyone tell me how to do this with a single SQL query instead? Or tell me how to improve the code to speed things up further in other ways?

Thank you.

Answer 1

You can use JOIN for such queries.

Using a LEFT JOIN You join the two tables using the private key of first table (left) and a foreign key of the second (right) while if there is no row found in the second (right) table, the columns for the right table will be filled with NULL values:

SELECT c.*
FROM certificates AS c
LEFT JOIN rel__staff_certificate AS sc ON sc.cert_id = c.cert_id
WHERE sc.staff_id = ?
ORDER BY c.cert_id

USING an INNER JOIN You join two tables the same way as in LEFT JOIN but if there is no entry for the private key of left table in the right table, those rows are skipped:

SELECT c.*
FROM certificates AS c
INNER JOIN rel__staff_certificate AS sc ON sc.cert_id = c.cert_id
WHERE sc.staff_id = ?
ORDER BY c.cert_id

More on joins can be found in the documentation - basic JOIN structure and JOIN operators.