Reviews.
Upload a location → it pins to the map + feed. Rate any organization — restaurant, tech, shop, anything. Sort by category, stars, complaints.
Organization name, category, location, stars, review. It pins to the map + feed instantly (mock).
Complaint = “I had a problem.” Not a 1-star. A place can have 4 stars and 40 complaints — that tells you something different. File one with the button on each card.
⬇ Backend — Reviews Backend — Template
Right now Reviews is local-only — feed is a hardcoded let reviews = [...] in JS, addReview() just unshifts a card, pins are placed by hashPos(name) fake math, fileComplaint() just does reviews[i].complaints++ in the browser, and pin clicks are alert(). This backend is the map to make it real: places + reviews live on the server, stars are one-per-user, complaints are stored separately from stars, map pins come from real lat/lng. Proposed, not connected.
01 / Files to create
api/reviews/feed.php GET /api/reviews/feed?cat=all&sort=starsDesc api/reviews/add.php POST /api/reviews/add (auth required) api/reviews/rate.php POST /api/reviews/rate api/reviews/complaint.php POST /api/reviews/complaint api/reviews/top.php GET /api/reviews/top sql/reviews_tables.sql
Keep the tinyfiles PHP pattern: each endpoint loads /api/bootstrap.php, uses json_in() / json_out(), prepared PDO statements.
02 / Database tables
-- sql/reviews_tables.sql
review_places
id INT AUTO_INCREMENT PRIMARY KEY
name VARCHAR(150) NOT NULL -- "Joe's Pizza"
cat ENUM('Restaurant','Tech','Retail','Healthcare','Education','Other') NOT NULL
location VARCHAR(150) NOT NULL -- "Westtown, PA"
lat DECIMAL(10,7) NULL -- real pin, replaces hashPos()
lng DECIMAL(10,7) NULL
created_by INT NOT NULL -- FK -> users.id
created_at DATETIME NOT NULL
UNIQUE(name, location) -- one place row, many reviews
review_reviews
id INT AUTO_INCREMENT PRIMARY KEY
place_id INT NOT NULL -- FK -> review_places.id
user_id INT NOT NULL
stars TINYINT NOT NULL -- 1-5
body TEXT NOT NULL -- one-line review
created_at DATETIME NOT NULL
UNIQUE(place_id, user_id) -- one review per user per place
review_complaints
id INT AUTO_INCREMENT PRIMARY KEY
place_id INT NOT NULL
user_id INT NOT NULL
body TEXT NULL -- optional "what happened"
created_at DATETIME NOT NULL
UNIQUE(place_id, user_id) -- one complaint per user per place
Stars and complaints are separate tables on purpose — that’s the page’s rule: “Complaint = ‘I had a problem.’ Not a 1-star. A place can have 4 stars and 40 complaints.” Never fold complaints into the star average.
03 / API endpoints & request flow
GET /api/reviews/feed?cat=all&sort=starsDesc
- returns { "ok": true, "reviews": [ { id, name, cat, location,
lat, lng, stars, complaints, author, body, created_at }, ... ] }
- cat in [all, Restaurant, Tech, Retail, Healthcare, Education, Other]
- sort in [starsDesc, starsAsc, complaintsDesc, complaintsAsc, newest]
- stars = AVG(review_reviews.stars) for that place, 1 decimal
- complaints = COUNT(review_complaints) for that place
POST /api/reviews/add (auth required)
{ "name": "Joe's Pizza", "cat": "Restaurant",
"location": "Westtown, PA", "stars": 4, "body": "..." }
- validates cat allow-list, name/location 1-150 chars, stars 1-5, body 1-500
- finds or creates review_places row, then INSERT review_reviews
- returns { "ok": true, "place_id": N }
POST /api/reviews/rate
{ "place_id": 12, "stars": 4, "body": "..." }
- auth required, INSERT ... ON DUPLICATE KEY UPDATE (re-review = update)
POST /api/reviews/complaint
{ "place_id": 12, "body": "Waited 2 hours" }
- auth required, one complaint per user per place
- returns { "ok": true, "complaints": 43 } — new count
GET /api/reviews/top
- returns { "ok": true, "topRated": [...3], "mostComplained": [...3] }
- same math as feed, used for the two sidebar boxes
Feed / map / sidebar all read from the same three tables — no second source of truth. Map pins use lat/lng from review_places, not hash math.
04 / Geocoding, moderation & validation (server owns this)
// Geocoding — server only, never the browser: // on add, server geocodes "Westtown, PA" -> lat/lng via a server-side // geocoder, stores it in review_places. If geocode fails, lat/lng = NULL // and the place still lists in the feed, just without a pin. // Moderation: // reviews start live, but a place with 3+ complaints gets flagged // for admin review — it does NOT auto-hide. Admin decides. // body is plain text — escape on render, never store HTML. // Validation: // name / location: 1-150 chars, trimmed, no URLs in body // stars: integer 1-5 only, body: 1-500 chars // rate-limit: max 10 reviews / 10 complaints per user per hour
Keep the page’s rule: “Rate any organization — restaurant, tech, shop, anything. Sort by category, stars, complaints.” The backend enforces the one-review / one-complaint part of that.
05 / Frontend wiring
// Replace the hardcoded let reviews = [...] in this page:
let reviews = []; // filled from server, same shape as now
async function loadReviews(){
const url = '/api/reviews/feed?cat=' + curCat + '&sort=' + curSort;
const res = await fetch(url);
const data = await res.json();
// data.reviews -> renderAll(), same .rev-card + .pin HTML as now
// pin position: x = (lng - minLng) / span, y = (lat - maxLat) / span
// — or keep the CSS map as a fake map until a real map is wired
}
// Replace addReview():
async function addReview(){
const res = await fetch('/api/reviews/add', { method:'POST',
headers:{'Content-Type':'application/json'},
body: JSON.stringify({ name, cat, location, stars: upStars, body }) });
// on ok: loadReviews(), show "Pinned!" — same toast as now
}
// Replace fileComplaint(i):
async function fileComplaint(placeId){
const res = await fetch('/api/reviews/complaint', { method:'POST',
headers:{'Content-Type':'application/json'},
body: JSON.stringify({ place_id: placeId }) });
// update complaint count from response, same UI as now
}
// Replace setCat / setSort:
// setCat / setSort -> loadReviews(), keep .pill active logic
// On load: loadReviews() + fetch('/api/reviews/top') for sidebar
// If API fails, fall back to current hardcoded reviews + sidebar.
Keep the current hardcoded reviews + fake map playable with no backend — if /api/reviews/feed fails, use the in-page list as the fallback.
api/reviews/ + sql/reviews_tables.sql — no GitHub needed. The <pre> blocks are the backend spec: server-owned places + one-review-per-user + complaints kept separate from stars + server geocoding, same layout, placeholders stay until wired.