How to make Multiply / divide sql tables to show real price?

Question

logicaweb 0 Newbie Poster

11 Years Ago

I'm trying to make multiplication in web shop that will show real price:

discount * tax * product price * 1 piece= real price

But I just don't know how to do it, I try 1 million things, spend about 48h just to make it, but, without success, no matter that is not surprising, I'm not a PRO. I hope someone will help me to finally finish this.

My tables names:

Table "Products" - Structure "Product_price" // That is Product price (it's normal number)

Table " _config" / Param "tax" // That is Tax (it's percent (%))

Table " Products" / Structure "Product_discount" // That is Discount (it's percent (%))

My Product page code:

    <?
     class Produto {
        var $serie;
        var $codigo;
        var $descricao;
        var $emb;
        var $umv;
        var $preco;
        var $pagina;
        var $obs;



        /** CONSTRUCTOR **/
        function Produto($serie, $codigo, $descricao, $emb, $umv, $preco, $pagina='', $desconto, $obs='') {
          $this->serie = $serie;
          $this->codigo = $codigo;
          $this->descricao = $descricao;
          $this->emb = $emb;
          $this->umv = $umv;
          $this->preco = $preco;
          $this->obs = $obs;
          $this->pagina = $pagina;
          $this->desconto = $desconto;
        }

        function setInfo($serie, $codigo, $descricao, $emb, $umv, $preco, $pagina='', $desconto, $obs='') {
          $this->serie = $serie;
          $this->codigo = $codigo;
          $this->descricao = $descricao;
          $this->emb = $emb;
          $this->umv = $umv;
          $this->preco = $preco;
          $this->obs = $obs;
          $this->pagina = $pagina;
          $this->desconto = $desconto;
        }

     } 


     function ListarProducts($sql) {
       global $adb, $cfg, $_t;
         $lines='';

        $count = $adb->Execute($sql);
        if($count->RecordCount()>0)
          $results = $count->RecordCount();
        $offset = 0;
        if($results>$cfg['settings']['maxrecords']) {

        if(isset($_GET['offset'])) {
          $offset = $_GET['offset'];
        }  
        $self = basename($_SERVER['REQUEST_URI']);
        if (strpos($self, "?") !== false) $self = reset(explode("?", $self));


          $next = $offset+$cfg['settings']['maxrecords'];
          $previous = $offset-$cfg['settings']['maxrecords'];

          $max = floor($results / $cfg['settings']['maxrecords'])*$cfg['settings']['maxrecords'];

          if(isset($_GET['cat']))
            $q = '?cat='.$_GET['cat'].'&';
          else
          if(isset($_GET['q']))
            $q = '?q='.urlencode($_GET['q']).'&';
          else
          $q='?';


          if($previous>=0) {
            $page = '<a href="'.$self.$q.'offset=0" border="0">'.$_t['button_product_arrowprevious'].'</a>&nbsp;';
            $page .= '<a href="'.$self.$q.'offset='.$previous.'" border="0">'.$_t['button_product_previous'].'</a> &nbsp;&nbsp; ';
          }

          if($next<=$max) {
          $page .= '<a href="'.$self.$q.'offset='.$next.'" border="0">'.$_t['button_product_next'].'</a>&nbsp;';
          $page .= '<a href="'.$self.$q.'offset='.$max.'" border="0">'.$_t['button_product_arrownext'].'</a>';
          }
        }

        $tax = $adb->Execute("select param_value as tax from _esconfig where param like 'tax'")->fields['tax'];
        $rs = $adb->Execute($sql.' LIMIT '.$offset.' , '.$cfg['settings']['maxrecords']);

        if($rs->RecordCount()>0) {
          $odd = true;

          $prod = new Produto(null,
                              null,
                              null,
                              null,
                              null,
                              null,
                              null,
                              null,
                              null);

          $iter = 0;
          while(!$rs->EOF) {
            $iter++;
            if($odd)
              $row = "odd";
            else
              $row = "even";

            $prod->setInfo($rs->fields['Product_id'],
                           $rs->fields['Product_code'],
                           $rs->fields['Product_name'],
                           $rs->fields['Product_emb'],
                           $rs->fields['Product_itempack'],
                           $rs->fields['Product_price'],
                           $rs->fields['Product_pagenumber'],
                           $rs->fields['Product_discount'],
                           $rs->fields['Product_obs']);

            $image_t = null;
            $image_n = null;

              $image_n = $prod->GetImage('n');
              $image_t = preg_replace('/-2/', '-1', $image_n);

            $obs = '';
            if($prod->obs!='')
                $obs = '<br><small><strong>'.$_t['label_product_obs'].': </strong>'.$prod->obs.'</small>';

            $lines .= '<tr id="main_'.$prod->serie.'" class="'.$row.'">'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')">'.$prod->codigo.'</td>'.
                      '<td class="galleryImg" onclick="ShowHide(\'sec_'.$prod->serie.'\')"><a href="'.$image_n.'" ><img src="'.$image_n.'" height="40" width="60"></a></td>'.
                      '<td id="naziv" onclick="ShowHide(\'sec_'.$prod->serie.'\')"><span class="nome_prod">'.$prod->descricao.'</span></td>'.
                      //'<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">'.$prod->emb.'</td>'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">'.$prod->umv.'</td>'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">'.$prod->desconto.' %</td>'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">'.$tax.' %</td>'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">REAL PRICE SPACE</td>'.
                      '<td align="center"><form name="form_'.$prod->serie.'"action="folder/document.php" method="GET"><input type="hidden" name="action" value="additem"><input type="hidden" name="user" value="'.$_SESSION['siteusr']['userid'].'"><input type="hidden" name="prod" value="'.$prod->serie.'"><input type="text" size="2" name="add_'.$prod->serie.'" id="add_'.$prod->serie.'" class="cart_qtd" value="1"></td>'.
                      '<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">'.number_format($prod->preco, 0, '.', ',').' дин.</td>'.
                      '<td><input onclick=\'addCartItem('.$prod->serie.', '.$_SESSION['siteusr']['userid'].', "'.$_GET['lang'].'"); return false;\' type="image" src="add.gif" alt="'.$_t['msg_product_addtocart'].'"></form></td>'.
                      '</tr>';
            $lines .= '<tr id="sec_'.$prod->serie.'" style="display: none; visibility: hidden;" class="'.$row.'">'.
                      '<td class="galleryImg" colspan="3"><a href="'.$image_n.'" ><img src="'.$image_n.'" height="65" width="100"></a><br></td>'.
                      '<td colspan="6"><strong>'.$_t['label_product_obs'].': </strong>'.$prod->obs.'<br><strong>'.$_t['label_product_page'].': </strong>'.$prod->pagina.'</td>'.
                      '</tr>';

            $odd = !$odd;
            $rs->MoveNext();
          }
        }

        $paging = '<table class="tablenav" border="0" width="100%">
                     <tr><td align="left">'.$_t['msg_product_found'].' '.$results.' '.$_t['msg_product_products'].'</td><td align="right">'.$page.
                  '</td></tr></table>';

        $html = $paging.'<table cellpadding="1" cellspacing="1" border="0" class="ListProducts" width="100%">'.
                        '<tr>'.
                        '<th>'.$_t['col_product_code'].'</th>'.
                        '<th>'.$_t['col_image'].'</th>'.
                        '<th>'.$_t['col_product_description'].'</th>'.
                        //'<th>'.$_t['col_product_emb'].'</th>'.
                        '<th>'.$_t['col_product_umv'].'</th>'.
                        '<th>'.$_t['col_product_discount'].'</th>'.
                        '<th>'.$_t['col_pdv'].'</th>'.
                        '<th>'.$_t['col_REAL_PROCE_TEXT'].'</th>'.
                        '<th>'.$_t['col_product_qty'].'</th>'.
                        '<th>'.$_t['col_product_price'].'</th>'.
                        '<th></th>'.
                        '</tr>'.$lines.'</table>'.$paging;
        return $html;
     }
    ?>

