Code: Select all
$query = "SELECT sum(points) FROM datatable WHERE driver = 'myname' ORDER BY points DESC LIMIT 3";Any suggestions?
Moderator: General Moderators
Code: Select all
$query = "SELECT sum(points) FROM datatable WHERE driver = 'myname' ORDER BY points DESC LIMIT 3";Surely this query would only return 1 record, you are doing a SUM on a field based on only 1 driver, so it is going to sum all points for that 1 driver, there is no other grouping involved.Matt Phelps wrote:I have the following snippit of code from my site and it doesn't work. I think I am trying to do something with MYSQL that it doesn't like:
What I am trying to do is to find the lowest three numbers in the field 'points' and add them together. Can this be done using MYSQL or will I have to manipulate the data in php to find the answer?Code: Select all
$query = "SELECT sum(points) FROM datatable WHERE driver = 'myname' ORDER BY points DESC LIMIT 3";
Any suggestions?
Code: Select all
if ($drop_worst_three == 'yes')
{
$query = "SELECT driver from $series WHERE position = '1' ";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$num_races = mysql_num_rows($result);
if ($num_races > 3)
{
// drop worst three only if there have been more than three races
$query = "SELECT sum(points) FROM $series WHERE driver = '$driverї$counter]' ORDER BY points ASC LIMIT 3";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$lowest_three_points = mysql_result($result, 0);
$total_points = $total_points - $lowest_three_points;
}
}Hi llimllib,llimllib wrote:There could be several references to myname in that one table - imagine that it's a foreign key, and there are 3 cars in this table witih 'myname' as a driver. You're making the assumption that myname is the primary key for the table.
Code: Select all
if ($drop_worst_three == 'yes')
{
if ($num_races > 3)
{
// drop worst three only if there have been more than three races
$query = "SELECT points FROM $series WHERE driver = '$driverї$counter]' ORDER BY points ASC LIMIT 3";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$lowest_three_points = '0';
while ($cursor = mysql_fetch_array($result))
{
$number = array_pop($cursor);
$lowest_three_points = $lowest_three_points + array_pop($cursor);
}
$total_points = $total_points - $lowest_three_points;
}
}