PDA

View Full Version : Vehicle ID's Loading in random order. MySQL R41


Tass007
19/07/2018, 06:04 AM
Hey guys. I have a vehicle system and I've rebuilt it so that in the database it isn't just carid that is auto incremented and so when deleting and creating vehicles it doesn't make a mess.

However now I've come into an issue.

My issue, is in my database it won't the vehicle to load in order of the carid's in the database. For example I've made a check statement for loading the vehicles in the output and I get.

Vehicle ID: 0 Vehicle Model: 523
Vehicle ID: 1 Vehicle Model: 523
Vehicle ID: 2 Vehicle Model: 596
Vehicle ID: 3 Vehicle Model: 596
Vehicle ID: 4 Vehicle Model: 596
Vehicle ID: 5 Vehicle Model: 426
Vehicle ID: 6 Vehicle Model: 426
Vehicle ID: 7 Vehicle Model: 560
Vehicle ID: 8 Vehicle Model: 560
Vehicle ID: 9 Vehicle Model: 541
Vehicle ID: 10 Vehicle Model: 544

It seems to be grouping the vehicle model somewhat backwards??? However what it looks like in the DB is.

Vehicle ID: 0 Vehicle Model: 544
Vehicle ID: 1 Vehicle Model: 541
Vehicle ID: 2 Vehicle Model: 560
Vehicle ID: 3 Vehicle Model: 560
Vehicle ID: 4 Vehicle Model: 426
Vehicle ID: 5 Vehicle Model: 426
Vehicle ID: 6 Vehicle Model: 523
Vehicle ID: 7 Vehicle Model: 523
Vehicle ID: 8 Vehicle Model: 596
Vehicle ID: 9 Vehicle Model: 596
Vehicle ID: 10 Vehicle Model: 596


Here is my load code.
public Car_Load()
{
static
rows,
fields,
str[128];

cache_get_row_count(rows);
cache_get_field_count(fields);
for (new i = 0; i < rows; i ++) if (i < MAX_DYNAMIC_CARS)
{
CarData[i][carExists] = true;
cache_get_value_name_int(i, "carID",CarData[i][carID]);
cache_get_value_name_int(i, "carModel",CarData[i][carModel]);
cache_get_value_name_int(i, "carOwner",CarData[i][carOwner]);
cache_get_value_name_float(i, "carPosX",CarData[i][carPos][0]);
cache_get_value_name_float(i, "carPosY",CarData[i][carPos][1]);
cache_get_value_name_float(i, "carPosZ",CarData[i][carPos][2]);
cache_get_value_name_float(i, "carPosR",CarData[i][carPos][3]);
cache_get_value_name_int(i, "carColor1",CarData[i][carColor1]);
cache_get_value_name_int(i, "carColor2",CarData[i][carColor2]);
cache_get_value_name_int(i, "carPaintjob",CarData[i][carPaintjob]);
cache_get_value_name_int(i, "carLocked",CarData[i][carLocked]);
cache_get_value_name_int(i, "carImpounded",CarData[i][carImpounded]);
cache_get_value_name_int(i, "carImpoundPrice",CarData[i][carImpoundPrice]);
cache_get_value_name_int(i, "carFaction",CarData[i][carFaction]);
cache_get_value_name_int(i, "carSiren",CarData[i][carSiren]);
printf("Vehicle ID: %d Vehicle Model: %d", Iter_Count(Vehs), CarData[i][carModel]);
for (new j = 0; j < 14; j ++)
{
if (j < 5)
{
format(str, sizeof(str), "carWeapon%d", j + 1);
cache_get_value_name_int(i, str, CarData[i][carWeapons][j]);

format(str, sizeof(str), "carAmmo%d", j + 1);
cache_get_value_name_int(i, str,CarData[i][carAmmo][j]);
}
format(str, sizeof(str), "carMod%d", j + 1);
cache_get_value_name_int(i, str,CarData[i][carMods][j]);
}

Iter_Add(Vehs, i);
Car_Spawn(i);
}
for (new j = 0; j < MAX_DYNAMIC_CARS; j ++) if (CarData[j][carExists]) {
format(str, sizeof(str), "SELECT * FROM `carstorage` WHERE `ID` = '%d'", CarData[j][carID]);

mysql_tquery(g_iHandle, str, "OnLoadCarStorage", "d", j);
}
printf(" [Vehicle System] Loaded %d Vehicles.", Iter_Count(Vehs));
return 1;
}

