Member Avatar for CarterLangley

Can never seem to get things to work!

<?php
if(isset($_POST['add']))
{
$person_id = $_POST['person_id'];

if (isset($_POST["cv"])) {
    $cv=1;
} else {
    $cv=0;
}   
if (isset($_POST["document_id"])) {
    $document_id=1;
} else {
    $document_id=0;
}
if (isset($_POST["application_form"])) {
    $application_form=1;
} else {
    $application_form=0;
}
if (isset($_POST["passport"])) {
    $passport=1;
} else {
    $passport=0;
}
if (isset($_POST["criminal_disclosure"])) {
    $criminal_disclosure=1;
} else {
    $criminal_disclosure=0;
}
if (isset($_POST["medical_clearance"])) {
    $medical_clearance=1;
} else {
    $medical_clearance=0;
}
if (isset($_POST["reference"])) {
    $reference=1;
} else {
    $reference=0;
}
if (isset($_POST["educational"])) {
    $educational=1;
} else {
    $educational=0;
}
if (isset($_POST["passport_photo"])) {
    $passport_photo=1;
} else {
    $passport_photo=0;
}
if (isset($_POST["innoculation"])) {
    $innoculation=1;
} else {
    $innoculation=0;
}

$sql = "INSERT INTO `teaching`.`documents` (`document_id`, `person_id`,'cv','application_form','passport','criminal_disclosure','medical_clearance','reference','educational','passport_photo','innoculation') 
VALUES ('NULL', '$person_id', '$cv','$application_form','$passport','$criminal_disclosure','$medical_clearance','$reference','$educational','$passport_photo','$innoculation')";

Bunch of check boxes that need to be saved. Below is the error.

Could not enter data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cv','application_form','passport','criminal_disclosure','medical_clearance','re' at line 1

Where have I gone wrong this time? Thought if I cycled through each checkbox it would work?

  • 1 Contributor
  • 1 Reply
  • 278 Views
  • 2 Minutes Discussion Span
  • Latest Post Latest Post by CarterLangley

Dani AI

Generated

Good catch, — that was the classic quoting issue. In SQL single quotes denote string literals; using 'cv' in the column list makes MySQL see a string where it expects an identifier, which triggers the syntax error. Either leave simple column names unquoted or use backticks consistently. Also avoid passing the literal string 'NULL' for an AUTO_INCREMENT column — use NULL (no quotes) or simply omit that column from the INSERT column list.

A safer, clearer pattern is to:

  • collect the checkbox flags as 1/0,
  • omit the auto-increment id from the INSERT,
  • and use a prepared statement to avoid injection and quoting mistakes.

Example pattern (concise, different from your original code):

$person_id = intval($_POST['person_id']);
$fields = ['cv','application_form','passport','criminal_disclosure','medical_clearance','reference','educational','passport_photo','innoculation'];

$values = [$person_id];
foreach ($fields as $f) {
    $values[] = isset($_POST[$f]) ? 1 : 0;
}

$stmt = $mysqli->prepare(
  "INSERT INTO `teaching`.`documents` (`person_id`, `cv`, `application_form`, `passport`, `criminal_disclosure`, `medical_clearance`, `reference`, `educational`, `passport_photo`, `innoculation`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
);
$stmt->bind_param('iiiiiiiiii', $values[0], $values[1], $values[2], $values[3], $values[4], $values[5], $values[6], $values[7], $values[8], $values[9]);
$stmt->execute();

Quick checklist if this pops up again:

  • Ensure column names are not wrapped in single quotes.
  • Use NULL (no quotes) for SQL NULL or leave the AUTO_INCREMENT column out.
  • Confirm the number of columns matches the number of VALUES.
  • Validate/escape input (or use prepared statements).
  • Debug with mysqli_error / $stmt->error to get the exact DB message.

Your follow-up that backticks were the culprit is correct — fixing those and switching to a prepared INSERT will make this robust.

Member Avatar for CarterLangley

Never mind, managed to figure this one out. Damn back ticks catching me out again!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.