Mysql querie isn't working

Valium

Hi all,

First of all, I'm a newbie at this so bare with me, I'm making a database driven site for my parents shop and it ain't working 🙁

this is the query I'm trying to use

mysql_query ("insert into besteloostkamp (datum, b1103, b1101, b1104, b1105, b1014, b1013, b1015, b1018, b1016, b1017, b2103, b2101, b2105, b1106, b1107, b2106, b3102, b3101, b3013, b1050, b1051, b1701, b1551, b1554, b1703, b1560, b1501, b1511, b1605, b1570, b1571, b1622, b1621, b1620, b3020, b3021, b3022, b6034, b6031, b5001, b5002, b5003, b5004, b5005, b5006, b5007, b5009, b5008, b5010, b5014, b5011, b5012, b5015, b5013, b1003, b1001, b1004, b1005, b2003, b2001, b2005, b1006, b1007, b2006, b3002, b3001, b1601, b1553, b1556, b1703b, b1503, b1513, b1605b, b6032, b1552, b1555, b1502, b1502b, b6033, b6001, b6002, b6005, b6007, b6004, b6006, b6101, b6301, b6201, b6401, b6207, b7001, b7006, b7011, b7016, b7021, b7100, b7105, b7026, b7095, b7031, b7041, b7120, b7080, b7065, b7115, b8002, b8802, b8081, b8091, b8071, b8101, b8042, b8045, b8171, b8531, b8545, b8111, b8062, b8121, b8211, b7141, b7131, b7151, b8561, b8501, b8151, b7049, b7050, b2501, b2502, b7002, b7007, b7012, b7017, b7022, b7101, b7106, b7027, b7096, b7032, b7042, b7121, b7081, b7066, b7116, b8004, b8804, b8082, b8092, b8072, b8102, b8044, b8046, b8173, b8541, b8112, b8063, b8122, b8213, b8562, b8502, b8152, b7003, b7008, b7013, b7018, b7023, b7102, b7108, b7028, b7097, b7034, b7043, b7122, b7082, b7067, b7117, b8005, b8805, b8083, b8093, b8073, b8103, b8047, b8174, b8113, b8064, b8123, b8214, b8563, b8503, b8153)
values ($datum, $b1103, $b1101, $b1104, $b1105, $b1014, $b1013, $b1015, $b1018, $b1016, $b1017, $b2103, $b2101, $b2105, $b1106, $b1107, $b2106, $b3102, $b3101, $b3013, $b1050, $b1051, $b1701, $b1551, $b1554, $b1703, $b1560, $b1501, $b1511, $b1605, $b1570, $b1571, $b1622, $b1621, $b1620, $b3020, $b3021, $b3022, $b6034, $b6031, $b5001, $b5002, $b5003, $b5004, $b5005, $b5006, $b5007, $b5009, $b5008, $b5010, $b5014, $b5011, $b5012, $b5015, $b5013, $b1003, $b1001, $b1004, $b1005, $b2003, $b2001, $b2005, $b1006, $b1007, $b2006, $b3002, $b3001, $b1601, $b1553, $b1556, $b1703b, $b1503, $b1513, $b1605b, $b6032, $b1552, $b1555, $b1502, $b1502b, $b6033, $b6001, $b6002, $b6005, $b6007, $b6004, $b6006, $b6101, $b6301, $b6201, $b6401, $b6207, $b7001, $b7006, $b7011, $b7016, $b7021, $b7100, $b7105, $b7026, $b7095, $b7031, $b7041, $b7120, $b7080, $b7065, $b7115, $b8002, $b8802, $b8081, $b8091, $b8071, $b8101, $b8042, $b8045, $b8171, $b8531, $b8545, $b8111, $b8062, $b8121, $b8211, $b7141, $b7131, $b7151, $b8561, $b8501, $b8151, $b7049, $b7050, $b2501, $b2502, $b7002, $b7007, $b7012, $b7017, $b7022, $b7101, $b7106, $b7027, $b7096, $b7032, $b7042, $b7121, $b7081, $b7066, $b7116, $b8004, $b8804, $b8082, $b8092, $b8072, $b8102, $b8044, $b8046, $b8173, $b8541, $b8112, $b8063, $b8122, $b8213, $b8562, $b8502, $b8152, $b7003, $b7008, $b7013, $b7018, $b7023, $b7102, $b7108, $b7028, $b7097, $b7034, $b7043, $b7122, $b7082, $b7067, $b7117, $b8005, $b8805, $b8083, $b8093, $b8073, $b8103, $b8047, $b8174, $b8113, $b8064, $b8123, $b8214, $b8563, $b8503, $b8153)
");
echo mysql_error();

when I then try it i get the following error :

You have an error in your SQL syntax near ' 321, 1, 321, 1, 321, 321, 321, 21, 321, 321, 321, 31, 321, , , , , , , , , , , ' at line 2

Could it be that I'm trying to put to much data in it at once?

I can't find the syntax error in that query so if anybody could help me?

Oh , I tried to find around were the error occured and the values the error shows tell me that the erros is somewhere around the following line :

$b1003, $b1001, $b1004, $b1005, $b2003, $b2001, $b2005

If anybody could help me I would be very gratfull thx in advance

Greetz

P.S. just excuse me for the long post, I just wanted to include the complete query ...

jshea

What you should have also included in your post is the "type" of data that your variables make up (are they text, decimal, numbers, what?)

most likely, you're trying to insert data into a table that wasn't designed to except the data. ie. text into a decimal field, etc.

without more info it's hard to tell what the problem is.

Valium

sorry bout that,

all data sent is numbers (accounting stuff and such) all integer class numbers except date offcourse which is .... a date hehe ...

hope this helps

jshea

The only other help I can offer is that I use a different syntax for queries when inserting:

mysql_query("INSERT into mytable VALUES('$variable1',$variable2','$variable3',...);

not sure if it is proper or not, but I find you don't need the field names at all, as long as the variables you're trying to insert are in the same order as your table fields.

That may at least cut down on the code a little.

The only other time I've had problems inserting always turned out to be something stupid. (something I would have caught with a fresh mind.) Maybe you've been working on it too long?

Valium

Hi thx again for your reply,

but the values that I'm posting are not the only values in the table. The table consists bout 200 different values and when inserting new values it's always about 100 to 150 values that need to be inserted (random picked) since mostly it's salesfigures on products. So I really need to specify the fields. Unless my parents shop would suddenly boom and they would sell all products at least once a day but that would be .... wishfull thinking right 🙂

anyway thanks for the pointers and I've been working on this for a couple of hours now, maybe it's time for a break.

Greetz

jshea

no problem, sorry I couldn't help more.

Depending on how you're posting to mysql (html form. phpMyAdmin, shell access) you may still be able to exclude the field names.

If you're getting the numbers via POST you can do

mysql_query("INSERT into mytable VALUES('$variable1','$variable2','$variable3','$variable4','','','','','','','','','','','','','','','','','','','','','','','','','','');

if there's no value for the user's input, nothing will be inserted.

Don't take my word for it though because I'm not positive.

MrAlaska

Originally posted by jshea
not sure if it is proper or not, but I find you don't need the field names at all, as long as the variables you're trying to insert are in the same order as your table fields.

You can get away with not putting the field names in, but it is good practice to have them there. You never know when you might need to insert another column in the future and you don't want to go back fixing all your old code.

As far as the problem with this sql statement, I don't see it right off. You might get a clue by echoing your statement back after you have built it, that will sometimes expose a problem.

$sql="insert...values...etc...";
mysql_query($sql);
echo $sql;

Valium

Something very strange is happening here,

by just changing the values that need to be inputted the error message changes.

first it was line 16 then it was line 28 and now by changing all values to 1 it is line 30????

what's going on here?????????,

siwis

Simple off the wall suggestion:

Have you tried placing all your variables into single quotes???

ie values ('$datum', '$b1103', '$b1101', '$b1104'
etc?

Valium

Yes I tried placing them in single quotes, tried withoud, tried with double quotes( although I know this won't work) ...

No go ..

🙁

MrAlaska

Did you try echoing the sql statement back like I suggested? That along with the mysql error will illuminate the vast majority of errors.

Valium

Yes I tried that, the echo came out perfectly clean, all data was there and in the perfect order too?

But I've changed strategie and tried to break everything up into smaller tables but I've run into another problem with that.

I put up another post here with the specific errors on that maybe you can have a look there too?

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10207912

Thx for the replies 🙂

MrAlaska

I'll take a look at the other thread, but regarding your sql error it seems it wouldn't generate a syntax error if everything is in perfect order. Just for grins, capture the sql statement and post it along with the error it generated. Sometimes you get so wrapped in your code you miss the obvious, a new pair of eyes can often spot something wrong that you have grown used to being there.

Valium

Well I'll search for the code and regenerate the error, I put it somewhere in those dark little corners on my harddrives, can't seem to find it back right away but I'll get to it as soon as I find it. Didn't think I'd get back to it since I change everything to using smaller tables lol.

Greetz

mattr

Dear lord, that database design is, well, HORRIBLE!

MrAlaska

Originally posted by Valium
Well I'll search for the code and regenerate the error, I put it somewhere in those dark little corners on my harddrives, can't seem to find it back right away but I'll get to it as soon as I find it. Didn't think I'd get back to it since I change everything to using smaller tables lol.

Greetz

NO! If you have abandoned this code altogether, don't bring it back. We'll be just fine working on the smaller tables.