public function getAll($recursive) { $sql = "SELECT * FROM reuse_and_repair_db.Business\n LEFT JOIN reuse_and_repair_db.Address\n ON reuse_and_repair_db.Business.address_id = reuse_and_repair_db.Address.address_id\n LEFT JOIN reuse_and_repair_db.Hours\n ON reuse_and_repair_db.Business.hours_id = reuse_and_repair_db.Hours.hours_id"; $prepared = $this->db->link->prepare($sql); $success = $prepared->execute(); $all = $prepared->fetchAll(); //var_dump($all); foreach ($all as $row) { $address = new Address($row['address_id'], $row['street_number'], $row['street_name'], $row['city'], $row['state'], $row['zip'], $row['geolocation']); // $id, $category, $name, $address, $hours, $website $business = new Business($row['business_id'], $row['category_name'], $row['name'], $address, $row['hours_id'], $row['website']); $this->results[] = $business->jsonSerialize(); } return $this->getJSON(); }
/** * Get a businesses by category_id subcategory_id * @param $object * @return string */ public function getByCategoryAndSubcategory($object) { $sql = "SELECT * FROM reuse_and_repair_db.Business\n LEFT JOIN reuse_and_repair_db.Address\n ON reuse_and_repair_db.Business.fk_address_id = reuse_and_repair_db.Address.address_id\n LEFT JOIN reuse_and_repair_db.Hours\n ON reuse_and_repair_db.Business.fk_hours_id = reuse_and_repair_db.Hours.hours_id\n INNER JOIN reuse_and_repair_db.Business_Subcategory\n ON reuse_and_repair_db.Business.business_id = reuse_and_repair_db.Business_Subcategory.fk_business_id\n INNER JOIN reuse_and_repair_db.Category\n ON reuse_and_repair_db.Business.fk_category_id = reuse_and_repair_db.Category.category_id\n WHERE reuse_and_repair_db.Business.fk_category_id = ? AND reuse_and_repair_db.Business_Subcategory.fk_subcategory_id = ?\n ORDER BY reuse_and_repair_db.Business.business_name;"; $prepared = $this->db->link->prepare($sql); $prepared->bindParam(1, $object['category_id']); $prepared->bindParam(2, $object['subcategory_id']); $success = $prepared->execute(); $all = $prepared->fetchAll(); foreach ($all as $row) { $category = new Category($row['category_id'], $row['category_name']); $address = new Address($row['address_id'], $row['street_number'], $row['street_name'], $row['city'], $row['state'], $row['zip'], $row['geolocation']); $hours = new Hours($row['hours_id'], $row['hours_entry']); // $id, $category, $name, $address, $hours, $website $business = new Business($row['business_id'], $category, $row['business_name'], $address, $row['phone'], $hours, $row['website'], $row['description']); $this->results[] = $business->jsonSerialize(); } return $this->getJSON(); }