Complex SQL troubleshooting

[HeadBut]

I am having trouble with a complex SQL select. It was working and I copied it to another machine when it broke. The PHP version is the same and the mysql version is the same, what else should I check?

Mac Powerbook G4 vs MacMini G4
PHP Version 5.1.2
Client API version 4.1.14

Thanks
M

[vincevincevince]

1. Check PHP magic_quotes settings (php.ini)
2. Check case sensitivity of table names.
3. Check all other php.ini and my.cnf settings

If none of those help, post back with the error and extract...

[HeadBut]

magic_quotes_gpc Off Off
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off

Same on both

"2. Check case sensitivity of table names." Do you mean a mysql setting?

"3. Check all other php.ini and my.cnf settings" not real sure what to look for, also not familiar with my.cnf

here is a typical error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check . TableID AS TableID , `Status` , `ServerID` , `CreationDate` , `FoodAdjus' at line 1

thanks
M

[vincevincevince]

I'm not sure about the OS you are running on the powerbook vs. the macmini, but a common problem when changing OS is that unix based system are generally case sensitive, whilst others are frequently not. As MySQL uses named files for the database and tables you need to match the same case if it's a unix-based OS. I believe this applies to OS X. i.e. if you created the table called 'Boxes', you can't SELECT FROM boxes - it must be SELECT FROM Boxes

Try adding the back-ticks around `check`.`TableID` as `TableID`

If you can't figure it out - care to give us a bit of a longer extract from the SQL than the error message gave? A few words each side at least...

[HeadBut]

ok, here it is:

$query_ClosedCheckDisplay = "SELECT `CheckID`, `check`.`TableID` AS TableID, `Status`, `ServerID`, `CreationDate`, `FoodAdjustText`, check.RID AS RID, `BarAdjustText`, check.Covers AS Covers, `AdjustFood`, `AdjustBar`, `Meal` , rtables.TableNumber AS TableNumber , rtables.ServersArea AS ServersArea , restaurants.FoodTax AS FoodTax , restaurants.MandatoryTipCovers AS MandatoryTipCovers , restaurants.MandatoryTipPercent AS MandatoryTipPercent , restaurants.TipPercent AS TipPercent , restaurants.BarTax AS BarTax
FROM `check`
JOIN rtables ON rtables.TableID = check.TableID
JOIN restaurants on restaurants.RID = check.RID
WHERE check.RID = '".$_SESSION['RID']."' AND `Status` = 'Closed' AND `ServerID` = '".$_SESSION['ServerID']."' AND date(check.CreationDate) = date(CURRENT_DATE)
ORDER BY TableNumber";

I added the ticks : `check`.`TableID` AS TableID and it still works on the powerbook and not on the macmini

Thanks
M

[sonjay]

"check" is a reserved word in MySQL. You have to put backticks around it everywhere you use it to refer to table name.
[dev.mysql.com...]

I would rename the check table to something else instead.