SQL annoyance

[Dan]

Hi there, quick and annoying SQL problem, was debating about whether to post
it here or DBI, I figured DBI was for DBI related issues, and this is just a
plain SQL problem. Excuse me if I'm wrong though, but thought I'd ask
anyway.
I have an SQL statement as follows,
SELECT * FROM memodata WHERE to=1 AND readdate=sent ORDER BY id ASC
which fails giving an error on the to=1 part.
I've tried doing that statement without the 'to=1 AND' part, and it works
fine returning all and more of the data that I want. However when i ask it
to be more specific, it fails. I've also tried
SELECT * FROM memodata WHERE to=1
which also fails on the to=1 part. I've also tried putting in other column
names, like from=1, but that fails too. And I'm totally clueless as to why,
as the syntax follows the manual on mysql.com exactly.
Any pointers as to where I'm going wrong?
Many thanks, and apologies once again if I should have posted this
elsewhere. I'll know next time if I should ;)
-Dan

[Philipp Traeder]

Hi Dan,

On Sun, 2004-01-04 at 03:34, dan wrote:

> I have an SQL statement as follows,
> SELECT * FROM memodata WHERE to=1 AND readdate=sent ORDER BY id ASC
> which fails giving an error on the to=1 part.
> I've tried doing that statement without the 'to=1 AND' part, and it works
> fine returning all and more of the data that I want. However when i ask it
> to be more specific, it fails. I've also tried
> SELECT * FROM memodata WHERE to=1
> which also fails on the to=1 part. I've also tried putting in other column
> names, like from=1, but that fails too. And I'm totally clueless as to why,
> as the syntax follows the manual on mysql.com exactly.
> Any pointers as to where I'm going wrong?

just wildly speculating, but I'd guess that "to" and "from" are typical
string values (and not integers) - have you tried something like

SELECT * FROM memodata WHERE to = '1'

BTW: You can find out the column types by typing "describe memodata;" on
your mysql command line.

HTH,

Philipp

[Rob Dixon]

Dan wrote:

>
> Hi there, quick and annoying SQL problem, was debating about whether to post
> it here or DBI, I figured DBI was for DBI related issues, and this is just a
> plain SQL problem. Excuse me if I'm wrong though, but thought I'd ask
> anyway.
>
> I have an SQL statement as follows,
>
> SELECT * FROM memodata WHERE to=1 AND readdate=sent ORDER BY id ASC
>
> which fails giving an error on the to=1 part.
>
> I've tried doing that statement without the 'to=1 AND' part, and it works
> fine returning all and more of the data that I want. However when i ask it
> to be more specific, it fails. I've also tried
>
> SELECT * FROM memodata WHERE to=1
>
> which also fails on the to=1 part. I've also tried putting in other column
> names, like from=1, but that fails too. And I'm totally clueless as to why,
> as the syntax follows the manual on mysql.com exactly.
>
> Any pointers as to where I'm going wrong?

It would be nice to know what error your getting, but the reason is certainly
that both 'to' and 'from' are reserved words in MySQL. Take a look here

[url]http://www.mysql.com/doc/en/Legal_names.html[/url]

Quote your column names with backticks:

SELECT * FROM memodata
WHERE `to` = 1 AND `readdate` = `sent`
ORDER BY `id` ASC

and it should work for you.

Cheers,

Rob

[R. Joseph Newton]

dan wrote:

> Hi there, quick and annoying SQL problem, was debating about whether to post
> it here or DBI, I figured DBI was for DBI related issues, and this is just a
> plain SQL problem.

So it should really be posted on an RDBMS list.

> Excuse me if I'm wrong though, but thought I'd ask
> anyway.
> I have an SQL statement as follows,
> SELECT * FROM memodata WHERE to=1 AND readdate=sent ORDER BY id ASC

Please put spaces in your condition clauses, there is nothing magical about
torturing the eyes.

In addition to the concerns Rob raised about using reserved words [you can
generalize this to say that generic names are just bad style anyway] I would
suggest naming the fields you are taking. Some SQL engines cannot address
fields that are not selectd by name. The select * construct is for dumps, not
for analytical selects.

select sender, receiver, time, date form mailhistory where receiver =
$target_recipient;

Joseph