Professional Web Applications Themes

SELECT: Syntax error. Please help - PHP Development

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!...

  1. #1

    Default SELECT: Syntax error. Please help

    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!


    sam Guest

  2. #2

    Default Re: SELECT: Syntax error. Please help

    "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" <rbaba99caramail.com> wrote in message
    news:bl91mf$hfq$1news.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!
    >
    >

    Jørn-Inge Kristiansen Guest

  3. #3

    Default Re: SELECT: Syntax error. Please help

    [ 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.
    Pedro Guest

  4. #4

    Default Re: SELECT: Syntax error. Please help


    On 29-Sep-2003, "sam" <rbaba99caramail.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
    [url]www.creative-light.com[/url]
    Tom Thackrey Guest

  5. #5

    Default Re: SELECT: Syntax error. Please help

    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.
    Pedro Guest

  6. #6

    Default Re: SELECT: Syntax error. Please help

    In article <bl91mf$hfq$1news.cybercity.dk>,
    "sam" <rbaba99caramail.com> wrote:
    > FROM users u, orders o
    users AS u, orders AS o ?

    Just guessing,
    JP

    --
    Sorry, <devnullcauce.org> is een "spam trap".
    E-mail adres is <jpk"at"akamail.com>, waarbij "at" = .
    Jan Pieter Kunst Guest

  7. #7

    Default Re: SELECT: Syntax error. Please help

    On Mon, 29 Sep 2003 12:35:42 +0200, "sam" <rbaba99caramail.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 (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  8. #8

    Default Re: SELECT: Syntax error. Please help

    "sam" <rbaba99caramail.com> wrote in message
    news:<bl91mf$hfq$1news.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
    Nikolai Chuvakhin Guest

  9. #9

    Default Re: SELECT: Syntax error. Please help

    On 29 Sep 2003 11:36:52 -0700, [email]nciname.com[/email] (Nikolai Chuvakhin) wrote:
    >"sam" <rbaba99caramail.com> wrote in message
    > news:<bl91mf$hfq$1news.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 (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  10. #10

    Default Re: SELECT: Syntax error. Please help


    On 29-Sep-2003, [email]nciname.com[/email] (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
    [url]www.creative-light.com[/url]
    Tom Thackrey Guest

  11. #11

    Default Re: SELECT: Syntax error. Please help

    I noticed that Message-ID:
    <u5%db.95$NW6.21104809newssvr13.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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]
    Geoff Berrow Guest

  12. #12

    Default Re: SELECT: Syntax error. Please help


    On 29-Sep-2003, Geoff Berrow <blckdog.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
    [url]www.creative-light.com[/url]
    Tom Thackrey Guest

  13. #13

    Default Re: SELECT: Syntax error. Please help

    I noticed that Message-ID:
    <Ds1eb.7701$Vl1.178newssvr27.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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]
    Geoff Berrow Guest

  14. #14

    Default Re: SELECT: Syntax error. Please help


    On 29-Sep-2003, Geoff Berrow <blckdog.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
    [url]www.creative-light.com[/url]
    Tom Thackrey Guest

  15. #15

    Default Re: SELECT: Syntax error. Please help

    Thanks guys for your help,

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

    THANKS.

    "sam" <rbaba99caramail.com> wrote in message
    news:bl91mf$hfq$1news.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!
    >
    >

    sam Guest

Similar Threads

  1. Need help formatting syntax for SELECT query on a Bittype data field
    By ca_nimrod in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 11th, 03:23 AM
  2. Tricky SQL SELECT syntax, can it be done?
    By Mattsala in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 15th, 04:27 PM
  3. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  4. Help with Select Syntax
    By Chaster in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 10:24 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139