def get_business_profile(self, business_id): """Fetch full business details + reviews""" biz = db.query_one("SELECT * FROM businesses WHERE id = ?", [business_id]) if not biz: return None reviews = db.query("SELECT * FROM reviews WHERE business_id = ? ORDER BY created_at DESC", [business_id]) biz['reviews'] = reviews return biz
def get_top_rated(self, limit=10): """List businesses with highest average rating""" sql = """ SELECT b.id, b.name, b.logo_url, AVG(r.rating) as avg_rating, COUNT(r.id) as review_count FROM businesses b JOIN reviews r ON b.id = r.business_id WHERE b.is_active = 1 GROUP BY b.id HAVING review_count >= 3 ORDER BY avg_rating DESC LIMIT ? """ return db.query(sql, [limit]) <!-- Display category list --> <div class="categories"> <h2>Browse by Category</h2> <ul> <?php foreach($categories as $cat): ?> <li><a href="category.php?id=<?= $cat['id'] ?>"><?= htmlspecialchars($cat['name']) ?></a></li> <?php endforeach; ?> </ul> </div> <!-- Search form --> <form method="GET" action="search.php"> <input type="text" name="q" placeholder="e.g., plumber, cafe, dentist" required> <input type="text" name="location" placeholder="City or ZIP"> <button type="submit">Search</button> </form> business directory script
CREATE TABLE reviews ( id INT PRIMARY KEY AUTO_INCREMENT, business_id INT NOT NULL, rating INT CHECK (rating BETWEEN 1 AND 5), comment TEXT, reviewer_name VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE ); # Example Python/Flask-style logic (read as pseudo-code) class BusinessDirectory: IN NATURAL LANGUAGE MODE) as relevance FROM businesses
def get_businesses_by_category(self, category_id, limit=20, offset=0): """Return paginated businesses in a category""" sql = """ SELECT b.*, c.name as category_name, AVG(r.rating) as avg_rating FROM businesses b JOIN categories c ON b.category_id = c.id LEFT JOIN reviews r ON b.id = r.business_id WHERE b.category_id = ? AND b.is_active = 1 GROUP BY b.id ORDER BY b.name ASC LIMIT ? OFFSET ? """ return db.query(sql, [category_id, limit, offset]) params) def add_review(self
def search_businesses(self, keyword, location=None): """Search by name, description, or address""" sql = """ SELECT *, MATCH(name, description) AGAINST(? IN NATURAL LANGUAGE MODE) as relevance FROM businesses WHERE is_active = 1 AND (name LIKE ? OR description LIKE ? OR address LIKE ?) """ like_term = f"%{keyword}%" params = [keyword, like_term, like_term, like_term] if location: sql += " AND address LIKE ?" params.append(f"%{location}%") sql += " ORDER BY relevance DESC" return db.query(sql, params)
def add_review(self, business_id, rating, comment, reviewer_name): """Submit a review and rating""" if rating < 1 or rating > 5: return False sql = "INSERT INTO reviews (business_id, rating, comment, reviewer_name) VALUES (?,?,?,?)" return db.execute(sql, [business_id, rating, comment, reviewer_name])
def add_business(self, name, category_id, description, address, phone, email, website): """Insert a new business listing""" if not name or not category_id: raise ValueError("Name and category are required") sql = "INSERT INTO businesses (name, category_id, description, address, phone, email, website) VALUES (?,?,?,?,?,?,?)" return db.execute(sql, [name, category_id, description, address, phone, email, website])