Update this timeline entry

Added quiz questions to database

Source: Amy and ChatGPT #1356

Tap the circle to mark the correct option.
2) question_submit.php 4) { fail(422, 'Pick the correct option (1–4).'); } // Store answer as the correct option number (simple + robust) // Alternative: store the correct option text instead. $answer = (string)$correctChoice; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !preg_match('/^-?\d+(\.\d+)?$/', $num)) { fail(422, 'Numeric answer is required and must be a number.'); } $answer = $num; // options stay null } try { $pdo = new PDO($dsn, $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer) VALUES (:project_id, :source_id, :question_type, :question_text, :option_1, :option_2, :option_3, :option_4, :answer)"; $stmt = $pdo->prepare($sql); $stmt->execute([ ':project_id' => $projectId, ':source_id' => $sourceId, ':question_type' => $qType, ':question_text' => $qText, ':option_1' => $option1, ':option_2' => $option2, ':option_3' => $option3, ':option_4' => $option4, ':answer' => $answer, ]); $newId = (int)$pdo->lastInsertId(); } catch (Throwable $e) { fail(500, 'Database insert failed.'); } // Basic success UI ?> Saved

Saved ✅

Question ID:

Add another question

The one design decision you should make now (or you’ll hate yourself later) Right now, the multiple-choice answer is stored as 1–4 (the correct option index). That’s solid because: You can change the option text later without breaking the “correct answer”. It’s fast to grade. If you’d rather store the actual correct option text in answer, tell me and I’ll adjust the submit logic. If you paste your actual projects table schema (column names), I’ll tailor the dropdown query and display label exactly to your DB. You said: I have a separate php file that holds the database login. I replaced your CONFIG section with "include '../run_code/database.php';" ChatGPT said: Good. That’s the right move—keep credentials out of random pages. But you can’t just drop in include '../run_code/database.php'; unless that file actually creates a $pdo connection (or at least exposes the variables needed to create one). Here are the clean, no-drama patterns. Pick one and stick to it. Pattern A (best): database.php returns a PDO instance ../run_code/database.php PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]); return $pdo; } Then in both pages Replace the whole config + connection block with: PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]); Then in both pages require_once __DIR__ . '/../run_code/database.php'; // $pdo is now defined This is fine, just a bit more “global-variable-y”. Important: your include path is probably wrong in production You wrote: include '../run_code/database.php'; That depends on the current working directory, which changes depending on how PHP is executed. Use this instead: require_once __DIR__ . '/../run_code/database.php'; __DIR__ is the directory of the current script, so it doesn’t break randomly. Also: use require_once, not include, because if your DB file is missing, you want a hard fail, not a page limping along like a zombie. Minimal edits to my two files In question_form.php Replace the config/DSN/PDO try/catch connection setup with: require_once __DIR__ . '/../run_code/database.php'; $pdo = db(); // if using Pattern A (or omit db() if Pattern B and $pdo exists already) Then keep this: $projects = $pdo->query("SELECT project_id, project_name FROM projects ORDER BY project_name ASC")->fetchAll(); In question_submit.php Replace the config/DSN/PDO creation with: require_once __DIR__ . '/../run_code/database.php'; $pdo = db(); // Pattern A and delete the duplicate $dsn/$dbUser/$dbPass stuff. One more thing (security reality check) If Chronology is “private” but accessible on the web, add a hard gate before the form loads and before inserts happen (session login check). Otherwise “private” is just vibes. If you show me what your database.php currently contains (just the structure—redact credentials), I’ll tell you exactly which pattern you’re closest to and give you the exact line edits for each page. You said: I don't use $pdo. I use $connection. Here is what it returns: $connection = mysqli_connect($db_server, $db_user, $db_password, $db_name); if($connection){ } else { echo "Could not connect to the {$db_name} database.?"; }; ChatGPT said: Got it. You’re on mysqli, not PDO. That’s fine—just means we need to stop pretending $pdo exists and wire this cleanly around $connection. I’ll be blunt: your current database.php works, but it’s bare-bones and a little sloppy. It connects, but it doesn’t fail hard, doesn’t set charset, and encourages SQL injection if you’re not disciplined. We’ll fix that without changing your architecture. Below is the correct way to adapt the two files to your $connection pattern. 1️⃣ Fix database.php (do this once) Keep mysqli, but make it safer and predictable. 4) { fail(422, 'Invalid correct answer'); } $answer = (string)$correct; } // Numeric elseif ($type === 'numeric') { $num = trim($_POST['numeric_answer'] ?? ''); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer required'); } $answer = $num; } else { fail(422, 'Invalid question type'); } // Prepare insert $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) { fail(500, 'Prepare failed'); } mysqli_stmt_bind_param( $stmt, 'iisssssss', $projectId, $sourceId, $type, $qText, $option1, $option2, $option3, $option4, $answer ); if (!mysqli_stmt_execute($stmt)) { fail(500, 'Insert failed'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); 4️⃣ Why this setup is solid (no fluff) ✔ Uses prepared statements (no SQL injection) ✔ Keeps your existing $connection model ✔ Mobile-friendly inputs already handled ✔ No dependency on PDO ✔ Safe failure behavior ✔ Works cleanly with your Chronology private tooling One opinionated improvement you should seriously consider You’re storing the correct answer as 1–4 for multiple choice. That’s the right call for grading speed and flexibility. But document that decision in a comment or schema note now—future you will forget and curse past you. If you want next steps, I can: Add inline question preview Add AJAX submit (no page reload) Add bulk question entry Normalize answers into a separate table (if this grows big) Just say the word. You said: I don't know what exactly to replace. Rewrite question_form.php (mysqli version) and question_submit.php (mysqli + prepared statements) in full. ChatGPT said: Here you go—full working files, mysqli-based, using your $connection include, mobile-friendly, and prepared statements on insert. Assumptions: Your DB include is at ../run_code/database.php and it defines $connection. Your projects table is projects(project_id, project_name). Change that query if yours differs. Your questions table is quiz_questions with columns: question_id (AI), project_id, source_id, question_type, question_text, option_1..option_4, answer. question_form.php (FULL) Add Quiz Question

Add Quiz Question Chronology

Heads up: source_id is 0/missing. This form will still render, but submit will fail unless source_id is posted in.
Multiple choice shows 4 options. Numeric uses a phone-friendly number input.
Tap the circle to mark which option is correct. Stored as answer = 1–4.
question_submit.php (FULL) 4) { fail(422, 'Correct choice must be 1–4.'); } // Store the correct option index (1–4) $answer = (string)$correct; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer is required.'); } $answer = $num; // leave options null $option1 = $option2 = $option3 = $option4 = null; } // INSERT with prepared statement $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) { fail(500, 'Prepare failed.'); } // Bind types: // i = int, s = string // option_* are bound as strings; if null, mysqli will send NULL if you use mysqli_stmt_bind_param? No—mysqli binds by reference as strings. // Easiest: convert nulls to NULL via set to null and then use mysqli_stmt_send_long_data? Overkill. // Practical approach: store '' for options when not used, OR make them nullable and pass null via "s" still ends up empty string. // We'll pass empty strings for unused options so it’s consistent. $o1 = $option1 ?? ''; $o2 = $option2 ?? ''; $o3 = $option3 ?? ''; $o4 = $option4 ?? ''; mysqli_stmt_bind_param( $stmt, 'iisssssss', $projectId, $sourceId, $type, $qText, $o1, $o2, $o3, $o4, $answer ); if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail(500, 'Insert failed.'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); ?> Saved

Saved ✅

Question ID:

Add another question

One thing you should decide (because it affects grading later) Right now: Multiple choice stores answer = "1".."4" (the correct option index). Numeric stores answer = "1492" etc. That’s sane. If you’d rather store the actual option text instead of the index, say so and I’ll switch it (but indexes are cleaner). If your projects table doesn’t have project_name, tell me your real column names and I’ll adjust the dropdown query. You said: The projects table has project_title rather than project_name. Multiple choice questions need stored textual answers. ChatGPT said: Done. Two full files again, with your two changes: Projects dropdown uses project_title (not project_name). Multiple-choice stores the textual answer (the actual option string), not 1–4. I’m keeping your $connection mysqli include. question_form.php (FULL, project_title, MC stores text) Add Quiz Question

Add Quiz Question Chronology

Heads up: source_id is 0/missing. Submit will fail unless source_id is posted in.
Multiple choice shows 4 options. Numeric uses a phone-friendly number input.
Tap the circle to mark the correct option. Stored as the actual option text in answer.
question_submit.php (FULL, MC answer stored as option text) 4) { fail(422, 'Correct choice must be 1–4.'); } // Store the actual option text in answer $options = [ 1 => $option1, 2 => $option2, 3 => $option3, 4 => $option4, ]; $answer = $options[$correct]; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer is required.'); } $answer = $num; // Clear options for numeric questions $option1 = $option2 = $option3 = $option4 = ''; } // INSERT with prepared statement $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) { fail(500, 'Prepare failed.'); } // Bind types: i i s s s s s s s mysqli_stmt_bind_param( $stmt, 'iisssssss', $projectId, $sourceId, $type, $qText, $option1, $option2, $option3, $option4, $answer ); if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail(500, 'Insert failed.'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); ?> Saved

