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

RayWilk · February 21, 2012, 8:26am

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

r937 · February 21, 2012, 11:05am

the error message is telling you exactly where to look

the ‘20’ occurs right at the end of the query –

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

RayWilk · February 21, 2012, 1:42pm

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

r937 · February 21, 2012, 1:47pm

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

RayWilk · February 21, 2012, 1:52pm

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

r937 · February 21, 2012, 2:03pm

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…

RayWilk · February 21, 2012, 2:19pm

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?

RayWilk · February 21, 2012, 2:31pm

Hello r937

ok I found it, reg_date in the list_site_users.php

r937 · February 21, 2012, 2:43pm

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

RayWilk · February 21, 2012, 2:43pm

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?

RayWilk · February 21, 2012, 2:52pm

Hi r937

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

a LIMIT=1 and a LIMIT=20

and now ?

RayWilk · February 21, 2012, 3:05pm

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

r937 · February 21, 2012, 3:53pm

you need php help

i’ve flagged this thread to ask a moderator to move it to php

RayWilk · February 21, 2012, 3:54pm

Hi r937 thank you, your help greatly appreciated