SQL questions

May 252012

If I have a 2 character varchar field, and I want to insert
the value ’6′

into it, I would always need to use quotes, right?

INSERT INTO table SET field=’6′ WHERE foo=’something’

And, if it’s a 2 character field, will that always be
inserted as “06″?

–

Murray — ICQ 71997575

Adobe Community Expert

(If you *MUST* email me, don’t LAUGH when you do so!)

==================

http://www.projectseven.com/go
- DW FAQs, Tutorials & Resources

http://www.dwfaq.com – DW FAQs,
Tutorials & Resources

==================

|||
Murray *ACE* wrote:

> If I have a 2 character varchar field, and I want to
insert the value

> ’6′ into it, I would always need to use quotes, right?

>

> INSERT INTO table SET field=’6′ WHERE foo=’something’

>

> And, if it’s a 2 character field, will that always be
inserted as “06″?

No, it would insert “6″, but if you used char as the field
type it would

insert “6 “.

When you use varchar or char it doesn’t know that its a
number, it just

sees a character.

Steve

|||
Typo in your response?

> No, it would insert “6″, but if you used char as the
field type it would

> insert “6 “.

Anyhow, then why am I getting this error message?

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 = ‘My

Birthday!’ WHERE (month = ’06′ AND day = ’05′ AND year =
’2008′)’ at line 1

The full line is -

$sql = “UPDATE calendar SET desc = ‘” . $_POST['message'] .
“‘ WHERE (month

= ” . $_POST['mm'] . ” AND day = ” . $_POST['dd'] . ” AND
year = ” .

$_POST['yy'] . “)”;

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jeaj$6ft$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> If I have a 2 character varchar field, and I want to
insert the value ’6′

>> into it, I would always need to use quotes, right?

>>

>> INSERT INTO table SET field=’6′ WHERE
foo=’something’

>>

>> And, if it’s a 2 character field, will that always
be inserted as “06″?

>

> No, it would insert “6″, but if you used char as the
field type it would

> insert “6 “.

>

> When you use varchar or char it doesn’t know that its a
number, it just

> sees a character.

>

> Steve

|||
Murray *ACE* wrote:

> Typo in your response?

>

>> No, it would insert “6″, but if you used char as the
field type it

>> would insert “6 “.

The second one has a space after the 6

> Anyhow, then why am I getting this error message?

>

> 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 =

> ‘My Birthday!’ WHERE (month = ’06′ AND day = ’05′ AND
year = ’2008′)’ at

> line 1

>

> The full line is -

>

> $sql = “UPDATE calendar SET desc = ‘” .
$_POST['message'] . “‘ WHERE

> (month = ” . $_POST['mm'] . ” AND day = ” . $_POST['dd']
. ” AND year =

> ” . $_POST['yy'] . “)”;

>

You might possibly be using reserved words, like month, day
and year.

But why are you storing numbers as a character? Surely you
want to be

using integers? And if you are using integers, then you need
to omit the

single quotes from the values during the update.

Steve

|||
The fields are now INT fields.

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 = ‘My

Birthday!’ WHERE (month = 6 AND day = 5 AND year = 2008)’ at
line 1

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jf18$7hp$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> Typo in your response?

>>

>>> No, it would insert “6″, but if you used char as
the field type it would

>>> insert “6 “.

>

> The second one has a space after the 6

>

>> Anyhow, then why am I getting this error message?

>>

>> 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 = ‘My

>> Birthday!’ WHERE (month = ’06′ AND day = ’05′ AND
year = ’2008′)’ at line

>> 1

>>

>> The full line is -

>>

>> $sql = “UPDATE calendar SET desc = ‘” .
$_POST['message'] . “‘ WHERE

>> (month = ” . $_POST['mm'] . ” AND day = ” .
$_POST['dd'] . ” AND year = ”

>> . $_POST['yy'] . “)”;

>>

>

> You might possibly be using reserved words, like month,
day and year. But

