SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help - A Mysql error has occurred while running the script:

    Hello

    just installed a script and I get this message on opening one of the Admin area titles.

    how easy is it to fix ?

    completely new to this, yor help greatly appreciated

    Ray


    A Mysql error has occurred while running the script:

    The query you are trying to run is invalid
    Mysql Error Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 20' at line 9
    SQL Query: SELECT u.user_id, u.name, u.username, u.email, u.active, u.approved, u.reg_date, u.payment_mode, u.balance, u.tax_account_type, u.tax_reg_number, u.tax_apply_exempt, u.tax_exempted, u.is_seller, u.preferred_seller, u.auction_approval, u.seller_verified, u.preferred_seller_exp_date, u.tax_company_name, c.name AS country_name, s.name AS state_name, u.mail_activated, u.bidder_verified FROM users u LEFT JOIN countries c ON u.country=c.id LEFT JOIN countries s ON u.state=s.id ORDER BY u.reg_date DESC LIMIT , 20

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the error message is telling you exactly where to look

    the '20' occurs right at the end of the query --
    Code:
    ... ORDER BY u.reg_date DESC LIMIT , 20
    the LIMIT option takes one or two parameters

    you've got only one, with a dangling comma
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937

    thank you for your reply,

    does this error occur because:

    1: script is faulty during the Installation/Creation of the database?

    2: or bad connection at the time of the data base creation?

    or ?

    your help greatly appreciated

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    only query is faulty

    cannot comment on installation/creation of database, presumably it's okay

    connection was fine, otherwise you wouldn't've got an error message on the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937

    ok I understand (a little lol)

    now to correct the query, I go to the database and? how do I find ... ORDER BY u.reg_date DESC LIMIT , 20

    allot of folder in my database

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you correct the query in the source code, not in the database

    i would venture that a text search for the string 'ORDER BY u.reg_date' will find it...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937

    ok so it is a source code problem,

    using searchmyfiles on my computer I search the folder containing all the files I uploaded to the server yet no "ORDER BY u.reg_date" found

    I do not think it is searching the code within the files ( yet is set for Text search )

    what programme should I use to search?


  8. #8
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937

    ok I found it, reg_date in the list_site_users.php


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    great!

    what you have to do next is find where the LIMIT option values php variables are being set

    there are usually two variables used in the LIMIT clause, and this technique implements pagination

    the first is the number of rows in the query result to skip over, and the second is the number of rows to retrieve

    so, the first time you use this query, it would be LIMIT 0,20 (or just LIMIT 20, with 0 being the default)

    when you click to go to the next page, the same script is called up again, but this time the variables are different, and you end up with LIMIT 20,20

    on the third time, LIMIT 40,20, and so on

    one of your php variables for the first page was probably set incorrectly

    but this is the mysql forum... and i don't do php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    the original error message said: Mysql Error Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 20' at line 9

    yet line 9: session_start();

    line 49: $row_user = $db->get_sql_row("SELECT u.user_id, u.username, u.email, u.active, u.approved, u.reg_date,

    line 418: $order_field = ($_REQUEST['order_field']) ? $_REQUEST['order_field'] : 'u.reg_date';

    line 529: $sql_select_users = $db->query("SELECT u.user_id, u.name, u.username, u.email, u.active, u.approved, u.reg_date,

    just not sure what I am looking for?


  11. #11
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    yes if I search the php there are several LIMIT & limit

    a LIMIT=1 and a LIMIT=20

    and now ?

  12. #12
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    is it possible that the problem is not on that php file?

    I just copied a good working list_site_users.php and pasted it into/over the problem list_site_users.php

    then in Admin when I clicked on User Management I got the same Error code and info

    A Mysql error has occurred while running the script:

    The query you are trying to run is invalid
    Mysql Error Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 20' at line 9
    SQL Query: SELECT u.user_id, u.name, u.username, u.email, u.active, u.approved, u.reg_date, u.payment_mode, u.balance, u.tax_account_type, u.tax_reg_number, u.tax_apply_exempt, u.tax_exempted, u.is_seller, u.preferred_seller, u.auction_approval, u.seller_verified, u.preferred_seller_exp_date, u.tax_company_name, c.name AS country_name, s.name AS state_name, u.mail_activated, u.bidder_verified FROM users u LEFT JOIN countries c ON u.country=c.id LEFT JOIN countries s ON u.state=s.id ORDER BY u.reg_date DESC LIMIT , 20

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you need php help

    i've flagged this thread to ask a moderator to move it to php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Feb 2012
    Location
    Kiwi in Bangkok
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937 thank you, your help greatly appreciated


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
  •