[DarkLored]

So I am working on normalizing my database for my gamemode and I have ran into an issue with variables, I can't figure out a way where I can assign two different results into two different variables when looking for a value.

Here is the SELECT code

Код:

format(Query, sizeof(Query), "SELECT * FROM `Users`, `Weapons` WHERE Users.UserID = Weapons.ID", DB_Escape(pData[playerid][pID]));

This is the tricky part for me, I don't know if I will get the same rowid if I use for the two different variables.

Код:

db_get_field_assoc(Result, "Weapon", Query, 3);
db_get_field_assoc(Result, "Ammo", Query, 5);

When using SQLite Browser I get the results that I want from two different row IDs I just don't know how to separate the two into different variables.

Variables

Код:

pData[playerid][pGunI]
pData[playerid][pGunII]
pData[playerid][pAmmoI]
pData[playerid][pAmmoII]

Example of what I am trying to accomplish

Код:

Row 1 = 2 //Same ID as Row 2
Row 2 = 24 //Same ID as Row 1

pData[playerid][pGunI] = 2 //Row one for the same player
pData[playerid][pGunII] = 24 //Row two for the same player

Thanks in advance :3

[iggy1]

Shouldn't this:

Код:

format(Query, sizeof(Query), "SELECT * FROM `Users`, `Weapons` WHERE Users.UserID = Weapons.ID", DB_Escape(pData[playerid][pID]));

Be this:

Код:

format(Query, sizeof(Query), "SELECT * FROM `Users`, `Weapons` WHERE Users.UserID = %d AND Weapons.ID = %d", pData[playerid][pID], pData[playerid][pID]);//don't escape integers, only strings

Im not sure if your original query would work, and if it does work, your selecting everything 'Users' and 'Weapons' tables where Users.UserID is equal to Weapons.ID. IE, you're not putting an actual user ID into the query.

And then you'd do something like this:

Код:

db_get_field_assoc(Result, "Weapon", Query, 3);//"Weapon" might need to be "Weapons.Weapon" (not sure how db_get_field_assoc works)
pData[playerid][pGunI] = strval(Query);

db_get_field_assoc(Result, "Weapon2", Query, 3);//"Weapon2" might need to be "Weapons.Weapon2"
pData[playerid][pGunII] = strval(Query).
//...

[DarkLored]

Quote:

Originally Posted by iggy1 Shouldn't this: Код: format(Query, sizeof(Query), "SELECT * FROM `Users`, `Weapons` WHERE Users.UserID = Weapons.ID", DB_Escape(pData[playerid][pID])); Be this: Код: format(Query, sizeof(Query), "SELECT * FROM `Users`, `Weapons` WHERE Users.UserID = %d AND Weapons.ID = %d", pData[playerid][pID], pData[playerid][pID]);//don't escape integers, only strings Im not sure if your original query would work, and if it does work, your selecting everything 'Users' and 'Weapons' tables where Users.UserID is equal to Weapons.ID. IE, you're not putting an actual user ID into the query. And then you'd do something like this: Код: db_get_field_assoc(Result, "Weapon", Query, 3);//"Weapon" might need to be "Weapons.Weapon" (not sure how db_get_field_assoc works) pData[playerid][pGunI] = strval(Query); db_get_field_assoc(Result, "Weapon2", Query, 3);//"Weapon2" might need to be "Weapons.Weapon2" pData[playerid][pGunII] = strval(Query). //...

But 'Weapon2' is not a column in my 'Weapons' table, what I used before gives me the rows that are related to the user ID, the whole point of this is how to separate them both without having to create a new column.

[iggy1]

Yeah that was just an example, you was supposed to adapt that to your column names...

Not quite sure what you mean by "the whole point of this is how to separate them both without having to create a new column", separate what? Can't see you create a new column there either. If you could explain more it'd be great.

[DarkLored]

What I meant is every time a player gets a gun a row is created for him with his ID, maximum rows are two for each player, now if the player has two existing rows for himself I am trying to figure out how to get the two rows separately and then putting them into two separate rows.

I'll show you my table itself

pawn Код:

strcat(Query, "CREATE TABLE IF NOT EXISTS `Weapons` (`ID` INTEGER DEFAULT 0 NOT NULL, `Weapon` INTEGER DEFAULT 0 NOT NULL, `Ammo` INTEGER DEFAULT 0 NOT NULL, FOREIGN KEY(`ID`) REFERENCES `Users`(`UserID`) ON UPDATE CASCADE ON DELETE CASCADE);", sizeof(Query));