suming prices of products in database

[itworks]

i'm making a section in a site for a computers online store that consists in building your pc online, so i've made a function that generates a drop down menu for each component with the options being called from the products table in the database.

when you've chose all the components you have a button to go to a page where it stores the components in the built table and shows the results.

the problem is that in this results page i should be able to show the total price of the computer according with the prices of the components you've chosen.

maybe the problem is that i took the wrong direction to make the other functions and now i just ain't got a clue how to make this sum of prices.

any help would be great. if any piece of my code is needed to figure out this, just ask.

Thanks in advance.

[adamgeorge]

well, what does your <select> elements look like?

pretty much if you have something like this:

<select name=MemoryTypeID[]>
   <option value="2">DDR333</option>
   <option value="4">DDR400</option>
</select>

<select name=CPUTypeID[]>
   <option value="2">Althlon 2400</option>
   <option value="7">Althlon 2600</option>
</select>

when the form is posted whatever they selected will be put into an array: If you've set it up right, you'll pass the productID in the form. Then just use that to select the product price out of the database, and sum it up:

<?
$aMemory = $_POST["MemoryTypeID"];
$aCPU = $_POST["CPUTypeID"];
$aProducts = array_merge($aMemory, $aCPU);

foreach($aProducts as $key => $value)
   $sSQL .= "'" . $value . "'";
$sSQL = substr($sSQL, 0, -1);

$sSQL = "select sum(productPrice) from products where productID IN (" . $sSQL . ")";

?>

then just parse that query and get the result. Couldn't be too hard...

hopefully you've designed your database tables in a logical maner, in which case something like this should work fine.

well, good luck.
-Adam 🙂

[itworks]

ok, let's see if i can explain how i did it:

i have a menu in index with 5 links each one with a platform option (pentium 4 (Rimm), Athlon (DDR), etc...) this calls a page that according to the choices calls the function with this:

<?php
include("BDMySQL.class.php");						// script que faz a ligação á base de dados
include("IEConstruir.class.php");					// script com as funções

$construir = new IEConstruir()
?>
  Escolha os componentes que deseja para contruir o PC:<br><br>
<form name="form1" method="post" action="index.php?pagina=verpc&action=ver">
  <table width="100%" border="0" align="center" cellpadding="0" cellspacing="1" class="caixas">
    <tr>
      <td class="caixa"><table width="100%" border="0">
          <tr> 
            <td class="menu"> <div align="right">Processador:</div></td>
			<td><select name="processador">
<?php      $construir->processador($processador);				// chama a função de desenhar o cabeçalho da tabela dos resultados ?>
    </select></td></tr>
			<tr>
			<td class="menu"> <div align="right">Mem&oacute;ria:</div></td>
			<td><select name="memoria">
<?php      $construir->memoria($memoria);				// chama a função de desenhar o cabeçalho da tabela dos resultados ?>
    </select></td></tr>
//... other options	
		<tr>
</table></td></tr>
  </table>
  <br>
    <input type="image" class="botoes" src="imagens/botoes/construir_pc.gif">
</form>
<?php
$construir->endIEConstruir();						// chama a função que fecha a ligação á base de dados
?>

this is an example of the function that queries the database for the product according to the choice (i have one for each component):

<?php
// função que mostra os detalhes de uma marca
        function processador($processador) {
            $sql = "SELECT * FROM produtos WHERE categoria='Processador' AND tipo='$processador' ORDER BY preco ASC";
			$rs=$this->bd->executarSQL($sql);

		echo "<option>seleccione</option>\n";
		while($row=mysql_fetch_array($rs))
		{
		echo "<option name=\"$row[modelo]\" value=\"$row[modelo]\">$row[modelo] - $row[preco] €</option>\n";
		}
	}
?>

then when the form is submited the components are stored in a table and i go to another page that shows the choices selected like this:

<?php
include("BDMySQL.class.php");						// script que faz a ligação á base de dados
include("IEConstruir.class.php");					// script com as funções
	$construir = new IEConstruir();					// chama a função que inicia a ligação á base de dados
	$construir->inserirPc($processador, $memoria, $motherboard, $disco, $cdrom, $dvd, $disquetes, $placasom, $placagrafica, $monitor, $colunas, $teclado, $rato, $sistema, $login_utilizador);			// chama a função que adiciona os componentes do computador a construir
	$construir->endIEConstruir();					// chama a função que fecha a ligação á base de dados
