Fetch all rows and add them up

[JenniferLawrence]

Hi. I have a problem with basic syntax. I'm trying to fetch all rows with the column name "user_id" from the table "posts_determine". From there, I select the column name "post_counts". "post_counts" is going to be a numeric string so I want to convert the array of rows into strings. Then add them all up to get a total count. That is what I hope to achieve, but I can't seem to do it. Basically, I'm trying to figure out the total of posts someone makes per account.

$user_id = filter_var($_SESSION['user_id'], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
$prepare = $db->prepare("SELECT post_counts FROM posts_determine WHERE user_id = ?");
$prepare->bind_param("i", $user_id);
$prepare->execute();
$prepare->store_result();
if($prepare->num_rows) {
    $prepare->bind_result($post_counts);
    while($prepare->fetch()) {
        $total = $post_counts+$post_counts;
        print($total);
    }
}

However, since I am adding post_counts to itself, it won't work because it is doubling instead of just adding up the total. That's where I am a bit confused about. If I append [] to $post_counts, it becomes an array and there for I cannot add a regular string to an array of strings. However, if I have an array of strings, I can append a number to it and the number will be added to the numeric string. This however has flaws. Since I cannot predict how much a person posts, I cannot append just a random number to the array. Such as $posts_counts[29]. It's string because that is the only way I could convert an array to a regular string and then add it up. Need some guidance as to what I'm supposed to be doing.

[QuickOldCar]

One way is to define a blank array and then add to it, then use count() on the array.

$total = array();
while($prepare->fetch()) {
$total[] = $post_counts;
}

echo count($total);

if you want to calculate all the values together can use array_sum()

echo array_sum($total);

Edited June 10, 2015 by QuickOldCar

[CroNiX]

You shouldn't use php to calculate that. Use mysql's SUM() function.

 

This might be all you need to change:

$prepare = $db->prepare("SELECT SUM(post_counts) as total_posts FROM posts_determine WHERE user_id = ?");

Now you'll just a single row returned with "total_posts" in the result set with a total and don't need to loop over everything as mysql has done that internally for you.

Edited June 10, 2015 by CroNiX

[QuickOldCar]

I agree with cronix as to having mysql do it for you.

 

For the record your original way would be something as this.

 

Adding post counts to the original total.

$total = 0;
while($prepare->fetch()) {
$total = $total + $post_counts;
}

print($total);

[JenniferLawrence]

You shouldn't use php to calculate that. Use mysql's SUM() function.

 

This might be all you need to change:

$prepare = $db->prepare("SELECT SUM(post_counts) as total_posts FROM posts_determine WHERE user_id = ?");

Now you'll just a single row returned with "total_posts" in the result set with a total and don't need to loop over everything as mysql has done that internally for you.

 

Thanks a lot. That actually solved my problem with 1 single line.

 

 

One way is to define a blank array and then add to it, then use count() on the array.

$total = array();

while($prepare->fetch()) {

$total[] = $post_counts;

}

 

echo count($total);

if you want to calculate all the values together can use array_sum()

echo array_sum($total);

 

 

Thanks for the help. I think I'll stick to CroNix's answer for now. Although yours works as well.

 

EDIT:

 

 

I agree with cronix as to having mysql do it for you.

 

For the record your original way would be something as this.

 

Adding post counts to the original total.

$total = 0;

while($prepare->fetch()) {

$total = $total + $post_counts;

}

 

print($total);

 

 

If it saves me time, I'll most likely use some else's approach since my approach isn't correct.

Edited June 10, 2015 by JenniferLawrence

[QuickOldCar]

 

If it saves me time, I'll most likely use some else's approach since my approach isn't correct.

It's not incorrect, all matters what you need to do with the data.

 

For instance you may want to do a single query, then display users and each count, in the loop could show or calculate it different.

 

But if all you need is a simple count then the less you need to do with code the better.

[JenniferLawrence]

It's not incorrect, all matters what you need to do with the data.

 

For instance you may want to do a single query, then display users and each count, in the loop could show or calculate it different.

 

But if all you need is a simple count then the less you need to do with code the better.

Makes sense. Thanks for the help though. Much appreciated.