[Resolved] Order Query by mathematical operation

Aurasia

Supposed I wanted to order the querry array by two variables it's pulling down from the database ... here's my code:

$query = "SELECT id, title, author, rating, rating_total, date FROM data_array WHERE title LIKE '%$searchtopic%' OR ";
if ($modeauthor == "author") {
	$query .= "author LIKE '%$searchtopic%'";
	}
elseif($modesubject == "subject") {
	$query .= "subject1 LIKE '%$searchtopic%' OR subject2 LIKE '%$searchtopic%'";
	}
else { // method is both subject & author
	$query .= "author LIKE '%$searchtopic%' OR subject1 LIKE '%$searchtopic%' OR subject2 LIKE '%$searchtopic%'";
	}

$query .= "ORDER BY rating DESC";

but that last line really needs to be the ordering by "rating / rating_total" ... how is such a thing accomplished?

paulnaj

You can sort by multiple columns in the ORDER BY clause:

ORDER BY field1 dir1, field2 dir2, .... , fieldN dirN

where dir is either ASC (default) or DESC.

Paul 🙂

Aurasia

Right but what I need is for it to divide the variable in the database called "rating" by the variable called "rating_total" ... and order the query array but those values ...

The rating is just that - people vote for pages/books/articles etc ... and the only way to know how many people voted for it is to keep the rating_total with it.

So the rating say ... 45 points ... and the rating total being 90 points ... means that 18 people voted giving it an average score of 2.5 points out of 5. that's what I want to order the results by.

Sorry if I wasn't clear.

paulnaj

Generally, you can use calculated fields directly in the ORDER BY clause, for example ...

ORDER BY rating/rating_total DESC

... but I always find it simpler to create an alias and use that ...

SELECT *, rating/rating_total as rating_order
... (rest of sql)...
ORDER BY rating_order DESC

Having said that, which actual number (given your example) do you actually want to order by. If it's the 2.5 (average) then the calc seems to me to be ...

5*rating/rating_total ( ... = rating/(rating_total/5)) ... )

Aurasia

ORDER BY rating/rating_total DESC

That did it! I'm calculating a percentage of High user approval to order these responses ... I'm going to put the rest of what you said down in my notes though ... as it looks useful ... so you can actually define new arrays in the query string?

You said

rating/rating_total as rating_order

So - the rating_order now becomes a new ... array then? Or just a new variable associated with each row ... almost like a new invisible column?

Thanks you Paul - this issue is resolved though. I am most appreciative!

paulnaj

Yes,

SQL is pretty powerful ... check out the page on SELECT in the MySQL manual for some examples of what you can do ... bear in mind the version of MySQL you are using.

Paul 🙂