relationships between tables

Any tips and tricks that has to with the ADDT that doesn't fit into one of the other categories
Post Reply
xtroino
Posts: 1
Joined: 2010-06-30 16:11

relationships between tables

Post by xtroino » 2010-06-30 16:39

Hi all..

I am making a system of stock management using Dreamweaver and ADDT.
My system has a database with two tables, table items and table movements.
The two have a common key "id_artigo.

Table ITEMS

Code: Select all

`id_artigo` int(10) unsigned NOT NULL auto_increment,
  `id_categoria` int(10) unsigned NOT NULL,
  `id_armazem` int(10) unsigned NOT NULL,
  `artigo` varchar(250) NOT NULL,
  `unidade` varchar(2) default NULL,
  `referencia` varchar(45) NOT NULL,
  `designacao` varchar(250) NOT NULL,
  `minimo` int(11) NOT NULL,
  `saldo` int(11) NOT NULL,
  `disponivel` enum('Y','N') NOT NULL,
  PRIMARY KEY  (`id_artigo`)
table MOVEMENTS

Code: Select all

`id_movimento` int(10) unsigned NOT NULL auto_increment,
  `id_artigo` int(10) unsigned NOT NULL,
  `tipo_movimento` enum('S','E') NOT NULL,
  `historico` text NOT NULL,
  `data_movimento` date NOT NULL,
  `quantidade` int(11) NOT NULL,
When entering data in the table movements, needed to update the table field items "SALDO", where the field "SALDO"=(SALDO-QUANTIDADE).

Code of the page

Code: Select all

<?php require_once('Connections/stock.php'); ?>
<?php
// Load the common classes
require_once('includes/common/KT_common.php');

// Load the tNG classes
require_once('includes/tng/tNG.inc.php');

// Load the KT_back class
require_once('includes/nxt/KT_back.php');

// Make a transaction dispatcher instance
$tNGs = new tNG_dispatcher("");

// Make unified connection variable
$conn_stock = new KT_connection($stock, $database_stock);

// Start trigger
$formValidation = new tNG_FormValidation();
$formValidation->addField("tipo_movimento", true, "text", "", "", "", "Campo de selecção obrigatória!");
$formValidation->addField("quantidade", true, "numeric", "", "", "", "É necessário indicar um valor!");
$tNGs->prepareValidation($formValidation);
// End trigger



if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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_stock, $stock);
$query_rsartigos = "SELECT * FROM artigos ORDER BY artigo ASC";
$rsartigos = mysql_query($query_rsartigos, $stock) or die(mysql_error());
$row_rsartigos = mysql_fetch_assoc($rsartigos);
$totalRows_rsartigos = mysql_num_rows($rsartigos);

// Make an insert transaction instance
$ins_movimentos = new tNG_multipleInsert($conn_stock);
$tNGs->addTransaction($ins_movimentos);
// Register triggers
$ins_movimentos->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");
$ins_movimentos->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
$ins_movimentos->registerTrigger("END", "Trigger_Default_Redirect", 99, "includes/nxt/back.php");
// Add columns
$ins_movimentos->setTable("movimentos");
$ins_movimentos->addColumn("id_artigo", "NUMERIC_TYPE", "POST", "id_artigo");
$ins_movimentos->addColumn("tipo_movimento", "STRING_TYPE", "POST", "tipo_movimento");
$ins_movimentos->addColumn("historico", "STRING_TYPE", "POST", "historico");
$ins_movimentos->addColumn("data_movimento", "DATE_TYPE", "POST", "data_movimento", "{NOW}");
$ins_movimentos->addColumn("quantidade", "NUMERIC_TYPE", "POST", "quantidade");
$ins_movimentos->setPrimaryKey("id_movimento", "NUMERIC_TYPE");

// Make an update transaction instance
$upd_movimentos = new tNG_multipleUpdate($conn_stock);
$tNGs->addTransaction($upd_movimentos);
// Register triggers
$upd_movimentos->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Update1");
$upd_movimentos->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
$upd_movimentos->registerTrigger("END", "Trigger_Default_Redirect", 99, "includes/nxt/back.php");
// Add columns
$upd_movimentos->setTable("movimentos");
$upd_movimentos->addColumn("id_artigo", "NUMERIC_TYPE", "POST", "id_artigo");
$upd_movimentos->addColumn("tipo_movimento", "STRING_TYPE", "POST", "tipo_movimento");
$upd_movimentos->addColumn("historico", "STRING_TYPE", "POST", "historico");
$upd_movimentos->addColumn("data_movimento", "DATE_TYPE", "POST", "data_movimento");
$upd_movimentos->addColumn("quantidade", "NUMERIC_TYPE", "POST", "quantidade");
$upd_movimentos->setPrimaryKey("id_movimento", "NUMERIC_TYPE", "GET", "id_movimento");

