homepage Welcome to WebmasterWorld Guest from 54.166.113.249
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Complex SQL troubleshooting
HeadBut

10+ Year Member



 
Msg#: 3077040 posted 4:29 am on Sep 9, 2006 (gmt 0)

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

WebmasterWorld Senior Member vincevincevince us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3077040 posted 4:38 am on Sep 9, 2006 (gmt 0)

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

10+ Year Member



 
Msg#: 3077040 posted 5:01 am on Sep 9, 2006 (gmt 0)

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

WebmasterWorld Senior Member vincevincevince us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3077040 posted 5:39 am on Sep 9, 2006 (gmt 0)

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

10+ Year Member



 
Msg#: 3077040 posted 6:23 am on Sep 9, 2006 (gmt 0)

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

10+ Year Member



 
Msg#: 3077040 posted 1:12 pm on Sep 9, 2006 (gmt 0)

"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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved