Automatic increase a number

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Automatic increase a number

Post by RonP » 2013-12-11 17:01

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

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Automatic increase a number

Post by peebee » 2013-12-12 00:36

Try "Auto Increment" for the desired field
auto_increment.jpg
auto_increment.jpg (96.33 KiB) Viewed 19778 times

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-12 11:24

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

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Automatic increase a number

Post by peebee » 2013-12-12 21:42

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?

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-12 22:44

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

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-15 14:23

Hi,
Someone a suggestion?

Ron

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Automatic increase a number

Post by AhmedBR » 2013-12-16 00:06

You can solve this by using MAX function in a hook:

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

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-19 15:20

Hi AhmedBR,
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;
	}
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

mervyngroves
Posts: 6
Joined: 2013-12-19 20:15

Re: Automatic increase a number

Post by mervyngroves » 2013-12-19 20:20

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.

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.

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-20 13:20

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:

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 hitting the Save New button I still have to fill in a value for field LidNR
You cannot leave this field empty.
After that and pushing again Save New i get an error:
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
So, what am I doing wrong?

Ron

mervyngroves
Posts: 6
Joined: 2013-12-19 20:15

Re: Automatic increase a number

Post by mervyngroves » 2013-12-20 22:04

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:

Code: Select all

$sql = mysql_query("SELECT `column` FROM tableName WHERE variable='$var' ORDER BY `column` DESC LIMIT 1") or die(mysql_error()
or

Code: Select all

$sql = mysql_query(" SELECT MAX(column) AS `column` FROM tableName WHERE variable='$var' ") or die(mysql_error());

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-21 14:48

HI mervyngroves
Thanks again.
I cann't follow the
variable='$var'
part.
What value does $var have and where is that value set?

Ron

mervyngroves
Posts: 6
Joined: 2013-12-19 20:15

Re: Automatic increase a number

Post by mervyngroves » 2013-12-22 14:14

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)

Code: Select all

UPDATE mytable 
  SET logins = logins + 1 
  WHERE id = 12
2: Inserting a record (function_after_insert)

Code: Select all

INSERT into mytable (logins) 
  SELECT max(logins) + 1 
  FROM mytable
Hope this helps.

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-23 13:52

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.

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;
Used PhPAdmin:

Code: Select all

INSERT into contacten (LidNR) 
  SELECT max(LidNR) + 1 
  FROM contacten

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-27 16:49

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

Code: Select all

INSERT into contacten (LidNR) 

  SELECT max(LidNR) + 1 
  FROM contacten
I've already tried:

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;
But the screen for displaying table 'contacten' records turns blank
Thanks so far
Ron

Bertv
Veteran Member
Posts: 65
Joined: 2013-12-11 15:59

Re: Automatic increase a number

Post by Bertv » 2013-12-29 14:56

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.
Bert
I am using Appgini 5.75

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-29 15:33

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:
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
So I like to have field "LidNR" update by one.

Ron

Bertv
Veteran Member
Posts: 65
Joined: 2013-12-11 15:59

Re: Automatic increase a number

Post by Bertv » 2013-12-29 21:38

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;
}
Bert
I am using Appgini 5.75

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2013-12-30 13:01

Hi Bertv,
THANK YOU SO MUCH :D

It is great that a forum exists and that people like you help finding an answer.
Happy Newyear and a healthy 2014!

Ron

iruslan
Posts: 1
Joined: 2014-01-30 07:01

Re: Automatic increase a number

Post by iruslan » 2014-01-30 07:36

hi Ron

where to put the function

User avatar
RonP
Veteran Member
Posts: 219
Joined: 2013-08-27 13:36
Location: Heiloo, The Netherlands
Contact:

Re: Automatic increase a number

Post by RonP » 2014-01-30 12:40

Hi iruslan,
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;
}
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

Post Reply