php group by value

[xbat]

how do you group by value ?

I have it where it selects by drop down...

select sum (table) where item group by ?????? VALUE ????

The value I use is select

[|-|x]

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

[xbat]

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

[|-|x]

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'

[xbat]

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.

[|-|x]

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';

[Biiim]

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>

[Biiim]

<?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'];

[xbat]

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"); ?>

[xbat]

:)

[Biiim]

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"));

[xbat]

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.

[Biiim]

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

[xbat]

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