Welcome to WebmasterWorld Guest from 54.91.71.108

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Insert statement help w/error

I'm missing something obvious

     
4:17 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts: 7577
votes: 4


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]

4:47 pm on Mar 5, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5832
votes: 182


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. ;)
5:03 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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','bob@nospam.com','','','','','','','','','','','','','","','','')


I was hoping for an obvious error - guess I'll have to do this the brute force way... meh.
6:35 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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

[us3.php.net...]
6:41 pm on Mar 5, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5832
votes: 182


I'm not a PHP expert, but I don't think you need that for just a simple query.
7:16 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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.
7:25 pm on Mar 5, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5832
votes: 182


I seriously doubt it's the issue, but try taking out the comma in 'September 1, 2012'.
7:27 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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?
8:27 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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!
8:30 pm on Mar 5, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


But it didn't insert either .... no error given. Grrr
10:17 am on Mar 6, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Oct 15, 2004
posts:942
votes: 0


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
3:25 pm on Mar 6, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3127
votes: 1


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.
5:21 pm on Mar 6, 2014 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2


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.
5:27 pm on Mar 6, 2014 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2


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}'
)"
;
6:26 pm on Mar 6, 2014 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members