How to Query

How to Query
Best Practices

Best Practices

Bellow is a list of dos and don'ts for querying the DB.

Use the new DB Class instead of require connection

Previously, the only way we had to create the DB object was to require the connection file but a new Class was created to replace this method. Please refrain to Use DB Class on how to create the DB object.

Use Statements and Prepares

Statements and prepares should be used as a security and performance best practice.

// Wrong example
$sql    = "SELECT column FROM table WHERE id = '123'";
$rsSql  = $db->Execute($sql);

// Correct way to do it
$sql    = "SELECT column FROM table WHERE id = ?";
$rsSql  = $db->Execute($sql, ['123']);

// Using Statements for repetitive inserts/updates
$stmt   = $db->prepare("INSERT INTO table (id, value) VALUES (?, ?)");

foreach ($array as $id => $value) {
    $db->execute($stmt, [$id, $value]);
}

Do not use die() when running the query

The following example is wrong and shouldn't be used:

$sql    = "SELECT column FROM table";
$rsSql  = $db->Execute($sql) or die($db->ErrorMsg());

If you want to capture the Error Message it should be done like this:

$sql    = "SELECT column FROM table";
$rsSql  = $db->Execute($sql);

if ($db->ErrorMsg()) {
    // Do anything
}

Do not SELECT * FROM

This is a common one and should be avoided at all costs.

$sql    = "SELECT * FROM table";
$rsSql  = $db->Execute($sql);

The * should be replaced with the columns needed to avoid unnecessary processing and bandwith usage. Many times we see people using the * and only use 1 column.

The correct way to do it is:

$sql    = "SELECT id, column_1, column_2 FROM table";
$rsSql  = $db->Execute($sql);

getArray, getRow and more

The getArray, getRow and similar functions can be called directly instead of the Execute avoiding extra lines of code.

// Wrong usage
$sql    = "SELECT id, column_1, column_2 FROM table";
$rsSql  = $db->Execute($sql);
$array  = $rsSql->getArray();

// Correct usage
$sql    = "SELECT id, column_1, column_2 FROM table";
$array  = $db->getArray($sql);

Avoid using RecordCount()

Many times we need to get the number of records that exists in a table. A common usage is:

$sql    = "SELECT id FROM table";
$rsSql  = $db->Execute($sql);
$count  = $rsSql->RecordCount();

There are other ways to do it. As the MySQL server is faster that PHP itself, it could be done like this:

$sql    = "SELECT count(1) AS count FROM table";
$rsSql  = $db->getRow($sql);
$count  = $rsSql['count'];

↑ (Última atualização: 20/06/2025)