SA-MP Forums Archive
Playlist System[MySQL] - 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: Playlist System[MySQL] (/showthread.php?tid=617547)


Playlist System[MySQL] - vassilis - 22.09.2016

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]; 


Re: Playlist System[MySQL] - Shinja - 22.09.2016

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

Re: Playlist System[MySQL] - vassilis - 22.09.2016

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?

Re: Playlist System[MySQL] - X337 - 23.09.2016

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]);


Re: Playlist System[MySQL] - PrO.GameR - 23.09.2016

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.