Insert statement help w/error

I'm missing something obvious

[lorax]

This is the Insert statement:

$theinsert = "INSERT INTO my_profiles(fname,lname,title,mrank,website,linkedin,twitter,facebook,photo,cv, speaker,speaktopics,stafforfac,startdate,building,room,phone,email,courses,college,program,institution1,year1,
degree1,institution2, year2,degree2,institution3,year3,degree3,assocs,awards,pubs,interests) VALUES('".$fname."','".$lname."','".$title."','".$mrank."','".$website."','".$linkedin."','".$twitter."','".$facebook."','','','". $speaker."','".$speaktopics."','".$stafforfac."','".$startdate."','".$building."','".$room."','".$phone."','".$email."',
'".$courses."','".$college."','".$program."','".$institution1."','".$year1."','".$degree1."','".$institution2."','". $year2."','".$degree2."','".$institution3."','".$year3."','".$degree3."','".$assocs.'","'.$awards."','".$pubs."','".$interests."')";

 $r=mysqli_query($mysqli,$theinsert) or die(mysqli_error($mysqli)." Q=".$theinsert);

The error I get is "Column count doesn't match value count at row". I've counted them and there all there so I'm stumped on what I'm missing.

[edited by: lorax at 5:02 pm (utc) on Mar 5, 2014]

[LifeinAsia]

What do you see if you echo $theinsert?

I've come across similar situations and had to resort to doing one INSERT statement with a minimal amount of fields, then an UPDATE statement for the rest, trying to see exactly where the problem was.

Another trick I started trying recently for improved readability is instead of typing all the fields horizontally, I do them vertically.
Ex: Instead of
INSERT TableName (Field1,Field2,Field3)
VALUES (1,2,3)
try
INSERT TableName (Field1
,Field2
,Field3)
VALUES (1
,2
,3)

This also helps immensely with debugging as you can comment individual fields easily.

Added bonus: when cutting/pasting to WebmasterWorld, it doesn't make the screen go all the way to the neighboring cubical. ;)

[lorax]

Point taken... :)

Output:

INSERT INTO my_profiles(fname,lname,title,mrank,website,linkedin,twitter,facebook,photo,cv,speaker,speaktopics,stafforfac,startdate,
building,room,phone,email,courses,college,program,institution1,year1,degree1,institution2,year2,degree2,institution3,year3,
degree3,assocs,awards,pubs,interests) VALUES('Firstname','Lastname','Dean','','http://www.nospam.com','','','','','','Yes',
'Anything web related.','Staff','September 1, 2012','Flint Hall','','802-333-3333','[e-mail]','','','','','','','','','','','','','","','','')

I was hoping for an obvious error - guess I'll have to do this the brute force way... meh.

[lorax]

Reading through the docs again at PHP.net I see references to Statements. Now I'm wondering if I need these?

[us3.php.net...]

[LifeinAsia]

I'm not a PHP expert, but I don't think you need that for just a simple query.

[lorax]

It's the only thing I can think of that I haven't tried. I've broken up the cols and matched them with their values. They match. What I'm left wonder is if I need to explicitly define each variable.

[LifeinAsia]

I seriously doubt it's the issue, but try taking out the comma in 'September 1, 2012'.

[lorax]

Worth a try even though it didn't matter. But thanks!

Where or where has our coopster dog gone oh where or where could he be?

[lorax]

Aha! Found it. I had a typo in the quotation marks. Actually two that cancelled each other out but left me with less values than I had columns in the database!

[lorax]

But it didn't insert either .... no error given. Grrr

[omoutop]

your error message is clear... something is altering the counting of columns

first, alter your insert statement to have only non-empty values (you'll be left with 1/4 of the columns, as i see it, easier to check)

then, try escaping the inserted values by:
mysqli_real_escape_string($mysqli, $theValue);

hopefully, you will debug it at that stage

[penders]

For such a big SQL statement consider using PHP's HEREDOC syntax to avoid quote mismatch and string concatenation - much easier to read. Also consider MySQL's alternative INSERT syntax eg. INSERT INTO TableName SET col1 = val1, col2 = val2, ... which avoids column/value mismatch. And also consider parameterized/prepared query which covers all the above and escapes your data.

Check the INSERT statement against your table definition.

[coopster]

instead of typing all the fields horizontally, I do them vertically

I do the same. Then if I have an extensive column list I can copy/paste the options next to the VALUES in a spreadsheet for a comparison. Also, I do not use concatenated statements either. I tend to embed the variables right in the string and I use braces syntax on the variables. I just looked for the syntax quick and found this section of the PHP manual showing what I mean:

// With one exception: braces surrounding arrays within strings allows constants
// to be interpreted
print "Hello {$arr[fruit]}"; // Hello carrot
print "Hello {$arr['fruit']}"; // Hello apple

Resource: [php.net...]

penders solution regarding HEREDOC syntax works very well too. My IDE is set up to show me different colors for different PHP types too. Priceless.

[coopster]

BTW, after copying/pasting your syntax into my editor and laying out the options/values vertically I found quoting mismatches starting with $assocs. Here is the corrected syntax, vertical alignment, with braces ...

$theinsert =  
  "INSERT INTO my_profiles 
   ( 
    fname, 
    lname, 
    title, 
    mrank, 
    website, 
    linkedin, 
    twitter, 
    facebook, 
    photo, 
    cv, 
    speaker, 
    speaktopics, 
    stafforfac, 
    startdate, 
    building, 
    room, 
    phone, 
    email, 
    courses, 
    college, 
    program, 
    institution1, 
    year1, 
    degree1, 
    institution2, 
    year2, 
    degree2, 
    institution3, 
    year3, 
    degree3, 
    assocs, 
    awards, 
    pubs, 
    interests 
  ) 
  VALUES 
  ( 
    '{$fname}', 
    '{$lname}', 
    '{$title}', 
    '{$mrank}', 
    '{$website}', 
    '{$linkedin}', 
    '{$twitter}', 
    '{$facebook}', 
    '', 
    '', 
    '{$speaker}', 
    '{$speaktopics}', 
    '{$stafforfac}', 
    '{$startdate}', 
    '{$building}', 
    '{$room}', 
    '{$phone}', 
    '{$email}', 
    '{$courses}', 
    '{$college}', 
    '{$program}', 
    '{$institution1}', 
    '{$year1}', 
    '{$degree1}', 
    '{$institution2}', 
    '{$year2}', 
    '{$degree2}', 
    '{$institution3}', 
    '{$year3}', 
    '{$degree3}', 
    '{$assocs}', 
    '{$awards}', 
    '{$pubs}', 
    '{$interests}' 
  )" 
;

[lorax]

Hi guys/gals, Thanks. I found those typos in the quotes too. Fixed and THAT issue went away.

Now I'm onto why the script runs - doesn't throw an error - but doesn't actually insert the data into the table.