[NeXoR]

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

[Konstantinos]

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.

[Slawiii]

try changing

Код:

mysql_tquery(mysql, query);

to :

Код:

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

[PrO.GameR]

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.

[Slawiii]

Quote:

Originally Posted by PrO.GameR 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.

oh sorry i m just trying

[NeXoR]

Quote:

Originally Posted by PrO.GameR 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.

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

[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.

[NeXoR]

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 ?

[Sithis]

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.