PHP + MySQL Conventions

Project-level conventions for a PHP + MySQL codebase, applying Database Design Basics@markdowners/database-design-basics's rules within MySQL's specific defaults and PHP's request lifecycle.

Database access

• Always use prepared statements (PDO or mysqli, parameterized) for every query that includes any variable input — string-concatenated SQL is the direct cause of SQL injection and has no legitimate justification in application code.
• Set PDO's error mode to exceptions explicitly — the default silent/warning mode lets a failed query continue executing as if it succeeded, corrupting downstream logic.
• Use a connection charset of utf8mb4 (not utf8, which in MySQL is a 3-byte-max legacy alias that cannot store full Unicode, including emoji and many CJK characters) — set it on the connection itself, not just the schema, or client-side mangling can still occur.

MySQL-specific schema choices

• Prefer InnoDB for every table (the default in modern MySQL, but verify) — it's the only mainstream engine with real foreign key enforcement, row-level locking, and transaction support; legacy non-transactional engines should not be used for new tables.
• Use DATETIME with an explicit application-level UTC convention, or TIMESTAMP only within its valid range and DST-handling caveats — be deliberate about which one and document the choice, since MySQL's two time types behave differently around timezones.
• Use a utf8mb4-compatible case-insensitive collation consistently for text comparison and sorting — an inconsistent collation across tables/columns causes join and comparison behavior that's inconsistent in confusing ways.

PHP request lifecycle

• Never assume in-memory state (a global, a static property) persists between requests — PHP's default shared-nothing, per-request lifecycle means anything that must persist belongs in the database, a cache (Redis/Memcached), or the session, not a process-lifetime variable.
• Close/release long-running resources (file handles, DB connections held open with an unusual persistent-connection configuration) explicitly at the end of a request rather than assuming garbage collection alone handles it promptly under load.

Sessions and config

• Store PHP session data server-side (default file-based sessions are fine for a single server; use a shared store like Redis once running more than one app server) — never rely on sticky sessions/single-server assumptions as your only session strategy once you might scale horizontally.
• Load configuration and secrets via environment variables or a dedicated config file outside the web root, never a PHP file with hardcoded credentials committed to the repo, and never a config file placed anywhere directly web-accessible.

Error handling

• Set display_errors = Off in any production php.ini — a raw PHP error/warning rendered directly into an HTTP response can leak file paths, query fragments, or stack traces to any visitor. Log errors server-side instead, per general error-handling practice.