Little help with SUM

[trekky28]

This is what I tried. Gives no error but doesn't display any results either. What am I missing here?

<?

$grab_votes = "SELECT SUM(Item1), SUM(Item2), SUM(Item3) FROM votes";
$result = mysql_query($grab_votes);
sql_query("$result", "$error_messages[04]");

$itemVotes = array("Item1","Item2","Item3"); 
$itemNames = array("Item1","Item2","Item3"); 

$num_rows = mysql_num_rows($result);

IF (mysql_num_rows($result) == 0)
{
echo ("<SPAN CLASS='error_text'>
No entries exist.
</SPAN>");
}


ELSE
{
?>

<DIV ALIGN='CENTER'>
<TABLE BORDER=0 COLS=2 WIDTH=300>
<TR>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Item</B>
</TD>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Votes</B>
</TD></tr>


<?


$row_counter = 0;

while ($row = mysql_fetch_array($result))
{
++$row_counter;
$itemNames = $row["item"];
$itemVotes = $row["votes"];
rounded ($row_counter);
?>



<TR>

<TD ALIGN='CENTER' CLASS='<? require ("votes_config.php"); echo ($row_background); ?>'>
<? echo ($item); ?>
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($votes); ?>
</TD>

</TR>

[qadeer_ahmad]

Hi,

Just remove this line and check the result
sql_query("$result", "$error_messages[04]");

[trekky28]

Nope, same result. Nothing is displayed apart from table header..

[qadeer_ahmad]

while ($row = mysql_fetch_array($result))
{

before this again run mysql_query because you already get data from this $result on num method so before this again write this
$result = mysql_query($grab_votes);
it will give you data, hopefully.

[trekky28]

Nope, I tried that too before. Even removed the num check. It doesn't display data.
I think something's wrong with the overall array display..

[qadeer_ahmad]

Ohh ic one thing i chech
IF (mysql_num_rows($result) == 0)
php is casesentisive in if so its should be
if (mysql_num_rows($result) == 0)
also
ELSE will be else

perform all changes above which i tell you before this post and check again.

[trekky28]

Nope, didn't work either...
I can get the item values displayed each but I want them to be sorted desc..else¨
I would just write <? echo ($Item1); ?>
and so on, on each row.. Same with item name which is the same as column name.

[qadeer_ahmad]

and
<? echo ($item); ?>
where you get value for $item thier will be
<? echo ($itemNames); ?>
and
<? echo ($votes); ?>

will replace with
<? echo ($itemVotes); ?>

[qadeer_ahmad]

can you tell me structure of votes table?

[qadeer_ahmad]

ok i arrange a script that will help you

<? 

$grab_votes = "SELECT SUM(Item1) as item1, SUM(Item2) as item2, SUM(Item3) as item3 FROM votes"; 
$result = mysql_query($grab_votes); 

$itemVotes = array("Item1","Item2","Item3"); 
$itemNames = array("Item1","Item2","Item3"); 



if (mysql_num_rows($result) == 0) 
{ 
echo ("<SPAN CLASS='error_text'> 
No entries exist. 
</SPAN>"); 
} 
else 
{ 
?> 

<DIV ALIGN='CENTER'> 
<TABLE BORDER=0 COLS=2 WIDTH=300> 
<TR> 
<TD CLASS='table_header' ALIGN='CENTER'> 
<B>Item</B> 
</TD> 
<TD CLASS='table_header' ALIGN='CENTER'> 
<B>Votes</B> 
</TD></tr> 


<? 


$row_counter = 0; 
$result2 = mysql_query($grab_votes); 
while ($row = mysql_fetch_array($result2)) 
{ 
++$row_counter; 
$item1 = $row["item1"]; 
$item2 = $row["item2"];
$item3 = $row["item3"];

rounded ($row_counter); 
?> 



<TR> 

<TD ALIGN='CENTER' CLASS='<? require ("votes_config.php"); echo ($row_background); ?>'> 
<? echo ($item1); ?> 
</TD> 

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'> 
<? echo ($item2); ?> 
</TD> 

</TR> 

it will be use to show sum if you want to show all records then you need new query before while statment like this

$grab_votes = "SELECT * FROM votes";
then
these value depend on your votes table structure
if thier is three field you want to get then you can also use like this
$item1 = $row[0];
$item2 = $row[1];
$item3 = $row[2];
or give field name exact as table fields name

i hope this will solve your problem
Best of Luck

[Blulagoon]

Try and see if this works for you:

$grab_votes = "SELECT SUM(Item1) AS vote1, SUM(Item2) AS vote2, SUM(Item3) AS vote3 FROM votes"; 
$result = mysql_query($grab_votes); 
$row = mysql_fetch_assoc($result);
$vote1 = $row['vote1'];  // and same for vote2 and vote3
echo $vote1;

This should get your SUM calculations into appropriate variables which you can then use as required elsewhere in the script. Always a good idea to echo out the variables right at the beginning so you know that you have the right values there, then delete that line of code later.

HTH - Blu

[trekky28]

Hi guyz.

The $row = mysql_fetch_assoc($result); does work.

However,

Table display should be

<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item1); ?> // Itemname here (Item1)
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item1); ?> //Itemvalue here (Item1)
</TD>

</TR>
<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item2); ?> // Itemname here (Item2)
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($Item2); ?> //Itemvalue here (Item2)
</TD>

</TR>
And so on.. Sorted bt Itemvalue desc.

[Blulagoon]

Once you've got the SUM values ($vote1 etc from my example) you could insert them into a temporary table along with the item name, then do a select on the temp table ordering the output by value desc.

Probably not the neatest way of doing it, but it should work just fine. The temp table should only exist for the duration of the mysql connection, however with temp tables I always use mysql_close($conn), which of course one always should use ........

HTH - Blu

[trekky28]

I've never done a temporary table. How does it work?

[Houdini]

They work the same as a regular table except when you are finished using it it is destroyed, you might want to search for tutorails on how to create and use them.

[Blulagoon]

I've been trying to find a web resource for you, will post one if I find one:

Broadly you use the same syntax as you would with MySQL's CREATE TABLE function:

$sql="CREATE TEMPORARY TABLE temptable (item_name VARCHAR(30), vote_score INT)";
$result=mysql_query($sql);

Then use INSERT INTO temptable(columns) values(values) as you would any table.

HTH - Blu

[Blulagoon]

If your site is busy and you are worried about a second user trying to create a temporay table with the same name as an existing one (unlikely but ....... ) you can do something like this:

<?PHP

$rand=mt_rand(1,1000);
$tablename="temptable".$rand;

$sql="CREATE TEMPORARY TABLE ".$tablename." (item_name varchar(20), vote_score int)";

echo $tablename;
echo "<br>";
echo $sql;
?>

[trekky28]

I'm not sure what I'm doing and it's not working...

Here's what I've done:

<?

$mysql_link = mysql_pconnect( "$DBhost", "$DBuser", "$DBpass") or die
("Cannot reach the server. Please send a mail to $admin_email.");
mysql_select_db( "$DBName") or die( "Cannot reach the database. Please send a mail to $admin_email.");


 $grab_votes = "SELECT SUM(al) AS sum_al, SUM(ad) AS sum_ad, SUM(am) AS sum_am FROM votes";
$result = mysql_query($grab_votes);
$row = mysql_fetch_assoc($result);
$sum_al = $row['sum_al']; 
$sum_ad = $row['sum_ad']; 
$sum_am = $row['sum_am'];  

$sql="CREATE TEMPORARY TABLE temptable (item VARCHAR(30), total INT)";


$insert_votes ="INSERT INTO temptable(item, total) VALUES('al', '$sum_al'),('ad', '$sum_ad'),('am', '$sum_am');



$grab_total = "SELECT SUM(total) as total, item FROM temptable GROUP BY item ORDER BY total DESC , item ASC";
$result4 = mysql_query($row,$sql,$insert_votes,$grab_total);
sql_query("$result4");
?>

<DIV ALIGN='CENTER'>
<TABLE BORDER=0 COLS=2 WIDTH=300>
<TR>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Item</B>
</TD>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Votes</B>
</TD></tr>


<?


$row_counter = 0;

while ($row = mysql_fetch_array($result4))
{
++$row_counter;
$item = $row["item"];
$total = $row["total"];
rounded ($row_counter);
?>



<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($item); ?>
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($total); ?>
</TD>

</TR>

<?
}
?>

[trekky28]

I've given it some thought and came up with this as well.
I get a strange error though.

Something is wrong with $insert_total

<?

$mysql_link = mysql_pconnect( "$DBhost", "$DBuser", "$DBpass") or die
("Cannot reach the server. Please send a mail to $admin_email.");
mysql_select_db( "$DBName") or die( "Cannot reach the database. Please send a mail to $admin_email.");

$insert_total = "REPLACE INTO temptable(item, total) VALUES('al', 'SUM(al)'),('ad', 'SUM(ad)') FROM votes";
$result3 = mysql_query($insert_total);


$grab_total = "SELECT SUM(total) as total, item FROM temptable GROUP BY item ORDER BY total DESC , item ASC";
$result4 = mysql_query($grab_total);
sql_query("$result3","$result4");
?>

<DIV ALIGN='CENTER'>
<TABLE BORDER=0 COLS=2 WIDTH=300>
<TR>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Item</B>
</TD>
<TD CLASS='table_header' ALIGN='CENTER'>
<B>Votes</B>
</TD></tr>


<?


$row_counter = 0;

while ($row = mysql_fetch_array($result4))
{
++$row_counter;
$item = $row["item"];
$total = $row["total"];
rounded ($row_counter);
?>



<TR>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($item); ?>
</TD>

<TD ALIGN='CENTER' CLASS='<? echo ($row_background); ?>'>
<? echo ($total); ?>
</TD>

</TR>

<?
}
?>

[trekky28]

Think it works now.. A whole new world opened up!
🙂

$grab_votes = "SELECT SUM(al) AS sum_al, SUM(ad) AS sum_ad, SUM(am) AS sum_am FROM votes";
$result = mysql_query($grab_votes);
$row = mysql_fetch_assoc($result);
$sum_al = $row['sum_al'];
$sum_ad = $row['sum_ad'];
$sum_am = $row['sum_am'];  

$insert_total = "REPLACE INTO temptable(item, total) VALUES('al', '$sum_al'),('ad', '$sum_ad'),('am', '$sum_am')";
$result3 = mysql_query($insert_total);


$grab_total = "SELECT SUM(total) as total, item FROM temptable GROUP BY item ORDER BY total DESC , item ASC";
$result4 = mysql_query($grab_total);
sql_query("$row","$result3","$result4");