/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Sorting by a calculated field

Copy linkTweet thisAlerts:
Jun 12.2014

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.

[code=php]$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;[/code]

to post a comment
PHP

10 Comments(s) ↴

Copy linkTweet thisAlerts:
@ginerjmJun 12.2014 — I'm guessing your query is not returning any rows. Reason is your where clause is flawed. Perhaps you should try:
<i>
</i>"SELECT.....
WHERE lss1.lawyer = '$profname' and lss1.lawyer=lss2.lawyer
..... "
Copy linkTweet thisAlerts:
@NogDogJun 12.2014 — 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?
Copy linkTweet thisAlerts:
@beylahauthorJun 12.2014 — I am getting no output

I have also tried the WHERE lss1.lawyer = "$profname" and lss1.lawyer=lss2.lawyer - and get no output
Copy linkTweet thisAlerts:
@NogDogJun 12.2014 — 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.
Copy linkTweet thisAlerts:
@NogDogJun 12.2014 — I am getting no output

I have also tried the WHERE lss1.lawyer = "$profname" and lss1.lawyer=lss2.lawyer - and get no output[/QUOTE]

You may want to make sure display_errors is enabled in your development PHP configuration.
Copy linkTweet thisAlerts:
@ginerjmJun 12.2014 — 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.
Copy linkTweet thisAlerts:
@deathshadowJun 12.2014 — 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 [url=http://www.php.net//manual/en/function.mysql-connect.php]giant red warning boxes in the manual[/url] telling us to "CUT IT OUT!!!"

Which is also why you shouldn't be blindly dumping variables into your querystrings.
Copy linkTweet thisAlerts:
@deathshadowJun 12.2014 — BTW, this is possibly what you are after for a query:

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?
Copy linkTweet thisAlerts:
@beylahauthorJun 17.2014 — 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.
Copy linkTweet thisAlerts:
@beylahauthorJun 17.2014 — 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?
×

Success!

Help @beylah spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.16,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @nearjob,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,
)...