php group by value

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

What are you trying to group, what does your data look like?

The basic format is

SELECT SUM(col1) FROM table GROUP BY col2 

I just want to add the current value..Sorry I do not have the code with me at this time..

The value will come from select

I have a drop down then after selecting the drop down the data displays... the form value is select...... I want to group by the drop down

do you mean group or filter?

if you want to total the values that match the dropdown selection only then you want something like this:

SELECT SUM(col1) FROM table WHERE col2 = 'dropdownvalue' 

i just want to display the total amount the sum of the number thats in the column of the one selected..... heres sort of like what i have..

<form id="form1" name="form1" method="post" action=""> <p> <label for="select"></label> <select name="select" onchange="form1.submit()" id="select"> <option value="">Please Select Cart</option> <?php do { ?> <option value="<?php echo $row_bobsapple['bobsorange']?>"><?php echo $row_bobsapple['bobsorange']?></option> <?php } while ($row_bobsapple = mysql_fetch_assoc($bobsapple)); $rows = mysql_num_rows($bobsapple); if($rows > 0) { mysql_data_seek($bobsapple, 0); $row_bobsapple = mysql_fetch_assoc($bobsapple); } ?> </select> <?php echo $_POST['select']; ?> <th scope="col"><div class="size250"><?php echo $row_orange['bobsorange']; ?></div></th> <th scope="col"><div class="size150">pink</div></th> <th scope="col"><div class="size150"><?php echo $row_orange['blue']; ?></div></th> <th scope="col"><div class="size150"><?php echo $row_orange['date']; ?></div></th> <th scope="col"><div class="size150"><?php echo $row_orange['color']; ?></div></th> <th scope="col"><div class="size150"><a href="fruitytutr.php?idviewrec=<?php echo $row_orange['id']; ?>"> 

sum total of select of all current records selected.

sorry mate, still not sure I understand your problem correctly.

so the dropdown determines the column you want to sum? if that is the case you need to build your query dynamically.

$sql = 'SELECT SUM('.$_POST['select'].') FROM table'; 

Hows this work for you, not too sure what you're after - maybe its just the group by query also no idea what your table structure is so made a sample one. Will work in a file by itself

<?php //use this to get the data from database //field_to_count is the field you want the select to display on click /*$amountsQ = "SELECT `field_to_count`,count(*) as `amt` FROM `table` WHERE 1 GROUP BY `field_to_count`"; $amountsR = mysql_query($amountsQ); $bobsamounts = array(); while($row = mysql_fetch_assoc($amountsR)){ $bobsamounts[$row['field_to_count']] = $row['amt']; }*/ //test array to see how it works $bobsamounts = array("cars"=>55,"Trucks"=>33,"Bikes"=>81,"Ships"=>5,"Bobs"=>12); ?> <script type='text/javascript'> var bobsData = new Array(); <?php foreach($bobsamounts as $k=>$v){ echo "bobsData[\"{$k}\"] = \"$v\"\r\n"; } ?> function updateValue(obj,targeteid){ var target = document.getElementById(targeteid); if(obj.value != ''){ target.innerHTML = bobsData[obj.value]; //target.innerHTML = '<h1>'+bobsData[obj.value]+'</h1>';//if you want to add more html }else{ target.innerHTML = 'Please Select'; } } </script> <select name="select" onchange="updateValue(this,'displayDiv');"> <option value="">Please Select Cart</option> <?php foreach($bobsamounts as $k=>$v){ echo "<option value='{$k}'>{$k}</option>"; } ?> </select> <div id='displayDiv'>Please Select</div> 

<?php echo $_POST['select']; ?>

Actually looking at that you might only be wanting:

