Page 1 of 1

MySQL - PHP - How to filter text and calculate average

Posted: Tue Nov 01, 2011 4:17 pm
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
";
}
}

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

Posted: Tue Nov 01, 2011 4:30 pm
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.

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

Posted: Tue Nov 01, 2011 4:31 pm
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);
}

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

Posted: Tue Nov 01, 2011 4:32 pm
by social_experiment
Or like that :)