Error in your SQL syntax - Form Checkboxes

[jkaye]

I have racked my brain and can't find the error for the incorrect SQL syntax. I basically have a form with a bunch of fields. Some of them are checkboxes (single instance check boxes) and I think that's where the code is breaking, but values of the checkboxes are 'Yes' or ''. I'm not sure why that would break it. Here's some code:

Submittal Form

<strong>Submission of this form signifies that the '.$group.' is entering a bid for gender and age division(s) in which they can viably form a team, based on the Games Governing Rules for a slot in the National Games 2014 lottery draw.

        <br><br>The '; if($group == 'Region') {echo 'Regional Commissioner';} else {echo'Area Director';}echo' also has read and understands the following policies:</strong>
        <br>
        <form name="intent5" method="post" action="lotteryform_submit.php">
            <input type="hidden" name="intent" value="'.$intent.'">
            <input type="hidden" name="section" value="'.$section.'">
            <input type="hidden" name="group" value="'.$group.'">
            <input type="hidden" name="regionnum" value="'.$regionnum.'">
            <input type="hidden" name="areanum" value="'.$areanum.'">
            <input type="hidden" name="region1" value="'.$region1.'">
            <input type="hidden" name="region2" value="'.$region2.'">
            <input type="hidden" name="region3" value="'.$region3.'">
            <input type="hidden" name="region4" value="'.$region4.'">
            <input type="hidden" name="region5" value="'.$region5.'">
            <input type="hidden" name="region6" value="'.$region6.'">
            <input type="hidden" name="region7" value="'.$region7.'">
            <input type="hidden" name="region8" value="'.$region8.'">
            <input type="hidden" name="region9" value="'.$region9.'">
            <input type="hidden" name="region10" value="'.$region10.'">
            <input type="hidden" name="region11" value="'.$region11.'">
            <input type="hidden" name="divisionu10g" value="'.$divisionu10g.'">
            <input type="hidden" name="divisionu10b" value="'.$divisionu10b.'">
            <input type="hidden" name="divisionu12g" value="'.$divisionu12g.'">
            <input type="hidden" name="divisionu12b" value="'.$divisionu12b.'">
            <input type="hidden" name="divisionu14g" value="'.$divisionu14g.'">
            <input type="hidden" name="divisionu14b" value="'.$divisionu14b.'">
            <input type="hidden" name="divisionu16g" value="'.$divisionu16g.'">
            <input type="hidden" name="divisionu16b" value="'.$divisionu16b.'">
            <input type="hidden" name="divisionu19g" value="'.$divisionu19g.'">
            <input type="hidden" name="divisionu19b" value="'.$divisionu19b.'">
            <input type="hidden" name="under30women" value="'.$under30women.'">
            <input type="hidden" name="under30men" value="'.$under30men.'">
            <input type="hidden" name="under30coed" value="'.$under30coed.'">
            <input type="hidden" name="over30women" value="'.$over30women.'">
            <input type="hidden" name="over30men" value="'.$over30men.'">
            <input type="hidden" name="over30coed" value="'.$over30coed.'">
            <input type="hidden" name="location" value="'.$location.'">
            <blockquote>
            <input type="checkbox" name="PurposeNationalGames" value="Agree"> Purpose of the National Games<br>
            <input type="checkbox" name="NationalGamesPlayerEligibilityPolicy" value="Agree"> National Games Player Eligibility Policy<br>
            <input type="checkbox" name="TeamFormationPolicy" value="Agree"> Team Formation Policy<br>
            <input type="checkbox" name="CoachCertificationPolicy" value="Agree"> Coach Certification Policy

        </blockquote>
        <br><br>
        When you click SUBMIT, a confirmation email will go to the following email address, where the '; if($group == 'Region') {echo 'Regional Commissioner';} else {echo'Area Director';}echo' will have to confirm the submission.<br><br>

        ';

        if($group == 'Region') {
            $result = mysql_query("SELECT firstname, lastname, email FROM adrcdata WHERE section = '$section' AND area = '$areanum' AND region = '$regionnum' AND position = 'RC'");
        } else {
            $result = mysql_query("SELECT firstname, lastname, email FROM adrcdata WHERE section = '$section' AND area = '$areanum' AND position = 'AD'");
        }

        $found = mysql_fetch_array($result);

        $firstname = $found['firstname'];
        $lastname = $found['lastname'];
        $email = $found['email'];

        $fullname = $firstname . " " . $lastname;

        echo '<em style="color: #ef3e33;">'.$email.'</em>';

        if($group == 'Region') {echo '<input type="hidden" name="rcname" value="'.$fullname.'"><input type="hidden" name="rcemail" value="'.$email.'">';} else {echo'<input type="hidden" name="adname" value="'.$fullname.'"><input type="hidden" name="ademail" value="'.$email.'">';}
        echo'   
        <br><br>
        <input type="submit" name="submit3" value="SUBMIT LOTTERY FORM"  onclick="checkPolicy()">

        </form>';

Database Code

$result = mysql_query("INSERT INTO `nglotteryintent` VALUES (NULL, '$intent', '$section', '$group', '$regionnum', '$areanum', '$region1',  '$region2', '$region3', '$region4, '$region5', '$region6', '$region7', '$region8', '$region9', '$region10', '$region11', '$divisionu10g', '$divisionu10b', '$divisionu12g', '$divisionu12b', '$divisionu14g', '$divisionu14b', '$divisionu16g', '$divisionu16b', '$divisionu19g', '$divisionu19b', '$under30women', '$under30men', '$under30coed', '$over30women', '$over30men', '$over30coed', '$location', '$purposenationalgames', '$playereligibilitypolicy', '$teamformationpolicy', '$coachcertificationpolicy', '$adname', '$ademail', '$rcname', '$rcemail', '$received', 'no', NULL)") or die (mysql_error());

Note that all of my variables are escaped before submitting to the database using mysql_real_escape_string().

Here's the error I'm getting from the database. The YES values would be coming from the variables labeled divisionu10g, etc.:

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 'Yes', '', '', 'Yes', '', 'Yes', '', '', '', '', '', '', '', '', '', '', '', 'Agr' at line 1

This whole schema DB looks really awkward. You seem to have categories as fields, instead of normalizing tables and using a relational model. Therefore your form looks bloated to hell - all those hidden fields. I'm assuming that these hidden fields hold values from a previous form submission. It doesn't help that you're mixing html (with inline styling), and php. It looks like you're placing all your html into php - that probably isn't the best way to do it.

Your one big table, may be able to be broken down into more tables, but as I don't know how your data is supposed to relate to each other, I can't advise.

[jkaye]

This form is simple, but is basically three forms in one. I have it so that as they fill it out, the next section that applies to them appears. The only way I know to do this is to have a bunch of separate forms that submit each time a new section needs to appear.

Stepped forms are all well and good, but if they are sent to the server (submitted) at every step, you could go at it to save the data to the DB at every step. All you'd need to do would be to get the current record id - from the mysql_insert_id() call after the first submission and propagate that value as a hidden field. No need to drag every single value along with you like a bag of dead rats.
The idea would be the same for ajax forms.

[jkaye]

I got the form to work, and thanks for the advice. I'll give it a shot to help clean the code up. It's always a learning process.