[vassilis]

Hello guys i have 3 tables

players - songs - playlists

-To players table i have added a row named PlayerPlaylistID(UNSIGNED UNIQUE)
-To songs table i have 4 rows. SongID[, SongStream,SongName,PlayerPlaylistID(UNSIGNED UNIQUE)
-To playlists table i have 3 rows. PlaylistID(UNSIGNED AUTO_INCREMENT PRIMARY), PlayerPlaylistID(UNSIGNED UNIQUE), SongsInserted(I want to make up to 10 songs as a limit in each playlist)

I have created some commands and when i insert the song the stream works correctly. However when i print Song name it prints blank and mysql triggers error.

I have made connection between these 3 PlayerPlaylistIDS.

ERROR 1: Triggers Mysql error.

Код:

[01:15:35] [DEBUG] mysql_format - connection: 1, len: 350, format: "INSERT INTO `songs` (`SongStream`, `SongName`) VALUES('%s', '%s')"
[01:15:35] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `songs` (`SongStream`, `SongName`) VALUES('http://ww", callback: "(null)", format: "(null)"
[01:15:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[01:15:35] [ERROR] CMySQLQuery::Execute[] - (error #1452) Cannot add or update a child row: a foreign key constraint fails (`server`.`songs`, CONSTRAINT `songs_ibfk_1` FOREIGN KEY (`PlayerPlaylistID`) REFERENCES `playlists` (`PlayerPlaylistID`) ON DELETE CASCADE ON UPDATE CASCADE)
[01:15:35] [DEBUG] mysql_format - connection: 1, len: 720, format: "UPDATE `players` SET `Admin`=%d, `VIP`=%d, `Money`=%d, `Health` =%f, `Armour` =%f, `posX`=%f, `posY`=%f, `posZ`=%f,`Score` =%d, ..."
[01:15:35] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError

What could be the mistake?

In script code:

PHP код:

                case DIALOG_CUSTOMPLAYLISTSONGNAME:
                        {
                            if( response )
                            {
                                new query[350];
                                if(isnull(inputtext)) return ShowPlayerDialog(playerid, DIALOG_CUSTOMPLAYLISTSONGNAME, DIALOG_STYLE_INPUT, ""COL_RED"Invalid Song Name","Please insert a valid song name.","Select", "");
                                mysql_format(mysql, query, sizeof(query), "INSERT INTO `songs` (`SongStream`, `SongName`) VALUES('%s', '%s')", PlaylistInfo[playerid][SongStream], inputtext);
                                mysql_tquery(mysql, query);
                                ShowPlayerDialog(playerid, DIALOG_CUSTOMPLAYLISTSONGPLAY, DIALOG_STYLE_MSGBOX, ""COL_GREEN"You have added the song","Do you want to play song now?","Yes", "No");
                            }
                        } 


ERROR:2 Prints blank song name.

PHP код:

                        case DIALOG_CUSTOMPLAYLISTSONGPLAY:
                        {
                            if( response )
                            {
                                
                                new string[60];
                                format(string,sizeof(string), "CURRENTLY PLAYING: %s", PlaylistInfo[playerid][SongName]);
                                SendClientMessage(playerid, -1, string);
                                PlayAudioStreamForPlayer(playerid, PlaylistInfo[playerid][SongStream]);
                                
                            }
                        } 


My enum:

PHP код:

enum Playlist
{
    PlaylistID,
    SongName[30],
    SongStream[255],
    SongsInserted
}
new PlaylistInfo[MAX_PLAYERS][Playlist]; 


[Shinja]

Quote:

Originally Posted by Vince Creating the foreign key You should've been brought back to the structures tab. Navigate there if this isn't the case. Underneath the structure definition you should see a link titled Relation view. Click this to be brought to the "relation creator". I'm creating a link to the id field in the table playerinfo in the database vcnr. Your table and database will be called differently. Select the proper ID. Note that for fields to show up in this list, they need to be defined as a key! We also want any changes made in the main table to be CASCADED into this table. Click Save. This concludes the table creation part in phpMyAdmin. Now back to Pawn.

If you don't get it, try rename PlayerPlayListID in each table to a different name

[vassilis]

Quote:

Originally Posted by Shinja If you don't get it, try rename PlayerPlayListID in each table to a different name

I don't get what you are trying to say. I have already done connections between these 3 using also UPDATE CASCADE and DELETE CASCADE. Could you explain?

[X337]

PlayerPlaylistID column in songs table can't be NULL, it has relation to PlayerPlaylistID column in players table, which every PlayerPlaylistID in songs should exists in players table.
Try this query

Код:

mysql_format(mysql, query, sizeof(query), "INSERT INTO `songs` (`SongStream`, `SongName`, `PlayerPlaylistID`) VALUES('%e', '%e', '%d')", PlaylistInfo[playerid][SongStream], inputtext, PlaylistInfo[playerid][PlaylistID]);

[PrO.GameR]

Simply because there is no `PlayerPlaylistID` in playlists for song to refer to, you simply need to pass that value in your query too.
You are doing the whole foreign keys wrong tho, your schematics should look like this:

Playlists > AI ID - Name or w/e (thats all)
Songs > AI ID - PlaylistID - ( SongStream > have no idea what this is) - SongName
Players > PlayerPlaylistID
Both songs.PlaylistID and PlayerPlaylistID should refer to playlist AI ID. You can simply check how many songs are inserted in a playlist with a query

Quote:

SELECT COUNT(*) FROM `songs` WHERE PlaylistID=%i

Which you can combine with your other queries.