SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    very complicated mysql

    Think your up for it? This is one hell of a query but here goes...

    "SELECT *, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"

    This returns invalid use of group function i also know that it is to do with the 'AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) )' part

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do
    SELECT unituser.username, sum...
    instead of
    SELECT *, sum...
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can also simplify a little:

    SELECT unituser.username, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( strength * 2) < '".$power."') OR (('".$power."' * 2.5) < strength ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Karl
    You can also simplify a little:

    SELECT unituser.username, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( strength * 2) < '".$power."') OR (('".$power."' * 2.5) < strength ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"
    This says that strength does not exist. Also in response to sweetje it doesn't make a difference

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh and btw, if i remove the sum command from the
    ' AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) ) AND ' bit it doesn't return an error but doesn't work as expected (which is obvious why)

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so i take no one can help me on this?

    Or is it just not possible

    (IF YOU FIND THIS POST AS SPAM CLOSE THE TOPIC)

  7. #7
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What is the value of $power and why do you have a number in single quotes within your query?

    Also what happens if you remove the GROUP BY clause?
    Wayne Luke
    ------------


  8. #8
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    numbers in quotes 'oops' well that doesn't matter anyway, about the GROUP BY clause it doesn't make a difference if i remove it

    Thanks any wayy


Bookmarks

Posting Permissions

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