[DarrenReeder]

Hello,

Basicly, this is more of a Database question rather than Pawn itself...But its related, so i put it in this forum, designed for "Related for creating scripts for Pawn"...

---

So, I have got a table for Organisation Vehicles and the Primary key (ID) is auto incremented..

this gives me a problem because if i delete record, then the AI number just keeps going... so, at the moment my table looks like:

Код:

ID 
3
5
9

See what i mean? So, ID 1,2,4,6,7,8 are all missing because i deleted them, but when i insert the next record, it inserts it into id 10..

--

this creates a MASSIVE problem, becuase at the moment i have a loop like this:

pawn Код:

for(new i = 0; i < orgVehs; i++){

Basicly, orgvehs is the amount of vehicles i have currently got in the server and i run throught the amount of vehicles and check the database for the ID ( if i=2, i will look for the vehicle with ID 2 in the database)..

But because ID 1,2,4 etc are being skipped, then that means i am runing 6 loops that query the database which do nothing... so i have to run 9 loops, and i am only using 3 of them..

so when it gets to a larger scale...this will be VERY bad..

-----

So, anyone know how i can make it so it automaticly insert the smallest ID possible?

-thanks

[legodude]

for(new i=0;i<orgvehs;i++)
{
if(!isset(i))
{
set(i);
break;
}
}

you see what i mean right?

[DarrenReeder]

I dont think that would work because, basicly this is running a query to load the records into an enum... there is no way to avoid it really, i need to make sure all my ID's in my table go from 1+ without missing numbers :S

[Nero_3D]

Than stop the primary key from auto incremented

Just search through the database for missing numbers and fill their the data

[DarrenReeder]

Quote:

Originally Posted by ****** Or use array index to reference, rather that database id. What are you actually using the primary key for? A database primary key is a unique reference to an element within a database for the purposes of cross referencing. Anyway, even if you could fill in the gaps, if you only have 3 and delete number 2, you'll have a gap until you create a new one.

The ID is so that i can do use /setveh [Orgid] in game...then it gets the Model ID, X,Y,Z and the ORgid to set the veh in tat position...

then i can use /delveh and it looks on the enum for "orgVeh[id][pId]" and delete that from the database (I use orgVeh[pTempID] to store the Vehicle ID ingame.. so then when i try to delete, i look through the enums and use..

if(GetVehicleID(veh) == orgVehp[i][pTempID])

Get me?

--

Quote:

Anyway, even if you could fill in the gaps, if you only have 3 and delete number 2, you'll have a gap until you create a new one.

And do you mean if i use the index thing it does that? because thats what i need it to do, but with primary key using A_I it doesnt do that.

[DarrenReeder]

The problem is that... i am looping through numbers that are not needed...this is basicly what my script looks like: (Not exact...)

pawn Код:

#define orgVehs 25 // Or whatever the highest "ID" is in my table.


public loadOrgVehs(){
     for(new i=0; i<orgVehs; i++){
     format(string, sizeof(string), "SELECT * FROM table WHERE ID='%d' ", i);
     mysql_query(string);

     //THEN STORE DATA AND PUT INTO MY ENUM (orgVehs[orgvehID][vModel/vOrgID/vX/vY etc...]
     }
     return 1;

}

So, as you can see... if my IDs in my table look like:

ID - Model - X - Y -Z
2 - 411 - x - y -z
5 - 560 - x - y -z

Then it will store null values when it looks through 1,3 and 4... SEe what i mean??

But when i INSERT INTO table (field, field, field) VALUES (field, field, field) the ID is Auto incremented so it then adds the next record as ID 6..


So, this will mean... even tho at the moment there is 2 records.. i need 5 loops so i get to ID 5 and therefore orgVehs need to be 5..

I hope you can see my problem :S

(If i get up to ID 100 and i only am using 10 of them...thats 90 queries/loops that do nothing )

[DarrenReeder]

So, now how do i put each record into my enums from that?

EDIT:
I still need to store the ID in the Enum though, right? So i know which receord to delete when i use /delveh while ingame...

[DarrenReeder]

When i use

Код:

SELECT * FROM table

How do i put each record into an enum? This is what i use atm:

pawn Код:

format(query, sizeof(query), "SELECT * FROM orgvehicles WHERE ID='%d'", i);
    mysql_query(query);
    mysql_store_result();
    while(mysql_retrieve_row()){
        new data[128];
        mysql_fetch_field_row(data, "ID");
        orgVeh[i][vID] = strval(data);

        mysql_fetch_field_row(data, "model");
        orgVeh[i][vModel] = strval(data);

        mysql_fetch_field_row(data, "x");
        orgVeh[i][vX] = strval(data);

        mysql_fetch_field_row(data, "y");
        orgVeh[i][vY] = strval(data);

        mysql_fetch_field_row(data, "z");
        orgVeh[i][vZ] = strval(data);

        mysql_fetch_field_row(data, "angle");
        orgVeh[i][vAngle] = strval(data);
        
        mysql_fetch_field_row(data, "orgID");
        orgVeh[i][vOrgID] = strval(data);


    }
    mysql_free_result();

    orgVeh[i][vTempID] = AddStaticVehicle(orgVeh[i][vModel],orgVeh[i][vX],orgVeh[i][vY],orgVeh[i][vZ],orgVeh[i][vAngle],-1,-1); // Car 1

EDIT:
srry for not getting how to do this..

i culd do it in PHP but not in Pawn... there is no mysql_fetch_array or anything so im not sure what to do

[DarrenReeder]

So like this?

pawn Код:

for(new i=0; i<orgVehs; i++){
    while(mysql_retrieve_row()){
        new data[128];
        mysql_fetch_field_row(data, "ID");
        orgVeh[i][vID] = strval(data);

        mysql_fetch_field_row(data, "model");
        orgVeh[i][vModel] = strval(data);

        mysql_fetch_field_row(data, "x");
        orgVeh[i][vX] = strval(data);

        mysql_fetch_field_row(data, "y");
        orgVeh[i][vY] = strval(data);

        mysql_fetch_field_row(data, "z");
        orgVeh[i][vZ] = strval(data);

        mysql_fetch_field_row(data, "angle");
        orgVeh[i][vAngle] = strval(data);
        
        mysql_fetch_field_row(data, "orgID");
        orgVeh[i][vOrgID] = strval(data);


    }
}

but that doesnt look rigt to me...looks like it it run that code in the while loop for every row on each loop :S

[Lenny the Cup]

remove the for loop, put new i; before the while loop and i++ inside it