Saved ✅

Question ID:

Add another question

One gotcha you should not ignore If you ever edit option text later, storing the answer as text can drift out of sync. If you plan to allow editing, the more robust approach is: store answer_key as 1–4 (or a FK), and store option text, display option text, grade by key. But if your workflow is “create once, never edit,” storing the answer text is fine. If you want, I can tweak this so it stores both (answer_key + answer_text) without changing your form UX. You said: yes ChatGPT said: Alright — we’ll store both: answer_key = 1–4 for multiple choice (fast + stable for grading, survives option text edits) answer_text = the actual option text (nice for reporting / exports) For numeric: answer_key = NULL and answer_text = numeric value This means your table needs two columns instead of one answer. 1) Update your table (one-time SQL) If your table currently has answer only, do this: ALTER TABLE quiz_questions ADD COLUMN answer_key TINYINT UNSIGNED NULL AFTER option_4, ADD COLUMN answer_text VARCHAR(255) NOT NULL AFTER answer_key; -- Optional: if you already have answer and want to keep it temporarily: -- ALTER TABLE quiz_questions ADD COLUMN answer_text VARCHAR(255) NOT NULL AFTER answer_key; -- Then migrate and later drop old answer. If you want to ditch the old answer column after migrating, you can: ALTER TABLE quiz_questions DROP COLUMN answer; (Only do that once you’re sure nothing else depends on it.) 2) question_form.php (same UX, but now “stores both”) Form doesn’t need new fields — we already capture correct choice + options. Here’s the full file again with your project_title setup (unchanged from last version): Add Quiz Question

Add Quiz Question Chronology

Heads up: source_id is 0/missing. Submit will fail unless source_id is posted in.
For multiple choice we store both answer_key (1–4) and answer_text (option text).
3) question_submit.php (stores answer_key + answer_text) 4) { fail(422, 'Correct choice must be 1–4.'); } $answerKey = $correct; $options = [ 1 => $option1, 2 => $option2, 3 => $option3, 4 => $option4, ]; $answerText = $options[$correct]; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer is required.'); } $answerKey = null; $answerText = $num; // Clear options for numeric questions $option1 = $option2 = $option3 = $option4 = ''; } // INSERT with prepared statement // NOTE: answer_key is nullable; binding null with mysqli is annoying. // We handle it by having two SQL paths: one with answer_key, one with NULL literal. if ($answerKey === null) { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); // 8 params + answer_text = 9 params mysqli_stmt_bind_param( $stmt, 'iisssssss', $projectId, $sourceId, $type, $qText, $option1, $option2, $option3, $option4, $answerText ); } else { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param( $stmt, 'iissssssiss', $projectId, $sourceId, $type, $qText, $option1, $option2, $option3, $option4, $answerKey, // int $answerText // string ); } if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail(500, 'Insert failed.'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); ?> Saved

Saved ✅

Question ID:

Add another question

Quick sanity checklist (so this doesn’t mysteriously “not work”) Your table must have: answer_key (nullable) and answer_text (not null). Your include must set $connection and charset should be utf8mb4. Your projects table must have project_title. If you paste your exact quiz_questions schema (SHOW CREATE TABLE), I’ll align types/lengths so you don’t get silent truncation (especially answer_text). You said: how can I copy the schema from phpMyAdmin? ChatGPT said: Straight answer: phpMyAdmin makes this easy, but it hides it in two different places depending on what you want. Here’s the clean, no-nonsense way. Option 1 (best): Copy the exact schema SQL (SHOW CREATE TABLE) This gives you the authoritative truth — column types, indexes, engine, charset, everything. Steps Open phpMyAdmin Click your database Click the table (e.g. quiz_questions) Click the “SQL” tab Paste this and run it: SHOW CREATE TABLE quiz_questions; phpMyAdmin will return one row with a big SQL block Copy everything inside the CREATE TABLE ... statement That’s the schema I want if you paste it here. ✅ This is what devs mean by “the schema.” Option 2: Copy schema via Export (good for sharing) If you want the table definition without data: Click your table Click Export Choose: Export method: Quick or Custom Format: SQL If Custom, make sure: ✅ Structure ❌ Data Click Export Open the downloaded .sql file and copy the CREATE TABLE block This works, but includes extra comments you may not care about. Option 3: Visual copy (least useful, but human-readable) Click the table Click Structure Scroll and visually inspect columns, indexes, engine, collation Good for eyeballing. Bad for accuracy. Don’t use this for migrations. Which one you should use (no hedging) For debugging / alignment → Option 1 (SHOW CREATE TABLE) For backup / migration → Option 2 For curiosity only → Option 3 Pro tip (save future-you time) When you paste it here, paste only the CREATE TABLE block, not the phpMyAdmin comments. Once you do, I’ll: sanity-check column types tighten anything sloppy make sure your quiz logic won’t break later Paste it when ready. You said: 127.0.0.1:3306/u130990525_chronology_amy/quiz_questions/ https://auth-db1566.hstgr.io/index.php?route=/table/sql&db=u130990525_chronology_amy&table=quiz_questions Your SQL query has been executed successfully. SHOW CREATE TABLE quiz_questions; quiz_questions CREATE TABLE quiz_questions ( question_id in... ChatGPT said: You’re almost there — phpMyAdmin is just truncating the display, which is why you’re seeing: quiz_questions CREATE TABLE quiz_questions ( question_id in... That’s normal. You didn’t do anything wrong. Here’s how to get the full, copyable schema instead of the chopped preview. The missing step: expand the result After you run: SHOW CREATE TABLE quiz_questions; phpMyAdmin shows the result in a grid with two columns: Table Create Table The Create Table column is collapsed by default. Do this 👇 Look at the results row In the Create Table column: Click inside the cell OR click the small “Copy” icon (📋) at the right of the cell OR double-click the cell to expand it (depends on theme) phpMyAdmin will then show the entire CREATE TABLE statement, usually in a popup or expanded view. If the UI still truncates it (Hostinger sometimes does) Use this bulletproof workaround: 1️⃣ Re-run the command SHOW CREATE TABLE quiz_questions\G Notice the \G instead of ; 2️⃣ phpMyAdmin will display the result vertically, like: *************************** 1. row *************************** Table: quiz_questions Create Table: CREATE TABLE `quiz_questions` ( `question_id` int unsigned NOT NULL AUTO_INCREMENT, ... ) This version is never truncated. 👉 Now you can select and copy the entire CREATE TABLE block cleanly. What I want you to paste here Paste only this part (no URLs, no headers): CREATE TABLE `quiz_questions` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; That’s it. Why this matters (straight talk) Until I see the full schema, I can’t: verify answer_key is nullable confirm column lengths won’t truncate answers ensure charset/collation won’t mangle text guarantee grading logic won’t silently fail Once you paste it, I’ll: audit it line by line fix anything fragile give you the final, future-proof version Go ahead — grab it with \G and paste it. You said: Unexpected character. (near "\" at position 32) ChatGPT said: Yep — that error tells us exactly what’s happening, and it’s not your fault. phpMyAdmin does NOT support the MySQL CLI-style \G. That works in the terminal, not in phpMyAdmin’s SQL box. So phpMyAdmin is choking on the backslash. Let’s do this the phpMyAdmin-native way. Correct way in phpMyAdmin (guaranteed to work) Step-by-step (no tricks) In phpMyAdmin, click your database Click the quiz_questions table Click the Structure tab At the top-right of the Structure view, click “Export” (or click Export from the top menu while the table is selected) Export settings Export method: Custom Format: SQL Scroll down and set: ✅ Structure ❌ Data ❌ DROP TABLE / DROP VIEW ❌ IF NOT EXISTS (optional, but cleaner) Then click Export. What you’ll get A downloaded .sql file that starts with comments like: -- phpMyAdmin SQL Dump -- version 5.x Scroll down until you see: CREATE TABLE `quiz_questions` ( `question_id` int(11) NOT NULL AUTO_INCREMENT, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; What to paste here Paste only this part: CREATE TABLE `quiz_questions` ( ... ); No comments. No URLs. No phpMyAdmin headers. Why phpMyAdmin behaves this way (quick reality check) \G is a mysql client formatter, not SQL phpMyAdmin runs SQL in a web context and rejects it Hostinger’s phpMyAdmin also aggressively truncates grid output So exporting the structure is the cleanest escape hatch. Once you paste it… I’ll: verify column types and lengths confirm answer_key nullability adjust your PHP insert logic if needed lock this schema so you don’t revisit it later You’re one copy-paste away from being done. You said: CREATE TABLE quiz_questions ( question_id int(10) UNSIGNED NOT NULL, project_id int(10) UNSIGNED NOT NULL, source_id int(10) UNSIGNED NOT NULL, question_type enum('multiple_choice','numeric') NOT NULL, question_text text NOT NULL, option_1 varchar(255) DEFAULT NULL, option_2 varchar(255) DEFAULT NULL, option_3 varchar(255) DEFAULT NULL, option_4 varchar(255) DEFAULT NULL, answer_key tinyint(3) UNSIGNED DEFAULT NULL, answer_text varchar(255) NOT NULL, created_at timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table quiz_questions -- ALTER TABLE quiz_questions ADD PRIMARY KEY (question_id), ADD KEY project_id (project_id), ADD KEY source_id (source_id); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table quiz_questions -- ALTER TABLE quiz_questions MODIFY question_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ChatGPT said: Perfect — that schema matches the “store both” plan, and it’s compatible with the PHP I gave you. Two tweaks I recommend (because they’ll save you pain later), then I’ll give you an updated question_submit.php that binds NULL cleanly and doesn’t need two separate SQL strings. What your schema says (and why it’s good) answer_key is TINYINT UNSIGNED NULL ✅ (so numeric questions can leave it NULL) answer_text is VARCHAR(255) NOT NULL ✅ (so every question always has an answer value) options are nullable ✅ Only missing piece: question_id should ideally be declared AUTO_INCREMENT directly in the CREATE TABLE, but your ALTER fixes it, so you’re fine. Recommended schema polish (optional but smart) 1) Add a guard so answer_key can only be 1–4 (MySQL 8+) If your MySQL supports CHECK constraints: ALTER TABLE quiz_questions ADD CONSTRAINT chk_answer_key_range CHECK (answer_key IS NULL OR (answer_key BETWEEN 1 AND 4)); 2) Make answer_text TEXT if you ever expect longer answers Right now it’s capped at 255 chars. That’s fine for MC and most numerics, but if you ever do “short answer” later you’ll hit a wall. Updated question_submit.php (FULL) This version: Stores both answer_key + answer_text Uses one INSERT query Correctly sends NULL for answer_key using mysqli_stmt_bind_param + mysqli_stmt_send_long_data is overkill, so we do the normal mysqli null-binding trick: bind as int and set it to NULL does not work reliably across builds, so we keep the two-query approach but tighten it and add helpful debugging without leaking details. Given Hostinger/shared hosting variability, the two-path insert is the most reliable. 4) { fail(422, 'Correct choice must be 1–4.'); } $answerKey = $correct; $options = [ 1 => $option1, 2 => $option2, 3 => $option3, 4 => $option4, ]; $answerText = $options[$correct]; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer is required.'); } $answerKey = null; $answerText = $num; // For numeric, store options as NULLs $option1 = $option2 = $option3 = $option4 = null; } // Normalize NULL option handling: mysqli bind_param won't send SQL NULL unless you use "s" and pass null // In practice, many builds convert null to empty string. That’s acceptable if your app doesn’t care. // If you *do* care, keep them as null and accept that they may store "" depending on environment. $o1 = $option1; $o2 = $option2; $o3 = $option3; $o4 = $option4; // INSERT — two paths to reliably set answer_key as NULL vs INT if ($answerKey === null) { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param( $stmt, 'iisssssss', $projectId, $sourceId, $type, $qText, $o1, $o2, $o3, $o4, $answerText ); } else { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param( $stmt, 'iissssssiss', $projectId, $sourceId, $type, $qText, $o1, $o2, $o3, $o4, $answerKey, $answerText ); } if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail(500, 'Insert failed.'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); ?> Saved

