PHP inserting into database

Question

Hey guys, im trying to insert some checkboxes into a database and im pretty sure my code is correct however i keep getting the error ERROR INSERTING: Column count doesn't match value count at row 1

Basically i am adding each checkbox to a different column in my database

Here is my code

    $idextra=$_POST['extras'];
    $arr_num=count($idextra);
    $i=0;
    while ($i < $arr_num)
   {

    $qu="INSERT INTO bs_reservations (deodoriser,carpet,carpetrepair,furniture,tabs,urine) VALUES ('$idextra[$i]')";
    $res=mysql_query($qu) or die('ERROR INSERTING: '.mysql_error());
      $i++;
    }

Hey guys here is the HTML for my check boxes and contact form.

`

          <tr>
            <td height="30" align="right" class="align_right">Your Name*:&nbsp;</td>
            <td>
              <input type="text" name="name" id="name" value="<?php echo $name?>"  onchange="checkFieldBack(this)"/>
            </td>
          </tr>
          <tr>
            <td height="30" align="right" class="align_right">Phone*:&nbsp;</td>
            <td><input type="text" name="phone" id="phone" value="<?php echo $phone?>"  onchange="checkFieldBack(this)" onkeyup="noAlpha(this)"/></td>
          </tr>

          <tr>
            <td height="30" align="right" class="align_right">E-mail*:&nbsp;</td>
            <td><input type="text" name="email" id="email"  value="<?php echo $email?>" onchange="checkFieldBack(this);"/></td>
          </tr>

          <tr>
            <td align="right"  valign="top" class="align_right">Address*:&nbsp;</td>
            <td><textarea name="comments" id="comments" cols="15" rows="5" onchange="checkFieldBack(this)"><?php echo $comments?></textarea></td>
          </tr>

    <tr>
<td width="236" height="25" align="left">Drop off at:</td>
<td width="548" height="23"><select name="dropoff">

             <option value="05:00" <?php echo $dropoff=="05:00"?"selected":""?>>05:00</option>
             <option value="06:00" <?php echo $dropoff=="06:00"?"selected":""?>>06:00</option>
             <option value="07:00" <?php echo $dropoff=="07:00"?"selected":""?>>07:00</option>
             <option value="08:00" <?php echo $dropoff=="08:00"?"selected":""?>>08:00</option>
             <option value="09:00" <?php echo $dropoff=="09:00"?"selected":""?>>09:00</option>                      
             <option value="10:00" <?php echo $dropoff=="10:00"?"selected":""?>>10:00</option>
             <option value="11:00" <?php echo $dropoff=="11:00"?"selected":""?>>11:00</option>
             <option value="12:00" <?php echo $dropoff=="12:00"?"selected":""?>>12:00</option>
             <option value="13:00" <?php echo $dropoff=="13:00"?"selected":""?>>13:00</option>
             <option value="14:00" <?php echo $dropoff=="14:00"?"selected":""?>>14:00</option>
             <option value="15:00" <?php echo $dropoff=="15:00"?"selected":""?>>15:00</option>
             <option value="16:00" <?php echo $dropoff=="16:00"?"selected":""?>>16:00</option>
             <option value="17:00" <?php echo $dropoff=="17:00"?"selected":""?>>17:00</option>
             <option value="18:00" <?php echo $dropoff=="18:00"?"selected":""?>>18:00</option>
             <option value="19:00" <?php echo $dropoff=="19:00"?"selected":""?>>19:00</option>


  </select> 
      </td>

    <tr>
            <td height="10" align="right" class="align_right">Deodoriser:&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="deodoriser" value="Deodoriser>"/>
            </td>
          </tr>

<tr>
            <td height="30" align="right" class="align_right">Carpet Protector (5 litre):&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="carpet" value="Carpet Protector (5 litre)"/>
            </td>
          </tr>
<tr>
            <td height="30" align="right" class="align_right">Carpet Repair Tools:&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="carpetrepair" value="Carpet Repair Tools"/>
            </td>
          </tr>
<tr>
            <td height="30" align="right" class="align_right">Furniture Moving Equipment:&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="furniture" value="Furniture Moving Equipment"/>
            </td>
          </tr>
<tr>
            <td height="30" align="right" class="align_right">Furniture Tabs:&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="tabs" value="Furniture Tabs"/>
            </td>
          </tr>
<tr>
            <td height="30" align="right" class="align_right">Urine Decontamination Treatment:&nbsp;</td>
            <td>
              <input type="checkbox" name="extras[]" id="urine" value="Urine Decontamination Treatment"/>
            </td>
          </tr>

`

