Page 1 of 1

[SOLVED] ORDER BY FIELD shows err in PHP but works in MySQL

Posted: Sun Mar 05, 2006 6:53 am
by anjanesh
I have an SQL statement that works fine in mysql command line and phpMyAdmin.
It uses ORDER BY FIELD(id,8,6,2)
But in my PHP script I get the msg FUNCTION db1.FIELD does not exist

Code: Select all

$Res = mysql_query($SQL) or die("\n<!--$SQL-->\n".mysql_error());
I exectued what was inbetween <!-- and --> in phpMyAdmin and worked ! But my PHP script is giving this error.

Any idea why ?

Thanks

Posted: Sun Mar 05, 2006 7:05 am
by Benjamin
Is your script connecting to a different database?

Posted: Sun Mar 05, 2006 8:00 am
by anjanesh
Nope. Only one database involved here.

Posted: Sun Mar 05, 2006 9:44 am
by RobertGonzalez
Is this on a development server or hosted server? What version of MySQL are you running, what version of PHP and what version of the MySQL Client API?

EDIT: Can you post the entire query also?

Posted: Sun Mar 05, 2006 11:32 am
by anjanesh
Running PHP 5.1.2 on MySQL 5.0.17 on my WinXP machine.

Code: Select all

CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `t1` (`id`, `value`) VALUES 
(1, 'value 1'),
(2, 'value 2'),
(3, 'value 3'),
(4, 'value 4');
The following query executes in phpMyAdmin or MySQL commandline.

Code: Select all

SELECT `id`, `value`
FROM `t1`
WHERE (`id` = '3' || `id` = '2' || `id` = '4')
ORDER BY FIELD (`id`, '3', '2', '4');
This PHP code is giving an error

Code: Select all

<?php
mysql_connect("localhost", "root", "") or die("Couldnt connect to db");
mysql_select_db("test") or die("Couldnt select db");
$sql = "
SELECT `id`, `value`
FROM `t1`
WHERE (`id` = '3' || `id` = '2' || `id` = '4')
ORDER BY FIELD (`id`, '3', '2', '4');
";
$res = mysql_query($sql) or die(mysql_error());
?>
FUNCTION test.FIELD does not exist

This is terribly confusing !

Posted: Sun Mar 05, 2006 12:47 pm
by RobertGonzalez
That IS weird. It looks like PHP is looking for a function named FIELD. I don't think I have ever seen PHP do this when the "function" is inside a double-quoted string. This is beyond my scope of knowledge. Sorry I couldn't be more useful.

Posted: Sun Mar 05, 2006 1:44 pm
by John Cartwright
try putting FIELD in backticks ie.`FIELD`

I can't remember for sure, but I think columns are case sensitive, so you might want to check that aswell.

Posted: Sun Mar 05, 2006 2:27 pm
by Benjamin
Yeah FIELD is probably a reserved keyword. Those need backticks. I usually put backticks around everything so I don't have to worry about situations like this. That error is still confusing though. If that doesn't fix it maybe there is a missing ` ' " ) or } someplace.

Posted: Sun Mar 05, 2006 3:02 pm
by John Cartwright
Also I didn't think mysql supported those logic operators, and simply used AND/OR instead and && and || ..

sorry I can't be of any more help.

Posted: Sun Mar 05, 2006 4:31 pm
by feyd
FIELD() is a function in MySQL 5 guys.

http://dev.mysql.com/doc/refman/5.0/en/ ... #id3035662

I can't really offer much of a solution other than trying selecting the result of FIELD(). You may need to use the MySQLi extension instead of MySQL extension, but that's a stretch.

Posted: Sun Mar 05, 2006 8:54 pm
by anjanesh
FIELD exists in MySQL 4.x too - this worked on my hosting server having MySQL 4.0.x.

Anyway, I got the problem solved after feyd mentioned that it was a function. This made me think of getting rid of the whitespace inbetween FIELD and (`id`, '3', '2', '4');

Code: Select all

$sql = "
SELECT `id`, `value`
FROM `t1`
WHERE (`id` = '3' || `id` = '2' || `id` = '4')
ORDER BY FIELD(`id`, '3', '2', '4');
";
Thanks.