Saved ✅

Question ID:

Add another question

One last blunt note (important) Your schema makes answer_text NOT NULL, which is good. But if you ever add a question type like “free text” or “multi-answer”, VARCHAR(255) will start feeling cramped fast. You can leave it for now, just don’t be shocked later. If you want, I can also rewrite your quiz grading query to grade by answer_key when present, otherwise by numeric comparison (with tolerance rules if you need them). You said: There is no data in the 'quiz_questions' table. Rewrite it for optimum cleanliness without the alter statements ChatGPT said: Here’s a clean, single-shot CREATE TABLE for your quiz_questions table—no ALTERs, no junk, and it matches your requirements (multiple choice + numeric, store both answer_key + answer_text, projects + sources indexed). CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` INT(10) UNSIGNED NOT NULL, `source_id` INT(10) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, -- For multiple_choice: 1-4. For numeric: NULL. `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, -- Always populated: for multiple_choice it's the option text; for numeric it's the numeric value as text. `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_project_id` (`project_id`), KEY `idx_source_id` (`source_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; If you want “harder to mess up” (optional) If your MySQL version supports CHECK constraints (many shared hosts don’t enforce them reliably), you can add this later: answer_key must be NULL or 1–4 numeric questions must have NULL answer_key But the table above is the clean baseline and will work everywhere. If you also want foreign keys to projects(project_id) and sources(source_id), tell me your exact sources table name + key column and I’ll add them cleanly too. You said: rewrite the 'quiz_questions' table with the CHECK constraints you propose and the foreign keys to projects.project_id and sources.source_id ChatGPT said: Thought for 8s Here’s a clean single-shot CREATE TABLE with: Foreign keys to projects(project_id) and sources(source_id) CHECK constraints enforcing sane rules: answer_key is NULL or 1–4 If question_type = 'multiple_choice': answer_key must be 1–4 option_1..option_4 must be present and non-empty answer_text must be one of the four options If question_type = 'numeric': answer_key must be NULL option_1..option_4 must be NULL answer_text must be numeric-ish (basic format check) Important compatibility note (no sugar-coating) MySQL 8.0.16+ enforces CHECK constraints. Older MySQL silently ignores them. MariaDB supports CHECK but behavior/versioning differs. If your host is on ancient MySQL, constraints won’t protect you. (Your PHP validation still will.) quiz_questions with FKs + CHECKs (single clean CREATE) CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` INT(10) UNSIGNED NOT NULL, `source_id` INT(10) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, -- multiple_choice: 1-4; numeric: NULL `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, -- Always populated: MC = option text, numeric = numeric value stored as text `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_quiz_questions_project_id` (`project_id`), KEY `idx_quiz_questions_source_id` (`source_id`), CONSTRAINT `fk_quiz_questions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `fk_quiz_questions_source` FOREIGN KEY (`source_id`) REFERENCES `sources` (`source_id`) ON UPDATE CASCADE ON DELETE RESTRICT, -- answer_key is either NULL or 1..4 CONSTRAINT `chk_answer_key_range` CHECK (`answer_key` IS NULL OR `answer_key` BETWEEN 1 AND 4), -- Rules for multiple_choice vs numeric CONSTRAINT `chk_question_type_rules` CHECK ( ( `question_type` = 'multiple_choice' AND `answer_key` BETWEEN 1 AND 4 AND `option_1` IS NOT NULL AND TRIM(`option_1`) <> '' AND `option_2` IS NOT NULL AND TRIM(`option_2`) <> '' AND `option_3` IS NOT NULL AND TRIM(`option_3`) <> '' AND `option_4` IS NOT NULL AND TRIM(`option_4`) <> '' AND TRIM(`answer_text`) <> '' AND `answer_text` IN (`option_1`, `option_2`, `option_3`, `option_4`) ) OR ( `question_type` = 'numeric' AND `answer_key` IS NULL AND `option_1` IS NULL AND `option_2` IS NULL AND `option_3` IS NULL AND `option_4` IS NULL AND TRIM(`answer_text`) <> '' -- basic numeric format: digits with optional leading -, optional decimal AND `answer_text` REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; About the FK delete behavior (pick your poison) I set ON DELETE RESTRICT because it prevents you from deleting a project/source that still has questions (data integrity). If you’d rather automatically delete dependent questions when a project/source is deleted, switch both to: ON DELETE CASCADE If your sources table name or PK column is not exactly sources(source_id), tell me what it actually is and I’ll adjust the FK lines precisely. You said: Static analysis: 3 errors were found during analysis. A symbol name was expected! A reserved keyword can not be used as a column name without backquotes. (near "CHECK" at position 1281) Unexpected beginning of statement. (near "answer_key" at position 1288) Unrecognized statement type. (near "IS" at position 1301) SQL query: Copy CREATE TABLE quiz_questions ( question_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, project_id INT(10) UNSIGNED NOT NULL, source_id INT(10) UNSIGNED NOT NULL, question_type ENUM('multiple_choice','numeric') NOT NULL, question_text TEXT NOT NULL, option_1 VARCHAR(255) DEFAULT NULL, option_2 VARCHAR(255) DEFAULT NULL, option_3 VARCHAR(255) DEFAULT NULL, option_4 VARCHAR(255) DEFAULT NULL, -- multiple_choice: 1-4; numeric: NULL answer_key TINYINT(3) UNSIGNED DEFAULT NULL, -- Always populated: MC = option text, numeric = numeric value stored as text answer_text VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (question_id), KEY idx_quiz_questions_project_id (project_id), KEY idx_quiz_questions_source_id (source_id), CONSTRAINT fk_quiz_questions_project FOREIGN KEY (project_id) REFERENCES projects (project_id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_quiz_questions_source FOREIGN KEY (source_id) REFERENCES sources (source_id) ON UPDATE CASCADE ON DELETE RESTRICT, -- answer_key is either NULL or 1..4 CONSTRAINT chk_answer_key_range CHECK (answer_key IS NULL OR answer_key BETWEEN 1 AND 4), -- Rules for multiple_choice vs numeric CONSTRAINT chk_question_type_rules CHECK ( ( question_type = 'multiple_choice' AND answer_key BETWEEN 1 AND 4 AND option_1 IS NOT NULL AND TRIM(option_1) <> '' AND option_2 IS NOT NULL AND TRIM(option_2) <> '' AND option_3 IS NOT NULL AND TRIM(option_3) <> '' AND option_4 IS NOT NULL AND TRIM(option_4) <> '' AND TRIM(answer_text) <> '' AND answer_text IN (option_1, option_2, option_3, option_4) ) OR ( question_type = 'numeric' AND answer_key IS NULL AND option_1 IS NULL AND option_2 IS NULL AND option_3 IS NULL AND option_4 IS NULL AND TRIM(answer_text) <> '' -- basic numeric format: digits with optional leading -, optional decimal AND answer_text REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; MySQL said: Documentation #1005 - Can't create table u130990525_chronology_amy.quiz_questions (errno: 150 "Foreign key constraint is incorrectly formed") (Details…) ChatGPT said: Thought for 16s You hit two separate problems: phpMyAdmin’s “static analysis” choked because you pasted -- comments mid-statement without proper newlines. That makes the parser (and sometimes MySQL) interpret the rest as garbage. MySQL error errno: 150 "Foreign key constraint is incorrectly formed" means your FK target columns don’t exactly match (type/signedness/index/engine/table name). Below is a clean CREATE TABLE with no inline -- comments (so it will actually parse), plus the FK + CHECK constraints. Then I’ll tell you exactly what to verify to fix errno 150. Clean single-shot CREATE TABLE (FKs + CHECKs, no ALTERs, no inline comments) CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` INT(10) UNSIGNED NOT NULL, `source_id` INT(10) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_quiz_questions_project_id` (`project_id`), KEY `idx_quiz_questions_source_id` (`source_id`), CONSTRAINT `fk_quiz_questions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `fk_quiz_questions_source` FOREIGN KEY (`source_id`) REFERENCES `sources` (`source_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `chk_answer_key_range` CHECK (`answer_key` IS NULL OR `answer_key` BETWEEN 1 AND 4), CONSTRAINT `chk_question_type_rules` CHECK ( ( `question_type` = 'multiple_choice' AND `answer_key` BETWEEN 1 AND 4 AND `option_1` IS NOT NULL AND TRIM(`option_1`) <> '' AND `option_2` IS NOT NULL AND TRIM(`option_2`) <> '' AND `option_3` IS NOT NULL AND TRIM(`option_3`) <> '' AND `option_4` IS NOT NULL AND TRIM(`option_4`) <> '' AND TRIM(`answer_text`) <> '' AND `answer_text` IN (`option_1`, `option_2`, `option_3`, `option_4`) ) OR ( `question_type` = 'numeric' AND `answer_key` IS NULL AND `option_1` IS NULL AND `option_2` IS NULL AND `option_3` IS NULL AND `option_4` IS NULL AND TRIM(`answer_text`) <> '' AND `answer_text` REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; If your server is older MySQL that ignores/enforces CHECK weirdly, the table may still create but constraints won’t be enforced. The FK error you got is the real blocker. Fixing the FK error (#1005 errno 150) — what’s actually wrong Foreign keys fail when any of these are off by even a hair: 1) The referenced tables/columns must exist exactly Make sure these exist: projects.project_id sources.source_id Run (in phpMyAdmin SQL tab): SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; 2) The referenced columns must be indexed (PRIMARY KEY or UNIQUE/INDEX) Typically project_id and source_id should be PRIMARY KEY on their tables. 3) Data types must match exactly This is the #1 cause. If your projects.project_id is INT(10) (signed) but your quiz_questions.project_id is INT(10) UNSIGNED, FK creation fails. You need same: INT size UNSIGNED vs signed NULL vs NOT NULL doesn’t need to match, but the referenced key must be NOT NULL if it’s PK. 4) Both tables must be InnoDB If projects or sources is MyISAM, FK fails. Check quickly: SHOW TABLE STATUS WHERE Name IN ('projects','sources','quiz_questions'); Look at the Engine column. The fastest way to get this done without guessing Paste the output of: SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; …and I’ll rewrite the quiz_questions CREATE TABLE so the FKs match your real definitions exactly (correct unsigned/signed, key names, etc.), and it will run cleanly the first time. Right now, you’re trying to FK to a shape you think those tables have. MySQL is telling you they don’t. You said: Error Static analysis: 3 errors were found during analysis. A symbol name was expected! A reserved keyword can not be used as a column name without backquotes. (near "CHECK" at position 1103) Unexpected beginning of statement. (near "answer_key" at position 1110) Unrecognized statement type. (near "IS" at position 1123) SQL query: Copy CREATE TABLE quiz_questions ( question_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, project_id INT(10) UNSIGNED NOT NULL, source_id INT(10) UNSIGNED NOT NULL, question_type ENUM('multiple_choice','numeric') NOT NULL, question_text TEXT NOT NULL, option_1 VARCHAR(255) DEFAULT NULL, option_2 VARCHAR(255) DEFAULT NULL, option_3 VARCHAR(255) DEFAULT NULL, option_4 VARCHAR(255) DEFAULT NULL, answer_key TINYINT(3) UNSIGNED DEFAULT NULL, answer_text VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (question_id), KEY idx_quiz_questions_project_id (project_id), KEY idx_quiz_questions_source_id (source_id), CONSTRAINT fk_quiz_questions_project FOREIGN KEY (project_id) REFERENCES projects (project_id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_quiz_questions_source FOREIGN KEY (source_id) REFERENCES sources (source_id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT chk_answer_key_range CHECK (answer_key IS NULL OR answer_key BETWEEN 1 AND 4), CONSTRAINT chk_question_type_rules CHECK ( ( question_type = 'multiple_choice' AND answer_key BETWEEN 1 AND 4 AND option_1 IS NOT NULL AND TRIM(option_1) <> '' AND option_2 IS NOT NULL AND TRIM(option_2) <> '' AND option_3 IS NOT NULL AND TRIM(option_3) <> '' AND option_4 IS NOT NULL AND TRIM(option_4) <> '' AND TRIM(answer_text) <> '' AND answer_text IN (option_1, option_2, option_3, option_4) ) OR ( question_type = 'numeric' AND answer_key IS NULL AND option_1 IS NULL AND option_2 IS NULL AND option_3 IS NULL AND option_4 IS NULL AND TRIM(answer_text) <> '' AND answer_text REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; MySQL said: Documentation #1005 - Can't create table u130990525_chronology_amy.quiz_questions (errno: 150 "Foreign key constraint is incorrectly formed") (Details…) ChatGPT said: You’re getting two different “errors” from two different layers, and phpMyAdmin is muddying the water. What’s actually going on The “Static analysis” CHECK errors are phpMyAdmin being dumb, not necessarily MySQL. phpMyAdmin’s parser often mis-flags valid SQL (especially with CHECK, REGEXP, TRIM, etc.). If MySQL truly rejected CHECK, you’d see a MySQL syntax error like #1064. You’re not. So ignore that part. Your real blocker is MySQL error #1005 errno 150: Foreign key constraint is incorrectly formed That has nothing to do with CHECK. It means your referenced columns don’t match exactly or aren’t eligible for FKs. So the fastest path is: Create the table WITHOUT foreign keys (and keep the CHECKs if you want), OR fix the FKs by matching types exactly. You asked for FKs, so here’s how to fix it without guessing. Step 1 — Find the exact problem (run this) In phpMyAdmin → SQL tab, run: SHOW ENGINE INNODB STATUS; Then search in the output for: LATEST FOREIGN KEY ERROR It will tell you exactly what mismatch is happening (unsigned vs signed, missing index, wrong collation, wrong engine, etc.). That’s the real diagnostic. Copy/paste that “LATEST FOREIGN KEY ERROR” section here and I’ll give you the exact corrected CREATE. Step 2 — Most common causes (90% of the time) A) Type mismatch: UNSIGNED vs signed Your table uses: project_id INT(10) UNSIGNED source_id INT(10) UNSIGNED If projects.project_id is INT(10) (signed) or BIGINT, the FK fails. B) Referenced column not indexed projects.project_id must be PRIMARY KEY or indexed (same for sources.source_id). C) Engine mismatch All tables must be InnoDB. Step 3 — While we diagnose, here’s a version that WILL create (CHECKs only, no FKs) This lets you move forward immediately and add FKs after we confirm types. CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` INT(10) UNSIGNED NOT NULL, `source_id` INT(10) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_quiz_questions_project_id` (`project_id`), KEY `idx_quiz_questions_source_id` (`source_id`), CONSTRAINT `chk_answer_key_range` CHECK (`answer_key` IS NULL OR `answer_key` BETWEEN 1 AND 4), CONSTRAINT `chk_question_type_rules` CHECK ( ( `question_type` = 'multiple_choice' AND `answer_key` BETWEEN 1 AND 4 AND `option_1` IS NOT NULL AND TRIM(`option_1`) <> '' AND `option_2` IS NOT NULL AND TRIM(`option_2`) <> '' AND `option_3` IS NOT NULL AND TRIM(`option_3`) <> '' AND `option_4` IS NOT NULL AND TRIM(`option_4`) <> '' AND TRIM(`answer_text`) <> '' AND `answer_text` IN (`option_1`, `option_2`, `option_3`, `option_4`) ) OR ( `question_type` = 'numeric' AND `answer_key` IS NULL AND `option_1` IS NULL AND `option_2` IS NULL AND `option_3` IS NULL AND `option_4` IS NULL AND TRIM(`answer_text`) <> '' AND `answer_text` REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; If this fails with a MySQL syntax error, then your server is old enough that CHECK isn’t supported—and we’ll drop CHECKs and rely on PHP validation. But your current error is FK-related, so this should create. Step 4 — To generate the correct FK version, I need one of these (pick the easier) Option A (best): paste these two outputs Run: SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; Paste the CREATE TABLE ... blocks here. Option B (fastest): paste the single InnoDB FK error detail Run: SHOW ENGINE INNODB STATUS; Paste only the LATEST FOREIGN KEY ERROR section. Why I’m not guessing your FK types Because Hostinger setups vary a lot. If your projects.project_id is BIGINT UNSIGNED, or INT signed, or the table is MyISAM, any “standard” FK script fails. The DB is literally telling you it doesn’t match. Send me the two SHOW CREATE TABLE outputs (or the LATEST FOREIGN KEY ERROR) and I’ll return the final FK+CHECK CREATE TABLE that runs cleanly on your exact schema. You said: Error SQL query: Copy SHOW ENGINE INNODB STATUS; MySQL said: Documentation #1227 - Access denied; you need (at least one of) the PROCESS privilege(s) for this operation ChatGPT said: Yep. Hostinger/shared MySQL often blocks SHOW ENGINE INNODB STATUS because it requires PROCESS privilege. So we’ll diagnose the FK issue the allowed way. You can fix this in minutes by checking three things in phpMyAdmin that don’t require special privileges: Do the referenced tables exist with those exact names? (projects, sources) Are the referenced columns indexed (PRIMARY KEY/UNIQUE)? Do the column types match exactly (including UNSIGNED)? 1) Run these two queries and paste the results In phpMyAdmin → SQL tab: SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; Paste the full CREATE TABLE ... output for each. That will tell us immediately whether: project_id and source_id are INT UNSIGNED or not they’re indexed engine is InnoDB table names are actually different (common: project, project_list, sources_amy, etc.) 2) If you don’t want to paste full CREATE TABLE, run these smaller checks A) Check column types (this is the big one) SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('projects','sources') AND COLUMN_NAME IN ('project_id','source_id'); You want to see something like: projects.project_id = int(10) unsigned sources.source_id = int(10) unsigned If either is signed int or bigint, your FK fails. B) Check indexes exist on those columns SHOW INDEX FROM projects; SHOW INDEX FROM sources; Look for project_id and source_id being PRIMARY (best) or at least indexed. C) Confirm both tables are InnoDB SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('projects','sources'); They must both be InnoDB. 3) The most common fix (and likely yours) Your quiz_questions uses INT(10) UNSIGNED for both IDs. If your parent tables are signed (INT(10) without UNSIGNED), you have two options: Option A (recommended): Make parent keys UNSIGNED too ALTER TABLE projects MODIFY project_id INT(10) UNSIGNED NOT NULL; ALTER TABLE sources MODIFY source_id INT(10) UNSIGNED NOT NULL; Option B: Make quiz_questions signed (match them) -- in the quiz_questions CREATE TABLE, use INT(10) (not UNSIGNED) `project_id` INT(10) NOT NULL, `source_id` INT(10) NOT NULL, Foreign keys are picky: they must match exactly. 4) Also: phpMyAdmin “static analysis” CHECK warnings Ignore them. They’re phpMyAdmin linting bugs. The real MySQL error you’re seeing is FK formation. Send me this and I’ll give you the final CREATE TABLE that runs cleanly Paste the output of: SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; and I’ll return the corrected CREATE TABLE quiz_questions with: working FKs your CHECK constraints matching types (no guesswork) You said: 127.0.0.1:3306/u130990525_chronology_amy/ https://auth-db1566.hstgr.io/index.php?route=/database/sql&db=u130990525_chronology_amy Your SQL query has been executed successfully. Your SQL query has been executed successfully. SHOW CREATE TABLE projects; SHOW CREATE TABLE sources; projects CREATE TABLE projects ( date_created datetim... sources CREATE TABLE sources ( date_created datetime... ChatGPT said: phpMyAdmin is truncating the output again, so we still don’t know the actual definitions of project_id and source_id—which is exactly what we need to fix the foreign keys. Since you can’t get the full SHOW CREATE TABLE to display cleanly in that grid, here are three ways that always work on Hostinger phpMyAdmin. Method 1 (best): Export structure for each table (no truncation) Do this twice: once for projects, once for sources. Click the table projects Click Export Choose: Export method: Custom Format: SQL Under “Output” (or similar): ✅ Structure ❌ Data Export and open the downloaded file Copy/paste here just the CREATE TABLE \projects`` block Repeat for sources. This is the same method you used successfully for quiz_questions. Method 2: Use information_schema to extract the exact column types (fast) Run this (it will not truncate): SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND ( (TABLE_NAME='projects' AND COLUMN_NAME='project_id') OR (TABLE_NAME='sources' AND COLUMN_NAME='source_id') ); Paste the output. With that one row per column, I can make your FK constraints perfect. Method 3: Get engine + key info (often the real culprit) Run these: SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('projects','sources'); SHOW INDEX FROM projects; SHOW INDEX FROM sources; Paste the outputs. Why you’re stuck right now (straight talk) Your FK error will not go away until these match exactly: quiz_questions.project_id must match projects.project_id (same INT/BIGINT and same UNSIGNED) quiz_questions.source_id must match sources.source_id projects.project_id and sources.source_id must be indexed (PK/UNIQUE) all tables must be InnoDB Right now we’re guessing, and MySQL doesn’t tolerate guessing. If you want to brute-force forward right now Create the table without FKs, then add FKs later once we know types. You’ll be productive immediately. But since you explicitly asked for FKs, do Method 2 (information_schema query) and paste the result — it’s the quickest path. Run this one query and paste what it returns: SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND ( (TABLE_NAME='projects' AND COLUMN_NAME='project_id') OR (TABLE_NAME='sources' AND COLUMN_NAME='source_id') ); You said: Table structure for table projects -- CREATE TABLE projects ( date_created datetime NOT NULL DEFAULT current_timestamp(), project_id tinyint(3) UNSIGNED NOT NULL, project_title varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, last_modifed timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table projects -- ALTER TABLE projects ADD PRIMARY KEY (project_id); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table projects -- ALTER TABLE projects MODIFY project_id tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT; COMMIT; --------------- ChatGPT said: There’s your FK failure. Your parent key is: projects.project_id = TINYINT(3) UNSIGNED AUTO_INCREMENT …but your quiz_questions.project_id was INT(10) UNSIGNED. Foreign keys require an exact type match (size + unsignedness). INT ≠ TINYINT, so MySQL throws errno 150. Also: your exported projects schema includes ALTER statements, but since there’s no data you should rebuild it clean too (or at least mirror its actual final shape). You still need to export/paste the sources table schema, because I can’t safely assume its type. Fix direction (non-negotiable) You have two sane options: Option A (recommended): keep projects tinyint Change quiz_questions.project_id to TINYINT(3) UNSIGNED to match. Option B: upscale projects to int Change projects.project_id to INT UNSIGNED (better long-term if you’ll have >255 projects). Then keep quiz_questions as INT. But that’s a bigger change (and you’d need to recreate projects / migrate). Given you’re building a small private system, Option A is fine. Clean projects table (no ALTERs, same final structure) If you want “optimum cleanliness” here too: CREATE TABLE `projects` ( `project_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, `project_title` VARCHAR(30) NOT NULL, `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`project_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Notes: I fixed your typo last_modifed → last_modified (do this now while it’s empty). Added ON UPDATE CURRENT_TIMESTAMP so it actually tracks modifications. Clean quiz_questions table with FK to projects (corrected type) This version sets project_id to TINYINT(3) UNSIGNED so the FK can form. BUT I still need your sources.source_id type to finish the sources FK correctly. So below I’m giving you a version that: FK’s to projects (safe) leaves sources FK out for the moment (otherwise you’ll hit errno 150 again) CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` TINYINT(3) UNSIGNED NOT NULL, `source_id` INT(10) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_quiz_questions_project_id` (`project_id`), KEY `idx_quiz_questions_source_id` (`source_id`), CONSTRAINT `fk_quiz_questions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `chk_answer_key_range` CHECK (`answer_key` IS NULL OR `answer_key` BETWEEN 1 AND 4), CONSTRAINT `chk_question_type_rules` CHECK ( ( `question_type` = 'multiple_choice' AND `answer_key` BETWEEN 1 AND 4 AND `option_1` IS NOT NULL AND TRIM(`option_1`) <> '' AND `option_2` IS NOT NULL AND TRIM(`option_2`) <> '' AND `option_3` IS NOT NULL AND TRIM(`option_3`) <> '' AND `option_4` IS NOT NULL AND TRIM(`option_4`) <> '' AND TRIM(`answer_text`) <> '' AND `answer_text` IN (`option_1`, `option_2`, `option_3`, `option_4`) ) OR ( `question_type` = 'numeric' AND `answer_key` IS NULL AND `option_1` IS NULL AND `option_2` IS NULL AND `option_3` IS NULL AND `option_4` IS NULL AND TRIM(`answer_text`) <> '' AND `answer_text` REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; What I need from you to finish this completely Paste the sources table structure (same way you did for projects). Specifically, I need to see: exact type of sources.source_id (tinyint? int? bigint? unsigned?) whether it’s primary key + auto_increment InnoDB Once you paste it, I’ll give you the final “optimum cleanliness” sources table (if you want) and the final quiz_questions with both FKs working first try. In the meantime, if you want to avoid back-and-forth, run this and paste results: SELECT COLUMN_TYPE, COLUMN_KEY, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='sources' AND COLUMN_NAME='source_id'; You said: Table structure for table sources -- CREATE TABLE sources ( date_created datetime NOT NULL DEFAULT current_timestamp(), source_id smallint(8) UNSIGNED NOT NULL, author_id smallint(8) UNSIGNED NOT NULL, collection_id tinyint(5) UNSIGNED DEFAULT NULL, date_published date DEFAULT NULL, medium_id tinyint(2) DEFAULT NULL, source_title varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, primary_doc tinyint(1) DEFAULT NULL, published_in varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, race_described varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, provenance text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, full_text tinyint(1) DEFAULT NULL, filename_received varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, filename_assigned varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, online_link varchar(350) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, project_id tinyint(3) UNSIGNED DEFAULT NULL, last_modifed timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table sources -- ALTER TABLE sources ADD PRIMARY KEY (source_id), ADD KEY author_id (author_id), ADD KEY collection_id (collection_id), ADD KEY project_id (project_id); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table sources -- ALTER TABLE sources MODIFY source_id smallint(8) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table sources -- ALTER TABLE sources ADD CONSTRAINT sources_ibfk_1 FOREIGN KEY (author_id) REFERENCES authors (author_id), ADD CONSTRAINT sources_ibfk_2 FOREIGN KEY (project_id) REFERENCES projects (project_id); COMMIT; ChatGPT said: Great — now we can make the FKs work for real. Why your FK was failing Your actual parent key types are: projects.project_id = TINYINT(3) UNSIGNED sources.source_id = SMALLINT(8) UNSIGNED So your quiz_questions must match those exactly: quiz_questions.project_id must be TINYINT(3) UNSIGNED quiz_questions.source_id must be SMALLINT(8) UNSIGNED Also: you’ve got the same typo last_modifed in both tables — if everything’s empty, fix it now or you’ll hate past-you later. ✅ Clean, single-shot CREATE TABLE quiz_questions (with working FKs + CHECKs) No ALTERs. No comments inside. Types match your actual schema. This should create cleanly. CREATE TABLE `quiz_questions` ( `question_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` TINYINT(3) UNSIGNED NOT NULL, `source_id` SMALLINT(8) UNSIGNED NOT NULL, `question_type` ENUM('multiple_choice','numeric') NOT NULL, `question_text` TEXT NOT NULL, `option_1` VARCHAR(255) DEFAULT NULL, `option_2` VARCHAR(255) DEFAULT NULL, `option_3` VARCHAR(255) DEFAULT NULL, `option_4` VARCHAR(255) DEFAULT NULL, `answer_key` TINYINT(3) UNSIGNED DEFAULT NULL, `answer_text` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`question_id`), KEY `idx_quiz_questions_project_id` (`project_id`), KEY `idx_quiz_questions_source_id` (`source_id`), CONSTRAINT `fk_quiz_questions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `fk_quiz_questions_source` FOREIGN KEY (`source_id`) REFERENCES `sources` (`source_id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `chk_answer_key_range` CHECK (`answer_key` IS NULL OR `answer_key` BETWEEN 1 AND 4), CONSTRAINT `chk_question_type_rules` CHECK ( ( `question_type` = 'multiple_choice' AND `answer_key` BETWEEN 1 AND 4 AND `option_1` IS NOT NULL AND TRIM(`option_1`) <> '' AND `option_2` IS NOT NULL AND TRIM(`option_2`) <> '' AND `option_3` IS NOT NULL AND TRIM(`option_3`) <> '' AND `option_4` IS NOT NULL AND TRIM(`option_4`) <> '' AND TRIM(`answer_text`) <> '' AND `answer_text` IN (`option_1`, `option_2`, `option_3`, `option_4`) ) OR ( `question_type` = 'numeric' AND `answer_key` IS NULL AND `option_1` IS NULL AND `option_2` IS NULL AND `option_3` IS NULL AND `option_4` IS NULL AND TRIM(`answer_text`) <> '' AND `answer_text` REGEXP '^-?[0-9]+(\\.[0-9]+)?$' ) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Two “optimum cleanliness” notes you should seriously consider 1) Stop mixing collations Your existing tables use utf8mb4_general_ci at the table level but some columns override to utf8mb4_unicode_ci. Pick one and stick to it. I’d standardize on: utf8mb4_unicode_ci everywhere (Or on newer servers: utf8mb4_0900_ai_ci, but Hostinger varies.) 2) Fix the last_modifed typo now Since you have no data, the clean move is to rebuild both projects and sources with: last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Right now your last_modifed doesn’t auto-update and is misspelled. If you want, I’ll rewrite projects and sources as clean single-shot CREATE statements too, matching your current columns but cleaned up (typo fixed, consistent collations, sensible defaults). One more thing: adjust your PHP types Because project_id is TINYINT and source_id is SMALLINT, your PHP insert code is still fine — i binding works — but you should ensure you’re not casting source_id as int then forcing values beyond SMALLINT. Realistically you won’t. If you want, paste your current question_submit.php and I’ll align it perfectly with this schema (especially around setting NULL options for numeric). You said: how can i make last_modified auto-update? ChatGPT said: Use a TIMESTAMP (or DATETIME in MySQL 8.0.13+) with ON UPDATE CURRENT_TIMESTAMP. Best simple pattern `last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP That makes MySQL set it: when the row is first inserted (DEFAULT) every time the row is updated (ON UPDATE) If your column already exists (rename + fix typo + auto-update) Projects (your current column is last_modifed) ALTER TABLE `projects` CHANGE `last_modifed` `last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; Sources (same typo) ALTER TABLE `sources` CHANGE `last_modifed` `last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; One important gotcha Your tables also have date_created as DATETIME DEFAULT current_timestamp(). That’s fine, but don’t try to make it auto-update too. You want: date_created = set once on insert last_modified = changes on every update If you want, I can give you the clean single-shot CREATE TABLE versions for projects and sources with consistent collations + defaults (since you said there’s no data). You said: There is a lot of data in the sources' and projects' tables. The 'new quiz_questions table is empty. Rewrite question_form.php and question_submit.php to match the table scheme we just cleaned up. ChatGPT said: Got it. Since projects and sources already have real data, we won’t touch them. We’ll only make the PHP match the cleaned quiz_questions schema: projects.project_id is TINYINT UNSIGNED sources.source_id is SMALLINT UNSIGNED quiz_questions stores: answer_key (NULL or 1–4) answer_text (always filled) For numeric questions: option_1..4 must be NULL (per your CHECK) Also: your sources table is huge; we do not query it for the form. We just accept source_id posted in and validate it exists. Below are full rewrites. question_form.php (FULL — mysqli, project_title, source_id from POST, phone-friendly) Add Quiz Question

Add Quiz Question

Missing source_id. This form expects source_id to be posted in from your Source page. Submit will fail until you pass it in.
Source ID:
Stored as answer_key (1–4) and answer_text (the option text).
question_submit.php (FULL — mysqli prepared statements, matches CHECK + FK types) Key details: Validates source_id exists in sources (cheap query by PK). Validates project_id exists in projects. For numeric: inserts NULL options and NULL answer_key to satisfy your CHECK. For MC: inserts options, answer_key, answer_text. 255) fail(422, 'Invalid project_id.'); if ($sourceId <= 0 || $sourceId > 65535) fail(422, 'Invalid source_id.'); if ($qText === '') fail(422, 'Question text is required.'); $allowedTypes = ['multiple_choice', 'numeric']; if (!in_array($type, $allowedTypes, true)) { fail(422, 'Invalid question_type.'); } // Verify project exists $stmt = mysqli_prepare($connection, "SELECT 1 FROM projects WHERE project_id = ? LIMIT 1"); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param($stmt, 'i', $projectId); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); if (mysqli_stmt_num_rows($stmt) !== 1) { mysqli_stmt_close($stmt); fail(422, 'project_id does not exist.'); } mysqli_stmt_close($stmt); // Verify source exists $stmt = mysqli_prepare($connection, "SELECT 1 FROM sources WHERE source_id = ? LIMIT 1"); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param($stmt, 'i', $sourceId); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); if (mysqli_stmt_num_rows($stmt) !== 1) { mysqli_stmt_close($stmt); fail(422, 'source_id does not exist.'); } mysqli_stmt_close($stmt); // Build row fields $option1 = $option2 = $option3 = $option4 = null; $answerKey = null; // NULL for numeric, 1–4 for MC $answerText = ''; if ($type === 'multiple_choice') { $option1 = trim((string)($_POST['option_1'] ?? '')); $option2 = trim((string)($_POST['option_2'] ?? '')); $option3 = trim((string)($_POST['option_3'] ?? '')); $option4 = trim((string)($_POST['option_4'] ?? '')); if ($option1 === '' || $option2 === '' || $option3 === '' || $option4 === '') { fail(422, 'All four options are required.'); } $correct = (int)($_POST['correct_choice'] ?? 0); if ($correct < 1 || $correct > 4) { fail(422, 'Correct choice must be 1–4.'); } $answerKey = $correct; $options = [ 1 => $option1, 2 => $option2, 3 => $option3, 4 => $option4, ]; $answerText = $options[$correct]; } else { // numeric $num = trim((string)($_POST['numeric_answer'] ?? '')); if ($num === '' || !is_numeric($num)) { fail(422, 'Numeric answer is required.'); } // Must satisfy CHECK: options NULL and answer_key NULL $option1 = $option2 = $option3 = $option4 = null; $answerKey = null; $answerText = $num; } // Insert: two SQL paths so answer_key can be a real NULL (reliable across mysqli builds) if ($answerKey === null) { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, NULL, NULL, NULL, NULL, NULL, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param($stmt, 'iisss', $projectId, $sourceId, $type, $qText, $answerText); } else { $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); if (!$stmt) fail(500, 'Prepare failed.'); mysqli_stmt_bind_param( $stmt, 'iissssssiss', $projectId, $sourceId, $type, $qText, $option1, $option2, $option3, $option4, $answerKey, $answerText ); } if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail(500, 'Insert failed.'); } $newId = mysqli_insert_id($connection); mysqli_stmt_close($stmt); ?> Saved

Saved ✅

Question ID:

Add another question

One sharp edge to watch Your CHECK requires numeric questions to have all options NULL. Many mysqli bindings silently turn null into '' depending on how you bind. That’s why the numeric insert uses literal NULL in the SQL for the option columns—so it cannot violate the CHECK. If you want the form to also allow loading by GET (e.g., question_form.php?source_id=123), say so and I’ll modify it safely. You said: I will enter the form from a source page. I want the form to collect the source_id from the URL ChatGPT said: Perfect — that’s the cleanest flow. We’ll pull source_id from the URL ($_GET), lock it in a hidden field, validate it against sources, and refuse to submit if it’s missing or invalid. Below are full rewrites of both files, adjusted only where needed to support source_id from the URL. Everything else stays aligned with your cleaned schema and CHECK/FK rules. ✅ question_form.php Key changes Reads source_id from $_GET['source_id'] Validates it exists in sources Hard-fails if missing or invalid (no silent bad data) Shows the Source ID clearly so you know what you’re attaching to 65535) { http_response_code(400); exit('Missing or invalid source_id in URL.'); } /* Validate source exists */ $stmt = mysqli_prepare($connection, "SELECT source_id FROM sources WHERE source_id = ? LIMIT 1"); mysqli_stmt_bind_param($stmt, 'i', $sourceId); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); if (mysqli_stmt_num_rows($stmt) !== 1) { mysqli_stmt_close($stmt); http_response_code(404); exit('Source not found.'); } mysqli_stmt_close($stmt); /* ---------------- Load projects ---------------- */ $sql = "SELECT project_id, project_title FROM projects ORDER BY project_title ASC"; $res = mysqli_query($connection, $sql); if (!$res) { http_response_code(500); exit('Could not load projects.'); } $projects = []; while ($row = mysqli_fetch_assoc($res)) { $projects[] = $row; } mysqli_free_result($res); ?> Add Quiz Question

Add Quiz Question

Attached to Source ID:

>
✅ question_submit.php Matches the schema exactly project_id → TINYINT source_id → SMALLINT Numeric inserts literal NULL options MC stores both answer_key and answer_text Validates both foreign keys exist 255) fail('Invalid project'); if ($sourceId <= 0 || $sourceId > 65535) fail('Invalid source'); if ($text === '') fail('Question text required'); /* Verify FKs */ foreach ([ ['projects','project_id',$projectId], ['sources','source_id',$sourceId] ] as [$table,$col,$val]) { $stmt = mysqli_prepare($connection, "SELECT 1 FROM {$table} WHERE {$col}=? LIMIT 1"); mysqli_stmt_bind_param($stmt, 'i', $val); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); if (mysqli_stmt_num_rows($stmt) !== 1) fail("Invalid {$table} reference"); mysqli_stmt_close($stmt); } if ($type === 'numeric') { $num = trim($_POST['numeric_answer'] ?? ''); if ($num === '' || !is_numeric($num)) fail('Numeric answer required'); $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, 'numeric', ?, NULL, NULL, NULL, NULL, NULL, ?)"; $stmt = mysqli_prepare($connection, $sql); mysqli_stmt_bind_param($stmt, 'iiss', $projectId, $sourceId, $text, $num); } else { $opts = []; for ($i = 1; $i <= 4; $i++) { $opts[$i] = trim($_POST["option_$i"] ?? ''); if ($opts[$i] === '') fail('All options required'); } $key = (int)($_POST['correct_choice'] ?? 0); if ($key < 1 || $key > 4) fail('Invalid correct choice'); $sql = "INSERT INTO quiz_questions (project_id, source_id, question_type, question_text, option_1, option_2, option_3, option_4, answer_key, answer_text) VALUES (?, ?, 'multiple_choice', ?, ?, ?, ?, ?, ?, ?)"; $stmt = mysqli_prepare($connection, $sql); mysqli_stmt_bind_param( $stmt, 'iissssssis', $projectId, $sourceId, $text, $opts[1], $opts[2], $opts[3], $opts[4], $key, $opts[$key] ); } if (!mysqli_stmt_execute($stmt)) { mysqli_stmt_close($stmt); fail('Insert failed', 500); } $id = mysqli_insert_id($connection); mysqli_stmt_close($stmt); echo "Saved question #{$id}"; What you now have (clean & correct) ✅ source_id is authoritative from the URL ✅ Impossible to submit a question without a valid source ✅ DB CHECK constraints will never be violated ✅ FK integrity guaranteed ✅ Phone-friendly UX ✅ No accidental orphan questions If you want next: auto-redirect back to the source page after save bulk question entry per source preview/test quiz from a source Say the word. You said: I put a button on source_run.php hyperlinked to 'question_form.php'. How can I make it open in a new window? ChatGPT said: Dead simple. You’ve got three clean options—pick based on how much control you want. Option 1 (recommended): HTML anchor with target="_blank" If your button is just a styled link (most are): Add Quiz Question Why this is the right default target="_blank" opens a new tab/window rel="noopener noreferrer" prevents the new page from messing with the opener (security best practice) Works everywhere, no JS needed Option 2:
button that opens a new window If you’re using a form button (POST or GET):
Use this if You want to keep parameters out of the URL bar after submit You already use forms for navigation Option 3: JavaScript window.open() (most control) Only use this if You need size/position control You’re okay with popup blockers sometimes killing it What not to do ❌ Don’t rely on JS alone if a normal link will do ❌ Don’t omit rel="noopener" on _blank links ❌ Don’t use target="_new" (creates unlimited tabs) My recommendation for your setup Use Option 1 unless you have a strong reason not to. It fits your Chronology workflow cleanly and doesn’t fight the browser. If you want the new window to auto-close after save or refresh the source page, I can wire that up next. You said: I used your Option 1 HTML styled link, but within PHP. Nothing happens when I click it. What's wrong? Here is the code: echo " Add Quiz Question "; ChatGPT said: This is a classic PHP gotcha — your link isn’t broken, your PHP is never executing inside that string. What’s wrong (plain English) You used PHP short echo syntax inside a quoted PHP string. PHP does not evaluate PHP code inside strings. So your output literally contains: ?source_id= That makes the URL invalid → browser clicks → nothing happens. ✅ Correct way (string concatenation) Rewrite it like this: echo " Add Quiz Question "; That’s it. Now PHP actually injects the value. ✅ Cleaner & safer (recommended): break out of echo Much easier to read and less error-prone: ?> Add Quiz Question "; // ❌ PHP does NOT run inside strings Only these work: String concatenation: "foo " . $bar Breaking out of PHP: ?> "; echo "QUERY_STRING: "; var_dump($_SERVER['QUERY_STRING'] ?? null); echo "GET: "; var_dump($_GET); echo ""; exit; Now reload: https://chronology.amyjohoward.com/pages/question_form.php?source_id=1459 If you see: QUERY_STRING is empty and $_GET is empty → rewrite is stripping it (fix .htaccess with QSA) $_GET contains source_id → your code is different than you think (wrong file deployed, caching, or earlier exit) Remove this debug block after you confirm. 3) Make sure your link isn’t accidentally malformed Even if you typed the URL manually, your button link could still be broken. When you generate the link in PHP, make sure it outputs this exact pattern: No newlines inside the href value. (Newlines can break attributes in ugly ways.) Use this safe PHP version: echo 'Add Quiz Question'; What I need from you Paste your .htaccess rewrite section (just the rewrite rules) or paste what the debug block prints for QUERY_STRING and $_GET. Then I’ll tell you the exact one-line fix. You said: I added your temporary 
 block and reloaded https://chronology.amyjohoward.com/pages/question_form.php?source_id=1459. The browser couldn't load the page. Chrome said:
This page isn’t working
chronology.amyjohoward.com is currently unable to handle this request.
HTTP ERROR 500

ChatGPT said:
That 500 happened because your site is configured to hide PHP errors, so my debug snippet triggered a fatal error or your server choked before it could output anything. Either way, we need to get the real error message from the server logs or temporarily enable error display just for this page.

Here’s the fastest way to diagnose without guessing.

1) Turn on error reporting at the top of question_form.php (temporary)
Put this as the very first thing in the file (literally right after  debug block right after it:

echo "
";
echo "QUERY_STRING: "; var_dump($_SERVER['QUERY_STRING'] ?? null);
echo "GET: "; var_dump($_GET);
echo "
";
exit;
Reload the page again.

What should happen
Instead of a blank 500 page, you’ll see an actual PHP error like:

“Call to undefined function …”

“Cannot modify header information…”

“Failed opening required …/database.php”

etc.

Copy/paste that error here.

2) The most likely cause of the 500 (based on your setup)
On shared hosting, the #1 culprit is a bad include path:

require_once __DIR__ . '/../run_code/database.php';
If question_form.php is in /pages/, then ../run_code/database.php points to /run_code/database.php at the web root. If your folder structure is different (common on Hostinger), PHP dies → HTTP 500.

Once error display is on, you’ll see something like:

failed to open stream: No such file or directory

If that’s the error, fix the path to the actual location. Example possibilities:

require_once __DIR__ . '/../../run_code/database.php';
or

require_once $_SERVER['DOCUMENT_ROOT'] . '/run_code/database.php';
3) If you don’t want to show errors to the browser (safer)
Use Hostinger’s error log:

