Automatic increase a number
Automatic increase a number
Hi,
I like to increase a number by 1.., based on the number from the former record
So if you have 3 records number 1,2,3 and the newest number should be 4.
When you add the new record then number 4 should show (in this example of course)
Ron
I like to increase a number by 1.., based on the number from the former record
So if you have 3 records number 1,2,3 and the newest number should be 4.
When you add the new record then number 4 should show (in this example of course)
Ron
Re: Automatic increase a number
Try "Auto Increment" for the desired field
Re: Automatic increase a number
Peebee,
Thanks you for pointing this out. It looks simple.... However I already have an "auto incremented field" that is the Primary key field.
It seems that you only have one auto incremented field.
Any more solutions?
Ron
Thanks you for pointing this out. It looks simple.... However I already have an "auto incremented field" that is the Primary key field.
It seems that you only have one auto incremented field.
Any more solutions?
Ron
Re: Automatic increase a number
Sorry, wasn't aware you already had an existing auto-increment column. Yes, I believe MySQL supports only one auto-increment per table. Not sure how you can get around that.
As you already have one auto-increment column in the table, a second auto-increment would have the same values wouldn't it, as it would be updated at the same time - or are you wanting these two values to somehow be out of sync?
As you already have one auto-increment column in the table, a second auto-increment would have the same values wouldn't it, as it would be updated at the same time - or are you wanting these two values to somehow be out of sync?
Re: Automatic increase a number
Peebe,
The first field is the standard ID, Primary Key, Auto increment.
The field I like to be incremented is a kind of "order number" like. 12345 and the next added record should get number 12346.
So I this can be done within a hook, it is fine with me.
Ron
The first field is the standard ID, Primary Key, Auto increment.
The field I like to be incremented is a kind of "order number" like. 12345 and the next added record should get number 12346.
So I this can be done within a hook, it is fine with me.
Ron
Re: Automatic increase a number
Hi,
Someone a suggestion?
Ron
Someone a suggestion?
Ron
Re: Automatic increase a number
You can solve this by using MAX function in a hook:
http://stackoverflow.com/questions/1547 ... n-a-column
http://stackoverflow.com/questions/1547 ... n-a-column
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper
Re: Automatic increase a number
Hi AhmedBR,
Thank for pointing to some SQL facts.
I've tried this option:
But the named table: Contacten didn't show up after these statements in "Hook" contacten.php.
The screen became "white"(no data was shown)
So.... Some syntax error or so?
Could you please help me out?
Ron
Thank for pointing to some SQL facts.
I've tried this option:
Code: Select all
function contacten_before_insert(&$data, $memberInfo, &$args){
/* Add field LidNr by 1 */
sql("select MAX'({data['LidNR']})", $eo);
sql("update `contacten` set 'LidNr'= '{$data['LidNr'] + 1 }'", $eo);
return TRUE;
}
The screen became "white"(no data was shown)
So.... Some syntax error or so?
Could you please help me out?
Ron
-
- Posts: 6
- Joined: 2013-12-19 20:15
Re: Automatic increase a number
Not sure if this is what you are looking for. In our case we are counting number of calls made to a client based on the updating of a client tracking table.
Here we check current value of call_number then add 1 and post the increased number back into call_number.
Code: Select all
$call_count = sqlValue("select call_number from client_tracking where client_name = '{$data['client_name']}'");
++$call_count;
sql("update `client_tracking` set `call_number` = '$call_count' where `client_name` = '{$data['client_name']}'", $o);
Here we check current value of call_number then add 1 and post the increased number back into call_number.
Re: Automatic increase a number
Hi mervyngroves,
Thank you for the tip, however....
Adjusting it to "my case" I get not the expected result.
My case shoul be:
A field, LidNR, shout be automatically increased by one.
After hitting the ADD button I should expect the newly calculated value displayed in the field 'LidNR' already.
Nothing happens, instead of that I get an error.
The used code, adjusted to my tabel and field:
After hitting the Save New button I still have to fill in a value for field LidNR
Ron
Thank you for the tip, however....
Adjusting it to "my case" I get not the expected result.
My case shoul be:
A field, LidNR, shout be automatically increased by one.
After hitting the ADD button I should expect the newly calculated value displayed in the field 'LidNR' already.
Nothing happens, instead of that I get an error.
The used code, adjusted to my tabel and field:
Code: Select all
function contacten_before_insert(&$data, $memberInfo, &$args){
$call_count = sqlValue("select MAX '{data['LidNR']}", $eo);
++$call_count;
sql("update `contacten` set `LidNR` = '$call_count'", $eo);
return TRUE;
}
After that and pushing again Save New i get an error:You cannot leave this field empty.
So, what am I doing wrong?You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LidNR']}' at line 1
Ron
-
- Posts: 6
- Joined: 2013-12-19 20:15
Re: Automatic increase a number
In my case it's a hook in function_after_update, it's not designated a required field since the "system" populates it. We read the current value of cell_count, we are operating on one particular record and counting the number of times it has been updated. In your case you are looking to find the value of the last record entered and adding one to it.
I'm not sure your Max selection can work.
I think it should be something like this:
or
I'm not sure your Max selection can work.
I think it should be something like this:
Code: Select all
$sql = mysql_query("SELECT `column` FROM tableName WHERE variable='$var' ORDER BY `column` DESC LIMIT 1") or die(mysql_error()
Code: Select all
$sql = mysql_query(" SELECT MAX(column) AS `column` FROM tableName WHERE variable='$var' ") or die(mysql_error());
Re: Automatic increase a number
HI mervyngroves
Thanks again.
I cann't follow the
What value does $var have and where is that value set?
Ron
Thanks again.
I cann't follow the
part.variable='$var'
What value does $var have and where is that value set?
Ron
-
- Posts: 6
- Joined: 2013-12-19 20:15
Re: Automatic increase a number
I'm not an expert by any means, they were just two examples I found by searching. What they are trying to do is find the highest value contained in a column and I guess that is where $var is related. I'm a try everything I can find and eventually I get it to work.
Here's another example from Stackoverflow, my main source of solutions.
1: Updating a record where the primary id=12 (function_after_update)
2: Inserting a record (function_after_insert)
Hope this helps.
Here's another example from Stackoverflow, my main source of solutions.
1: Updating a record where the primary id=12 (function_after_update)
Code: Select all
UPDATE mytable
SET logins = logins + 1
WHERE id = 12
Code: Select all
INSERT into mytable (logins)
SELECT max(logins) + 1
FROM mytable
Re: Automatic increase a number
Hi mervyngroves,
Thanks again. Almost there !
Executing the given statements in PhPAdmin works great!
After modifying this to "AppGini code" it won't do anything.
The screen for form "contacten" turns "empty"
So something is wrong with the given "AppGini-code"
Tablename:contacten
Fieldname that has to be increased: LidNR
Placs in the çontacten_after_insert' function.
Used PhPAdmin:
Thanks again. Almost there !
Executing the given statements in PhPAdmin works great!
After modifying this to "AppGini code" it won't do anything.
The screen for form "contacten" turns "empty"
So something is wrong with the given "AppGini-code"
Tablename:contacten
Fieldname that has to be increased: LidNR
Placs in the çontacten_after_insert' function.
Code: Select all
function contacten_after_insert($data, $memberInfo, &$args){
sql("INSERT into `contacten` `LidNR` SELECT max '{$data['(LidNR)']} + 1 FROM contacten"; $eo);
return TRUE;
Code: Select all
INSERT into contacten (LidNR)
SELECT max(LidNR) + 1
FROM contacten
Re: Automatic increase a number
Hi There,
The case is:
A field, LidNR in table 'contacten', shout be automatically increased by one.
After hitting the ADD button I should expect the newly calculated value displayed in the field 'LidNR' already.
Nothing happens, instead of that I get an error.
Does someone can help me pointing the right "AppGini code" for
I've already tried:
But the screen for displaying table 'contacten' records turns blank
Thanks so far
Ron
The case is:
A field, LidNR in table 'contacten', shout be automatically increased by one.
After hitting the ADD button I should expect the newly calculated value displayed in the field 'LidNR' already.
Nothing happens, instead of that I get an error.
Does someone can help me pointing the right "AppGini code" for
Code: Select all
INSERT into contacten (LidNR)
SELECT max(LidNR) + 1
FROM contacten
Code: Select all
function contacten_after_insert($data, $memberInfo, &$args){
sql("INSERT into `contacten` `LidNR` SELECT max '{$data['(LidNR)']} + 1 FROM contacten"; $eo);
return TRUE;
Thanks so far
Ron
Re: Automatic increase a number
Ron,
the sql statement must be an UPDATE, not an INSERT; in the after_insert hook the record already exists in the database.
sql("UPDATE contacten t1 set t1.LidNR = (SELECT MAX (t2.LidNR) FROM contacten t2)
WHERE t1.<pk of contacten>) = " . $data['selectedID'] . ";", $o);
replace <pk of contacten> by the primary key field of the table contacten.
the sql statement must be an UPDATE, not an INSERT; in the after_insert hook the record already exists in the database.
sql("UPDATE contacten t1 set t1.LidNR = (SELECT MAX (t2.LidNR) FROM contacten t2)
WHERE t1.<pk of contacten>) = " . $data['selectedID'] . ";", $o);
replace <pk of contacten> by the primary key field of the table contacten.
Bert
I am using Appgini 5.75
I am using Appgini 5.75
Re: Automatic increase a number
Hi Bertv,
Thanks for your answer.
The field I like to have updated is NOT the PK-field. That field will be updated automatically by SQL.
So, I think, that I can’t refer to that field because that is a different range.
Content of Field "LidNR" should always one higher than the former record
Given error:
Ron
Thanks for your answer.
The field I like to have updated is NOT the PK-field. That field will be updated automatically by SQL.
So, I think, that I can’t refer to that field because that is a different range.
Content of Field "LidNR" should always one higher than the former record
Given error:
So I like to have field "LidNR" update by one.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID) = 192' at line 2
Ron
Re: Automatic increase a number
Ron,
I know that LidNr is not the pk-field. The subquery in the update did not work fine. Below an new script: the select of the max(LidNr0 is now separted form the update.
I have tested it; it works fine.
function table1_after_insert($data, $memberInfo, &$args){
$sql_string = "SELECT MAX(LidNr) + 1 FROM contacten;";
$new_lidnr = sqlValue($sql_string, $o);
$sql_string = "UPDATE contacten set LidNr = " . $new_lidnr . " WHERE id = " . $data['selectedID'] . ";";
$result = sql($sql_string, $o);
mysql_free_result($result);
return TRUE;
}
I know that LidNr is not the pk-field. The subquery in the update did not work fine. Below an new script: the select of the max(LidNr0 is now separted form the update.
I have tested it; it works fine.
function table1_after_insert($data, $memberInfo, &$args){
$sql_string = "SELECT MAX(LidNr) + 1 FROM contacten;";
$new_lidnr = sqlValue($sql_string, $o);
$sql_string = "UPDATE contacten set LidNr = " . $new_lidnr . " WHERE id = " . $data['selectedID'] . ";";
$result = sql($sql_string, $o);
mysql_free_result($result);
return TRUE;
}
Bert
I am using Appgini 5.75
I am using Appgini 5.75
Re: Automatic increase a number
Hi Bertv,
THANK YOU SO MUCH
It is great that a forum exists and that people like you help finding an answer.
Happy Newyear and a healthy 2014!
Ron
THANK YOU SO MUCH
It is great that a forum exists and that people like you help finding an answer.
Happy Newyear and a healthy 2014!
Ron
Re: Automatic increase a number
hi Ron
where to put the function
where to put the function
Re: Automatic increase a number
Hi iruslan,
You have to put this code:
Directly at first in de <tablename>_after_insert(.... function
Ofcourse you have to substiute <tablename> in the right tabelname (the same for the fieldname [Lidnr=)
Ron
You have to put this code:
Code: Select all
{
$sql_string = "SELECT MAX(LidNr) + 1 FROM contacten;";
$new_lidnr = sqlValue($sql_string, $o);
$sql_string = "UPDATE contacten set LidNr = " . $new_lidnr . " WHERE id = " . $data['selectedID'] . ";";
$result = sql($sql_string, $o);
mysql_free_result($result);
return TRUE;
}
Ofcourse you have to substiute <tablename> in the right tabelname (the same for the fieldname [Lidnr=)
Ron