// Make an instance of the transaction object
$del_movimentos = new tNG_multipleDelete($conn_stock);
$tNGs->addTransaction($del_movimentos);
// Register triggers
$del_movimentos->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Delete1");
$del_movimentos->registerTrigger("END", "Trigger_Default_Redirect", 99, "includes/nxt/back.php");
// Add columns
$del_movimentos->setTable("movimentos");
$del_movimentos->setPrimaryKey("id_movimento", "NUMERIC_TYPE", "GET", "id_movimento");

// Execute all the registered transactions
$tNGs->executeTransactions();

// Get the transaction recordset
$rsmovimentos = $tNGs->getRecordset("movimentos");
$row_rsmovimentos = mysql_fetch_assoc($rsmovimentos);
$totalRows_rsmovimentos = mysql_num_rows($rsmovimentos);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<link href="includes/skins/mxkollection3.css" rel="stylesheet" type="text/css" media="all" />
<script src="includes/common/js/base.js" type="text/javascript"></script>
<script src="includes/common/js/utility.js" type="text/javascript"></script>
<script src="includes/skins/style.js" type="text/javascript"></script>
<?php echo $tNGs->displayValidationRules();?>
<script src="includes/nxt/scripts/form.js" type="text/javascript"></script>
<script src="includes/nxt/scripts/form.js.php" type="text/javascript"></script>
<script type="text/javascript">
$NXT_FORM_SETTINGS = {
  duplicate_buttons: false,
  show_as_grid: false,
  merge_down_value: false
}
</script>
</head>
<body>
<?php
	echo $tNGs->getErrorMsg();
?>
<div class="KT_tng">
  <h1>
    <?php 
// Show IF Conditional region1 
if (@$_GET['id_movimento'] == "") {
?>
      <?php echo NXT_getResource("Insert_FH"); ?>
      <?php 
// else Conditional region1
} else { ?>
      <?php echo NXT_getResource("Update_FH"); ?>
      <?php } 
