help with MySQL queries

[a2bardeals]

ok so i am fairly new to both PHP and MySQL and I am trying to learn the basics. I created a rating application everything so far works but now i would like to make a top 10. I have a table ('ratings') with rows:
id (the id for each rating from each user)
article (which page the rating is for)
rate (the actuall rating)
ip (the ip address of the rate-or)

i am trying to basically query the rows "article" and "rate"
then create a top ten list where the articles are grouped together and the rates for each article are averaged and then ordered desc. i got this far:

$result = mysql_query("SELECT article, rate FROM ratings order by rate desc");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}

$row = mysql_fetch_row($result);
echo "Row 0:", $row[0];
echo "Row 1:", $row[1];
echo "Row 2:", $row[2];
echo "Row 3:", $row[3];
echo "Row 4:", $row[4];
echo "Row 5:", $row[5];

i dont think i am even close to what i am trying to do....any ideas?

any help will be greatly apperciated!

[fenway]

Sounds like you want something like the following (UNTESTED):

[code]SELECT article, AVG(rate) FROM ratings GROUP BY id ORDER BY rate DESC LIMIT 10[/code]

[a2bardeals]

[!--quoteo(post=379751:date=Jun 3 2006, 06:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 3 2006, 06:36 PM) [snapback]379751[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sounds like you want something like the following (UNTESTED):

[code]SELECT article, AVG(rate) FROM ratings GROUP BY id ORDER BY rate DESC LIMIT 10[/code]
[/quote]

that does point me in the right direction but i am still having trouble echo-ing and displaying the result in an organized list (aka) instead of Article integer an html tag to represent a link to another page

[fenway]

There are two parts to this -- the actual DB query, and then whatever you output via PHP. Make sure the query returns the right piece of information, and then output it as you see fit (e.g. as a link).

[a2bardeals]

yes i started to figure that out last night having a link rather than a article number
but the main thing is out how do i group the articles together and average the votes to round to the nearest .5

[fenway]

[!--quoteo(post=379962:date=Jun 4 2006, 01:32 PM:name=theadmininator)--][div class=\'quotetop\']QUOTE(theadmininator @ Jun 4 2006, 01:32 PM) [snapback]379962[/snapback][/div][div class=\'quotemain\'][!--quotec--]
yes i started to figure that out last night having a link rather than a article number
but the main thing is out how do i group the articles together and average the votes to round to the nearest .5
[/quote]
Well, the query above is already grouping/averaging -- you can always call the ROUND() function if you desire.

[a2bardeals]

actually i figured out how to group the articles and average them but i would like the article ID number to represent an hyper link with representitive text aka instead of it outputing

Article: 101
Avg Rating: 9.5

i need it to look like

[a href=\"http://testlink.html\" target=\"_blank\"]file name[/a]
Avg Rating: 9.5

------------------
so far i have:

$result = mysql_query("SELECT article, AVG(rate) FROM ratings GROUP BY article ORDER BY rate DESC LIMIT 10");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

printf("<b>Article:</b> %s<br><b>Avg Rate:</b> %s<br><br><hr>", $row[0], $row[1]);

}

mysql_free_result($result);



any ideas?

[fenway]

I'm not sure I know what you mean -- first, you should be grouping by ID. Second, if you return this ID in your column list, you can compose whatever URL you desire. I don't see how I can be of any more help -- please clarify. [!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][i]BTW, I'll be gone for the rest of the day.[/i][!--sizec--][/span][!--/sizec--]

[a2bardeals]

[!--quoteo(post=380006:date=Jun 4 2006, 03:29 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 4 2006, 03:29 PM) [snapback]380006[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm not sure I know what you mean -- first, you should be grouping by ID. Second, if you return this ID in your column list, you can compose whatever URL you desire. I don't see how I can be of any more help -- please clarify. [!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][i]BTW, I'll be gone for the rest of the day.[/i][!--sizec--][/span][!--/sizec--]
[/quote]

i am pretty sure i do want to group by article becuase i dont even call the data from the row id i only want the each articles average score. how ever all i am trying to do is convert the article which is an integer to a link to the article (article.php)

so far i get an array from $row[0] of all the article numbers grouped with $row[1] thier average rating.

i've been trying something like:

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

$article = $row[0];

//adding this if statement

[b]if ($article = '100' ) {
// code here run if $var is positive
$article = '<a href="article.php">The Full House</a>';[/b]
}

printf("<b>Article:</b> %s<br><b>Avg Rate:</b> %s<br><br><hr>", $article, $row[1]);

}

but it displays the link for all articles not just the article 100

thanks for all your help so far...

[fenway]

Oh... article is your UID. Why not do the following?

[code]if ($article = '100' ) {
// code here run if $var is positive
$article = '<a href="article.php?uid=$article">The Full House</a>';
}[/code]

[a2bardeals]

[!--quoteo(post=380263:date=Jun 5 2006, 01:08 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 5 2006, 01:08 PM) [snapback]380263[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Oh... article is your UID. Why not do the following?

[code]if ($article = '100' ) {
// code here run if $var is positive
$article = '<a href="article.php?uid=$article">The Full House</a>';
}[/code]
[/quote]



i did try that. it out puts every Article (UID) as the link not just the ones that are 100

would it work if i called all the other numbers as else if statements? if so how to i structer that?

[fenway]

Silly mistake -- you're using the assignment operator (=) instead of the comparison operator (==), so it always evaluating to true.

[a2bardeals]

thanks soooooo much
thats crazy i was sooo close the whole time.
ps. i have been working on this script straight for about 4 days.

on last question...

on my original rating script i have set it so that an ip address can only vote 1 time. but i think i did it wrong becuase i want it to be able to only rate each article one time and i can't vote on any other articles in the same ip heres the code so far:

on the article page it requires this file and calls fuction show form in the page.

[code]

$days     = 0;
$m        = 2;
$C        = 6;

@mysql_connect($server,$username,$password);
@mysql_select_db($database);

if($_POST['action'] == "doit")
{
  if($_POST['rates']>0 && $_POST['rates']<11 && !empty($_POST['rateit_id']))
  {

//get the users IP

   $ip  = getenv("REMOTE_ADDR");

//this query is the one i am wondering about

   $query = "SELECT count(*) AS num FROM ratings WHERE (datum>SUBDATE(CURRENT_DATE(),INTERVAL ".$GLOBALS['days']." DAY) AND ip='$ip' AND article='".$_POST['rateit_id']."')";

   $rs = mysql_query($query) or die("LINE 17:".mysql_error());
   $as = mysql_fetch_array($rs);
   if($as[num]==0)

//here is where it posts the ip and other info into the database

      mysql_query("INSERT INTO ratings VALUES(0,'".$_POST['rateit_id']."','".$_POST['rates']."','".$ip."',NOW())") or die(mysql_error());
   echo $ad[num];
  }
}

function ShowForm($id,$num,$rate_me="Rate Me",$rate_it="Rate!",$class="")
{
   echo '<form action="'.$GLOBALS['PHP_SELF'].'" method="post">';
   echo '<select name="rates" class="'.$class.'">';
   echo '<option value="x" selected>'.$rate_me.'</option>';
   for($x=$num;$x>0;$x--)
    echo '<option value="'.$x.'">'.$x.'</option>';
   echo '</select>';
   echo '<input type="hidden" name="rateit_id" value="'.$id.'">';
   echo '<input type="hidden" name="action" value="doit"> ';
   echo '&nbsp;<input type="submit" value="'.$rate_it.'" class="'.$class.'">';
   echo '</form>';
}[/code]

[fenway]

If you limit by IP, you might get into trouble with large groups of people behind the same router. Either way, you should do the logic in the DB -- like with an INSERT INTO IGNORE -- and have a unique key that includes the ip/vote.