> why are you storing numbers as a character? Surely you
want to be using

> integers? And if you are using integers, then you need
to omit the single

> quotes from the values during the update.

>

> Steve

|||
Murray *ACE* wrote:

> The fields are now INT fields.

>

> 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 =

> ‘My Birthday!’ WHERE (month = 6 AND day = 5 AND year =
2008)’ at line 1

Hmmm, have you tried without the brackets?

Steve

|||
Murray *ACE* wrote:

> The fields are now INT fields.

>

> 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 =

> ‘My Birthday!’ WHERE (month = 6 AND day = 5 AND year =
2008)’ at line 1

>

It could be the line before this…

Steve

|||
The parentheses?

Yes – same message….

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jfo7$8av$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> The fields are now INT fields.

>>

>> 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 = ‘My

>> Birthday!’ WHERE (month = 6 AND day = 5 AND year =
2008)’ at line 1

>

> Hmmm, have you tried without the brackets?

>

> Steve

|||
I’m thinking day and year may be reserved words. I’ll try
that.

|||
Murray *ACE* wrote:

> I’m thinking day and year may be reserved words. I’ll
try that.

In MSSQL day, month and year are reserved words, as they are
functions.

Steve

|||
That’s what I meant (month,year). However, this is an open
source

application, and the instructions say -

* CREATE TABLE `calendar` (

* `id` INT NOT NULL AUTO_INCREMENT ,

`* day` VARCHAR( 2 ) NOT NULL ,

* `month` VARCHAR( 2 ) NOT NULL ,

* `year` VARCHAR( 4 ) NOT NULL ,

* `link` VARCHAR( 255 ) NOT NULL ,

* `desc` TEXT NOT NULL ,

* PRIMARY KEY ( `id` )

* );

*

in describing how to create the table.

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jgaf$8u4$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> I’m thinking day and year may be reserved words.
I’ll try that.

>

> In MSSQL day, month and year are reserved words, as they
are functions.

>

> Steve

|||
See here -

http://web-developer.sitecritic.net/

|||
Murray *ACE* wrote:

> That’s what I meant (month,year). However, this is an
open source

> application, and the instructions say -

>

> * CREATE TABLE `calendar` (

> * `id` INT NOT NULL AUTO_INCREMENT ,

> `* day` VARCHAR( 2 ) NOT NULL ,

> * `month` VARCHAR( 2 ) NOT NULL ,

> * `year` VARCHAR( 4 ) NOT NULL ,

> * `link` VARCHAR( 255 ) NOT NULL ,

> * `desc` TEXT NOT NULL ,

> * PRIMARY KEY ( `id` )

> * );

> *

>

> in describing how to create the table.

Hmmm, I checked the MySQL reserved words and these aren’t, so
I don’t

know what else it could be.

|||
I’m verklempt!

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jgu5$9l1$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> That’s what I meant (month,year). However, this is
an open source

>> application, and the instructions say -

>>

>> * CREATE TABLE `calendar` (

>> * `id` INT NOT NULL AUTO_INCREMENT ,

>> `* day` VARCHAR( 2 ) NOT NULL ,

>> * `month` VARCHAR( 2 ) NOT NULL ,

>> * `year` VARCHAR( 4 ) NOT NULL ,

>> * `link` VARCHAR( 255 ) NOT NULL ,

>> * `desc` TEXT NOT NULL ,

>> * PRIMARY KEY ( `id` )

>> * );

>> *

>>

>> in describing how to create the table.

>

> Hmmm, I checked the MySQL reserved words and these
aren’t, so I don’t know

> what else it could be.

|||
Murray *ACE* wrote:

> I’m verklempt!

Have you tried entering the update statement directly in
myphpadmin or

whatever mysql webpage admin you have? That will tell you if
its your

SQL syntax or a problem with your code on the page.

Steve

|||
Yes, I have. It bounces with the same message, so it would
have to be the

SQL!

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2ji60$atk$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> I’m verklempt!

