SELECT: Syntax error. Please help

[sam]

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Help!

[Jørn-Inge Kristiansen]

"syntax error" means that what you wrote is not within the syntax that mysql
use, occures very often when you
write something wrong or just have the wrong order in stuff and so on

in this case I think it's because u referes to the alias u and o of the
table before u alias them

it should just be select * from ....

"sam" <rb*****@caramail.com> wrote in message
news:bl**********@news.cybercity.dk...

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Help!

[Pedro]

[ not posted to alt.php.sql ]

sam wrote:

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Help!

Are you including the semicolon in the string?
Don't!

<?php
$errsql = 'select * from table;';
$goodsql = 'select * from table';
?>

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

[Tom Thackrey]

On 29-Sep-2003, "sam" <rb*****@caramail.com> wrote:

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Help!

I ran the sql string (including the ;) as you wrote it and it does not get a
syntax error. This suggests that you aren't getting the sql string to MySQL
intact. Please post the code that defines the sql string and does the query.
--
Tom Thackrey
www.creative-light.com

[Pedro]

Tom Thackrey wrote:
[...]

I ran the sql string (including the ;) as you wrote it and it does not get a
syntax error. This suggests that you aren't getting the sql string to MySQL
intact. Please post the code that defines the sql string and does the query.

Oops ... I failed to test the semicolon (no error for me either).

You may want to check the sql error message:

<?php
// connect to database
$sql = "select * from table";
$res = mysql_query($sql)
or die(mysql_error() . ' in [' . $sql . ']');
// rest of script
?>
--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

[Jan Pieter Kunst]

In article <bl**********@news.cybercity.dk>,
"sam" <rb*****@caramail.com> wrote:

FROM users u, orders o

users AS u, orders AS o ?

Just guessing,
JP

--
Sorry, <de*****@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.

[Andy Hassall]

On Mon, 29 Sep 2003 12:35:42 +0200, "sam" <rb*****@caramail.com> wrote:

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Post the surrounding PHP; are you sure that statement is actually what is
being executed?

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Nikolai Chuvakhin]

"sam" <rb*****@caramail.com> wrote in message
news:<bl**********@news.cybercity.dk>...

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Yes, it is a syntax error, right there in the first line of your
query. If a query is based on more than one table, you can't use
* as it leads to naming conflicts. To fix this error, you will
have to explicitly specify all fields you want to obtain, preferably
with an alias for each field.

Also, using WHERE o.client_id = u.user_id is commonly considered
bad style. A better idea is to use o.client_id = u.user_id as
a join condition.

So your query should be rewritten along the following lines:

SELECT
u.field1 AS u_field1,
u.field2 AS u_field2,
u.field3 AS u_field3,
o.field1 AS o_field1,
o.field2 AS o_field2,
o.field3 AS o_field3
FROM users u LEFT JOIN orders o
ON o.client_id = u.user_id
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10;

Cheers,
NC

[Andy Hassall]

On 29 Sep 2003 11:36:52 -0700, [e-mail] (Nikolai Chuvakhin) wrote:

"sam" <rb*****@caramail.com> wrote in message
news:<bl**********@news.cybercity.dk>...

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????
Yes, it is a syntax error, right there in the first line of your
query. If a query is based on more than one table, you can't use
* as it leads to naming conflicts. To fix this error, you will
have to explicitly specify all fields you want to obtain, preferably
with an alias for each field.

No, that's not a syntax error, you just lose fields on the way into PHP.

mysql> select t1.*, t2.* from t1, t2;
+------+------+
| id | id |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
Also, using WHERE o.client_id = u.user_id is commonly considered
bad style. A better idea is to use o.client_id = u.user_id as
a join condition.

So your query should be rewritten along the following lines:

SELECT
u.field1 AS u_field1,
u.field2 AS u_field2,
u.field3 AS u_field3,
o.field1 AS o_field1,
o.field2 AS o_field2,
o.field3 AS o_field3
FROM users u LEFT JOIN orders o
ON o.client_id = u.user_id

Why use LEFT JOIN? Surely you mean [INNER] JOIN; why go for an outer join
unless you actually need one?

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Tom Thackrey]

On 29-Sep-2003, [e-mail] (Nikolai Chuvakhin) wrote:

SELECT u.*, o.*
FROM users u, orders o

(snip) Yes, it is a syntax error, right there in the first line of your
query. If a query is based on more than one table, you can't use
* as it leads to naming conflicts. To fix this error, you will
have to explicitly specify all fields you want to obtain, preferably
with an alias for each field.

Wrong. u.*, o.* works as coded.

--
Tom Thackrey
www.creative-light.com

[Geoff Berrow]

I noticed that Message-ID:
<u5*******************@newssvr13.news.prodigy.co m> from Tom Thackrey
contained the following:

Wrong. u.*, o.* works as coded.

How is it different from SELECT * ?

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Tom Thackrey]

On 29-Sep-2003, Geoff Berrow <[e-mail]> wrote:

Wrong. u.*, o.* works as coded.

How is it different from SELECT * ?

It allows you to select all the columns from one table (i.e.
u.*,o.name,o.price) where * selects all columns from all tables.

--
Tom Thackrey
www.creative-light.com

[Geoff Berrow]

I noticed that Message-ID:
<Ds****************@newssvr27.news.prodigy.com> from Tom Thackrey
contained the following:

>Wrong. u.*, o.* works as coded.

How is it different from SELECT * ?

It allows you to select all the columns from one table (i.e.
u.*,o.name,o.price) where * selects all columns from all tables.

I can see that. But in this instance, no difference, surely?

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Tom Thackrey]

On 29-Sep-2003, Geoff Berrow <[e-mail]> wrote:

>Wrong. u.*, o.* works as coded.

How is it different from SELECT * ?

It allows you to select all the columns from one table (i.e.
u.*,o.name,o.price) where * selects all columns from all tables.

I can see that. But in this instance, no difference, surely?

None, but it didn't cause the syntax error either.

--
Tom Thackrey
www.creative-light.com

[sam]

Thanks guys for your help,

The problem was:
My query string contains a ' in the wrong place.

THANKS.

"sam" <rb*****@caramail.com> wrote in message
news:bl**********@news.cybercity.dk...

When I run this SQL query:

SELECT u.*, o.*
FROM users u, orders o
WHERE
TO_DAYS(o.order_date)
BETWEEN
TO_DAYS('2003-09-20')-10
AND
TO_DAYS('2003-09-20')+10
AND
o.client_id = u.user_id;

I get this error:

You have an arror in your SQL syntax '' at line 1

As you see, this error message have nothing to do
with the stucture of my tables or with what they contain.
SYNTAX ERROR ???????????????????

Help!