homepage Welcome to WebmasterWorld Guest from 54.198.130.203
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 / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySql Query that should work on all servers!
Spot any glaring errors?
Matthew1980




msg:4550387
 11:34 am on Mar 2, 2013 (gmt 0)

Hi there guru's of MySql!

It's been a while since i've posted on here ;P

Can I get advice on why this simple query doesn't function on some servers:-

SELECT Count(*) as `QueryTotal`, `newbuild`.`id_number`, `newbuild`.`date` FROM `newbuild` WHERE `newbuild`.`serialnumber` = 23456 AND `newbuild`.`current` = TRUE ORDER BY `newbuild`.`date`

Any thought's/suggestions/ideas welcome, this has had me flummaxed for a few hours now..

Cheers,
MRb

 

phranque




msg:4550522
 7:39 pm on Mar 2, 2013 (gmt 0)

welcome back, MRb!

i would try using GROUP BY rather than ORDER BY and see if that solves it.

Matthew1980




msg:4550534
 8:26 pm on Mar 2, 2013 (gmt 0)

Why thank you Phranque :)

Yeah, I'm just playing with that now as it happens. I get a really odd "illegal operation" notice from the server at work when I run this query, yet on my local dev machine, it's fine.

I'll let you know how it gets on.

Cheers,
MRb

coopster




msg:4552145
 2:50 pm on Mar 7, 2013 (gmt 0)

It is likely a server mode configuration difference. You can run a MySQL server in different modes.

[dev.mysql.com...]
[dev.mysql.com...]

The reference phranque mentioned for GROUP BY could certainly be an issue if the mode is different. It could come down to the "Single Value Rule" which you can read more about here ...

[webmasterworld.com...]
[dev.mysql.com...]

Matthew1980




msg:4554447
 10:13 pm on Mar 13, 2013 (gmt 0)

Hi there Coopster,

Thanks for the reply, only just seen it & you're absolutely right. Works live server is setup differently to my local machine; so I've had to replicate their .conf file onto my local machine so that future developments are exactly as they would be on the live server.

I'm dreading it soon as we're moving to IIS, *hopefully* the IT guy assures me this will be a painless changeover!

Thanks for the link pointers too - they'll be useful to read.

[EDIT]
I've changed the way I've done the query, as I wanted a var to return the amount of rows returned with each query; I'm just checking for a NULL now (this is vb.net so it's a bit more flexible with assigning null's *seems-like-it-anyway*) when I try to read a returned value, if it's not set then I just continue exit the while and go to the error handler.

Cheers,
MRb

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
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