My Car save, also I haven't used the car save function I've inputted these in through the database SQL option.
Car_Save(carid)
{
static
query[900];
if(!Iter_Contains(Vehs, carid)) return 0;
if (CarData[carid][carVehicle] != INVALID_VEHICLE_ID)
{
for (new i = 0; i < 14; i ++) {
CarData[carid][carMods][i] = GetVehicleComponentInSlot(CarData[carid][carVehicle], i);
}
}
format(query, sizeof(query), "UPDATE `Vehicles` SET `carModel` = '%d', `carOwner` = '%d', `carPosX` = '%.4f', `carPosY` = '%.4f', `carPosZ` = '%.4f', `carPosR` = '%.4f', `carColor1` = '%d', `carColor2` = '%d', `carPaintjob` = '%d', `carLocked` = '%d'",
CarData[carid][carModel],
CarData[carid][carOwner],
CarData[carid][carPos][0],
CarData[carid][carPos][1],
CarData[carid][carPos][2],
CarData[carid][carPos][3],
CarData[carid][carColor1],
CarData[carid][carColor2],
CarData[carid][carPaintjob],
CarData[carid][carLocked]
);
format(query, sizeof(query), "%s, `carMod1` = '%d', `carMod2` = '%d', `carMod3` = '%d', `carMod4` = '%d', `carMod5` = '%d', `carMod6` = '%d', `carMod7` = '%d', `carMod8` = '%d', `carMod9` = '%d', `carMod10` = '%d', `carMod11` = '%d', `carMod12` = '%d', `carMod13` = '%d', `carMod14` = '%d'",
query,
CarData[carid][carMods][0],
CarData[carid][carMods][1],
CarData[carid][carMods][2],
CarData[carid][carMods][3],
CarData[carid][carMods][4],
CarData[carid][carMods][5],
CarData[carid][carMods][6],
CarData[carid][carMods][7],
CarData[carid][carMods][8],
CarData[carid][carMods][9],
CarData[carid][carMods][10],
CarData[carid][carMods][11],
CarData[carid][carMods][12],
CarData[carid][carMods][13]
);
format(query, sizeof(query), "%s, `carImpounded` = '%d', `carImpoundPrice` = '%d', `carFaction` = '%d' , `carSiren` = '%d', `carWeapon1` = '%d', `carWeapon2` = '%d', `carWeapon3` = '%d', `carWeapon4` = '%d', `carWeapon5` = '%d', `carAmmo1` = '%d', `carAmmo2` = '%d', `carAmmo3` = '%d', `carAmmo4` = '%d', `carAmmo5` = '%d' WHERE `carID` = '%d'",
query,
CarData[carid][carImpounded],
CarData[carid][carImpoundPrice],
CarData[carid][carFaction],
CarData[carid][carSiren],
CarData[carid][carWeapons][0],
CarData[carid][carWeapons][1],
CarData[carid][carWeapons][2],
CarData[carid][carWeapons][3],
CarData[carid][carWeapons][4],
CarData[carid][carAmmo][0],
CarData[carid][carAmmo][1],
CarData[carid][carAmmo][2],
CarData[carid][carAmmo][3],
CarData[carid][carAmmo][4],
CarData[carid][carID]
);
return mysql_tquery(g_iHandle, query);
}

Any help would be much appreciated

Banditul18
19/07/2018, 09:16 AM
Its nothing wrong, when data is loadin from database its loading from ID 0 to maximum ID. If you want the cars to be loaded by model you can use GROUP BY in the SELECT query but its same thing

Tass007
19/07/2018, 09:30 AM
But I want them to load so that the ID matches the database as ingame? I want the vehicles to be loaded so that Car ID 0 in the database is equal to car ID 0 in the game.

Tass007
19/07/2018, 10:22 AM
How would you suggest I create this system then? Can I get some recommendations?

Calisthenics
19/07/2018, 11:33 AM
Database should have a primary key (auto increment) to identify each vehicle. The rest of the data such as model is what you only need. Then you store in variable the ID of the vehicle so you can update the database if needed or delete records from it. The in-game vehicleid is not meant to be stored, you use it as index in arrays.

Tass007
19/07/2018, 11:38 AM
Can you give me an example?

Calisthenics
19/07/2018, 11:51 AM
CarData has size of say MAX_VEHICLES. What you can do is store the data in local variables:

cache_get_value_name_int(i, "carID", car_ID);
cache_get_value_name_int(i, "carModel", car_Model);
cache_get_value_name_int(i, "carOwner", car_Owner);
cache_get_value_name_float(i, "carPosX", car_Pos[0]);
cache_get_value_name_float(i, "carPosY", car_Pos[1]);
cache_get_value_name_float(i, "carPosZ", car_Pos[2]);
cache_get_value_name_float(i, "carPosR", car_Pos[3]);
cache_get_value_name_int(i, "carColor1", car_Color1);
cache_get_value_name_int(i, "carColor2", car_Color2);
...

and then create the vehicle

new created_vehicle = CreateVehicle(car_Model, car_Pos[0], car_Pos[1], car_Pos[2], car_Pos[3], car_Color1, car_Color2, respawn_delay_here, siren_here..);


if (created_vehicle != INVALID_VEHICLE_ID) // not reached limit or invalid modelid
{
//assign vehicleid as index
CarData[created_vehicle][carID] = car_ID;
CarData[created_vehicle][carModel] = car_Model;
...
}

so now anything related to vehicle in-game as to what player is currently in, you can use the index to retrieve data from it. However, when you want to save the said vehicle:

"UPDATE ... SET ... WHERE carID=%d", ..., CarData[iteration_variable_here][carID]);


And do not store components in the same table, use a different table (child) and carID will be the FOREIGN KEY: http://forum.sa-mp.com/showthread.php?t=420363

Tass007
20/07/2018, 01:17 AM
Okay. Well I had that originally. Before I somewhat rebuilt my code. But the problem I was having was the fact that the ID in the database table is Auto Incremented so if I deleted a car I would lost it completely. So I tried to recreate it so it wasn't reliant on the carid being auto incremented. If you could help me with that, it would be awesome.

public Car_Load()
{
static
rows,
fields,
str[128];

cache_get_data(rows, fields, g_iHandle);

for (new i = 0; i < rows; i ++) if (i < MAX_DYNAMIC_CARS)
{
CarData[i][carExists] = true;
CarData[i][carID] = cache_get_field_int(i, "carID");
CarData[i][carModel] = cache_get_field_int(i, "carModel");
CarData[i][carOwner] = cache_get_field_int(i, "carOwner");
CarData[i][carPos][0] = cache_get_field_float(i, "carPosX");
CarData[i][carPos][1] = cache_get_field_float(i, "carPosY");
CarData[i][carPos][2] = cache_get_field_float(i, "carPosZ");
CarData[i][carPos][3] = cache_get_field_float(i, "carPosR");
CarData[i][carColor1] = cache_get_field_int(i, "carColor1");
CarData[i][carColor2] = cache_get_field_int(i, "carColor2");
CarData[i][carPaintjob] = cache_get_field_int(i, "carPaintjob");
CarData[i][carLocked] = cache_get_field_int(i, "carLocked");
CarData[i][carImpounded] = cache_get_field_int(i, "carImpounded");
CarData[i][carImpoundPrice] = cache_get_field_int(i, "carImpoundPrice");
CarData[i][carFaction] = cache_get_field_int(i, "carFaction");

for (new j = 0; j < 14; j ++)
{
if (j < 5)
{
format(str, sizeof(str), "carWeapon%d", j + 1);
CarData[i][carWeapons][j] = cache_get_field_int(i, str);

format(str, sizeof(str), "carAmmo%d", j + 1);
CarData[i][carAmmo][j] = cache_get_field_int(i, str);
}
format(str, sizeof(str), "carMod%d", j + 1);
CarData[i][carMods][j] = cache_get_field_int(i, str);
}
Car_Spawn(i);
}
for (new i = 0; i < MAX_DYNAMIC_CARS; i ++) if (CarData[i][carExists]) {
format(str, sizeof(str), "SELECT * FROM `carstorage` WHERE `ID` = '%d'", CarData[i][carID]);

mysql_tquery(g_iHandle, str, "OnLoadCarStorage", "d", i);
}
return 1;
}