hPanel → Websites → your site → Advanced → Error log

Or File Manager → look for error_log in your site directory

Reload the page once and then check the log. Paste the newest entry.

4) After you find the error, undo the debug stuff
Once we confirm why it’s 500’ing:

remove display_errors lines

remove the 
 block

keep the final logic

Do this now
Add the 3-line error reporting header and reload.
Paste the exact PHP error text that shows up. That will let me give you the exact fix in one shot.


You said:
Chrome still says this page isn't working. Here is the page code that you wrote:
";
echo "QUERY_STRING: "; var_dump($_SERVER['QUERY_STRING'] ?? null);
echo "GET: "; var_dump($_GET);
echo "
";
exit;

declare(strict_types=1);

require_once __DIR__ . '../run_code/database.php';
session_start();

/* ---------------- CSRF ---------------- */
if (empty($_SESSION['csrf_token'])) {
  $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
$csrfToken = $_SESSION['csrf_token'];

/* ---------------- source_id from URL ---------------- */
$source_id = isset($_GET['source_id']) ? (int)$_GET['source_id'] : 0;
if ($sourceId <= 0 || $sourceId > 65535) {
  http_response_code(400);
  exit('Missing or invalid source_id in URL.');
}

/* Validate source exists */
$stmt = mysqli_prepare($connection, "SELECT source_id FROM sources WHERE source_id = ? LIMIT 1");
mysqli_stmt_bind_param($stmt, 'i', $sourceId);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
if (mysqli_stmt_num_rows($stmt) !== 1) {
  mysqli_stmt_close($stmt);
  http_response_code(404);
  exit('Source not found.');
}
mysqli_stmt_close($stmt);

/* ---------------- Load projects ---------------- */
$sql = "SELECT project_id, project_title FROM projects ORDER BY project_title ASC";
$res = mysqli_query($connection, $sql);
if (!$res) {
  http_response_code(500);
  exit('Could not load projects.');
}

$projects = [];
while ($row = mysqli_fetch_assoc($res)) {
  $projects[] = $row;
}
mysqli_free_result($res);
?>





Add Quiz Question






  
Add Quiz Question

  
  
Attached to Source: 


  

    
    

    
    

    
    

    
    

    

      

      
        

          >
          
        

      
    


    

    
  









ChatGPT said:
You’ve got multiple fatal PHP problems in that file. Any one of them can throw a 500. Here are the big ones, in plain terms, and then I’ll give you a clean fixed full file you can paste in.

What’s wrong (the real issues)
1) declare(strict_types=1); is in the wrong place
In PHP it must be the first statement in the file (after 
It’s not defined

You’re casting it to int (nonsense)
That won’t necessarily fatal, but it’s wrong.

6) Your form action points somewhere else
You used:

action="../run_code/question_submit.php"
But earlier we were using question_submit.php in the same folder as the form. If yours is actually in /run_code/, fine — but then you need to keep paths consistent.

✅ Paste-this fixed version: pages/question_form.php
This version:

Pulls source_id from URL

Validates it exists

Fetches source_title to display (optional but helpful)

Loads projects using project_title

Posts to question_submit.php (same folder as this file). If your submit file is elsewhere, change ONE line.

 65535) {
  http_response_code(400);
  exit('Missing or invalid source_id in URL.');
}

/* ---------------- Validate source + get title ---------------- */
$sourceTitle = '';
$stmt = mysqli_prepare($connection, "SELECT source_title FROM sources WHERE source_id = ? LIMIT 1");
if (!$stmt) {
  http_response_code(500);
  exit('DB prepare failed (sources).');
}
mysqli_stmt_bind_param($stmt, 'i', $sourceId);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $sourceTitle);
if (!mysqli_stmt_fetch($stmt)) {
  mysqli_stmt_close($stmt);
  http_response_code(404);
  exit('Source not found.');
}
mysqli_stmt_close($stmt);

/* ---------------- Load projects ---------------- */
$res = mysqli_query($connection, "SELECT project_id, project_title FROM projects ORDER BY project_title ASC");
if (!$res) {
  http_response_code(500);
  exit('Could not load projects.');
}
$projects = [];
while ($row = mysqli_fetch_assoc($res)) {
  $projects[] = $row;
}
mysqli_free_result($res);
?>





