By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

SELECT: Syntax error. Please help

P: n/a
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!
Jul 17 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"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!

Jul 17 '05 #2

P: n/a
[ 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.
Jul 17 '05 #3

P: n/a

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
Jul 17 '05 #4

P: n/a
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.
Jul 17 '05 #5

P: n/a
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" = @.
Jul 17 '05 #6

P: n/a
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)
Jul 17 '05 #7

P: n/a
"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
Jul 17 '05 #8

P: n/a
On 29 Sep 2003 11:36:52 -0700, nc@iname.com (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)
Jul 17 '05 #9

P: n/a

On 29-Sep-2003, nc@iname.com (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
Jul 17 '05 #10

P: n/a
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/
Jul 17 '05 #11

P: n/a

On 29-Sep-2003, Geoff Berrow <bl@ckdog.co.uk.the.cat> 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
Jul 17 '05 #12

P: n/a
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/
Jul 17 '05 #13

P: n/a

On 29-Sep-2003, Geoff Berrow <bl@ckdog.co.uk.the.cat> 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
Jul 17 '05 #14

P: n/a
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!

Jul 17 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.