Need count the duplicated fields in a table

Developers Programming
#1

Hi,

I need to count the duplicated specific fields ina table, in this case the client id because I need to assign the next id number to a new client insertion.

example

table (asuntos)
id - int(11)
id_cliente int(11)
id_cons_cli int(11)

I need to count how many times id_cliente is in (asuntos) table, after this I need to update the field

id_cons_cli with the count + 1

I try with the next code…

$res_sql = ‘SELECT COUNT(id_cliente) FROM asuntos’;
{id_cons_cli} = $res_sql + 1;

I use in Ajax event when OnChange, but when run the field id_cons_cli appears 1, and in the table exist 9 registers with the same client (id_cliente)

Thanks in advance

Eduardo C?zares

#2

Re: Need count the duplicated fields in a table

If you want a key field to get the max current value + 1, then I would suggest using AUTO_INCREMENT in datebase en SC application. This way the new id value is generated automatically for you.

#3

Re: Need count the duplicated fields in a table

Yes,

I understand and I use the auto increment key in the database, the problem is that the system I actually need to update need to increment the field for the id_cliente (customer id) and the customer is already in a table that already use auto increment to increment id for the (asuntos table)

the code that I post solve the issue, but the problem is that not calculated, when I try in SQL of PHPmyAdmin it works, but when I use in ScriptCase don?t works, I don?t know if is a problem os type mismatch.

I need to count the repeat field (id_cliente) on (asuntos) table and add 1 to the count and put that cantity in the field id_cons_cli.

Tell me is the syntax is correct or why don?t works the operation.

Thanks in advance

Eduardo Cazares

#4

Re: Need count the duplicated fields in a table

$res_sql = 'SELECT COUNT(id_cliente) FROM asuntos';

In the above line, you put a string in variable $res_sql. You are not actually firing the query to the database.
Instead, try this piece of code:


$res_sql = 'SELECT COUNT(id_cliente) FROM asuntos';
sc_select(my_data, $res_sql);
{id_cons_cli} = $my_data->fields[1] + 1;
$my_data->Close();

Are you sure you don’t need MAX instead of COUNT?

#5

Re: Need count the duplicated fields in a table

I use the code, and the problem is the field id_cons_cli appears in blank!!!

When must be 12 the number shown in the field.

Whats wrong?

Eduardo Cazares

#6

Re: Need count the duplicated fields in a table

Sorry, use this instead:

$res_sql = 'SELECT COUNT(id_cliente) FROM asuntos';
sc_lookup(dataset,$res_sql);
{id_cons_cli} = {dataset}[0][0] + 1;
#7

Re: Need count the duplicated fields in a table

Freezer,[table][tr][td][/table]

You?re the best!!!

It works, thank you very much!!!

Eduardo Cazares

#8

Re: Need count the duplicated fields in a table

Freezer,

There was an error, because when I insert a new registry in the table the code only give to me the next register of any record, and I need the next record of the next customer, I try with this but the result = 1 always

$res_sql = ‘SELECT COUNT(*) FROM asuntos WHERE id_cliente = {id_cliente}’;
sc_lookup(dataset,$res_sql);
{id_cons_cli} = {dataset}[0][0] + 1;

If the table has 15 registers with id_cliente = 1, I need 15+1 for the next customer
if the table has 0 registers with id_cliente = 2, I need 0+1 for the first customer

Thanks in advance,

Eduardo Cazares

#9

Re: Need count the duplicated fields in a table

Run your SQL in a DB manager, are you getting the result you want?
If you are, then the problem is the SC vars. echo {dataset}[0][0] to the screen and see what you get. 15+1 or are you getting “15”+1, or is the var blank?

Debug your code and quit trying to guess what is happening.

Regards,
Scott.

#10

Re: Need count the duplicated fields in a table

This should do the trick:

$res_sql = 'SELECT COUNT(*) FROM asuntos WHERE id_cliente = '.{id_cliente};
…

#11

Re: Need count the duplicated fields in a table

Freezer,

Again, you?re the best!

Thanks

Eduardo C?zares