使用CodeIgniter从DB中删除除前50行之外的所有行

问题描述：

I've been getting a multitude of errors with the request I'm trying to make. I'd like to grab the top 50 ids from my keywords table ordered by count and delete the rest. Any ideas what could be acting up here?

I'm getting a 500 Internal Server Error when making the request...

        //1. get ids of top 50
        $this->CI->db->query("SELECT id FROM keywords ORDER BY count DESC LIMIT 50");
        $top_fifty = $this->CI->db->get();
        //return $top_fifty;

        //2. loop through and create string of ids "1,2,3,4,5"
        $top_fifty_string = implode(",", $top_fifty);

        // 3. DELETE FROM keywords WHERE id NOT IN(string created in step 2)
        $this->CI->db->query("DELETE FROM keywords WHERE id NOT IN($top_fifty_string)");

答

Fixed function :

// //1. get ids of top 50
$top_fifty = $this->CI->db->query("SELECT id FROM keywords ORDER BY count DESC LIMIT 50 ");

// //2. array of ids to string "1,2,3,4,5"
$output ="";

foreach ($top_fifty->result() as $k) {
$output .= $k->id . ",";
}
$top_fifty_string = rtrim($output, ",");

// // 3. DELETE FROM keywords WHERE id NOT IN(string created in step 2)
$this->CI->db->query("DELETE FROM keywords WHERE id NOT IN(.$top_fifty_string.)");

答

Try to write it as a subquery:

$this->CI->db->query("DELETE FROM keywords WHERE id NOT IN(SELECT id FROM keywords ORDER BY count DESC LIMIT 50)");

使用CodeIgniter从DB中删除除前50行之外的所有行

相关推荐