and here is my complete php code for inserting into the data base

`$idextra=$_POST['extras']; $arr_num=count($idextra); $i=0; while ($i < $arr_num) {

$qu="INSERT INTO bs_reservations (deodoriser,carpet,carpetrepair,furniture,tabs,urine) VALUES ('{$idextra[1]}','{$idextra[2]}','{$idextra[3]}','{$idextra[4]}','{$idextra[5]}','{$idextra[6]}')";
$res=mysql_query($qu) or die('ERROR INSERTING: '.mysql_error());
  $i++;
}


$q="INSERT INTO bs_reservations (dateCreated, name, email, phone, comments,status,eventID, qty,dropoff) VALUES (NOW(),'".$name."','".$email."','".$phone."','".$comments."','2','".$eventID."','".$qty."','".$dropoff."')";
$res=mysql_query($q) or die("error!");
$orderID=mysql_insert_id();`

I basically want to take all the inputs that the user selects and insert them into a data base.

I love how there is no validation/sanitize for the bare $_POST vars too ;) — Jakub
– Jakub, Commented Apr 9, 2011 at 10:27
Sorry, but your code is not correct. You're doing exactly what mysql is crying about. if you tell us what you're trying to do we can help you — JohnP
– JohnP, Commented Apr 9, 2011 at 10:27
Can you paste your html code to produce checkboxes here ? btw, you are only counting values that expected to be posted by the correct order which can lead you a serious/continous problem when html form code has been changed. — risyasin
– risyasin, Commented Apr 9, 2011 at 10:28

Nanne · Accepted Answer · 2011-04-09 10:33:43Z

4

You have these columns:

(deodoriser,carpet,carpetrepair,furniture,tabs,urine)

And you are inserting this:

'$idextra[$i]'

That's 6 columns and 1 value. As the error says, that's not the same.

You might have meant something like this:

('{$idextra[1]}','{$idextra[2]}','{$idextra[3]}','{$idextra[4]}','{$idextra[5]}','{$idextra[6]}')

If you want to make a string out of your array beforehand, use something like this using implode

$yourString = implode("','",$idextra);
$qu="INSERT INTO bs_reservations (deodoriser,carpet,carpetrepair,furniture,tabs,urine)
      VALUES ('{$yourString}')";

echo the query to be sure it's sane :)

edited Apr 9, 2011 at 10:33

answered Apr 9, 2011 at 10:25

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Simon Over a year ago

doing this works, however there are 6 check boxes and if the customer only selects say the first 2 and the last, when inserting into the database it inserts the last checkbox into the 3rd column

Nanne Over a year ago

A trick for the checkboxes is this: add a hidden field/input with value 0 BEFORE your checkbox. If it is checked, your value is overwritten by the second field, and gets a 1 (or true). If the second isn't checked, it is ignored by the form, but the first (hidden) one isn't, so it passes its 0. presto, 6 values, everybody happy :D

Simon Over a year ago

how do i add a "hidden value" just add a value ='0'

Nanne Over a year ago

<input type="hidden" name="Name" value="0"/>

Simon Over a year ago

does the input type not have to be checkbox?

|

Your Common Sense · Accepted Answer · 2011-04-09 10:51:08Z

1

Instead of using array for your checkboxes, give them distinct names, e.g.

<input type="checkbox" name="carpetrepair" id="carpetrepair" value="Carpet Repair Tools"/>

And then check if any of them were checked:

$options = explode(",","deodoriser,carpet,carpetrepair,furniture,tabs,urine");
$sql = "INSERT INTO bs_reservations SET ";
foreach($options as $opt){
  if (isset($_POST[$opt])) {
    $sql.= "`$opt`=1,";
  }
}
$sql = rtrim($sql,",");

edited Apr 9, 2011 at 10:51

answered Apr 9, 2011 at 10:45

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

1 Comment

Simon Over a year ago

sorry to be so late mate but just wondering how this actually works. Will it actually assign each variable a name and insert it into the data base or do i need to addmore code

Collectives™ on Stack Overflow

PHP inserting into database

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related