?>
  As suas escolhas foram as seguintes:<br><br><br>
<table width="100%" cellpadding="1" cellspacing="1" class="caixas">
  <tr>
    <td class="caixa"><table width="100%" border="0">
        <tr> 
          <td class="menu"> <div align="right"><strong>Processador:</strong></div></td>
          <td><div align="left"> 
              <?php $cod=$processador;
			$comp = new IEConstruir();
			$comp->verComponente($cod);
			$comp->endIEConstruir();
			?>
            </div></td>
        </tr>
        <tr> 
          <td class="menu"> <div align="right"><strong>Memória:</strong></div></td>
          <td><div align="left"> 
              <?php $cod=$memoria;
			$comp = new IEConstruir();
			$comp->verComponente($cod);
			$comp->endIEConstruir();
			?>
            </div></td>
        </tr>
// other componentes...
<?php
if($login_status==1) {
?>
<table width="100%" border="0">
  <tr>
    <td align="right">
<form method="post" action="index.php?pagina=inserirpc">
<input type="hidden" name="processador" value="<?php echo $processador; ?>">
<input type="hidden" name="memoria" value="<?php echo $memoria; ?>">
<input type="hidden" name="motherboard" value="<?php echo $motherboard; ?>">
<input type="hidden" name="disco" value="<?php echo $disco; ?>">
<input type="hidden" name="cdrom" value="<?php echo $cdrom; ?>">
<input type="hidden" name="dvd" value="<?php echo $dvd; ?>">
<input type="hidden" name="disquetes" value="<?php echo $disquetes; ?>">
<input type="hidden" name="placasom" value="<?php echo $placasom; ?>">
<input type="hidden" name="placagrafica" value="<?php echo $placagrafica; ?>">
<input type="hidden" name="monitor" value="<?php echo $monitor; ?>">
<input type="hidden" name="colunas" value="<?php echo $colunas; ?>">
<input type="hidden" name="teclado" value="<?php echo $teclado; ?>">
<input type="hidden" name="rato" value="<?php echo $rato; ?>">
<input type="hidden" name="sistema" value="<?php echo $sistema; ?>">
<input type="image" class="botoes" src="imagens/botoes/encomendar_pc.gif">
</form></td>
  </tr>
</table>
</p>
<?php
}
?>

this page uses the following function to deliver the results:

<?php
// função
		function verComponente($cod) {
			$sql = "SELECT * FROM produtos WHERE modelo='$cod'";
			$rs=$this->bd->executarSQL($sql);

		$row=mysql_fetch_array($rs);
		echo "$row[marca] $row[modelo] - <b>$row[preco] €</b>";
	}
?>

this might not be the best way to structure it but it was the way i found.

any idea how to show the total price of the components with this or is it better to redesign the whole section in a easier way

thanks in advance

[adamgeorge]

echo "<option name=\"$row[modelo]\" value=\"$row[modelo]\">$row[modelo] - $row[preco] €</option>\n";

Well, to start off with, <option> tags don't have a name attribute. You give the <select> tag the name attribute, and the <option> tags have the various value attributes.

But anyway... You know what $processador and $memoria are right? so... just do a sql select:

SELECT sum(price) FROM produtos WHERE modelo='$processador' or modelo='$memoria';

but what you should be doing is what I said before. Use arrays. In HTML if you give elements the same name they will be passed in an array back to the server:

<input type=hidden name=product[] value=1>
<input type=hidden name=product[] value=2>
<input type=hidden name=product[] value=3>
<input type=hidden name=product[] value=4>

then you just get the values out like normal:

<?
$aProducts = $_POST["product"];
?>

then you have an array of all the productID's passed, and you can do lots of stuff with it like SQL selects and whathaveyou.

It works for all elements:

eg:

<input type=checkbox name=product[] value=1>Cpu
<input type=checkbox name=product[] value=2>RAM
<input type=checkbox name=product[] value=3>Graphics Card
<input type=checkbox name=product[] value=4>Monitor

In this case only the checkboxes that are selected will be posted back to the server. All you have to do is

<?
$aProducts = $_POST["product"];
?>

and all the productsID's of the checkboxes the user selected will be in that array.

You should use this approach for your application too. It will make things alot easier for you. Just apply it to whatever elements you are using for the user to select their computer equipment, and then you will get an array posted back to the server of all the productID's they selected.

Then you can just select their respective prices out of the database, and sum them up, or display them, whatever.

Hope that makes sense anyway.
-Adam 🙂