You can see at line 136 there is space in table for calling functions "REAL PRICE SPACE".
I think SQL function can be something like this SELECT preco * (1-desconto/100.0) * (1-taxrate/100.0) AS total FROM Products but how to set that all?
Thanks in advance!

msql mysql php sql

3 Contributors
9 Replies
320 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by logicaweb

All 9 Replies

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Do you need to do this in SQL, or is in PHP code good enough? If you have all variables, why query again?

addme 0 Newbie Poster

11 Years Ago

Use JOIN to get all field from 3 tables then calculate price
If use php farmework you must create new controler

Edited 11 Years Ago by addme

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

'<td onclick="ShowHide(\'sec_'.$prod->serie.'\')" align="center">REAL PRICE SPACE</td>'.

'<td onclick="ShowHide(\'sec_' . $prod->serie . '\')" align="center">' . ($prod->preco * $prod->desconto * $tax) . '</td>' .

logicaweb commented: Solved my problem. +0

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

logicaweb 0 Newbie Poster · Answer 1 · 2012-08-10T14:40:13+00:00

I just need to make it work, but I do not know how to do it, do it in PHP or SQL does not matter, if you have any suggestions how this could work I would be grateful.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 2 · 2012-08-10T21:32:35+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Tell me which properties of $prod are used in the equasion.

