MySQL foreign key error [registration]

MySQL foreign key error [registration] - 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 foreign key error [registration] (/showthread.php?tid=606716)

MySQL foreign key error [registration] - NeXoR - 08.05.2016

Hey guys, I am trying to make a MySQL registration system using foreign keys
When I delete a character, the rows on the child tables gets deleted easily
But when I register a character, No rows are created on the child tables
Pawn code:

PHP код:



            new password[256], query[1000], string[128];

            WP_Hash(password, sizeof(password), inputtext);

            mysql_format(mysql, query, sizeof(query), "INSERT INTO `weapons` (`wSlot0`) VALUES (0)");

            mysql_tquery(mysql, query);

            mysql_format(mysql, query, sizeof(query), "INSERT INTO `toys` (`tModel0`) VALUES (0)");

            mysql_tquery(mysql, query);

            mysql_format(mysql, query, sizeof(query), "INSERT INTO `users` (`Name`, `Password`, `IP`, `Level`, `Gender`, `Age`) VALUES ('%e', '%s', '%s', 1, 0, 0)", RPNU(playerid), password, RPIP(playerid));

            mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);

               format(string, sizeof(string), "SERVER: {FFFFFF}You have successfully registered on {FF6347}American Life Roleplay{FFFFFF}. (Password: %s)", inputtext);

              TotalRegister++;

             SendClientMessage(playerid, COLOR_LIGHTRED, string);

            ShowDialog(playerid, 2);

Error codes from mysql log

Код:

[03:38:09] [DEBUG] mysql_format - connection: 1, len: 1000, format: "INSERT INTO `weapons` (`wSlot0`) VALUES (0)"
[03:38:09] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `weapons` (`wSlot0`) VALUES (0)", callback: "(null)", format: "(null)"
[03:38:09] [DEBUG] mysql_format - connection: 1, len: 1000, format: "INSERT INTO `toys` (`tModel0`) VALUES (0)"
[03:38:09] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `toys` (`tModel0`) VALUES (0)", callback: "(null)", format: "(null)"
[03:38:09] [DEBUG] mysql_format - connection: 1, len: 1000, format: "INSERT INTO `users` (`Name`, `Password`, `IP`, `Level`, `Gender`, `Age`) VALUES ('%e', '%s', '%s', 1, 0, 0)"
[03:38:09] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `users` (`Name`, `Password`, `IP`, `Level`, `Gender`", callback: "OnAccountRegister", format: "i"
[03:38:09] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[03:38:09] [ERROR] CMySQLQuery::Execute[] - (error #1452) Cannot add or update a child row: a foreign key constraint fails (`al-rp`.`weapons`, CONSTRAINT `weapons_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE)
[03:38:09] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError
[03:38:09] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[03:38:09] [ERROR] CMySQLQuery::Execute[] - (error #1452) Cannot add or update a child row: a foreign key constraint fails (`al-rp`.`toys`, CONSTRAINT `toys_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE)
[03:38:09] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError

Re: MySQL foreign key error [registration] - Konstantinos - 08.05.2016

You are supposed to insert the new row to users table first and then do stuff with the child tables.
I don't see why you'd need to set them to 0 though, insert data only when a weapon or toy is added. Having "0" for nothing is kind of pointless.

Re: MySQL foreign key error [registration] - Slawiii - 08.05.2016

try changing

Код:

mysql_tquery(mysql, query);

to :

Код:

mysql_tquery(mysql, query, "", "");

Re: MySQL foreign key error [registration] - PrO.GameR - 08.05.2016

Quote:

Originally Posted by Slawiii

try changing

Код:

mysql_tquery(mysql, query);

to :

Код:

mysql_tquery(mysql, query, "", "");

Just tell me, for what possible reason would giving 2 params into a function fix a MYSQL error?
a foreign key constraint fails
A feature of foreign keys are that you can't insert an invalid key, it fails, you are simply inserting something expecting it to know which player id you are inserting for, you need to first insert the player's info in users table, get the AI id, use that to insert your query for child tables.

Re: MySQL foreign key error [registration] - Slawiii - 08.05.2016

Quote:

Originally Posted by PrO.GameR

oh sorry i m just trying

Re: MySQL foreign key error [registration] - NeXoR - 08.05.2016

Quote:

Originally Posted by PrO.GameR

Thats my exact purpose, I want to add a row with the same ID from the users table to the child tables
how can I do so ?
Shall I add a foreign key from the users table to the children ?
I currently have only from the children to the parent table

Re: MySQL foreign key error [registration] - PrO.GameR - 09.05.2016

You can't get the inserted row id there, you need to insert into child tables under OnAccountRegister by using this function

new id = cache_insert_id();

now this is the SQLID of the player which you can use to insert into child tables.

Re: MySQL foreign key error [registration] - NeXoR - 09.05.2016

Quote:

Originally Posted by PrO.GameR

You can't get the inserted row id there, you need to insert into child tables under OnAccountRegister by using this function

new id = cache_insert_id();

now this is the SQLID of the player which you can use to insert into child tables.

So shall I remove the "AUTO INCREASE" from the child tables ?

Re: MySQL foreign key error [registration] - Sithis - 09.05.2016

Quote:

Originally Posted by NeXoR

So shall I remove the "AUTO INCREASE" from the child tables ?

That's not needed.

What needs to happen is:

User created -> get the inserted ID of the user using cache_insert_id(); -> insert toy -> insert weapon.