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.