Sums without MySQL side Algorithms

[philulrich]

So I have been working on a script on my local server. This script is a poll script and essentially people are voting either Republican or Democrat which is submitted with a myriad of other information into the database. The database has a single column for both the D and the R. I need to get a sum of both of those individually and display them onto a web page. Previously I had used the SQL below to achieve a dynamic database that updates with the votes and then displayed that on the page, however my host that I am going remote with, will not allow me to use it because I need root access.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `totals` AS select count(`votes`.`Party`) AS `total`,`votes`.`Party` AS `party` from `votes` group by `votes`.`Party`;

 

Please advise how I can achieve what I need through PHP rather than the SQL way I was going before. Thanks!

[Psycho]

Why are you making the query so complicated? Just use COUNT() with a GROUP BY. Just follow normal process of connecting to the database (look up a tutorial if needed).

SELECT Party, COUNT(Party) as total
FROM votes
GROUP BY Party

[philulrich]

Thank you for your help so far, so here is what I am trying, and I am not getting anything. Pardon me, but I am kind of a PHP n00b still.

<?php
mysql_select_db($database_ballot, $ballot);
$sql = "SELECT party, COUNT(party) as total FROM votes GROUP BY party";
$result_total = mysql_query($sql);
mysql_result($result_total,1);
?>

 

The initial variables for selecting the database are defined in a separate file that is required at the beginning.

[Psycho]

What do you mean by "...I am not getting anything.". Did you check that the query ran successfully? were there records returned? What?

 

There is nothing in the code above that would DO anything. You need to echo/output the results in some way. Plus, the mysql_result() function above is trying to access the second record.

 

Try the following:

mysql_select_db($database_ballot, $ballot);
$sql = "SELECT party, COUNT(party) as total FROM votes GROUP BY party";
$result = mysql_query($sql);
if(!$result)
{
    echo "The query failed: " . mysql_error();
}
elseif()
{
    echo "No results returned";
}
else
{
    while($row = mysql_fetch_assoc($result))
    {
        echo "{$row['party']}: {$row['total']}<br />\n":
    }
}

[philulrich]

I am getting syntax errors at line 8 and 12 of your code.

[noXstyle]

I am getting syntax errors at line 8 and 12 of your code.

 

obviously else if() takes some condition(s) in it... try: else if(mysql_num_rows($result)==0)

[philulrich]

 

obviously else if() takes some condition(s) in it... try: else if(mysql_num_rows($result)==0)

 

Yeah, I realized this after I posted and I was coming a blank of what I could put in there, but this works awesome, thanks!