I figured it out. So basically I exported my products from the products table, but failed to backup the product_description, and the products_to_categories tables.

In order for your products to be available in a particular category those three tables rely on each other.

Lesson learned.