I'm stumped - Adding new records! 4

[d0nny]

I've been mulling over this for about a week now and I really cannot see how this is failing.

I have quite a simple database which I can list the records of. What I am attempting to do now is to add new records to the table. I've done this before and I'm really just reusing the code from previous installations, but this just doesn't want to play ball.

I have a form for inputting new data which I then POST to a new script to save into the table.

So the start of the ADD script sets the POST variables sent from my form. Then I add the SQL INSERT statement using these variables (or some of them) to insert into the table. The problem is I constantly get this error:

Database ERROR: 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 'desc) VALUES ( , 1, testing)' at line 1

My INSERT statement looks like this:

$sql = "INSERT INTO po (supplier_id,createdby_id,desc) VALUES ( $s_id, 1, $desc)";

Any ideas?
Let me know what other info you might require. I will be very pleased if someone can put their finger on this!

 

[towerbase]

I would guess that $desc is a string variable because the error message shows testing. Strings should be delimited by quotes.

E.g.

INSERT INTO tb (fieldname) VALUES('testing')

Andrew
Hampshire, UK

 

[shavenlunatic]

i generally use

$sql = "INSERT INTO po (supplier_id,createdby_id,desc) VALUES (".$s_id.", 1, ".$desc.")";


but for all I know that could be a terrible case of bad practice

________
clueless

 

[d0nny]

My three fields in the table are:
INT, INT, VARCHAR in that order for the fields I have listed.

And towerbase, my INSERT statement contains variables I have set, so do I need to delimit my variable like so:
'$desc'?

 

[shavenlunatic]

INT, INT, VARCHAR in that order for the fields I have listed."

ok, then the following ammended query should post the data with no problems (as long as the data held in the variables is ok)

$sql = "INSERT INTO po (supplier_id,createdby_id,desc) VALUES (".$s_id.",1,'".$desc."')";

________
clueless

 

[towerbase]

Yes - if you read the error message it shows that the delimiters are missing!

desc) VALUES ( , 1, testing)

Also note that your variable $s_id appears to be null or a zero length string so you need to sort that out too.



Andrew
Hampshire, UK

 

[d0nny]

Actually, I've tried all your solutions and they don't work.

I starting thinking that the error starts at the 'desc bit, so I thought it might have a problem inserting data into the desc field. I took that out of the INSERT statement and it work (so I'm only inputting 2 variables to my table).
So there is something wrong with the desc field in my DB I'm thinking.

DESC is setup as this:
desc,varchar(255),latin1_general_ci,NULL and the action is 'fulltext'

 

[towerbase]

What do you mean by they don't work? Does PHP complain? Does MySQL complain? Does PHP complain? Does the database get updated incorrectly? What is the actual error message displayed or the evidence that it doesn't work?

What is the actual value of $sql before you use it to update your MySQL database?



Andrew
Hampshire, UK

 

[southbeach]

This kind of error normally happen due to:
1) data type miss-match
2) unterminated quote
3) invalid data type

What I mean is, whatever you attempt to write to your tables must match what you told the table the field type is. Also, if you set the field as NOT NULL and try to add a record with NULL value, you will get an error.

I am in the habit of enclosing all string data in quotes. If the string is blank, you are in a sense simply adding a blank space (not same as NULL).

So, I would write the query like as posted by shavenlunatic above. Additionally, if I expect a value of at least zero on a numeric field, I add 0 to the variable prior to writing data out.

That said, I would venture say that you have an error some where since I do not think you expect to have blank or zero supplier IDs. If zero is valid (none entered), then add zero to the variable to avoid the error you are getting.

$sql = "INSERT INTO po (supplier_id,createdby_id,desc) VALUES (".$s_id.",1,'".$desc."')";

--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[d0nny]

The error is listed above.
The $sql value is also listed above.
I've tried your alternative $sql statement with the same error.
There is definately a problem with the desc field. As I say, I took it out and the INSERT work fine. I added it back in and it failed with the same error as above.

I noted that my table has an Index for desc as well as an Index for my id field, so I deleted that, to no avail.

 

[towerbase]

Have you put quotes around your $desc?

Your INSERT statement in your original post is clearly incorrect because there are no quotes around $desc. Your INSERT statement is also incorrect because the variable $s_id contains nothing (either null or an empty string).

Please fix those problems and if it still fails then show us the value of $sql.

Andrew
Hampshire, UK

 

[d0nny]

Oh, OK, sorry, I added your new statement like this:

$sql = "INSERT INTO po (supplier_id,createdby_id,desc) VALUES (".$s_id.",1,'".$desc."')";

The error is this now:

Database ERROR: 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 'desc) VALUES (188,1,'dfgdfgdfgdfgdfgd')' at line 1

Its seems the variables are being recognised, but there is a problem with the desc field.

 

[IPGuru]

my usual aproach to this sort of problem is :-

Echo $sql;

Then copy the query in to phpMyAdmin this enable s me to tweek the query untill it works then correct my PHP code.

it usualt turns out to be somthing totaly stupid missing quote or wrong field name or my usual head scratched creating the Queriy in $Query then submiting it with mysql_query($query)

 

[jpadie]

you can't use desc as a field name without identifying it as such. because it is a reserved word. if you must use it, enclose it in backticks (`)

 

[d0nny]

Ah, that might be where my issue is, that desc is a reserved word!
At the risk of screwing up all my other scripts, I'll try changing this field name to say description

 

[Foamcow]

Awww, I was dying to type my response that it was because desc is a reserved SQL word. I hurridly scroll down only to find jpadie got in there first!


Desc is a reserved keyword. Use a different column name or put backticks around it - actually, you may be able to use single quotes.

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[d0nny]

I changed the field name and everything worked fine.
So if I change it back to desc, I just need to put backticks around the field name in my INSERT statement, right?

 

[towerbase]

It's because you are using desc which is a reserved word. If you enclose it in tick characters it should be okay:

$sql = "INSERT INTO po (supplier_id,createdby_id,[b]`[/b]desc[b]`[/b]) VALUES (".$s_id.",1,'".$desc."')";

Andrew
Hampshire, UK

 

[towerbase]

Drat!

jpadie and Foamcow got there before me!

Andrew
Hampshire, UK

 

[d0nny]

Did the backticks and it worked a dream!!!

jpadie does it again!!! Go to the top of the class.