php help

ilan · Jan 12, 2016

hi

i am working on a simple mysql app in b4j
i am getting/sending data to php script and then to mysql db

i also delete and insert new entries, the question is, after deleting an entry i would like to give all entries again an id (int 0 to ...)

like:

(entries

0 | ilan | 34
1 | david | 72
2 | michael | 52
...

if i delete entry 2 (1 | david | 72) i want to renumber again all other entries from 0 to MAX

i do it like this:

B4X:

    case "ReNumberPersons":  
        $q = mysql_query("SELECT name FROM '$table'");
        $rows = array();
        $id = 0;
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        foreach ($rows as $r) {
        mysql_query("UPDATE '$table' SET id = '$id' WHERE name = '$r[name]'");
        $id = $id + 1;
        }
        print "ReNumberDone";
    break;

but this is wrong because if i have 2 (or more) entries with the same name i will have for both the same id

so what is the right way to do it??

another question can i just edit an entry with a specific index and not id??

like delete entry in index 2 ...

thanx

sorex · Jan 12, 2016

you should have an autonumber ID (first column) and one that can be changed (last column)

the id is your all time reference to your records and shouldn't be changed.

sorex · Jan 12, 2016

this might be a single table thingy, but if you start joining based on IDs you'll get into some serious misery when you start renumbering things.
use another field to store its order altho most things get sorted on name or dates.

sorex · Jan 12, 2016

if you really want it try...

B4X:

case "ReNumberPersons":
        $q = mysql_query("SELECT id,name FROM '$table' order by id");    //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        $rows = array();
        $id = 0;
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        foreach ($rows as $r) {
        mysql_query("UPDATE '$table' SET id = '$id' WHERE id = '$r[id]'");
        $id++;
        }
        print "ReNumberDone";
    break;

but it remains bad practice

ilan · Jan 12, 2016

Thanx sorex

it is weird that i cannot just change a entry with a specific index without care about the id or name.

Just say delete entry (index) 0 in table1

sorex · Jan 12, 2016

well, your index is actually the autonumber/identity field I was talking about. (primary key)

notice that you can't rely on what an sql server returns unless your force it to sort right.
everything might look right when just inserting, but when you start deleting things might start shifting.

ilan · Jan 12, 2016

Yes that is correct. When you start deleting entries the problems starts.

Maybe i should avoid it...

KMatle · Jan 13, 2016

Either the number is a unique key (like a customer number):

- it is essential not to change it! (I use AI = Auto increment = Integer when I create a table)
- it is essential to use this ID for all actions (imagine you have 2 persons called Peter Smith -> which one is it? -> they have different ID's ->easy)

OR

- it's just a numbering while displaying
- then number it on the Screen if you like
- don't use it as a key! (you have the uniqued ID for that)

Updating any row will be done by using the unique ID only. This is safe and very easy

Generally:

- each object hast to be identified by a unique identifier
- so spend every object (like person, customer, item, order, etc.) a unique identifier

So if you want to update an item's Price you use the id: Update xxxx Set Price = NewPrice Where ID=ItemID

PS: ID is an reserved word so call it CustomerID f.e.

ilan · Jan 13, 2016

thanx @KMatle i am doing it like this.

so what i need to do is every time i add a new entry i need to get all entries and search for the highest id number and then add + to it and save the new entry
with that id. it is just a weird way for me, because if you have 10k entries you need to call all and then check the highest id num ...

can i do it in php script? with do while and return the highest number instead of call all entries and do it in my app??

ilan · Jan 13, 2016

i am trying it like this:

B4X:

    case "HighestId":
        $q = mysql_query("SELECT MAX(id) FROM $table");
        print $q;
    break;

but i am getting a wrong result i get 4 instead of 2

sorex · Jan 13, 2016

you can do it in 2 single queries (or 1 with subsequent queries)

B4X:

$q = mysql_query("SELECT max(orderId) FROM '$table'");
//add your fetch stuff here
$orderId=maxOrderId+1;
mysql_query("insert into '$table' (name,orderid) values ('$name',$orderId");

sorex · Jan 13, 2016

ilan said:
but i am getting a wrong result i get 4 instead of 2

that's normal if you use the ID, you need an orderID field.

if you want to reposition your records the orderID should be used.

(order is preserverd for sorting that's why I named it orderID)

sorex · Jan 13, 2016

you mean you have 2 records?

for counting you need count(*) instead of max(id) but it's irrelevant with what you want to do I guess as you will get duplicate order ids when you deleted records and add new ones.

ilan · Jan 13, 2016

sorex said:
you mean you have 2 records?

for counting you need count(*) instead of max(id) but it's irrelevant with what you want to do I guess as you will get duplicate order ids when you deleted records and add new ones.

no i am not counting i am asking for the max id number and i am getting the wrong number

i have 3 entries with id from 0 to 2

after adding a new entry i still get max(id) 4 and not the real max id

DonManfred · Jan 13, 2016

How is your table structure? Has id an autoicrement?

sorex · Jan 13, 2016

strange. hard to give advice without seeing anything in front of me.

DonManfred · Jan 13, 2016

i would suggest using an additional field name idx (int(8)).

Something like

B4X:

       $q = mysql_query("SELECT id,idx,name FROM '".$table."' order by id ASC;");   
        $rows = array();
        $id = 0;
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        foreach ($rows as $r) {
        mysql_query("UPDATE '".$table."' SET idx = '".$id."' WHERE id = '".$r["id"]."'");
        $id++;
        }
        print "ReNumberDone";

Lahksman · Jan 13, 2016

As far as i know it, mysql_query returns a result resource and not the actual value.
You could try this for the max(id)

B4X:

case "HighestId":
    $q = mysql_query("SELECT MAX(id) FROM $table");
    $row = mysql_fetch_row($q);
    $highest_id = $row[];
    print $highest_id;
break;

KMatle · Jan 13, 2016

ilan said:
so what i need to do is every time i add a new entry i need to get all entries and search for the highest id number and then add + to it and save the new entry

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?

sorex · Jan 13, 2016

I doubt it because you can merge max values with regular ones in a single query resultset.

I know that recordcount (_num_rows) works different tho as it's based on the connection handle.

php help

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Active Member

Expert

Expert

Privacy & Transparency

Privacy & Transparency