SA-MP Forums Archive
[MySQL] Storing data without duplicates. - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: [MySQL] Storing data without duplicates. (/showthread.php?tid=664143)


[MySQL] Storing data without duplicates. - CherryMond - 20.02.2019

How can I update a record if the slot and boneid already exist (for one player)?

At the moment he is constantly adding and not updating.

PHP код:
for(new i; i < MAX_PLAYER_ATTACHED_OBJECTS; i++){
    if(IsPlayerAttachedObjectSlotUsed(playerid, i)){
        if(i != AOP[playerid][i][ao_slot]){
            format(query, sizeof(query), "INSERT INTO `"MYSQL_PREFIX"aop` (pid, modelid, slot, boneid, fOffsetX, fOffsetY, fOffsetZ, fRotX, fRotY, fRotZ, fScaleX, fScaleY, fScaleZ) VALUES('%i', '%i', '%i', '%i', '%f', '%f', '%f', '%f', '%f', '%f', '%f', '%f', '%f');",
                Player[playerid][PID], AOP[playerid][i][ao_model], i, AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],
                AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz]);
            SendClientMessage(playerid, -1, "[debug] Added");
        } else {
            format(query, sizeof(query), "UPDATE `"MYSQL_PREFIX"aop` SET modelid = '%i', slot = '%i', fOffsetX = '%f', fOffsetY = '%f', fOffsetZ = '%f', fRotX = '%f', fRotY = '%f', fRotZ = '%f', fScaleX = '%f', fScaleY = '%f', fScaleZ = '%f' WHERE boneid = '%i' AND pid = '%i'",
                AOP[playerid][i][ao_model], i, AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],
                AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz], AOP[playerid][i][ao_bone], Player[playerid][PID]);
            SendClientMessage(playerid, -1, "[debug] Updated");
        }
        mysql_query(query);
        mysql_free_result();
    }
}
// AOP - AttachedObjectPlayer 
Table structure:
https://forum.sa-mp.com/showpost.php...postcount=4606 (when I add UNIQUE index on boneid and slot, data don't added)

Re: [MySQL] Storing data without duplicates. - CherryMond - 20.02.2019

Quote:
Originally Posted by MacDuck
Посмотреть сообщение
I don't know how it works at all.
Код:
public OnPlayerEditAttachedObject(playerid, response, index, modelid, boneid, Float:fOffsetX, Float:fOffsetY, Float:fOffsetZ, Float:fRotX, Float:fRotY, Float:fRotZ, Float:fScaleX, Float:fScaleY, Float:fScaleZ){
	if(response){
		AOP[playerid][index][ao_model] = modelid;
        AOP[playerid][index][ao_bone] = boneid;
        AOP[playerid][index][ao_slot] = index;
        AOP[playerid][index][ao_x] = fOffsetX;
        AOP[playerid][index][ao_y] = fOffsetY;
        AOP[playerid][index][ao_z] = fOffsetZ;
        AOP[playerid][index][ao_rx] = fRotX;
        AOP[playerid][index][ao_ry] = fRotY;
        AOP[playerid][index][ao_rz] = fRotZ;
        AOP[playerid][index][ao_sx] = fScaleX;
        AOP[playerid][index][ao_sy] = fScaleY;
        AOP[playerid][index][ao_sz] = fScaleZ;
    }
	return 1;
}
This is done when I save the position of an object.
Do you need something else?

Re: [MySQL] Storing data without duplicates. - Calisthenics - 20.02.2019

The UNIQUE KEY must be on two columns together (pid, slot) and not one key for each one of them. This allows you to have unique pairing and use INSERT INTO .. ON DUPLICATE syntax which executes an INSERT query if no records found for the `pid` and `slot` values provided otherwise update what columns you want.

If a player only wants to replace the attached object to another boneid, you should pass the same slot so it will update the data for the already existed record.

pawn Код:
format(query, sizeof(query), 
    "INSERT INTO `"MYSQL_PREFIX"aop` (pid,modelid,slot,boneid,fOffsetX,fOffsetY,fOffsetZ,fRotX,fRotY,fRotZ,fScaleX,fScaleY,fScaleZ) \
     VALUES (%i,%i,%i,%i,%f,%f,%f,%f,%f,%f,%f,%f,%f) \
         ON DUPLICATE KEY \
     UPDATE modelid=%i,boneid=%i,fOffsetX=%f,fOffsetY=%f,fOffsetZ=%f,fRotX=%f,fRotY=%f,fRotZ=%f,fScaleX=%f,fScaleY=%f,fScaleZ=%f \
     WHERE pid=%i \
       AND slot=%i", 
                
    Player[playerid][PID], AOP[playerid][i][ao_model], i, AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz],
    AOP[playerid][i][ao_model], AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx], AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz], Player[playerid][PID], i);


Re: [MySQL] Storing data without duplicates. - CherryMond - 20.02.2019

I've error:
PHP код:
errorid: 1064, error[]: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE pid=37 AND slot=0' at line 1", resultid: -1, extraid: -1, callback[]: "NULL", query[] 
--

PHP код:
format(query, sizeof(query), "INSERT INTO `"MYSQL_PREFIX"aop` (pid,modelid,slot,boneid,fOffsetX,fOffsetY,fOffsetZ,fRotX,fRotY,fRotZ,fScaleX,fScaleY,fScaleZ) \
    VALUES (%i,%i,%i,%i,%f,%f,%f,%f,%f,%f,%f,%f,%f) \
        ON DUPLICATE KEY \
    UPDATE modelid=%i,boneid=%i,fOffsetX=%f,fOffsetY=%f,fOffsetZ=%f,fRotX=%f,fRotY=%f,fRotZ=%f,fScaleX=%f,fScaleY=%f,fScaleZ=%f \
    WHERE pid=%i \
    AND slot=%i",
    Player[playerid][PID], AOP[playerid][i][ao_model], i, AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],
    AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz], AOP[playerid][i][ao_model], AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],
    AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz], Player[playerid][PID], i); 


Re: [MySQL] Storing data without duplicates. - Calisthenics - 21.02.2019

There is internal check which makes WHERE invalid, my mistake.

pawn Код:
format(query, sizeof(query), 
    "INSERT INTO `"MYSQL_PREFIX"aop` (pid,modelid,slot,boneid,fOffsetX,fOffsetY,fOffsetZ,fRotX,fRotY,fRotZ,fScaleX,fScaleY,fScaleZ) \
     VALUES (%i,%i,%i,%i,%f,%f,%f,%f,%f,%f,%f,%f,%f) \
         ON DUPLICATE KEY \
     UPDATE modelid=%i,boneid=%i,fOffsetX=%f,fOffsetY=%f,fOffsetZ=%f,fRotX=%f,fRotY=%f,fRotZ=%f,fScaleX=%f,fScaleY=%f,fScaleZ=%f", 
                
    Player[playerid][PID], AOP[playerid][i][ao_model], i, AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx],AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz],
    AOP[playerid][i][ao_model], AOP[playerid][i][ao_bone], AOP[playerid][i][ao_x], AOP[playerid][i][ao_y], AOP[playerid][i][ao_z], AOP[playerid][i][ao_rx], AOP[playerid][i][ao_ry], AOP[playerid][i][ao_rz], AOP[playerid][i][ao_sx], AOP[playerid][i][ao_sy], AOP[playerid][i][ao_sz]);


Re: [MySQL] Storing data without duplicates. - CherryMond - 21.02.2019

Yes, I read about it too. Thank you very much.