Good day, I am user of PME and am encountering two problems: after inserting a record in the table, if I give a update (refresh) on the page the script inserts a new record by duplicating the previous. How to avoid this?
Another problem is with an empty field that should be added to a data entry but the result is 0 Exp: 1 + 0 = empty when it should be 1
I am using a trigger to update the field :
Triggers
$sql = 'SELECT produto, entrada, saldo FROM estoque where produto =
"'.$newvals['produto'].'"';
$resultado = mysql_query($sql) or die ("Problema na Consulta");
while($row=mysql_fetch_assoc($resultado)){
$usus = $row['saldo'] + $newvals['entrada'];
}
if( !empty($newvals['saldo']) ) {
$newvals['saldo'] = $usus; }
else{
$newvals['saldo'] = $usus ;}
Table structure:
cod_estoque int(9) No
data date No
produto varchar(30) No
entrada int(9) No
saida int(9) No
saldo int(9) Yes NULL
cod_produto varchar(12) No
Script:
$opts['fdd']['cod_estoque'] = array(
'name' => 'cod estoque',
'nowrap' => true,
'select' => 'T',
'options' => 'RH',
'required' => true,
'sort' => true
);
$opts['fdd']['data'] = array(
'name' => 'Data',
'select' => 'T',
'maxlen' => 18,
'datemask' => 'd/m/Y',
'default' => date("Y-m-d"),
'sort' => true,
'calendar' => array(
'min' => date("Y-m-d"),
'showTime' => '12',
'dateFormat' =>'%Y-%m-%d'
)
);
$opts['fdd']['produto'] = array(
'name' => 'Produto',
'nowrap' => true,
'select' => 'T',
'options' => 'ACDL',
'escape' => false,
'strip_tags' => false,
'required' => false,
'sort' => true,
'values' => array(
'table' => 'produtos',
'column' => 'nome',
// 'filters' => "cod_cidade = {$_SESSION["cod_cidade"]}"
)
);
$opts['fdd']['entrada'] = array(
'name' => 'Entrada',
'nowrap' => true,
'select' => 'T',
'options' => 'ACDL',
'required' => false,
'sort' => true
);
$opts['fdd']['saldo'] = array(
'name' => 'Saldo',
'nowrap' => true,
'select' => 'T',
'options' => 'ACDL',
'default' => '',
'input' => '',
'required' => false,
'sort' => true
);
$opts['triggers']['update']['before']= 'triggers/entradas.inc.php';
$opts['triggers']['insert']['before']= 'triggers/entradas.inc.php';
// Now important call to phpMyEdit
require_once 'phpMyEdit.class.php';
new phpMyEdit($opts);
Thanks for all,
Paulo Fernando
Brasil
|
> if I give a update (refresh) on the page
> the script inserts a new record by
> duplicating the previous.
> How to avoid this?
Do not hit Refresh. I cannot think of a logical reason to hit the browser's Refresh button after adding a new record. The script will proceed to List the current data set. For public forms, there should always be validation of user input (past and present) to prevent adding the same record twice, but such methods can be complex and are outside the scope of this post.
There are many things that I do not understand.
Why do you use 'options' => 'RH' at $opts['fdd']['cod_estoque']
cod_estoque should be the unique identifier, usually a numeric auto_increment field, but the posted schema does not indicate the field properties. Better to use phpMyAdmin to export the schema and post the export.
An example of schema output from phpMyAdmin should look like the following:
CREATE TABLE IF NOT EXISTS `bs_token` (
`id` mediumint(6) NOT NULL AUTO_INCREMENT,
`token_key` char(20) NOT NULL,
`token_value` char(32) NOT NULL,
`submitted` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
It is not clear if estoque is used by both the main script *and* the trigger.
|
Good night,
the question of the refresh is not that important, what matters is that I need to add the input value when the table is empty. After the first record, when the field value is zero, the trigger behaves correctly.
If the field is set RH or CDL, I do not see a problem with that, both definitions make writable and hidden in addition. I realize that the limitation is in mysql but I think the SME must have a way to deal with it, correcting this problem. The table was created in phpMyAdmin and your schedule is:
cod_estoque int(9) No None AUTO_INCREMENT
data date No None
produto varchar(30) latin1_swedish_ci No None
entrada int(9) No None
saida int(9) No None
saldo int(9) Yes NULL
cod_produto varchar(12) latin1_swedish_ci No None
I waste time with criticism does not solve anything so I came to the forum hoping to get some help but if you can not forgive me.
Thanks, Paulo
|
<?php
$observa = 'cadastro';
$saldo = 0;
$conexao = mysql_connect("localhost", "*******", "*******") or die(mysql_error());
mysql_select_db("*********") or die(mysql_error());
$comando = "INSERT into
estoque (
historico,
saldo)
VALUES (
'$observa',
'$saldo')";
$query = mysql_query($comando,$conexao) or die(mysql_error());
?>
Trying to adapt the trigger but I can not even insert a record in another table
but nothing works. Someone with enough patience and intelligence to solve this problem?
this code correctly inserts the record if it is run as a php page but if executed as trigger (after) does not work:
how to call a trigger and insert a new record in another table? Are not given in the first table, are simple string data ? Thanks, Paulo
|
Here is mt only working example of a trigger that does an INSERT and UPDATE.
NOTE: Variables available within included triggers:
$this object reference
$this->dbh initialized MySQL database handle
$this->key primary key name
$this->key_type primary key type
$this->key_delim primary key deliminator
$this->rec primary key value (update and delete only)
$this->tb this table
$newvals associative array of new values (update and insert only)
$oldvals associative array of old values (update and delete only)
$changed array of keys with changed values
// test that a field named `deleted` exists in the table
if(isset($oldvals['deleted'])){
// change the value of `deleted` from 0 to 1
$query2 = sprintf('UPDATE %s SET `deleted` = "1" WHERE `%s` = "%s" LIMIT 1', $this->tb, $this->key, $this->rec);
if($this->MyQuery($query2)){
// Emulate the change_log function, saving a copy of the record flagged as deleted
if($this->logtable){
$query3 = sprintf('INSERT INTO %s (updated, user, host, operation, tab, rowkey, col, oldval, newval) VALUES (NOW(), "%s", "%s", "delete", "%s", "%s", "%s", "%s", "")',
$this->logtable, addslashes($this->get_server_var('REMOTE_USER')), addslashes($this->get_server_var('REMOTE_ADDR')), addslashes($this->tb), addslashes($this->rec), addslashes($key), addslashes(serialize($oldvals)));
$this->myquery($query3, __LINE__);
}
return false;
}else{
// abort if the query fails
echo "\n".'<p>'.htmlspecialchars(mysql_error()).'</p>';
echo "\n".'<p><a href="javascript:history.go(-1)" onmouseover="self.status=document.referrer;return true">Go Back</a></p>';
exit;
}
}
|
Doug, thanks for your reply but did not work for me.
when I register a product in the PRODUCT table form should call the trigger should insert a record in table STOCK simulating an entry with value 0 for the approaching entry is added to it and show the correct result. If there is a value of 0, the result will be empty, and not the correct sum.
I think the trigger approach is not correct, should have a way to treat the calculation EMPTY FIELD + NUMBER = NULL but what I need is that results 0
May treat the empty value with some php function? SQL ISNULL(), IFNULL() and COALESCE() Functions ?
$usus = $row['saldo'] + $newvals['saida'];
$row['saldo'] is EMPTY NOT 0 - all table is empty and result = EMPTY
anyway thanks for trying, you know me and remember the case of upload image, I never give up and I will find a solution sooner or later.
Paulo
|
I decided to change the approach to the problem, changed the structure of the field to NOT NULL in mysql and everything became easier.
$observa = $newvals[nome];
$saldo = 0;
$conexao = mysql_connect("localhost", "*****", "******") or die(mysql_error());
mysql_select_db("realmidi_systema") or die(mysql_error());
$comando = "INSERT into
estoque (
produto,
saldo)
VALUES (
'$observa',
'$saldo')";
$query = mysql_query($comando,$conexao) or die(mysql_error());
//atualiza
$comando = 'SELECT cod_estoque, produto, SAIDA, saldo FROM estoque where produto = "'.$newvals['produto'].'" ORDER BY cod_estoque DESC LIMIT 1';
$query = mysql_query($comando,$conexao) or die(mysql_error());
$row = mysql_fetch_array($query) or die(mysql_error());
$newvals[saldo] = $row[saldo] + $newvals[entrada] - $newvals[saida];
Many thanks to all who helped, Doug you for the attention you have afforded me in the last problems I faced. I always get!
|
Prihlásiť
Registrácia
English
