php help

He want to always have a value that's 1 higher than the current highest it seems.
 
He want to create a new id to the items (kind of reordering. reindexing.) He want to renumber the id...
 
this works fine in mysql (prolly not in mssql)

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

it's the subsequent querying I was talking about in post 11.
 
KMatle said:
I'm not sure that I understand what you are trying to do :rolleyes: So why do you want to get the highest number or the whole Thing re-numbered? What is it good for?
Click to expand...

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?
 
sorex said:
this works fine in mysql (prolly not in mssql)

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

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

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
 
now i am doing it like this

PHP:

Code: Copy 
    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:

Code: Copy 
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
 
Last edited:
look at my previous post.

altho the primary key (auto increment tag in mysql) will always increase and won't have duplicates.
 
sorex said:
look at my previous post.

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

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 :)
 
ilan said:
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
Click to expand...

that's what the query is doing.

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

sql.png


but as you see in the screenshot the real ID is always counting up aswell.
 
ilan said:
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 :)
Click to expand...

no, you only need that 1 query. no loops or anything.
 
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)
 
add a positions field (int) and try

Code: Copy 
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

Code: Copy 
case "HighestId":
mysql_query("INSERT INTO b4ausers ('name')  values ('$name')");
break;
 
sorex said:
the auto increment is where the pointer is in the image below

View attachment 40561
Click to expand...

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
 
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?
 
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?)
 
You must log in or register to reply here.
Back
Top