I was awake last night thinking about this and have been playing with the code all afternoon!
The database is working well and I am getting what I want but I have a problem with the location on side A and Side B. Whatever I do I only end up with one location as the second overwrites the first. I have tried creating a LocationA and a LocationB but I can only get one $row[‘place’]. Running the code through the SQL on phpmyadmin both locations are being picked up and when displaying the $row array with print_r() they are both there. I can get both locations if I do something like $row[‘27’] and $row[‘29’] but if I add something else to the database later I have to remember to change these two numbers.
This is the output of print_r():
Array
(
[album_id] => 1
[0] => 1
[file_number] => 1111
[1] => 1111
[title] => 1
[2] => 1
[size] => 10
[3] => 10
[source] => 1
[4] => 1
[company_ID] => 3
[5] => 3
[location_A] => 1
[6] => 1
[location_B] => 2
[7] => 2
[matrix_A] => IRX456
[8] => IRX456
[matrix_B] => IRX789
[9] => IRX789
[speed_A] => 76
[10] => 76
[speed_B] => 78
[11] => 78
[date_A] => 1960-12-04
[12] => 1960-12-04
[date_B] => 1959-01-01
[13] => 1959-01-01
[catalogue] => 123_r5
[14] => 123_r5
[date_aq] => 1975-05-05
[15] => 1975-05-05
[purchase] => 5
[16] => 5
[est_value] => 10
[17] => 10
[sold] => 0
[18] => 0
[sou_ID] => 1
[19] => 1
[origin] => C.P. Collection
[20] => C.P. Collection
[comp_ID] => 3
[21] => 3
[name] => Hermes records
[22] => Hermes records
[album_ID] => 1
[23] => 1
[locationA_ID] => 1
[24] => 1
[locationB_ID] => 2
[25] => 2
[loc_ID] => 2
[26] => 1
[place] => Abbey road
[27] => New York
[28] => 2
[29] => Abbey road
)
This is my query:
SELECT * FROM album
JOIN
source
ON
source.sou_ID=album.source
JOIN
company
ON
company.comp_ID=album.company_ID
JOIN
album_location
ON
album.album_ID=album_location.album_ID
JOIN
location
ON
location.loc_ID=album_location.locationA_ID
JOIN
location AS locB
ON
locB.loc_ID=album_location.locationB_ID
WHERE album.file_number = '1111'
These are my tables:
CREATE TABLE IF NOT EXISTS `album` (
`album_id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
`file_number` int(5) NOT NULL,
`title` int(4) NOT NULL COMMENT 'Track name side A',
`size` enum('8','10','Other') NOT NULL COMMENT 'Record size',
`source` int(4) NOT NULL COMMENT 'Source of the record',
`company_ID` int(4) NOT NULL,
`location_A` int(4) NOT NULL,
`location_B` int(4) NOT NULL,
`matrix_A` varchar(10) NOT NULL,
`matrix_B` varchar(10) NOT NULL,
`speed_A` int(6) NOT NULL COMMENT 'Speed side A',
`speed_B` int(6) NOT NULL COMMENT 'Side B speed',
`date_A` date NOT NULL COMMENT 'Date side A recorderd',
`date_B` date NOT NULL COMMENT 'Date side B recorderd',
`catalogue` varchar(12) NOT NULL COMMENT 'Catalogue number',
`date_aq` date NOT NULL COMMENT 'Purchase date',
`purchase` int(6) NOT NULL COMMENT 'Purchase price',
`est_value` int(6) NOT NULL COMMENT 'Current estimated value',
`sold` int(6) NOT NULL COMMENT 'Sold price',
PRIMARY KEY (`album_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TABLE IF NOT EXISTS `album_location` (
`album_ID` int(11) NOT NULL,
`locationA_ID` int(11) NOT NULL,
`locationB_ID` int(11) NOT NULL,
PRIMARY KEY (`album_ID`,`locationA_ID`,`locationB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `location` (
`loc_ID` smallint(4) NOT NULL AUTO_INCREMENT,
`place` varchar(50) NOT NULL DEFAULT 'Place',
PRIMARY KEY (`loc_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=3 ;