// endif Conditional region1
?>
    Movimentos </h1>
  <div class="KT_tngform">
    <form method="post" id="form1" action="<?php echo KT_escapeAttribute(KT_getFullUri()); ?>">
      <?php $cnt1 = 0; ?>
      <?php do { ?>
        <?php $cnt1++; ?>
        <?php 
// Show IF Conditional region1 
if (@$totalRows_rsmovimentos > 1) {
?>
          <h2><?php echo NXT_getResource("Record_FH"); ?> <?php echo $cnt1; ?></h2>
          <?php } 
// endif Conditional region1
?>
        <table cellpadding="2" cellspacing="0" class="KT_tngtable">
          <tr>
            <td class="KT_th"><label for="id_artigo_<?php echo $cnt1; ?>">Artigo:</label></td>
            <td><select name="id_artigo_<?php echo $cnt1; ?>" id="id_artigo_<?php echo $cnt1; ?>">
                <option value="" <?php if (!(strcmp("", KT_escapeAttribute($row_rsmovimentos['id_artigo'])))) {echo "selected=\"selected\"";} ?>>Seleccione um artigo</option>
                <?php
do {  
?>
                <option value="<?php echo $row_rsartigos['id_artigo']?>"<?php if (!(strcmp($row_rsartigos['id_artigo'], KT_escapeAttribute($row_rsmovimentos['id_artigo'])))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsartigos['artigo']?></option>
                <?php
} while ($row_rsartigos = mysql_fetch_assoc($rsartigos));
  $rows = mysql_num_rows($rsartigos);
  if($rows > 0) {
      mysql_data_seek($rsartigos, 0);
	  $row_rsartigos = mysql_fetch_assoc($rsartigos);
  }
?>
            </select>
                <?php echo $tNGs->displayFieldError("movimentos", "id_artigo", $cnt1); ?> </td>
          </tr>
          <tr>
            <td class="KT_th"><label for="tipo_movimento_<?php echo $cnt1; ?>_1">Movimento:</label></td>
            <td><div>
              <input <?php if (!(strcmp(KT_escapeAttribute($row_rsmovimentos['tipo_movimento']),"E"))) {echo "CHECKED";} ?> type="radio" name="tipo_movimento_<?php echo $cnt1; ?>" id="tipo_movimento_<?php echo $cnt1; ?>_1" value="E" />
              <label for="tipo_movimento_<?php echo $cnt1; ?>_1">Entrada</label>
            </div>
                <div>
                  <input <?php if (!(strcmp(KT_escapeAttribute($row_rsmovimentos['tipo_movimento']),"S"))) {echo "CHECKED";} ?> type="radio" name="tipo_movimento_<?php echo $cnt1; ?>" id="tipo_movimento_<?php echo $cnt1; ?>_2" value="S" />
                  <label for="tipo_movimento_<?php echo $cnt1; ?>_2">Saída</label>
                </div>
              <?php echo $tNGs->displayFieldError("movimentos", "tipo_movimento", $cnt1); ?> </td>
          </tr>
          <tr>
            <td class="KT_th"><label for="historico_<?php echo $cnt1; ?>">Descrição:</label></td>
            <td><input type="text" name="historico_<?php echo $cnt1; ?>" id="historico_<?php echo $cnt1; ?>" value="<?php echo KT_escapeAttribute($row_rsmovimentos['historico']); ?>" size="60" />
                <?php echo $tNGs->displayFieldHint("historico");?> <?php echo $tNGs->displayFieldError("movimentos", "historico", $cnt1); ?> </td>
          </tr>
          <tr>
            <td class="KT_th"><label for="data_movimento_<?php echo $cnt1; ?>">Data_movimento:</label></td>
            <td><input type="text" name="data_movimento_<?php echo $cnt1; ?>" id="data_movimento_<?php echo $cnt1; ?>" value="<?php echo KT_formatDate($row_rsmovimentos['data_movimento']); ?>" size="10" maxlength="22" />
                <?php echo $tNGs->displayFieldHint("data_movimento");?> <?php echo $tNGs->displayFieldError("movimentos", "data_movimento", $cnt1); ?> </td>
          </tr>
          <tr>
            <td class="KT_th"><label for="quantidade_<?php echo $cnt1; ?>">Quantidade:</label></td>
            <td><input type="text" name="quantidade_<?php echo $cnt1; ?>" id="quantidade_<?php echo $cnt1; ?>" value="<?php echo KT_escapeAttribute($row_rsmovimentos['quantidade']); ?>" size="7" />
                <?php echo $tNGs->displayFieldHint("quantidade");?> <?php echo $tNGs->displayFieldError("movimentos", "quantidade", $cnt1); ?> </td>
          </tr>
        </table>
        <input type="hidden" name="kt_pk_movimentos_<?php echo $cnt1; ?>" class="id_field" value="<?php echo KT_escapeAttribute($row_rsmovimentos['kt_pk_movimentos']); ?>" />
        <?php } while ($row_rsmovimentos = mysql_fetch_assoc($rsmovimentos)); ?>
      <div class="KT_bottombuttons">
        <div>
          <?php 
      // Show IF Conditional region1
      if (@$_GET['id_movimento'] == "") {
      ?>
            <input type="submit" name="KT_Insert1" id="KT_Insert1" value="<?php echo NXT_getResource("Insert_FB"); ?>" />
            <?php 
      // else Conditional region1
      } else { ?>
            <input type="submit" name="KT_Update1" value="<?php echo NXT_getResource("Update_FB"); ?>" />
            <input type="submit" name="KT_Delete1" value="<?php echo NXT_getResource("Delete_FB"); ?>" onClick="return confirm('<?php echo NXT_getResource("Are you sure?"); ?>');" />
            <?php }
      // endif Conditional region1
      ?>
          <input type="button" name="KT_Cancel1" value="<?php echo NXT_getResource("Cancel_FB"); ?>" onClick="return UNI_navigateCancel(event, 'includes/nxt/back.php')" />
        </div>
      </div>
    </form>
  </div>
  <br class="clearfixplain" />
</div>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($rsartigos);
?>
I tried to follow the tutorial "Orders Management Intranet" and I came to this solution

Code: Select all

//start Trigger_Custom trigger
function Trigger_Custom(&$tNG) {
$newQnty = $row_rsartigos['saldo']-$row_rsmovimentos['quantidade'];
$query = "UPDATE artigos SET saldo = $newQnty WHERE
id_artigo =" .$row_rsmovimentos['quantidade'];
}
//end Trigger_Custom trigger
but nothing happens.

Thanks for help
Top
User avatar
Fred
Site Admin
Posts: 491
Joined: 2010-02-15 12:10
Location: Armagh, Northern Ireland
Contact:
Contact Fred

Re: relationships between tables

Post by Fred » 2010-07-16 21:17

Did you managed to get this sorted?

What happens if you replace the variables with actual values?

Somthing looks funny with your sql query...

$query = "UPDATE artigos SET saldo = $newQnty WHERE
id_artigo =" .$row_rsmovimentos['quantidade'];

A simple counter (not using custom triggerss) for counting the number of times a page has been displayed would be:

mysql_query("UPDATE content SET pg_count = pg_count+1 WHERE pg_id = '$colname_rs_pg_id'");
Top
Post Reply

Return to “ADDT”