Geek Answers Handbook

Optimize this SQL query

I have a SQL query in a foreach loop. Sometimes there can be a lot of them, and I mean a lot of requests, depending on several criteria, up to 78 requests.

Now I know that premature optimization is the main cause of all evil, but I do not want to see 78 requests - this is just not great.

Here is the code:

$crumbs = explode(",", $user['data']['depts']);

foreach ($crumbs as &$value) {
    $data = $db->query("SELECT id FROM tbl_depts WHERE id = '" . $value . "'");
    $crumb = $data->fetch_assoc();
    $dsn = $db->query("SELECT msg, datetime FROM tbl_motd WHERE deptid = '" . $value . "'");
    $motd = $dsn->fetch_assoc();
    if ($motd['msg'] != "") {
        <?php echo $motd['msg']; ?>
    }
}

Can I do it better?

+1
source share
2 answers

Use IN MySQL operatorto search a set of values ​​for id:

$ids = '"' . implode('", "',$crumbs) . '"';
$query1 = "SELECT id FROM tbl_depts WHERE id IN (" . $ids . ")";
$query2 = "SELECT msg, datetime FROM tbl_motd WHERE deptid IN (" . $ids . ")";

And therefore, you will not need to retrieve all the data you need using the loop foreach, so you will only have 2 queries instead of 78.

. table 10 , : 1,2,3,4,5,6,7,8,9,10 ( ). , 1,5,8. :

$sql = "SELECT * FROM `table` WHERE id in (1,5,8);";

, & foreach, $crubms.

+1

, , .

SELECT msg, datetime
FROM tbl_depts td
INNER JOIN tbl_motd tm ON td.id = tm.deptid
+1

More articles:


All Articles