Finding sum of array elements then deleting element.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Finding sum of array elements then deleting element.

Post by primate »

Hi,

I have a results set based on a query from two separate tables. I've combined the results into one array. The results themselves are search results from forums. When I display the results I only want one result per PostID, but I want the sum of the score for all results for a given PostID so the results can be sorted by relevancy before being displayed.

I've got as far as this but can't get any further:

Code: Select all

foreach ($forum_results as $x => $array) { 
		  	
	$nextarray = next($forum_results);   //get the next sub-array
			
	if (!isset($key)){  //If $key is already set we must have a match so don't want to advance it
			$key=$x;   //get key for current position in $forum_results array so we can add the scores correclty
			}
				
	if ($array['PostID'] == $nextarray['PostID']){
				

		$forum_results[$key]['Score'] = $array['Score'] + $nextarray['Score'];  //add the score to the original array
				
		$x++; //increment $x to delete the next value ($nextvalue)
		unset ($forum_results[$x]);
		reset ($forum_results);
				
		} else { 
					
		unset ($key);  //if there is no match then we want $key to point to next new PostID
					
			}
			
		}
An example of an array being fed into the script is as follows:

Code: Select all

Array
(
    ї0] => Array
        (
            їPostID] => 53
            їTitle] => blah
            їScore] => 288
        )

    ї1] => Array
        (
            їPostID] => 53
            їScore] => 64
            їTitle] => blah
        )

    ї2] => Array
        (
            їPostID] => 53
            їScore] => 64
            їTitle] => blah
        )

    ї3] => Array
        (
            їPostID] => 53
            їScore] => 64
            їTitle] => blah
        )

    ї4] => Array
        (
            їPostID] => 53
            їScore] => 64
            їTitle] => blah
        )

    ї5] => Array
        (
            їPostID] => 53
            їScore] => 480
            їTitle] => blah
        )

    ї6] => Array
        (
            їPostID] => 53
            їScore] => 64
            їTitle] => blah
        )

    ї7] => Array
        (
            їPostID] => 56
            їScore] => 160
            їTitle] => testing
        )

    ї8] => Array
        (
            їPostID] => 57
            їTitle] => test
            їScore] => 192
        )

    ї9] => Array
        (
            їPostID] => 57
            їScore] => 240
            їTitle] => test
        )

    ї10] => Array
        (
            їPostID] => 57
            їScore] => 192
            їTitle] => test
        )

)
The output from this array is:


Code: Select all

Array
(
    ї0] => Array
        (
            їPostID] => 53
            їTitle] => blah
            їScore] => 544
        )

    ї7] => Array
        (
            їPostID] => 56
            їScore] => 160
            їTitle] => testing
        )

    ї8] => Array
        (
            їPostID] => 57
            їTitle] => test
            їScore] => 432
        )

    ї10] => Array
        (
            їPostID] => 57
            їScore] => 624
            їTitle] => test
        )

)
So its kind of getting there but sometimes it seems to add the results too many times, or in this case not match the final PostID but carries through the previous score....Just been going round and round through the script and can't seem to find a solution, any suggestions?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

This really sounds like something that could be solved with the query. I imagine the query that feeds this code is something like this:

Code: Select all

SELECT
   PostID,
   Title,
   Score
FROM
   some_table
WHERE
   initial_post_id = 'some_value'
It could be modified slightly to give you the sum and only one result per id

Code: Select all

SELECT
   PostID,
   Title,
   Score,
   sum(Score) as 'sum'
FROM
   some_table
WHERE
   initial_post_id = 'some_value'
GROUP BY
   PostID
Doing this work in the query will reduce processing overhead (which may be minimal anyway) and load time.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

That makes sense.....however the results set is based on querying two tables, there's a table for posts and a table for replies. So even if I manage to get a results set with unique PostID's for the replies table, I may still have a duplicate if the search term also appears in the original post - the PostID is the foreign key in the Replies table referring back to the original Post.

The query is also based on a search against a full text index of the tables so I am unsure how to change it as you suggest. The query currently looks like this:

Code: Select all

$query = "SELECT Posts_Tbl.PostID, Posts_Tbl.ForumID, Posts_Tbl.Title, Key_Tbl.RANK
FROM  Posts Posts_Tbl INNER JOIN
      CONTAINSTABLE(Posts, Post, '$search_string') Key_Tbl ON 
      Posts_Tbl.PostID = Key_Tbl.[KEY]
ORDER BY Key_Tbl.RANK DESC";
there's then a similar one against the Replies table.

So, what do you suggest?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Is that the one that does the searching too? Because I don't see that clause in there. I think this query will get all the data from the Post_Tbl you wanted, the sum of all the replies, and search the fulltext index. Having the MATCH...AGAINST line in the WHERE clause will also sort the results by relevancy. Grouping on the PostID should also limit you to one entry per post.

I make no promises on the speed of this query (or on it even working for that matter) :)

Code: Select all

SELECT
    p.PostID as 'PostID',
    p.ForumID,
    p.Title,
    k.RANK,
    r.ReplyID
    sum(r.Score) as 'Sum',
    MATCH(index_field1,index_field2,etc) AGAINST('$search_string') as 'FULLTEXT'
FROM
    Posts_Tbl as p,
    Key_Tbl as k,
    Results_Tbl as r
WHERE
    MATCH(index_field1,index_field2,etc) AGAINST('$search_string') AND
    r.PostID = p.POSTID
GROUP BY
    p.PostID
If nothing else, hopefully this query will set you in the right direction logically.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Sorry for not getting back, I'm not ignoring your assistance, just been out of the office or working on other bits and bobs, will try this out asap.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

If I'm reading correctly you need the sum of two integers.

set up your query, put the sum into a variable, loop through them and add them together.

Code: Select all

$query = mysql_query("SELECT number FROM table WHERE name = 'bob'");
while($array = mysql_fetch_array($query){ $total += $array['num']; }
$interger_1 = $total;

$query2 = mysql_query("SELECT number2 FROM table WHERE name = 'bob2'");
while($array2 = mysql_fetch_array($query2){ $total2 += $array2['num2']; }
$interger_2 = $total2;

$final_total = $integer_1 + $integer_2;
Post Reply