Add Quiz Question






  
Add Quiz Question

  
Source ID: 

  
Source title: 


  
  

    
    

    
    

    
    

    
    

    

      
      
        

          >
          
        

      
    


    

    
  








After you confirm it loads
Turn off error display by deleting these three lines (keep the rest):

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);
One more thing: your original “query string missing” mystery
Once this version loads, it will prove whether $_GET['source_id'] is coming through. If it still says missing, then you truly have a rewrite/QS stripping issue — but right now you’re not even getting that far because the file is fatally broken.

If you paste your current question_submit.php location (is it in /pages/ or /run_code/?), I’ll align the action= path and the include paths so you’re not playing whack-a-mole.


You said:
My database convention is source_id, not camel case.

ChatGPT said:
Cool — then don’t fight your own convention. Use snake_case in PHP too. That’s totally normal (and honestly cleaner for DB-driven pages).

Here’s the same question_form.php rewritten to use $_GET['source_id'] → $source_id everywhere, with the other bugs fixed (proper declare, fixed include path, defined $source_title, and consistent variables).

pages/question_form.php (snake_case throughout)
 65535) {
  http_response_code(400);
  exit('Missing or invalid source_id in URL.');
}

/* ---------------- Validate source + get title ---------------- */
$source_title = '';
$stmt = mysqli_prepare($connection, "SELECT source_title FROM sources WHERE source_id = ? LIMIT 1");
if (!$stmt) {
  http_response_code(500);
  exit('DB prepare failed (sources).');
}
mysqli_stmt_bind_param($stmt, 'i', $source_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $source_title);

if (!mysqli_stmt_fetch($stmt)) {
  mysqli_stmt_close($stmt);
  http_response_code(404);
  exit('Source not found.');
}
mysqli_stmt_close($stmt);

/* ---------------- Load projects ---------------- */
$res = mysqli_query($connection, "SELECT project_id, project_title FROM projects ORDER BY project_title ASC");
if (!$res) {
  http_response_code(500);
  exit('Could not load projects.');
}

$projects = [];
while ($row = mysqli_fetch_assoc($res)) {
  $projects[] = $row;
}
mysqli_free_result($res);
?>





Add Quiz Question






  
Add Quiz Question

  
Source ID: 

  
Source title: 


  
  

    
    

    
    

    
    

    
    

    

      
      
        

          >
          
        

      
    


    

    
  








Two quick notes
If your submit script lives in run_code/, set:

action="../run_code/question_submit.php"
If your database.php path is different, keep using the safe form:

require_once __DIR__ . '/../run_code/database.php';
If you want, paste your current question_submit.php location (and filename) and I’ll rewrite that file in snake_case too so everything matches end-to-end.