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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

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

Post 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
Last edited by anjanesh on Sun Mar 05, 2006 8:58 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Is your script connecting to a different database?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Nope. Only one database involved here.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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 !
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

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