php help

[sorex]

He want to always have a value that's 1 higher than the current highest it seems.

 

[DonManfred]

He want to create a new id to the items (kind of reordering. reindexing.) He want to renumber the id...

 

[sorex]

this works fine in mysql (prolly not in mssql)

INSERT INTO test ('name','position') select 'ilan',max(position)+1 from test

it's the subsequent querying I was talking about in post 11.

 

[ilan]

I'm not sure that I understand what you are trying to do So why do you want to get the highest number or the whole Thing re-numbered? What is it good for?

first i want to thanx all here for their help. i menaged to make my first b4j mysql app that i will share here incl. source code (my first touch
on mysql started 2 days ago and thanx to @KMatle examples i could make it)

the reason i wanted to renumber or get the highest id is because if i want to update or delete an entry i need a uniq value for it. so an id is a uniq value that only 1 entry can have

like this i write UPDATE ... WHERE id='$id'"
or DELETE ... WHERE id='$id'"

so after i delete an entry the problems starts so if i had 5 entries with and id fro 0 to 4

0...
1...
2...
3...
4...

and i deleted entry with the id 2 then i have now

0...
1...
3...
4...

so now i need to know when i will insert a new entry what id should i choose so i ask for the highest id in my table and add 1 to it

this is the reason i needed it, but i solved it by reading all entries and adding only the id to a list and look for the highest id
i dont know why SELECT MAX(id) FROM table does not work

do you know a faster solution for my problem?

 

[ilan]

this works fine in mysql (prolly not in mssql)

INSERT INTO test ('name','position') select 'ilan',max(position)+1 from test

it's the subsequent querying I was talking about in post 11.

max position is not ok because i could have only 4 entries with the highest id of 8 because i have delete few entries so i need to check for the highest id and then add 1 to it

 

[ilan]

now i am doing it like this

PHP:

    case "HighestId":
        $q = mysql_query("SELECT id FROM $table");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
    break;

B4J:

Dim maxid As String = "0"  
                If getstring.Contains(",") Then
                    Dim str() As String = Regex.Split(",",getstring)      
                    Dim highestidlist As List
                    highestidlist.Initialize
                  
                    For i = 0 To str.Length - 1
                        Dim str2() As String = Regex.Split(":",str(i))
                        highestidlist.Add(str2(1))
                    Next  

                    For i = 0 To highestidlist.Size - 1
                        maxid = Max(maxid, highestidlist.Get(i))
                    Next
                End If
                maxid = maxid + 1

 

[sorex]

look at my previous post.

altho the primary key (auto increment tag in mysql) will always increase and won't have duplicates.

 

[ilan]

look at my previous post.

altho the primary key (auto increment tag in mysql) will always increase and won't have duplicates.

Ok, i thought you mean with max position just to count how much entries i have and then use it as my next id... sorry

 

[sorex]

max position is not ok because i could have only 4 entries with the highest id of 8 because i have delete few entries so i need to check for the highest id and then add 1 to it

that's what the query is doing.

I ran it 3 times, deleted the 2nd one and added another one

but as you see in the screenshot the real ID is always counting up aswell.

 

[sorex]

Ok, i thought you mean with max position just to count how much entries i have and then use it as my next id... sorry

no, you only need that 1 query. no loops or anything.

 

[sorex]

also notice that it doesn't matter if it is 1,2,3,4 or 0,2,3,4 or 0,33,65,89 if you sort on it in the query it will display in the right order anyway.

gaps won't hurt and if you add it to a map in B4x you know the "real" order count aswell. (set the key with a counter)

 

[ilan]

no, you only need that 1 query. no loops or anything.

ok, but how do i add that 1 query?

this is what i have now:

 

[sorex]

add a positions field (int) and try

case "HighestId":
mysql_query("INSERT INTO b4ausers ('name','position') select '$name',max(position)+1 from b4ausers");
break;

or set the id field to auto increment if you don't want to reorder later.

then it will just be

case "HighestId":
mysql_query("INSERT INTO b4ausers ('name')  values ('$name')");
break;

 

[sorex]

the auto increment is where the pointer is in the image below

 

[ilan]

the auto increment is where the pointer is in the image below

View attachment 40561

i cannot make it A_I, i need to set it as a primary key first

and its not working

i will upload soon my project and you all can improve it if you like to

 

[ilan]

Btw. I would like to know if there is a limit for text.length that i can put in a single column?

(text can be up to 10k letters long) will it work?

 

[DonManfred]

http://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text

 

[sorex]

that's normal, you should set it before it has content

create a new field, name it ID2 and set it as primary and auto increment.

delete the current id

rename ID2 to ID

might need to truncate to get the numbering working tho.

yes, varchar can go up to 64K, text/blob even bigger I guess. (4Gig for longtext?)

 

[DonManfred]

varchar

Varchar can up to 256 bytes i think.

You should use MEDIUMTEXT

TINYTEXT - 256 bytes
TEXT - 65,535 bytes
MEDIUMTEXT - 16,777,215 bytes
LONGTEXT - 4,294,967,295 bytes

 

[sorex]

that was in the old mySQL versions, it has been extended to 64K several versions ago.