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

Visit PubCon.com
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




msg:3077042
 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




msg:3077044
 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




msg:3077050
 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




msg:3077054
 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




msg:3077063
 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




msg:3077223
 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