Hello.
I’m a proficient mysql user, and i believe that the problem i’m facing could be easily resolved with a more deeper knowledge of mysql.
any help is welcome!
Problem:
I have 2 columns named ‘artist’ and ‘release’ (schema below)
Release table have a field artist_id that references artist table, id field.
When admin try to add a new Release (musical release) created lets say by a single artist everything works fine.
Problem arised when the admin try to add a musical release created by two different artist, lets say
U2 and Marilyn Manson - Bloddy Love Album
at first glance i added
multiple => true
to the dropdown and i can recieve the data as array and thought. Ok need to convert this array into a single integer and set it into artist_id …
Release Controller
public function actionCreate()
{
$model=new Release;
// Uncomment the following line if AJAX validation is needed
// $this->performAjaxValidation($model);
if(isset($_POST['Release']))
{
// since mysql field integer PRIMARY KEY, AUTO_INCREMENT
// first record starts at 1, is safe to use 0 to implode
if (count($_POST['Release']['artist_id']) > 1) {
$_POST['Release']['artist_id'] = (int) implode(0, $_POST['Release']['artist_id']);
} elseif (count($_POST['Release']['artist_id']) === 1) {
$_POST['Release']['artist_id'] = (int) $_POST['Release']['artist_id'];
}
$model->attributes=$_POST['Release'];
// code goes one..
then i’ll get artist_id = 1023 ;
and get a DATABASE CONSTRAIN VIOLATION because i don’t have any artist in the artisttable with id = 1023;
so i’m stuck… i know i could remove the relation, create a pivot table between release and artist.
but that would make me do alot of requests to mysql just to server a single release/view/$id
isn’t there a more elegant way to accomplish this need with minimum mysql requests possible?
below are Artist model and Release model code.
Artist.php
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'releasesRelation' => array(self::HAS_MANY, 'Release', 'release_id'),
);
}
Release.php
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'formatRelation' => array(self::BELONGS_TO, 'ReleaseFormat', 'format_id'),
'typeRelation' => array(self::BELONGS_TO, 'ReleaseType', 'type_id'),
'artistRelation' => array(self::BELONGS_TO, 'Artist', 'artist_id'),
);
}
schema
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Table structure for table `artist`
--
CREATE TABLE IF NOT EXISTS `artist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL COMMENT 'project name.',
`description` text COMMENT 'artist description WYSIWYG editor.',
`image` varchar(128) DEFAULT NULL COMMENT 'image link.',
`site` varchar(128) DEFAULT NULL COMMENT 'official site link.',
`facebook` varchar(128) DEFAULT NULL COMMENT 'facebook page',
`twitter` varchar(128) DEFAULT NULL COMMENT 'twitter page.',
`soundcloud` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='artists table' AUTO_INCREMENT=4 ;
--
-- Table structure for table `release`
--
CREATE TABLE IF NOT EXISTS `release` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`region` varchar(128) DEFAULT NULL COMMENT 'worldwide release?',
`format_id` int(11) DEFAULT NULL COMMENT 'release format, digital, cd, vinyl etc.',
`type_id` int(11) DEFAULT NULL COMMENT 'Type of release, singlel, ep, album or VA.',
`date` date DEFAULT NULL COMMENT 'Release Date field',
`label` varchar(128) DEFAULT 'Cut Loose Music',
`artist_id` int(11) DEFAULT NULL COMMENT 'Artist id',
`name` varchar(128) DEFAULT NULL COMMENT 'Release Name.',
`cover` varchar(128) DEFAULT NULL COMMENT 'cover link, bigger size',
`code` varchar(32) DEFAULT NULL COMMENT 'release code ex. CLM0001',
`description` text COMMENT 'release description. to be used with a WYSIWYG editor.',
`tracklist` text COMMENT 'tracklist, to be used with WYSIWYG editor.',
`sample` varchar(128) DEFAULT NULL COMMENT 'link to sample mp3 file.',
PRIMARY KEY (`id`),
KEY `fk_release_release_format` (`format_id`),
KEY `fk_release_release_type` (`type_id`),
KEY `FK_release_artist` (`artist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='releases table.' AUTO_INCREMENT=7 ;
--
-- Table structure for table `release_format`
--
CREATE TABLE IF NOT EXISTS `release_format` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`format` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Table structure for table `release_type`
--
CREATE TABLE IF NOT EXISTS `release_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `release`
--
ALTER TABLE `release`
ADD CONSTRAINT `FK_release_artist` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_release_release_format` FOREIGN KEY (`format_id`) REFERENCES `release_format` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_release_release_type` FOREIGN KEY (`type_id`) REFERENCES `release_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
thanks for your help…