Calisthenics
20/07/2018, 06:01 AM
Again, the ID in the database is meant to be used as a way to identify a vehicle to update/delete. Nothing more, nothing less. You should not care if there are 2 records like this:

ID
----
1
2345

as the array would have 2 only indexes:

CarData[1][carID] = 1;
CarData[2][carID] = 2345;

Since index 0 is empty, you can set any default value so you can reset any index very easy.

I advise you with a better method, it is up to you what you will use. Especially consider the components in another table, it is bad design all-in-one.

Tass007
20/07/2018, 06:45 AM
I'm not really understanding how the method you said is any better than the one currently? I have most of my components in a different table these are mainly just checks and stuff. I really appreciate your help.

denNorske
20/07/2018, 06:58 AM
Shortly said: Your current system relies on a static ID in a database, which is not the case for a dynamic game.
When you create a database, you should create unique ID's to sort them by in a table/database. This unique value is only to identify the "database" entry - rest should be assigned to variables as Calisthenics told above.

So use auto increment on the unique value, but don't use the CARID as an unique value.

Then use count to determine amount of cars you've created (or simply a variable that adds/removes 1 for every change).

As mentioned above several times, try to re-architecture your approach here. What do you really want to do? There is a good way to do it just above here - why wouldn't that work for you?

Tass007
20/07/2018, 08:06 AM
I'm not saying it wouldn't work for me. I'm just not understand it. Can I get more detail or more examples? I don't understand how it's differing from what I already have.

Tass007
20/07/2018, 09:11 AM
Okay okay. Now I understand; This is the thing, I tried to rebuilt my system to act like this. However I don't think I went about it the correct way.

This is the code I started with.
public Car_Load()
{
static
rows,
fields,
str[128];

cache_get_data(rows, fields, g_iHandle);

for (new i = 0; i < rows; i ++) if (i < MAX_DYNAMIC_CARS)
{
CarData[i][carExists] = true;
CarData[i][carID] = cache_get_field_int(i, "carID");
CarData[i][carModel] = cache_get_field_int(i, "carModel");
CarData[i][carOwner] = cache_get_field_int(i, "carOwner");
CarData[i][carPos][0] = cache_get_field_float(i, "carPosX");
CarData[i][carPos][1] = cache_get_field_float(i, "carPosY");
CarData[i][carPos][2] = cache_get_field_float(i, "carPosZ");
CarData[i][carPos][3] = cache_get_field_float(i, "carPosR");
CarData[i][carColor1] = cache_get_field_int(i, "carColor1");
CarData[i][carColor2] = cache_get_field_int(i, "carColor2");
CarData[i][carPaintjob] = cache_get_field_int(i, "carPaintjob");
CarData[i][carLocked] = cache_get_field_int(i, "carLocked");
CarData[i][carImpounded] = cache_get_field_int(i, "carImpounded");
CarData[i][carImpoundPrice] = cache_get_field_int(i, "carImpoundPrice");
CarData[i][carFaction] = cache_get_field_int(i, "carFaction");

for (new j = 0; j < 14; j ++)
{
if (j < 5)
{
format(str, sizeof(str), "carWeapon%d", j + 1);
CarData[i][carWeapons][j] = cache_get_field_int(i, str);

format(str, sizeof(str), "carAmmo%d", j + 1);
CarData[i][carAmmo][j] = cache_get_field_int(i, str);
}
format(str, sizeof(str), "carMod%d", j + 1);
CarData[i][carMods][j] = cache_get_field_int(i, str);
}
Car_Spawn(i);
}
for (new i = 0; i < MAX_DYNAMIC_CARS; i ++) if (CarData[i][carExists]) {
format(str, sizeof(str), "SELECT * FROM `carstorage` WHERE `ID` = '%d'", CarData[i][carID]);

mysql_tquery(g_iHandle, str, "OnLoadCarStorage", "d", i);
}
return 1;
}