logicaweb 0 Newbie Poster · Answer 3 · 2012-08-11T00:22:31+00:00

Tell me which properties of $prod are used in the equasion.

I think $prod function is:

      $prod = new Produto(null,
                          null,
                          null,
                          null,
                          null,
                          null,
                          null,
                          null,
                          null);

Given that php create table as a list of products, I think that shows every product separately.
Specifically $prod functions that need to put in equation is:
$prod->preco * $prod->desconto * $tax
I'm not sure I understand what you asked me, because that I wrote a few things.
Sorry but my English is poor, I hope I understand what you ask.

Use JOIN to get all field from 3 tables then calculate price
If use php farmework you must create new controler

My knowledge of mysql and function JOIN is poor, but I will try something, if I make something I will post here.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 4 · 2012-08-11T08:05:37+00:00

$prod->preco * $prod->desconto * $tax

Did you try that to see what result it gave?

logicaweb 0 Newbie Poster · Answer 5 · 2012-08-11T10:45:25+00:00

Did you try that to see what result it gave?

Based on your suggestions I have tried several things:

To add this '$prod->preco * $prod->desconto * $tax' on line 136 where is REAL PRICE SPACE, but after that change when I try to open page, and page was blank. Function display_errors is on, but it doesn't appears any mistake although page was blank.
To add this $prod->preco * $prod->desconto * $tax on line 136 where is REAL PRICE SPACE, but $prod->preco * $prod->desconto * $tax appears like text without any function.
To add this '$prod->preco' * '$prod->desconto' * '$tax' on line 136 where is REAL PRICE SPACE, but after that change when I try to open page, and page was blank. Function display_errors is on, but it doesn't appears any mistake although page was blank.
To add this '$prod->preco * desconto * $tax' on line 136 where is REAL PRICE SPACE, but after that change when I try to open page, and page was blank. Function display_errors is on, but it doesn't appears any mistake although page was blank.
To add this '.$prod->preco. * .$prod->desconto. * .$tax.' on line 136 where is REAL PRICE SPACE, but after that change when I try to open page, and page was blank. Function display_errors is on, but it doesn't appears any mistake although page was blank.

I tried several more combinations but unfortunately all were unsuccessful.

logicaweb 0 Newbie Poster · Answer 6 · 2012-08-11T11:18:14+00:00

Thank you very much. Now shows the number.
This is equations: . ($prod->preco * (1-$prod->desconto/100.0) * (1+$tax/100.0)) .
Thank you very much again.

How to make Multiply / divide sql tables to show real price?

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers