0

I need to select some values from a table column which has a datatype of "longtext" where another column has a specific value and add those values up. I need to store the sum as a variable which I will a.) echo on a webpage, and b.) use the value in a calculation later in the page.

This works to find all of the values which I need to add up:

 $query = "SELECT meta_value 
 FROM wp_postmeta 
 WHERE meta_key = '_crowdfundingtotalprice' 
 ORDER BY CAST(`meta_value` AS UNSIGNED) DESC";

and I can display the results with:

$result = mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result)){
echo $row['meta_value']. "<br>";
}

From my searching, I think I am close with my query, but my page fails to load when I try to echo the results. Here is what doesn't work:

$query = "SELECT CAST(SUM('meta_value') as UNSIGNED) 
FROM wp_postmeta 
WHERE meta_key = '_crowdfundingtotalprice'";

$result = mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result)){
    echo $row[SUM('meta_value')]. "<br>";
}

As you may have guessed, this is a Wordpress database table, and I cannot change the datatype. As always, your help is appreciated!

EDIT - Trying Gordon Linoff's suggestion below, I have removed the single quotes around meta_value. It still doesn't work, but thank you for the suggestion:

$query = "SELECT CAST(SUM(meta_value) as UNSIGNED) 
FROM wp_postmeta 
WHERE meta_key = '_crowdfundingtotalprice'";

$result = mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result)){
    echo $row[SUM(meta_value)]. "<br>";
}
Jason
  • 871
  • 1
  • 8
  • 18
  • _please_, **please** STOP using the _deprecated_ `mysql` extension [***read the red warning box***](http://www.php.net/mysql_connect) on every single man page: the extension will be dropped in the future, and emits `E_DEPRECATED` notices if running PHP 5.5 and up. learn to use `PDO` or `mysqli_*` instead (the `i` stands for improved, BTW), and learn to love prepared statements – Elias Van Ootegem Mar 30 '15 at 18:16
  • @Elias Van Ootegem understood, but for testing purposes, down and dirty this is what I used. Your helpful input toward resolution will be very appreciated. – Jason Mar 30 '15 at 18:26
  • 1
    You have single quotes around `meta_value`. You don't need any quotes at all there. I vote to close these as a typographical error. – Gordon Linoff Mar 30 '15 at 18:56
  • @GordonLinoff thank you for your suggestion. Please see my edits above. I removed the single quotes, but this still doesn't work. Would you kindly make another suggestion? – Jason Mar 31 '15 at 14:16

1 Answers1

0

SOLVED:

Thank you to Gordon Linoff for pointing out my typographical error. I ended up making a sample table and hammering this out on my own. Indeed, the single quotes in my query were not needed. However, where I was echoing the results, I needed the single quotes, but they were in the wrong place. Note $row['SUM(meta_value)'] instead of $row[SUM('meta_value')].

Additionally, I did not need to use CAST (which didn't work), because MYSQL will by default treat values as mathematical values if the query uses math.

$query = "SELECT meta_key, SUM(meta_value) 
 FROM wp_postmeta 
 WHERE meta_key = '_crowdfundingtotalprice'";

$result = mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result)){
echo $row['SUM(meta_value)'] ."<br>";
}
Jason
  • 871
  • 1
  • 8
  • 18