SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Enthusiast geeOff's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta, GA 30328
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    :: oddé mySQL syntax error ::

    this is driving me nuts...why doesn't this line work ?
    PHP Code:
    $sql "select * from answers WHERE unit = $unit
    AND section = 
    $section AND question = $question AND
    group = '2' AND correct = 'y' ORDER By rand() LIMIT 1"

    i have tried it as "group = $question", "group = 2", "group = ''", "group = 'y'", etc... taking out the group part of the query, it always runs fine...anytime i add it i get
    You have an error in your SQL
    syntax near 'group = '2' AND correct = 'y' ORDER By
    rand() LIMIT 1' at line 1
    this group column is the same crap as unit, section, question...all int(11)s. i.e., this works fine:
    PHP Code:
    $sql "select * from answers WHERE
    unit = 
    $unit AND section = $section AND question =
    $question AND correct = 'y' ORDER By rand() LIMIT 1"
    any ideas ? thanks,

    Geoff

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm no expert but can you really use the rand() funtion in an order by statement?

    Sketch
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Enthusiast geeOff's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta, GA 30328
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    figured it out...'group' is a reserved word

    actually, i haven't had any problems with the "ORDER By rand() LIMIT 1". i love that line, works great.

    the problem was the column name group. this is a reserved word (that i have yet to use), correct ? i figured mebbe this was the case & changed group to "groups"...works like charm.

    thx -- G

  4. #4
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    uuhhhh....yeah, that was my second guess.

    Sketch
    Aaron Brazell
    Technosailor



  5. #5
    SitePoint Enthusiast geeOff's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta, GA 30328
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    heh-heh


  6. #6
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw, you have to put single quotes around the variables.
    section = '$section'
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  7. #7
    SitePoint Enthusiast geeOff's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta, GA 30328
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for whatever reason, PHP 4.0.4pl1 / MySQL 3.23.36 let's me use no quote with variables. stuff like this:
    PHP Code:
    "select * from answers WHERE unit = $unit 
    AND section = 
    $section AND question = $question 
    AND correct = 'y' ORDER By rand() LIMIT 1"

    is fine with "naked" $vars...but that makes me a little nervous, so i just changed them...thanks.

    Geoff

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To answer your question, variables that hold nothing more than numbers don't need quotes. If there are any other characters in there you must use single quotes.

    i'm no expert but can you really use the rand() funtion in an order by statement?
    New to 3.23
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, in fact you SHOULDN'T put quotes around numbers in your queries. MySQL lets you get away with it, but MattR says that other RDBMSes won't.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  10. #10
    SitePoint Enthusiast geeOff's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta, GA 30328
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AH -- that makes sense now (most of my $vars ARE all numbers)...thanks guys, this is "good stuff" for a newbie.

    Geoff


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
  •