>

> Have you tried entering the update statement directly in
myphpadmin or

> whatever mysql webpage admin you have? That will tell
you if its your SQL

> syntax or a problem with your code on the page.

>

> Steve

|||
If I convert that into a SELECT line -

SELECT * FROM calendar where month=’06′ AND day=’05′ AND
year=’2008′

it works. I’m stumped.

“Murray *ACE*” <forums@HAHAgreat-web-sights.com> wrote
in message

news:g2jimv$bji$1@forums.macromedia.com…

> Yes, I have. It bounces with the same message, so it
would have to be the

> SQL!

>

> —

> Murray — ICQ 71997575

> Adobe Community Expert

> (If you *MUST* email me, don’t LAUGH when you do so!)

> ==================

>
http://www.projectseven.com/go
- DW FAQs, Tutorials & Resources

>
http://www.dwfaq.com – DW FAQs,
Tutorials & Resources

> ==================

>

>

> “Dooza” <doozadooza@gmail.com> wrote in message

> news:g2ji60$atk$1@forums.macromedia.com…

>> Murray *ACE* wrote:

>>> I’m verklempt!

>>

>> Have you tried entering the update statement
directly in myphpadmin or

>> whatever mysql webpage admin you have? That will
tell you if its your SQL

>> syntax or a problem with your code on the page.

>>

>> Steve

>

|||
Murray *ACE* wrote:

> 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 =

> ‘My Birthday!’ WHERE (month = 6 AND day = 5 AND year =
2008)’ at line 1

DESC is a reserved word.

–

David Powers, Adobe Community Expert

Author, “The Essential Guide to Dreamweaver CS3″ (friends of
ED)

Author, “PHP Solutions” (friends of ED)

http://foundationphp.com/

|||
Murray *ACE* wrote:

> If I convert that into a SELECT line -

>

> SELECT * FROM calendar where month=’06′ AND day=’05′ AND
year=’2008′

>

> it works. I’m stumped.

Thats cos it was the field DESC that was the reserved word as
David has

just observed. I am surprised that you were able to create
the table

with the reserved word as a column.

Steve

|||
David Powers wrote:

> Murray *ACE* wrote:

>> 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 = ‘My Birthday!’ WHERE (month = 6 AND day
= 5 AND year =

>> 2008)’ at line 1

>

> DESC is a reserved word.

>

Ah ha, the error message was pointing right at it!

Steve

|||
Oh, good grief!

“Dooza” <doozadooza@gmail.com> wrote in message

news:g2jl97$e99$2@forums.macromedia.com…

> David Powers wrote:

>> Murray *ACE* wrote:

>>> 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 =

>>> ‘My Birthday!’ WHERE (month = 6 AND day = 5 AND
year = 2008)’ at line 1

>>

>> DESC is a reserved word.

>>

>

> Ah ha, the error message was pointing right at it!

>

> Steve

|||
Murray *ACE* wrote:

> Oh, good grief!

The doctor is in. 5 cents please, Charlie Brown.

And what’s worse, it’s that way in the DISTRIBUTION!

“David Powers” <david@example.com> wrote in message

news:g2jq7o$jju$2@forums.macromedia.com…

> Murray *ACE* wrote:

>> Oh, good grief!

>

> The doctor is in. 5 cents please, Charlie Brown.

>

> —

> David Powers, Adobe Community Expert

> Author, “The Essential Guide to Dreamweaver CS3″
(friends of ED)

> Author, “PHP Solutions” (friends of ED)

>
http://foundationphp.com/

|||
.oO(Dooza)

>Murray *ACE* wrote:

>> If I convert that into a SELECT line -

>>

>> SELECT * FROM calendar where month=’06′ AND day=’05′
AND year=’2008′

>>

>> it works. I’m stumped.

>

>Thats cos it was the field DESC that was the reserved
word as David has

>just observed. I am surprised that you were able to
create the table

>with the reserved word as a column.