And this is the code I finished with. I was trying to do it so that it wouldn't auto increment the car id. It would auto increment of a "persistent address" in the database called DBID which was the primary key. But I also have OCD, so when I look in the database and the vehicle IDs aren't spawning in that order I always want to fix it.

public Car_Load()
{
static
rows,
fields,
str[128];

cache_get_row_count(rows);
cache_get_field_count(fields);
for (new i = 0; i < rows; i ++) if (i < MAX_DYNAMIC_CARS)
{
CarData[i][carExists] = true;
cache_get_value_name_int(i, "carID",CarData[i][carID]);
cache_get_value_name_int(i, "carModel",CarData[i][carModel]);
cache_get_value_name_int(i, "carOwner",CarData[i][carOwner]);
cache_get_value_name_float(i, "carPosX",CarData[i][carPos][0]);
cache_get_value_name_float(i, "carPosY",CarData[i][carPos][1]);
cache_get_value_name_float(i, "carPosZ",CarData[i][carPos][2]);
cache_get_value_name_float(i, "carPosR",CarData[i][carPos][3]);
cache_get_value_name_int(i, "carColor1",CarData[i][carColor1]);
cache_get_value_name_int(i, "carColor2",CarData[i][carColor2]);
cache_get_value_name_int(i, "carPaintjob",CarData[i][carPaintjob]);
cache_get_value_name_int(i, "carLocked",CarData[i][carLocked]);
cache_get_value_name_int(i, "carImpounded",CarData[i][carImpounded]);
cache_get_value_name_int(i, "carImpoundPrice",CarData[i][carImpoundPrice]);
cache_get_value_name_int(i, "carFaction",CarData[i][carFaction]);
cache_get_value_name_int(i, "carSiren",CarData[i][carSiren]);
printf("Vehicle ID: %d Vehicle Model: %d", Iter_Count(Vehs), CarData[i][carModel]);
for (new j = 0; j < 14; j ++)
{
if (j < 5)
{
format(str, sizeof(str), "carWeapon%d", j + 1);
cache_get_value_name_int(i, str, CarData[i][carWeapons][j]);

format(str, sizeof(str), "carAmmo%d", j + 1);
cache_get_value_name_int(i, str,CarData[i][carAmmo][j]);
}
format(str, sizeof(str), "carMod%d", j + 1);
cache_get_value_name_int(i, str,CarData[i][carMods][j]);
}

Iter_Add(Vehs, i);
Car_Spawn(i);
}
for (new j = 0; j < MAX_DYNAMIC_CARS; j ++) if (CarData[j][carExists]) {
format(str, sizeof(str), "SELECT * FROM `carstorage` WHERE `ID` = '%d'", CarData[j][carID]);

mysql_tquery(g_iHandle, str, "OnLoadCarStorage", "d", j);
}
printf(" [Vehicle System] Loaded %d Vehicles.", Iter_Count(Vehs));
return 1;
}

AmigaBlizzard
21/07/2018, 07:34 AM
The problem with your current system is this (and will mess up your server big time):

