Delete all but top 50 rows from DB with CodeIgniter

debugcn Published at Dev

Lauren F

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)");

AdrienXL

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.)");

Collected from the Internet

Please contact [email protected] to delete if infringement.