PHP array_sum not working

Question

I am trying to query an SQL table, and then have all the values from one column (it's a "tinyint(1)") added up and then printed, using array_sum. (Currently there are only two rows).

When both rows have a "1" in that column, it gives 2, the correct answer. And when they are both "0" it gives 0, correct again. But when one is "1" and one "0" it gives 2, not 1.

This is my code

$con = mysql_connect("XXX","XXX","XXX");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("XXX_test", $con);

$day = $_GET["day"];

$query = mysql_query("SELECT $day FROM sites");

if (!$query)
  {
  die('Could not query: ' . mysql_error());
  }

$rows = mysql_fetch_array($query);

echo array_sum($rows);

mysql_close($con);

?>

Thanks for any help.

--UPDATE--
I just solved this myself.

With this, if you want to know: $query = mysql_query("SELECT name, SUM($day) AS Alerts FROM sites");

while($row = mysql_fetch_array($query))
  {
  echo $row['Alerts'];
  echo "<br />";
  }

Based on your code nothing is insinuating that $rows is an array. If you were $rows[] = mysql_fetch_result($query,0); then summed it I would wonder why it's not working. But you're getting a key-value array back, so what are you expecting to sum? — Brad Christie
– Brad Christie, Commented Dec 19, 2011 at 15:34

lonesomeday · Accepted Answer · 2011-12-19 15:35:43Z

4

This in fact never works correctly.

$rows = mysql_fetch_array($query);

That line only ever fetches the first row. The second row is never retrieved.

The reason it gives 2 using array_sum is that mysql_fetch_array gets the result in two different formats, with keys both by name and number. So you'll get something like this:

array(
    0 => '1',
    'Tuesday' => '1'
)

Obviously this will always be 2 or 0.

You should almost certainly do a count on the DB server:

SELECT COUNT(*) FROM sites WHERE $day=1

Note, however, that your biggest current problem is the enormous gaping SQL injection hole. Sanitise your input.

answered Dec 19, 2011 at 15:35

lonesomeday

239k54 gold badges330 silver badges329 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ACarter Over a year ago

Thanks, but what do you mean by the last two sentences?

lonesomeday Over a year ago

@ACarter Read up on SQL injection. Basically, with your code as it currently is, any malicious user could execute arbitrary SQL on your database.

ACarter Over a year ago

Oh, yes. When it is finished, I am planning to add a check, so the SQL connection doesn't even start unless a POST parameter is the same as a 'password' given in the code. But thanks for saying that.

ACarter Over a year ago

So if the new code is: $query = mysql_query("SELECT COUNT(*) FROM sites WHERE $day=1"); How do I print it? Thanks

lonesomeday Over a year ago

No, that won't necessarily help. In this case, you should probably check that $day is one of the possible days of the week. I'm not sure your database schema (days of the week as column names?) is well designed, but nonetheless... You should be able to do $row = mysql_fetch_row($query); $num = $row[0];

|

Explosion Pills · Accepted Answer · 2011-12-19 15:33:35Z

3

mysql_fetch_array() only fetches one row at a time. You need to fetch the rows in a while loop. Then you can either append the results to an array and sum those, or sum them in the loop. You can also SUM in mysql anyway, so you might look into using that.

I'm also obligated to suggest that you use PDO or some other DB wrapper instead of mysql_. You also need to escape variables you use in queries under most circumstances, especially if they come from _GET.

answered Dec 19, 2011 at 15:33

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

Criss · Accepted Answer · 2011-12-19 15:37:13Z

2

I would suggest doing it in the mysql query:

SELECT SUM($day) FROM sites

also, please escape the $day variable.

answered Dec 19, 2011 at 15:37

Criss

1,20110 silver badges12 bronze badges

2 Comments

ACarter Over a year ago

Thanks. What do you mean by "escape the variable"?

Criss Over a year ago

$day = mysql_real_escape_string($_GET['day'], $con); doing this, you prevent sql injection attacks. right now, you just pass the gat-variable without protecting it from bad input

hakre · Accepted Answer · 2011-12-19 15:36:36Z

1

More of a comment:

array_sum can not work with the array you're passing to it. You need to pass an array for what the function actually works, like array(1, 2, 3) which will give you 6 then.

Next to that, let MySQL itself calculate the SUM (or COUNT?), so it's easier to handle in your script as others have posted.

answered Dec 19, 2011 at 15:36

hakre

200k55 gold badges454 silver badges865 bronze badges

Comments

ACarter · Accepted Answer · 2012-02-17 12:53:41Z

0

I just solved this myself.

With this, if you want to know: $query = mysql_query("SELECT name, SUM($day) AS Alerts FROM sites");

while($row = mysql_fetch_array($query))
  {
  echo $row['Alerts'];
  echo "<br />";
  }

answered Feb 17, 2012 at 12:53

ACarter

5,7479 gold badges42 silver badges60 bronze badges

Collectives™ on Stack Overflow

PHP array_sum not working

5 Answers 5

6 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related