In the CREATE statement the name of the “descr” column was
quoted with

backticks, which is MySQL’s non-standard way to use reserved
words in

identifiers.

8.2. Schema Object Names

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Of course you can do the same in other queries:

UPDATE calendar

SET `desc` = …

WHERE …

Micha

|||
OMG – I didn’t notice that. Thanks so much, Micha!

“Michael Fesser” <netizen@gmx.de> wrote in message

news:mp5r44h3e5nkbu1d5s63sdmj280conscf0@4ax.com…

> .oO(Dooza)

>

>>Murray *ACE* wrote:

>>> If I convert that into a SELECT line -

>>>

>>> SELECT * FROM calendar where month=’06′ AND
day=’05′ AND year=’2008′

>>>

>>> it works. I’m stumped.

>>

>>Thats cos it was the field DESC that was the reserved
word as David has

>>just observed. I am surprised that you were able to
create the table

>>with the reserved word as a column.

>

> In the CREATE statement the name of the “descr” column
was quoted with

> backticks, which is MySQL’s non-standard way to use
reserved words in

> identifiers.

>

> 8.2. Schema Object Names

>
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

>

> Of course you can do the same in other queries:

>

> UPDATE calendar

> SET `desc` = …

> WHERE …

>

> Micha

|||
.oO(Murray *ACE*)

>OMG – I didn’t notice that. Thanks so much, Micha!

You’re welcome.

Micha

|||
On 09 Jun 2008 in macromedia.dreamweaver.appdev, Murray *ACE*
wrote:

> LOL – how remarkable dumb that usage was!

>

> And what’s worse, it’s that way in the DISTRIBUTION!

Don’t feel too bad. I did the same thing to myself once -
created a

MySQL database with a description field called – you guessed
it – ‘desc’.

Still running, still a PITA.

–

Joe Makowiec

http://makowiec.net/

Email:
http://makowiec.net/contact.php

|||
Murray *ACE* wrote:

> LOL – how remarkable dumb that usage was!

>

> And what’s worse, it’s that way in the DISTRIBUTION!

As Micha pointed out, using backticks around database and
column names

is the way to use reserved words and illegal characters in
them. There’s

a large number of reserved words in MySQL, so it’s easy to be
caught

out. However, using DESC as a column name is plain stupid.
Not only is

it a very common word in SQL queries, it also tells you
nothing about

the contents of the column. I presume that it’s meant to be
an

abbreviation for “description”, but it simply obfuscates the
code.

“David Powers” <david@example.com> wrote in message

news:g2kagf$80k$1@forums.macromedia.com…

> Murray *ACE* wrote:

>> LOL – how remarkable dumb that usage was!

>>

>> And what’s worse, it’s that way in the DISTRIBUTION!

>

> As Micha pointed out, using backticks around database
and column names is

> the way to use reserved words and illegal characters in
them. There’s a

> large number of reserved words in MySQL, so it’s easy to
be caught out.

> However, using DESC as a column name is plain stupid.
Not only is it a

> very common word in SQL queries, it also tells you
nothing about the

> contents of the column. I presume that it’s meant to be
an abbreviation

> for “description”, but it simply obfuscates the code.

>

> —

> David Powers, Adobe Community Expert

> Author, “The Essential Guide to Dreamweaver CS3″
(friends of ED)

> Author, “PHP Solutions” (friends of ED)

>
http://foundationphp.com/

|||
.oO(David Powers)

>However, using DESC as a column name is plain stupid. Not
only is

>it a very common word in SQL queries, it also tells you
nothing about

>the contents of the column. I presume that it’s meant to
be an

>abbreviation for “description”, but it simply obfuscates
the code.

But also keeps the queries shorter.

Micha

PS: I use “descr” …

Related posts brought to you by Yet Another Related Posts Plugin.

free icons download

SQL questions

Leave a Reply Cancel reply

Translate this Page

Recent Posts

Categories

Blogroll