if(ctype_alnum($_POST['select'])){ //should escape the posted data to prevent damage to database $field = $_POST['select']; }else{ $field = ''; } $Q = "SELECT count(*) as `count` FROM `table where `field` = '{$field}'"; $R = mysql_query($Q); $data = mysql_fetch_assoc($R); echo $data['count']; 

ok here is my exact code - what happens is you select the fruit record, then out displays the current reocrds of fruit.. I want to total the sum of COST which displays in fruit cost for all the records of the current fruit that is being selected.

<?php require_once('data.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } mysql_select_db($database_data, $data); $query_table1 = "SELECT * FROM table1"; $table1 = mysql_query($query_table1, $data) or die(mysql_error()); $row_table1 = mysql_fetch_assoc($table1); $totalRows_table1 = mysql_num_rows($table1); $maxRows_fruit22 = 10; $pageNum_fruit22 = 0; if (isset($_GET['pageNum_fruit22'])) { $pageNum_fruit22 = $_GET['pageNum_fruit22']; } $startRow_fruit22 = $pageNum_fruit22 * $maxRows_fruit22; $colname_fruit22 = "-1"; if (isset($_POST['select'])) { $colname_fruit22 = $_POST['select']; } mysql_select_db($database_data, $data); $query_fruit22 = sprintf("SELECT * FROM fruit22rd WHERE boborange LIKE %s ORDER BY id DESC", GetSQLValueString("%" . $colname_fruit22 . "%", "text")); $query_limit_fruit22 = sprintf("%s LIMIT %d, %d", $query_fruit22, $startRow_fruit22, $maxRows_fruit22); $fruit22 = mysql_query($query_limit_fruit22, $data) or die(mysql_error()); $row_fruit22 = mysql_fetch_assoc($fruit22); if (isset($_GET['totalRows_fruit22'])) { $totalRows_fruit22 = $_GET['totalRows_fruit22']; } else { $all_fruit22 = mysql_query($query_fruit22); $totalRows_fruit22 = mysql_num_rows($all_fruit22); } $totalPages_fruit22 = ceil($totalRows_fruit22/$maxRows_fruit22)-1; ?> <?php $title = "Total fruit"; ?> <?php require("header.php"); ?> <form id="form1" name="form1" method="post" action=""> <p> <label for="select"></label> <select name="select" onchange="form1.submit()" id="select"> <option value="">Please Select fruit</option> <?php do { ?> <option value="<?php echo $row_table1['boborange']?>"><?php echo $row_table1['boborange']?></option> <?php } while ($row_table1 = mysql_fetch_assoc($table1)); $rows = mysql_num_rows($table1); if($rows > 0) { mysql_data_seek($table1, 0); $row_table1 = mysql_fetch_assoc($table1); } ?> </select> </p> <p>Records for fruit currently showing - <?php echo $_POST['select']; ?> - Records Showing Newest to oldest</p> <p>Total fruit records for fruit selected : <?php echo $totalRows_fruit22 ?><br /> <BR /> Total for fruit Records<BR /> Total for fruit<BR /> Total in all - <BR /> </p> <table width="1000" border="0"><tr> <th> <div class="size250">fruit Vin</div></th> <th><div class="size150">fruit Owner</div></th> <th> <div class="size150">fruit Location</div></th> <th> <div class="size150">fruit Cost</div></th> <th> <div class="size150">fruit record?</div></th> <th> <div class="size150">View Record</div></th> </tr></table> <?php do { ?> <table width="1000" border="1"> <tr> <th scope="col"><div class="size250"><?php echo $row_fruit22['boborange']; ?></div></th> <th scope="col"><div class="size150">fruit Owner</div></th> <th scope="col"><div class="size150"><?php echo $row_fruit22['bobpineapple']; ?></div></th> <th scope="col"><div class="size150"><?php echo $row_fruit22['fruitcost']; ?></div></th> <th scope="col"><div class="size150"><?php echo $row_fruit22['fruitrecord']; ?></div></th> <th scope="col"><div class="size150"><a href="fruit-view-record.php?idviewrec=<?php echo $row_fruit22['id']; ?>">View Complete Record</a></div></th> </tr> </table> <?php } while ($row_fruit22 = mysql_fetch_assoc($fruit22)); ?> <p>&nbsp;</p>Current fruit Cost------ <?php $qry = mysql_query ("SELECT SUM(fruitcost) AS total FROM fruit22rd GROUP BY boborange"); $row = mysql_fetch_assoc($qry); echo $row['total']; ?> <BR /><BR /> <?php $qry = mysql_query ("SELECT SUM(fruitcost) AS total FROM fruit22rd =select"); $row = mysql_fetch_assoc($qry); echo $row['total']; ?> <p>&nbsp;</p> </form> <?php mysql_free_result($table1); mysql_free_result($fruit22); mysql_free_result($table1); mysql_free_result($fruit22); ?> <?php require("footer.php"); ?> 

:)

So whats your data structure?

table1 = the main data on fruit?

and fruit22rd has a many to one relationship with table1?
eg.
table1
pineapple

fruit22rd
33 pineapple
86 pineapple
112 pineapple
?

The code seems pretty complex for doing a simple thing but i think you just want to add something like this:

$qry = mysql_query ("SELECT SUM(fruitcost) AS total FROM fruit22rd WHERE `boborange` LIKE '%{$_POST['select']}%'"); $row = mysql_fetch_assoc($qry); echo $row['total']; 

From reading this bit:

$query_fruit22 = sprintf("SELECT * FROM fruit22rd WHERE boborange LIKE %s ORDER BY id DESC", GetSQLValueString("%" . $colname_fruit22 . "%", "text"));

I think table1 was suppose to be fruit... to answer your question. And by structure you mean how I am building tables? im not sure exactly what you are looking for.

Yes beautiful!! Thank you... Exactly...

One last question... when you mean by complex please explain... Is there a better way of doing this ? I use to do php years and years ago... but I have gotten rusty over the years... I'm trying to get back into it.

Thank you so much.. I owe you a few.

It might just be me being unable to understand it that makes it complex for me - i mean group by "boborange" ?

in a standard one to many setup you could have a setup like this for orders:

clients: cid,name 1,john 2,bill 3,sue orders: oid,cid,amt 1,1,39.99 2,2,19.99 3,1,79.98 4,3,8.99 5,1,35.96 

which your's has some relation like this i cant get.

Heres how i would do it for that client->order setup without going into Ajax:

<?php /* $cliQ = "SELECT `cid,`name` FROM `clients`"; $cliR = mysql_query($cliQ); $cliA = array(); while($row = mysql_fetch_assoc($cliR)){ $cliA[$row['cid']] = $row['name']; } */ //test array $cliA = array(1=>"John",2=>"Bill",3=>"Sue"); /* $ordQ = "SELECT * FROM `clients` `c`" ." LEFT JOIN `orders` `o` ON `c`.`cid` = `o`.`cid`"; $ordR = mysql_query($ordQ); $ordA = array(); while($row = mysql_fetch_assoc($ordR)){ $ordA[$row['cid']][$row['oid']] = $row; } */ //test order array $ordA = array( 1=>array( 0=>array('cid'=>1,'name'=>'John','oid'=>1,'cid'=>1,'amt'=>39.99), 1=>array('cid'=>1,'name'=>'John','oid'=>3,'cid'=>1,'amt'=>79.98), 2=>array('cid'=>1,'name'=>'John','oid'=>5,'cid'=>1,'amt'=>35.96) ), 2=>array( 0=>array('cid'=>1,'name'=>'Bill','oid'=>2,'cid'=>1,'amt'=>39.99) ), 3=>array( 0=>array('cid'=>1,'name'=>'Sue','oid'=>4,'cid'=>1,'amt'=>8.99) ) ); ?> <html> <head> <script type='text/javascript'> function displayClient(cid){ var displayDiv = document.getElementById('displayDiv'); var targetDiv = document.getElementById('client'+cid); displayDiv.innerHTML = targetDiv.innerHTML; } </script> <style type='text/css'> .cliDiv{ display:none; } </style> </head> <body> Pick Client: <select name="clients" onchange="displayClient(this.value);"> <option value=''>Select</option> <?php foreach($cliA as $k=>$v){ echo "<option value='{$k}'>{$v}</option>\r\n"; } ?> </select> <div id='displayDiv'></div> <?php foreach($cliA as $k=>$v){ echo "<div id='client{$k}' class='cliDiv'>\r\n"; echo "<h1>{$v}</h1>\r\n"; echo "<ul>\r\n"; foreach($ordA[$k] as $key=>$value){ echo "<li>Order id: {$value['oid']} Order Value: {$value['amt']}</li>\r\n"; } echo "</ul>\r\n"; echo "</div>\r\n"; } ?> </body> </html> 

Still uses javascript so theres no page refresh - the commented sql queries will build the arrays but i just manually created 2 sample arays so it works as is

I'm guessing you have a product table and a shopping cart, so the page pulls the available preset carts and the user picks one then the cart has items in it? Thats where i get lost. And is pretty hard without knowing the mysql data structure, for that I would use 3 tables -> products, carts, cart_contents

thank you.. I get what saying... :)