Why does SQL work on my desktop and not on my server?

[benphp]

I'm pulling my hair out.

 

Server: 4.0.26-nt

Desktop: 5.0.51b-community-nt

 

Tables are identical on both servers:

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| rid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| pid     | int(11)     | YES  |     | NULL    |                |
| qid     | int(11)     | YES  |     | NULL    |                |
| uid     | int(11)     | YES  |     | NULL    |                |
| score   | varchar(2)  | YES  |     | NULL    |                |
| ractive | varchar(1)  | YES  |     | NULL    |                |
| shift   | varchar(2)  | YES  |     | NULL    |                |
| rcount  | varchar(4)  | YES  |     | NULL    |                |
| moddate | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

 

Code is identical:

<?php
$selectR = "
select r1.score, r1.moddate, r1.rcount, r1.uid
from resp r1
where r1.rcount = (
    select r2.rcount
    from resp r2
    where r2.uid=r1.uid
ORDER BY r2.rcount DESC LIMIT 1
    )
";
?>

 

One works, and the other gives me "Fatal error: SQL in ..."

 

?????

 

[Maq]

One works, and the other gives me "Fatal error: SQL in ..."

 

It would be more helpful if you finished what the error said.

[benphp]

Not really.

 

Fatal error: SQL in C:\mydir\myscript.php on line 285

 

 

[Maq]

Please post the code for line 285.

 

Why is it giving you a fatal error for C:\mydir... when you are trying to run this on your server?

[benphp]

line 285:

$resultR = mysql_query($selectR) or trigger_error("SQL", E_USER_ERROR);

 

It's the PHP error. The MySQL error is a MySQL error, which I didn't post.

 

The problem lay in the MySQL versions. I upgraded the server to 5x and it fixed the problem, although I don't see what the problem was in the SQL.

 

[Maq]

Hmm, interesting.  You were probably using a function or something that only existed in the newer version.

[corbin]

Change

$resultR = mysql_query($selectR) or trigger_error("SQL", E_USER_ERROR);

 

to

 

$resultR = mysql_query($selectR) or trigger_error("SQL " . mysql_error(), E_USER_ERROR);

 

 

 

and get a useful error message.