CODEWITHSUNDEEP
mysqlsql
ebohatch
ebohatch

Reputation: 23

mysql statement works on my desktop but fails on my web host

I use XAMPP on my desktop (Apache/2.2.17 (Win32) mod_ssl/2.2.17 OpenSSL/0.9.8o PHP/5.3.4 mod_perl/2.0.4 Perl/v5.10.1 MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $)

I have a MYSQL SELECT statement that works fine in my desktop application, even ran it in phpMyadmin and works correctly.

When I move my application up to my web host the statement fails. My web host runs Apache with MySQL client version: 5.0.92 When I used phpMyadmin on my web host it gives me the error:

FUNCTION nwilin5_eib_jtest.FIELD does not exist

Here is the statement:

SELECT a.*, p.name as parent, p.id as parentid, c.name as cat
    , c.id as catid,u.username as user, f.itemid as featured
    , f.payment_date as FeaturedPayDate, f.limit_date as FeaturedExpDate  
FROM jos_classifiedsredux_ads as a  
LEFT JOIN jos_users as u 
    ON a.userid = u.id  
LEFT JOIN jos_classifiedsredux_pay4featuredad as f 
    ON a.id = f.itemid  
INNER JOIN jos_classifiedsredux_categories as c 
    ON a.category = c.id  
LEFT JOIN jos_classifiedsredux_categories as p 
    ON c.parent = p.id  
WHERE a.published = 1 
ORDER BY FIELD (a.id, 137, 226, 134, 135, 220, 240, 233, 219, 146, 138, 136, 133)

Upvotes: 1

Views: 82

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

You need to remove the space between FIELD and ( in your ORDER BY...

Change:

ORDER BY FIELD (a.id, 137, 226, 134, 135, 220...

To:

ORDER BY FIELD(a.id, 137, 226, 134, 135, 220...

Why does this matter?

Your test server and your production server likely have a different setting for the IGNORE_SPACE mode... which permits a space between the function call (FIELD), and the parameter list when enabled.

Permit spaces between a function name and the “(” character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 8.2, “Schema Object Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

mysql> CREATE TABLE count (i INT); 
ERROR 1064 (42000): You have an error in your SQL syntax 
The table name should be quoted:

mysql> CREATE TABLE `count` (i INT); 
Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always permissible to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

Upvotes: 1

Vasil Kolev
Vasil Kolev

Reputation: 11

Quick fix - change the ORDER BY to just ORDER BY a.id, ..., and it should work.

Upvotes: 0

Related Questions