Submit Your Article Forum Rules

Results 1 to 8 of 8

Thread: mysql_query

  1. #1
    Member
    Join Date
    Aug 2003
    Location
    NJ
    Posts
    81

    mysql_query

    hi wpw

    in the database there are multiple records for the same person.
    for example

    person 1 has 5 entries
    person 2 has 3 entries
    and so forth.

    all im trying to do is get a simple count of who has the most entries from highes to lowest.

    i wrote this query it works well for me but im using mysql version 5.1.33 locally.

    SELECT mname, COUNT(mname) FROM votes GROUP BY mname ORDER BY COUNT(mname) DESC

    the server this will run on uses a older version of mysql. im not sure which one though.

    any ideas on how i can rewrite this?
    It's better to do business with me than against me!

  2. #2
    Junior Member
    Join Date
    Aug 2009
    Posts
    6

    Re: mysql_query

    That looks perfectly fine for whatever version of MySQL you need...certainly on MySQL 5.0, and 4.x...

    Is there a reason you think it won't work? GROUP BY and COUNT are pretty standard stuff.

    As far as the query itself, you might want to group by the person ID (if there is one), rather than the name (in case 2 people have the same name like "John Smith"), otherwise no problem.

  3. #3
    Senior Member Uncle Dog's Avatar
    Join Date
    Apr 2008
    Posts
    325

    Re: mysql_query

    I'd agree with dmartin about compatibility (and ID). Have you actually tried (and failed) to run this on the live server yet or are you just assuming it will have problems?

  4. #4
    Member
    Join Date
    Aug 2003
    Location
    NJ
    Posts
    81

    Re: mysql_query

    Thanks Uncle Dog and dmartin.
    i should have mentioned that detail.

    I tried to run it.
    The error i receive is:
    Invalid use of group function

    if i run it without ORDER BY COUNT(mname) DESC
    it will output all the information but not in order.

    fyi

    the version of mysql that's live is 3.23.58
    It's better to do business with me than against me!

  5. #5
    Junior Member
    Join Date
    Aug 2009
    Posts
    6

    Re: mysql_query

    Hmmm....still seems valid, but try this:

    SELECT mname, COUNT(mname) as numvotes FROM votes
    GROUP BY mname
    ORDER BY numvotes DESC

    Maybe using an alias is needed in 3.23?

  6. #6
    Member
    Join Date
    Aug 2003
    Location
    NJ
    Posts
    81

    Re: mysql_query

    thanks dmartin

    That worked out fine.

    I dont know why my original way didn't work.

    Thanks again
    It's better to do business with me than against me!

  7. #7
    Junior Member
    Join Date
    Aug 2009
    Posts
    6

    Re: mysql_query

    Glad that worked - old MySQL versions are a little quirky in terms of the SQL they support - my guess is that expressions without aliases don't work in 3.23 with a GROUP BY...who knows.

    Maybe your installation can eventually upgrade to something better supported - 3.23 (and 4.0 and 4.1) have been "end of lifed".

  8. #8
    WebProWorld MVP wige's Avatar
    Join Date
    Jun 2006
    Posts
    2,981

    Re: mysql_query

    I believe this issue is that in older versions of MySQL you couldn't run a group function outside of the SELECT - in your case, COUNT. I think this was an optimization issue, the second count would require a new database call, whereas using an alias allows you to sort the results by the COUNT you already did in the SELECT statement.
    The best way to learn anything, is to question everything.
    Hidden Content

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •