MySQL - PHP - How to filter text and calculate average

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
Feno
Forum Newbie
Posts: 1
Joined: Tue Nov 01, 2011 4:08 pm

MySQL - PHP - How to filter text and calculate average

Post by Feno »

[text]Hi,

I have records in MySQL database with text like this:
- Good job, 7
- Beautiful lines, an 8

I would like to filter the text with php so only numbers are left. I will show the numbers behind the post as rating. After that I would like to calculate the average of all ratings and save it in a mysql record behind the subject rated.

I have got the following code which shows only the numbers, but I do not know how to calculate the average and put it in a record. Anyone any idea? Is the question clear?[/text]

Code: Select all

//CONNECTION MADE

$query = "SELECT review FROM reviews ";

$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){

while ($row = mysql_fetch_assoc($query)){
$review = $row['review'];
$id = $row['id'];

$string = "$review";
preg_match('/\d+/', $string, $number);
$number = $number[0];

echo "
$number
";
}
}
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: MySQL - PHP - How to filter text and calculate average

Post by social_experiment »

For an average you need to collect all the numeric values, add them together and divide them by die amount of numbers. You can add the value retrieved from the $number array to another array. Once you have an array with all the numbers you can use count($array) to get the amount of items, and array_sum($array) to get the sum of all the items in the array.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL - PHP - How to filter text and calculate average

Post by twinedev »

Code: Select all

//CONNECTION MADE

$query = "SELECT review FROM reviews ";

$query = mysql_query($query);
$numrows = mysql_num_rows($query);
$intTotal = 0;
if ($numrows > 0) {
    while ($row = mysql_fetch_assoc($query)){

        preg_match('/\d+/', $row['review'], $number);
        $intThisRating = $number[0];
        $intTotal += $intThisRating;

        echo $intThisRating;
    }
    $fAverage = $intTotal / $numrows;

    mysql_query('UPDATE whatever_table SET average_rating = '.$fAverage.' WHERE id='.$intWhateverIDtheReviewsAreFor);
}
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: MySQL - PHP - How to filter text and calculate average

Post by social_experiment »

Or like that :)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply