[RESOLVED] equation in php

slrobinson1983

Hey all. I have a db titled "tracks". in "tracks" there are 6 columns, "names", "5s", "4s", "3s", "2s", & "1s". This table is to track the # of 5s, 4s, 3s, 2s, & 1s a particular user has. I want to get the average number for a particular user and after searching the web i constructed this code:

$averagerating = "( SELECT((55s)+(44s)+(33s)+(22s)+(1*1s))/(5s+4s+3s+2s+1s) FROM tracks )";

$useravgrtng = mysql_query ("SELECT name, EVAL($averagerating) FROM tracks ORDER BY eval($averagerating) DESC Limit 1");
list ( $useravgrtng ) = mysql_fetch_row ( $useravgrtng );

<?php print ("$useravgrtng"); ?>

Its not giving me an error just no output. Any ideas? Thanks in advance.

bradgrafelman

slrobinson1983 wrote:
Its not giving me an error

How can you be so sure? You never check the return value of [man]mysql_query/man to make sure it isn't boolean FALSE (indicating that an error occurred - use [man]mysql_error/man in that case to determine what the error message was).

You'll probably find that the error message is complaining about the use of EVAL(), considering that there is no such function in MySQL.

Get rid of the eval() and just SELECT the mathematical expression itself (might want to throw a column alias on it so you can simply use the resulting array rather than creating separate variables).

slrobinson1983

Thanks. I keep forgetting about that mysql_error() function. It does help. As far as the eval() function i saw someone else online use something similar...guess they didnt know what they were doing either lol. I got the equation to work eventually...its kinda scary but heres the code... (i forgot to mention it finds the averages that are greater than 4)...

$avgformula = ("(((SUM(5s)5)+(SUM(4s)4)+(SUM(3s)3)+(SUM(2s)2)+(SUM(1s)*1))/((SUM(5s))+(SUM(4s))+(SUM(3s))+(SUM(2s))+(SUM(1s))))>4");

A user can have multiple entries in the table so how would i put this variable in a code to find the user with the MOST averages above 4. Everything ive tried comes up with incorrect answers.

bradgrafelman

slrobinson1983 wrote:
As far as the eval() function i saw someone else online use something similar...guess they didnt know what they were doing either lol.

What you saw was probably PHP code doing something else, since PHP does have an [man]eval/man function (though there's a common adage... "if eval() is the answer, then you're probably asking the wrong question"). However, unlike what your thread title suggests, the problem you're dealing with isn't PHP code at all.

slrobinson1983 wrote:
A user can have multiple entries in the table so how would i put this variable in a code to find the user with the MOST averages above 4. Everything ive tried comes up with incorrect answers.

Not really sure I understand... can you show us your table structure as well as what you've tried?

slrobinson1983

i know it sounds kinda confusing, i tried to eliminate a few factors to see if it would make more sense but let me see if i can explain it better...

name | song | 5s | 4s | 3s | 2s | 1s

john | song1 | 5 | 4 | 6 | 3 | 2

mark | song1 | 4 | 4 | 5 | 0 | 1

alex | song1 | 1 | 6 | 3 | 7 | 6

john | song2 | 5 | 4 | 0 | 3 | 4

alex | song2 | 5 | 8 | 3 | 2 | 1

john | song3 | 3 | 5 | 6 | 9 | 0

...each person has a song which was rated 1-5. the columns 5s, 4s, 3s, 2s, & 1s are the ratings. the numbers in those columns represent the number of times that particular song received that rating...alex's song 1 received 1 "5" rating, 6 "4" ratings, 3 "3" ratings and so on. The variable i created above ($avgformula ) will determine the average rating for a particular song but i'm tryng to figure out how to find the person who has the most songs with an average rating greater than 4.

ive tried...

$avg4orbetter = mysql_query ("SELECT rap_name, $avgformula FROM tracks GROUP BY rap_name ORDER BY $avgformula DESC LIMIT 1") or die(mysql_error());
list ( $avg4orbetter ) = mysql_fetch_row ( $avg4orbetter ) or die (mysql_error());

...its not giving me an error from the "(mysql_error());" its just not returnng the right answer. i guess a stepping stone would be to first write the code that finds the artist with the most songs in general the maybe add "where $avg4orbetter='True'" or something like that but the coding i attempted with that didnt work either.

i hope that explains it better, if not let me know and i'll try to explain it in further detail. thanks for the help.

slrobinson1983

finally figured it out...

$avgformula = ("sum((((5s5)+(4s4)+(3s3)+(2s2)+(1s*1))/(5s+4s+3s+2s+1s))>4.00)");

//finds the user with the most songs averaging 4 or more
$useravgrtng = mysql_query ("SELECT name, $avgformula from tracks");
list ( $useravgrtng ) = mysql_fetch_row ( $useravgrtng );

//finds the number of songs the user has averaging 4 or more
$useravgrtngNO = mysql_query ("SELECT $avgformula from tracks where name='$useravgrtng'");
list ( $useravgrtngNO ) = mysql_fetch_row ( $useravgrtngNO );

slrobinson1983

edit..., this is the one that works for me... the other only draws from record 1...

//finds the user with the most songs averaging 4 or more
$useravgrtng = mysql_query ("SELECT rap_name, $avgformula as occurrences from tracks group by name order by occurrences desc, date_added desc") or die(mysql_error());
list ( $useravgrtng ) = mysql_fetch_row ( $useravgrtng ) or die (mysql_error());