doujujian0052 2014-07-16 14:17
浏览 34
已采纳

如何改进这个SQL查询(SQLite和PHP)

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.

  • 写回答

3条回答 默认 最新

  • doujia2463 2014-07-16 14:37
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形