SEO

When I do "SELECT port, behavior FROM ip_addresses", I get an error
1054: Unknown column 'port' in 'field list'.

Doing a "SHOW COLUMNS FROM ip_addresses", however, shows that I *do*
in fact have a column 'port'. Here's the exact output I get:

Array
(
[Field] => ip_address
[Type] => varchar(8)
[Null] =>
[Key] => PRI
[Default] =>
[Extra] =>
)

Array
(
[Field] => behavior
[Type] => tinyint(2)
[Null] =>
[Key] =>
[Default] => 0
[Extra] =>
)

Array
(
[Field] => port
[Type] => varchar(4)
[Null] => YES
[Key] =>
[Default] => 0
[Extra] =>
)

Array
(
[Field] => last_checked
[Type] => int(11)
[Null] =>
[Key] =>
[Default] => 0
[Extra] =>
)

So why is MySQL simultaneously telling me that 'port' is a column and
isn't? And what can I do to fix it?

If it matters, the MySQL server in question is running on
ipowerweb.com.

On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <terra1024@yahoo.com> wrote:

>When I do "SELECT port, behavior FROM ip_addresses", I get an error
>1054: Unknown column 'port' in 'field list'.
>

Try

"SELECT `port`, behavior FROM ip_addresses"

and see if that works - if so, MySQL is treating "port" as a "reserved
word".

Lee

On Feb 9, 2:09 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <terra1...@yahoo.com> wrote:
>
> >When I do "SELECT port, behavior FROM ip_addresses", I get an error
> >1054: Unknown column 'port' in 'field list'.
>
> Try
>
> "SELECT `port`, behavior FROM ip_addresses"
>
> and see if that works - if so, MySQL is treating "port" as a "reserved
> word".
>
> Lee

Just tried it. Still get the same error

On Fri, 09 Feb 2007 21:44:37 +0100, yawnmoth <terra1024@yahoo.com> wrote:

> On Feb 9, 2:09 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
>> On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <terra1...@yahoo.com> wrote:
>>
>> >When I do "SELECT port, behavior FROM ip_addresses", I get an error
>> >1054: Unknown column 'port' in 'field list'.
>>
>> Try
>>
>> "SELECT `port`, behavior FROM ip_addresses"
>>
>> and see if that works - if so, MySQL is treating "port" as a "reserved
>> word".
>>
>> Lee
>
> Just tried it. Still get the same error
>

What does a SHOW CREATE TABLE `ip_adresses` show?

--
Rik Wasmus

On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> <snip>
> What does a SHOW CREATE TABLE `ip_adresses` show?

[Table] => phpbb_proxies
[Create Table] => CREATE TABLE `ip_addresses` (
`ip_address` varchar(8) NOT NULL default '',
`behavior` tinyint(2) NOT NULL default '0',
`port ` varchar(4) default '0',
`last_checked` int(11) NOT NULL default '0',
PRIMARY KEY (`ip_address`)
) TYPE=MyISAM

On Sat, 10 Feb 2007 02:18:52 +0100, yawnmoth <terra1024@yahoo.com> wrote:

> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>> <snip>
>> What does a SHOW CREATE TABLE `ip_adresses` show?
>
> [Table] => phpbb_proxies
> [Create Table] => CREATE TABLE `ip_addresses` (
> `ip_address` varchar(8) NOT NULL default '',
> `behavior` tinyint(2) NOT NULL default '0',
> `port ` varchar(4) default '0',

Did you spot the space (or maybe it's another whitespace character)?

SELECT `port ` FROM `phpbb_proxies`

Another fine reason to always use backticks
--
Rik Wasmus

On Sat, 10 Feb 2007 02:41:12 +0100, Rik <luiheidsgoeroe@hotmail.com> wrote:

> On Sat, 10 Feb 2007 02:18:52 +0100, yawnmoth <terra1024@yahoo.com> wrote:
>
>> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>>> <snip>
>>> What does a SHOW CREATE TABLE `ip_adresses` show?
>>
>> [Table] => phpbb_proxies
>> [Create Table] => CREATE TABLE `ip_addresses` (
>> `ip_address` varchar(8) NOT NULL default '',
>> `behavior` tinyint(2) NOT NULL default '0',
>> `port ` varchar(4) default '0',
>
> Did you spot the space (or maybe it's another whitespace character)?
>
> SELECT `port ` FROM `phpbb_proxies`
>
> Another fine reason to always use backticks

Hmmz, I'm actually curious how this came to be. My MySQL 5.0 won't let me
create it.

If you're still having troubles selecting from it with a space I suspect
it's some weird character. Easiest way to solve it:

CREATE TABLE `ip_addresses_temp` (
`ip_address` varchar(8) NOT NULL default '',
`behavior` tinyint(2) NOT NULL default '0',
`port` varchar(4) default '0',
`last_checked` int(11) NOT NULL default '0',
PRIMARY KEY (`ip_address`)
) TYPE=MyISAM

INSERT INTO `ip_addresses_temp` SELECT * FROM `ip_addresses`

DROP TABLE `ip_addresses`

RENAME TABLE ip_addresses_temp TO ip_addresses

--
Rik Wasmus

On Feb 9, 7:55 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Sat, 10 Feb 2007 02:41:12 +0100, Rik <luiheidsgoe...@hotmail.com> wrote:
> > On Sat, 10 Feb 2007 02:18:52 +0100,yawnmoth<terra1...@yahoo.com> wrote:
>
> >> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> >>> <snip>
> >>> What does a SHOW CREATE TABLE `ip_adresses` show?
>
> >> [Table] => phpbb_proxies
> >> [Create Table] => CREATE TABLE `ip_addresses` (
> >> `ip_address` varchar(8) NOT NULL default '',
> >> `behavior` tinyint(2) NOT NULL default '0',
> >> `port ` varchar(4) default '0',
>
> > Did you spot the space (or maybe it's another whitespace character)?

Nope. Nice catch!

>
> > SELECT `port ` FROM `phpbb_proxies`
>
> > Another fine reason to always use backticks
>
> Hmmz, I'm actually curious how this came to be. My MySQL 5.0 won't let me
> create it.

I was using MySQL 4.0.18.

Anyway, thanks!