Need some help formatting queries.

ZombiePriest · Post by **ZombiePriest** » 03.11.2006, 01:56

Hi, I'm pretty new to both delphi and MySQL so if this is something retarded ... well sorry.

If I run:

SELECT COUNT(*) FROM `users` WHERE ostatus = "Offline";

in mysql query browser it works perfectly, but if I try to use:

ZQuery1.SQL.Text := 'SELECT COUNT(*) FROM `users` WHERE ostatus = ("Offline");';

In my delphi project, I always get a full record count, not just the ones where ostatus = Offline (or online if I do it the other way round).

Just wondering what I need to change in my syntax to get this query working properly.

Any help much appreciated.

Post by **mdaems** » 03.11.2006, 02:41

Why do you use () in Delphi and not in mysql query browser?

Mark

ZombiePriest · Post by **ZombiePriest** » 03.11.2006, 06:29

ZQuery1.SQL.Text := 'SELECT COUNT(*) FROM `users` WHERE ostatus = ("Offline");';

Blah, that was a stupid copy/paste error. it isn't in my code my code is:

ZQuery1.SQL.Text := 'SELECT COUNT(*) FROM `users` WHERE ostatus = "Offline";';

I copy/pasted after trying a few things people shouted at me on my irc network, lol none of them use delphi it was an exercise in futility.

After further attempts I've come to the conclusion that I just have no idea how to use these components lol. I can't find any examples of them being used and no help documents appear to be available, except reference to some java bit.

Java could as well be chinese to me lol, so if anyone can provide an example using these components to connect to a mysql db, and retrieve a count of records with WHERE, and output the result to a memo, edit whatever.. I'll umm , give you my firstborn.

That's really all I have to offer, lol that and gratitude.

Post by **mdaems** » 03.11.2006, 08:00

I suppose you also tried to drop the backticks around the tablename? (Just wrote some code to allow them, but not in Beta version yet. Already in SVN Trunk.)
Also, how is the ostatus field defined? Simple character string or ENUM? (Not sure if that would make a difference, but you never know.)
Did you try to drop an ZSqlMonitor in your project? So you can see what's actually sent to the server. (Zeoslib does sometimes some parsing before the query is sent.)

Mark

ZombiePriest · Post by **ZombiePriest** » 03.11.2006, 08:28

I'll play around with all of that thanks, however I'm having some difficulty getting the return value, it turns out whatever I was doing was going to return 1 no matter what lol.

I don't even remember what I had, I had so much tried and commented out I just started the test project over.

I'll keep fiddling with it and googling away until I figure it out.

Thank you.

kmr · Post by **kmr** » 03.11.2006, 13:24

Hi!

I experienced a similar problem some months ago. I don't know anymore exactly under which circumstances they appeared, but I solved it using the keyfield of the table instead of *. Maybe that helps.

Btw: you do not try to get the result with ZQuery1.recordcount, don't you?

ZombiePriest · Post by **ZombiePriest** » 03.11.2006, 22:57

I tried using stuff like:

ZQuery1.SQL.Text := 'SELECT COUNT(*) FROM `users` WHERE ostatus = "Offline";';

Code: Select all


And the using .RecordCount but I always get a return value of 1 from RecordCount, without the WHERE it returns the correct number of rows.

kmr · Post by **kmr** » 04.11.2006, 00:22

I tried using stuff like:

ZQuery1.SQL.Text := 'SELECT COUNT(*) FROM `users` WHERE ostatus = "Offline";';

And the using .RecordCount but I always get a return value of 1 from RecordCount, without the WHERE it returns the correct number of rows.

Sounds a bit strange to me too, since ZQuery1.RecordCount should return 1 for both queries.

Try something like

Code: Select all

ZQuery1.SQL.Text := 'SELECT COUNT(*) erg FROM `users` WHERE ostatus = "Offline";';
showmessage(ZQuery1.fieldbyname('erg').asstring);

If this doesn't work, substitute the * with the keyfield of users.

fduenas · Post by **fduenas** » 04.11.2006, 02:09

Also try to update latest MySQL server, some time the server itself has bugs.
Also i recommend you to add and index to field, in this case ostatus, so the count can be fast.

You can also try:

Select ostatus, Count(*) AS total FROM users GROUP BY ostatus WHERE (ostatus = "Offline");

You can remove the WHERE clause to see how many ostatus field values you have in your table.

Remember also that maybe some records could have an "Offline" or "offline" or "OFFLINE" field value, so you must valid that all possible values for ostatus will have the same letter case.

ZombiePriest · Post by **ZombiePriest** » 07.11.2006, 08:12

Amazingly I got it all working lol, I started noticing some odd behaviors from some of my jedi components and thought it might be something b0rked within my delphi setup itself.

I removed all traces of delphi (except my sources of course, and my cvs) and reinstalled. Everything worked marvelously and is so far going great.

I'm deadly slow to learn anything to do with programming, but am making headway. I'm sure I'll be back again for more and I'll even see if I can find an oportunity to help someone else out if possible.

Fantastic work people, I am very pleased with ZeosLib and grateful that you put in the long hours to make it and keep it golden.

Cheers! [schild=4 fontcolor=000000 shadowcolor=C0C0C0 shieldshadow=1]Thank you Zeos Developers and Maintainers![/schild]