Page 2 of 2

Re: array_sum on key

Posted: Mon Aug 17, 2009 5:32 pm
by Ollie Saunders
get_points_by_id() is a bad name for a function that doesn't return points and points alone. Your get_points_by_id() returns a lot more than that. It returns a whole array of stuff so it's not returning points. This is a source of confusion between us. I'd question the necessity to create this whole dummy structure just for the case where no rows are returned.

Things you probably already know but might not, and I'm sorry if this is getting patronising but I'm just trying to cover possibilities here:
  • mysql_query() returns a MySQL query result resource that can be used for a variety of things including mysql_fetch_assoc() and mysql_num_rows().
  • mysql_fetch_assoc() is used to retrieve the rows from a query and takes a MySQL query result resource to ascertain which query that it.
  • mysql_fetch_assoc() returns a PHP array. That PHP array is that separate from MySQL and no longer has anything to do with the database. The database might change or explode but that array will stay the same. MySQL was involved in getting you that array but once you've got it MySQL can't help you anymore.
  • For all intents and purposes functions beginning with mysql_ will interact with the database.
  • mysql_num_rows() will tell you how many rows a query has returned by providing a result resource.
  • count() will tell you how many elements are in an array.

Code: Select all

$qRes = mysql_query($q);
$rows = array();
while ($rows[] = mysql_fetch_assoc($qRes));
echo count($rows); # that's how many rows I got

Code: Select all

$qRes = mysql_query($q);
echo mysql_num_rows($qRes); # that's how many rows I'm gonna get if I called mysql_fetch_assoc($qRes) in a while loop.
As a matter of simplicity for yourself. This:

Code: Select all

       $query = "SELECT * ";
        $query .= "FROM points ";
        $query .= "WHERE player_id=" . $player_id ." ";
may be shortened to

Code: Select all

$query = 'SELECT * FROM points ' . (int)$player_id;
Three lines becomes 1. Less code is better. The (int) will ensure that $play_id is converted to an integer before being concatenated to the query and is a security feature (Prevents SQL injection; Google those terms for more links).

Anyway knowing what I now do about your database schema. I think show you what you're trying to do:

Code: Select all

function getTotalPointsForPlayer($id) {
  global $connection;
  $qRes = mysql_query("SELECT * FROM player WHERE player_id " . (int)$id);
  $rows = array();
  while ($rows[] = mysql_fetch_assoc($qRes));
  $points = array();
  foreach ($rows as $r) { $points[] = $r['points']; }
  return array_sum($points); }
which can be shortened to:

Code: Select all

function getTotalPointsForPlayer($id) {
  global $connection;
  $qRes = mysql_query("SELECT * FROM player WHERE player_id " . (int)$id);
  $total = 0;
  while ($row = mysql_fetch_assoc($qRes)) { $total += $row['points']; }
  return $total; }
which can be shortened to:

Code: Select all

function getTotalPointsForPlayer($id) {
  global $connection; # It may be safe to remove this line also.
  $q = "SELECT sum(points) AS totalPoints FROM player WHERE player_id " . (int)$id;
  return mysql_fetch_object(mysql_query($q))->totalPoints; }

Re: array_sum on key

Posted: Mon Aug 17, 2009 6:19 pm
by Skip_B
get_points_by_id() is a bad name for a function that doesn't return points and points alone. Your get_points_by_id() returns a lot more than that. It returns a whole array of stuff so it's not returning points. This is a source of confusion between us. I'd question the necessity to create this whole dummy structure just for the case where no rows are returned.
Points are pretty much all it does return. I do think there is confusion here. That is not the purpose. This is not a flat file it is relational. I have a Tavern table with Players in another table and points in yet another. For each Tavern there are many players. For each player there are from 0 - many points records (rows). Other than the date and tournament name the only things the points database has in it are the player's id and the Tavern's id which create the relationships.

My first function isolates a particular tavern. My second gets a list of all the players at that tavern. This function gets all the points for all those players. I don't need the tournament date, number or place finished, which I discard after extracting the points and totaling them by player_id. But I need the tournament_id and player_id and the points for that player. Then I have to add up all the points fields for each player. The result_set returned from this function contains only a list of points with the player ids and the few extra fields in the table I don't need. The points can not be stored in the Player table since it is one to many. Using a while loop I plan to step through this result set once for each player, adding up the points for that player. Then I need to display the Tavern name, only once as a heading, from the Tavern database, a player's name from the Player's table, his point total, from summing the points according to his Player_id, and his ranking. Highest point is first and so forth.

Things you probably already know but might not, and I'm sorry if this is getting patronising but I'm just trying to cover possibilities here:
NOT AT ALL I REALLY like your explanation of the basics. Thank you.
I considered using count on the array since I could so easily get the array. :roll:

I am going to look over the code you have provided. At first blush it looks like you were thinking the points were merely a field in the player's table. I wish that were the case.

My total approach may have been wrong from the beginning. I actually asked for concept help on 3 forums and got no reply. You guys like to work with code and I get that. Anyway here is the deal. Tavern table. I have a simple one line lookup form to enter their tavern name. Then I need the list of players with their current point totals arranged in descending order. What sequence would you use? I could find all of the points under the Tavern_id then pick out the players and total them but that seemed like one extra step. One hurdle I have not addressed yet is what I am going to do with all the individual point totals. I assume I will have to build an array of player_ids and point totals, then sort them then display them. For now I would be happy to find one player, add up his points and echo those values.

Don't kill yourself on this but I do thank you for putting in the effort. I will figure this out one way or another but it is nice to have the collaboration.

Re: array_sum on key

Posted: Mon Aug 17, 2009 6:23 pm
by Skip_B
As far as building the query one line at a time. That is partly stylistic but my way has function in development. When I get an error message it points to the EXACT section of the query with the issue. I can also comment out sections if I need to. Once it is perfected I sometimes concatenate them. They are stored in functions so the code on my page is one line either way.

Re: array_sum on key

Posted: Mon Aug 17, 2009 9:58 pm
by Skip_B
Please don't waste another minute here. I have changed direction and am taking the easy way out. I will simply update a field in the players table every time new points are posted. Storing the cumulative points in the players table is fragile and easy to breakdown but it makes reporting easy and will be faster. I will create an audit routine which can verify the points values periodically to be sure no changes were made to the points table.

I rejected this approach in the beginning in favor of a dynamic system but no longer feel it is worth the trouble.

Thanks so much to those who pitched in. Maybe nex year I will convert it. :lol:

Re: array_sum on key

Posted: Mon Aug 17, 2009 10:35 pm
by Ollie Saunders
OK, well I decided I would give you a little more time before helping further anyway. Turns out that was the right decision. Sounds like you've made a good decision too. Good luck.

Re: array_sum on key

Posted: Tue Aug 18, 2009 8:50 am
by Skip_B
Thanks again and I look forward to working with you in the future.