SQL query not working; but works using Navicat

I get this error message from my sql query:


Parse error: syntax error, unexpected 'BB' (T_STRING), expecting ']' in /home3/tbmvent1/public_html/desk/build/webassist/mysqli/rsobj.php(246) : eval()'d code on line 3

The following sql query works when I run the query Navicat; I get the correct answer. However, when I run the query within your recordset I get the error mentioned above. Do you see anything that is keeping the page from loading and the query from running. Thx


<?php
$pab = new WA_MySQLi_RS("pab",$Connections,1);
$pab->setQuery("(SELECT
(SELECT COUNT(pa) as pabpct FROM bat WHERE (teamID = ? ) AND
(
(result = 'BB') OR
(result = 'HBP') OR
(result = '1B') OR
(move_run = '1') OR
(xbh = '1') OR
(OB = '1') OR
(result = 'Bunt') OR
(result = '2B') OR
(result = '3B') OR
(result = 'HR') OR
(result = 'SC-Fly') OR
(result = 'D3-Safe') OR
(result = 'SC-BNT') OR
(rbi > '0') OR
(result = 'ROE-No Out')
)) / (SELECT COUNT(pa) FROM bat))");
$pab->bindParam("i", "".(isset($_GET['teamID'])?$_GET['teamID']:"") ."", "-1"); //colname
$pab->execute();
?>

If you open up rsobj.php what version is listed on the top of the page? Can I get FTP access and a URL to debug the page directly? I don't see the problem, but I'm sure I can figure it out if I was able to play with the code.

You gave me the Database connection information. I'll need the FTP connection information so I can update the files.

The issue is that you are doing a complex calculation and not giving it a name, so it is trying to return the column name as the calculation and that has the '-' character that is a math operator and that throws the whole thing off.

The solution is to use AS to assign a name to your calculated result like:

$pab->setQuery("SELECT
((SELECT COUNT(pa) as pabpct FROM bat WHERE (teamID = ? ) AND
(
(result = 'BB') OR
(result = 'HBP') OR
(result = '1B') OR
(move_run = '1') OR
(xbh = '1') OR
(OB = '1') OR
(result = 'Bunt') OR
(result = '2B') OR
(result = '3B') OR
(result = 'HR') OR
(result = 'SC-Fly') OR
(result = 'D3-Safe') OR
(result = 'SC-BNT') OR
(rbi > '0') OR
(result = 'ROE-No Out')
)) / (SELECT COUNT(pa) FROM bat)) AS totpabpct");

That way it returns your calculated number as a column named: totpabpct

before it was trying to create a column named: "((SELECT COUNT(pa) as pabpct FROM bat WHERE (teamID = ? ) AND
(
(result = 'BB') OR
(result = 'HBP') OR
(result = '1B') OR
(move_run = '1') OR
(xbh = '1') OR
(OB = '1') OR
(result = 'Bunt') OR
(result = '2B') OR
(result = '3B') OR
(result = 'HR') OR
(result = 'SC-Fly') OR
(result = 'D3-Safe') OR
(result = 'SC-BNT') OR
(rbi > '0') OR
(result = 'ROE-No Out')
)) / (SELECT COUNT(pa) FROM bat))"

Which almost was allowed if not for the "-" characters in it.

Still getting the same error.

You have two recordsets with the same SQL statement. I think you updated the wrong one so I'm not sure what the second SQL statement used to say. The one I gave you was to replace line 258, but it looks like you replaced line 282.

What was the old SQL statement on line 282? I can advise you on how to update that one too but I don't know what it was originally at this point.

This post has been deleted.

Awesome. As always you are correct. I need to TRIM the leading 0 and use FORMAT to have only 3 numbers. May take me a while. Lol. any quick ideas? Thx