www.webdeveloper.com
Results 1 to 11 of 11

Thread: [RESOLVED] Sorting by a calculated field

  1. #1
    Join Date
    May 2009
    Posts
    235

    resolved [RESOLVED] Sorting by a calculated field

    I am trying to sort by a calculated field. I have 2 tables: lss1 and lss2. Each are the same. I am trying to get a sort order for their total number of hours.

    This is what I have but no total shows and the sorting does not happen.

    PHP Code:
    $sql="SELECT SUM(lss1.hours + lss2.hours) AS totalhours FROM lss1, lss2 WHERE lss1.lawyer=\"$profname\" OR lss2.lawyer=\"$profname\" ORDER BY totalhours desc";
    $result mysql_query($sql) or die(("Error: ".mysql_error().$sql));
    while (
    $rows=mysql_fetch_array($result)) {
    $totalhours=$rows['totalhours'];
    echo 
    $totalhours
    Last edited by beylah; 06-12-2014 at 08:38 AM.

  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    717
    I'm guessing your query is not returning any rows. Reason is your where clause is flawed. Perhaps you should try:
    Code:
    "SELECT.....
    WHERE lss1.lawyer = '$profname' and lss1.lawyer=lss2.lawyer
    ..... "

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Are you getting any output at all? The query looks a bit odd to me, but I've been in PostgreSQL world for the last few years, and don't recall all the idiosyncrasies of MySQL, so I'm wondering if the die() isn't showing because something else is failing before that or not parsing at all?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    May 2009
    Posts
    235
    I am getting no output

    I have also tried the WHERE lss1.lawyer = \"$profname\" and lss1.lawyer=lss2.lawyer - and get no output

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    I also think I'd need a better understanding of the tables involved and their relationships, as if you only join on the "lawyer" field and each table has multiple rows with that value for that field, you'd get multiple permutations and thus an inflated total.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Quote Originally Posted by beylah View Post
    I am getting no output

    I have also tried the WHERE lss1.lawyer = \"$profname\" and lss1.lawyer=lss2.lawyer - and get no output
    You may want to make sure display_errors is enabled in your development PHP configuration.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    717
    Check the numrows returned by your query. If it's not zero then there is some other problem. Turning php errors on may help you then.

  8. #8
    Join Date
    May 2014
    Posts
    900
    SELECT SUM(lss1.hours + lss2.hours) AS totalhours FROM lss1, lss2

    so we SEE a problem here? You're summing both tables. That query really doesn't even make sense... SUM as the only return parameter should only return by matches... I think you want two separate queries to get the totals from EACH table, not a total from BOTH tables... or are you trying to add them together and sort by the result? If so that would require a UNION or JOIN operation.

    Though I should probably read you the riot act; this is 2014 not 2004, stop using the deprecated mysql_ functions we've been told for the eight years since PHP 5 dropped to stop using, and that for over two years now there have been giant red warning boxes in the manual telling us to "CUT IT OUT!!!"

    Which is also why you shouldn't be blindly dumping variables into your querystrings.
    Java is to JavaScript as Ham is to Hamburger.

  9. #9
    Join Date
    May 2014
    Posts
    900
    BTW, this is possibly what you are after for a query:

    Code:
    	SELECT SUM(hours) AS totalhours
    	FROM (
    		SELECT hours FROM lss1
    		WHERE lawyer = :lawyerName
    		UNION
    		SELECT hours FROM lss2
    		WHERE lawyer = :lawyerName
    	)
    But since it returns just the sum and no other fields, it would only have a single row containing a single column as it's result... so there is nothing to 'sort'.

    I guess that's what I'm really not getting about what you are trying to do -- what is there to sort if you're only pulling up a total of hours?
    Java is to JavaScript as Ham is to Hamburger.

  10. #10
    Join Date
    May 2009
    Posts
    235
    Thank you deathshadow you pointed out my error - I was searching through the directory table first for those that are lawyers when i should have been searching after getting the content from the database and including the lawyer in the sort.

  11. #11
    Join Date
    May 2009
    Posts
    235
    thank you it makes sense what you are saying and i did use an join and it worked. Could you tell me what is old code so i dont use it anymore?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles