CODEWITHSUNDEEP
phpsqlsqlite
Nick
Nick

Reputation: 133

How to improve this SQL query (SQLite and PHP)

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

Tables:

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.

Upvotes: 0

Views: 85

Answers (3)

Ryan
Ryan

Reputation: 113

Try using a SQL join. Here's an example that gives the relevant information:

SELECT 
    staff.staff_id, 
    staff.name, 
    certificates.caption, 
    certificates.description, 
    rel__staff_certificate.cert_date, 
    rel__staff_certificate.comments
FROM rel__staff_certificate staff 
INNER JOIN certificates ON certificates.cert_id = rel__staff_certificate.cert_id 
INNER JOIN staff ON staff.staff_id = rel__staff_certificate.staff_id
WHERE staff.staff_id = ?

The WHERE statement may be optional if you want all staff members with all of their certs in one query.

Upvotes: 1

shadyyx
shadyyx

Reputation: 16065

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.

Upvotes: 0

CL.
CL.

Reputation: 180270

This can be done with join:

SELECT cert_date
FROM certificates
JOIN rel__staff_certificate USING (cert_id)
WHERE staff_id = ?

Upvotes: 1

Related Questions