You have 10 vehicles in your database for example, with ID's 1 to 10.

You delete one vehicle, let's say vehicle 5 and you want to replace it with another vehiclemodel.
Now VehicleID 5 is empty for the server.

Now a player buys/rents/spawns a temporary vehicle, which will not be saved in your database.
Since ID 5 is the first free ID, the server will use that ID.

After that, you create your vehicle and it will get ID 11 instead of ID 5, which you wanted to re-use.
Now you have ID's 1-4 and 6-11 in your database.

Then you shutdown your server and reload.
Your loading function will use those ID's to create the vehicles.
It will simply loop through the ROWS, not the ID's and you just blindly assume the row-index is exactly the same as the ID in your database, which clearly is not the case anymore.

When it comes to the 5th vehicle, which has ID 6 instead of 5 (ID 5 was a temporary rented vehicle), you're storing your vehicle-data in the wrong index of the array.

Another mistake is that vehicleID's go from 1-1999, while loading rows goes from 0-xxx.
So the data for vehicle 1 is stored at index 0 of your array.
When updating any vehicle, you'll be overwriting the wrong data in your array and in your database and nothing matches anymore.

See where this goes?
Just because of one unexpected vehicle, your entire database is messed up.

Even if everything would check out and you don't have unexpected vehicles when saving the data, you have to be sure there aren't any vehicles when you load the data as well.
If you load data for ID 7, and there are already 20 vehicles created on your server (temporary rented vehicles), you store the data in array index 7, but your vehicle, which is created afterwards at the end of the loading function, will get ID 27 instead of 7.

Before storing data in the array, first create the vehicle to get the vehicleID.
Then you know where you can safely store the data for that vehicle.
Blindly storing data at your expected index and creating the vehicle afterwards MAY mess up everything because of 1 unexpected vehicle.


When you give every new vehicle a unique ID, by using the auto-increment feature of MySQL, you'll never have this problem.
When you create a new vehicle, CreateVehicle gives you the vehicleID.
Store all data in your array using that ID.
Then you insert the vehicle in your database and with the auto-increment column, MySQL gives you a unique databaseID to reference that vehicle later on.
You can get that ID and store it in your array as well.
VehicleID 5 may get databaseID 308 for example.
So ingame you reference that vehicle using ID 5, but in the database you reference it using ID 308.

Upon loading, first load the data you need to be able to create the vehicle like model, position, rotation and color, load those values into temporary variables.
Then create the vehicle itself so you get the VehicleID.
Then you can store the data from your temporary variables into the array at the index returned by CreateVehicle and load the remaining data (also load the databaseID to be able to reference the vehicle afterwxards in the database in case you want to make changes to the vehicle such as color).

Of course, when you delete a vehicle, MySQL won't use that ID ever again, but should that be a problem?
I guess not, since the ID may go up to 2.1 billion, the maximum value for a signed 32-bit integer.

If your server runs for 10 years, that's 210 million vehicles you can create/delete every year before running out of ID's.
That's 575342 vehicles every day you can create/delete. I guess your server isn't that occupied to even come near that kind of activity.



I have a LineAgeII server running at home on which only me and my daughter were playing for a short time.
The first character created on that server had ID 268481355 instead of ID 1.
And the first item bought by that character has ID 268481356.
The second character created has ID 268481435.
Keeping the ID's in perfect order is a waste of time since nobody sees the ID's, except for the server owned who maintains the server.

Tass007
24/07/2018, 12:06 AM
Is there a way you can show me how to specifically implement this into my code?

Tass007
26/07/2018, 01:11 AM
Bump?

denNorske
26/07/2018, 12:08 PM
Amiga almost told you step by step how to make a code that features the mothod. I would suggest you try before asking for codes.

Tass007
02/08/2018, 12:47 AM
I just still don't understand how my script isn't doing what he's said. It has it's own ID that is assigned, it has a DB id in the database that has no relation. ?? Or is there a system that already exists that has a good system that I could look at?