php help

[ilan]

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:

    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]

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]

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]

if you really want it try...

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]

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]

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]

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

Maybe i should avoid it...

 

[KMatle]

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]

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]

i am trying it like this:

    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]

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

$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]

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]

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]

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]

How is your table structure? Has id an autoicrement?

 

[sorex]

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

 

[DonManfred]

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

Something like

       $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]

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)

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

 

[KMatle]

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]

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.