· 11 years ago · Sep 19, 2015, 01:32 PM
1-- phpMyAdmin SQL Dump
2-- version 4.4.12
3-- http://www.phpmyadmin.net
4--
5-- Host: 127.0.0.1
6-- Generation Time: Sep 19, 2015 at 03:18 PM
7-- Server version: 5.6.25
8-- PHP Version: 5.6.11
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8mb4 */;
18
19--
20-- Database: `kai`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `players`
27--
28
29CREATE TABLE IF NOT EXISTS `players` (
30 `player_id` int(11) NOT NULL,
31 `user_id` int(11) NOT NULL,
32 `player_jersey_number` int(11) DEFAULT NULL,
33 `player_jersey_size` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
34 `team_id` int(11) NOT NULL,
35 `player_position` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
36 `player_years_of_experience` int(11) NOT NULL,
37 `player_team_privilege` tinyint(1) NOT NULL DEFAULT '0',
38 `player_contribution` double NOT NULL,
39 `player_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
40) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
41
42--
43-- Dumping data for table `players`
44--
45
46INSERT INTO `players` (`player_id`, `user_id`, `player_jersey_number`, `player_jersey_size`, `team_id`, `player_position`, `player_years_of_experience`, `player_team_privilege`, `player_contribution`, `player_timestamp`) VALUES
47(4, 10, 5, '0', 53, '', 0, 0, 0, '2015-09-17 10:15:49'),
48(5, 10, 5, '0', 54, '', 0, 0, 0, '2015-09-17 10:17:14');
49
50-- --------------------------------------------------------
51
52--
53-- Table structure for table `seasons`
54--
55
56CREATE TABLE IF NOT EXISTS `seasons` (
57 `season_id` int(11) NOT NULL,
58 `season_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT 'Untitled Season',
59 `season_active` tinyint(1) NOT NULL DEFAULT '0',
60 `season_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
61) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
62
63--
64-- Dumping data for table `seasons`
65--
66
67INSERT INTO `seasons` (`season_id`, `season_name`, `season_active`, `season_timestamp`) VALUES
68(12, 'Untitled Season', 0, '2015-09-17 08:30:41'),
69(13, 'Untitled Season 1', 1, '2015-09-18 09:15:40');
70
71-- --------------------------------------------------------
72
73--
74-- Table structure for table `teams`
75--
76
77CREATE TABLE IF NOT EXISTS `teams` (
78 `team_id` int(11) NOT NULL,
79 `team_has_avatar` tinyint(1) NOT NULL,
80 `season_id` int(11) NOT NULL,
81 `team_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Untitled Team',
82 `team_wins_record` int(11) NOT NULL DEFAULT '0',
83 `team_loses_record` int(11) NOT NULL DEFAULT '0',
84 `team_number_of_members` int(11) NOT NULL,
85 `team_open` tinyint(1) NOT NULL DEFAULT '0',
86 `team_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
87) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
88
89--
90-- Dumping data for table `teams`
91--
92
93INSERT INTO `teams` (`team_id`, `team_has_avatar`, `season_id`, `team_name`, `team_wins_record`, `team_loses_record`, `team_number_of_members`, `team_open`, `team_timestamp`) VALUES
94(53, 1, 13, 'Untitled Team', 0, 0, 1, 1, '2015-09-17 08:33:49'),
95(54, 0, 12, 'Miami Heat', 0, 0, 1, 0, '2015-09-17 10:16:47');
96
97-- --------------------------------------------------------
98
99--
100-- Table structure for table `users`
101--
102
103CREATE TABLE IF NOT EXISTS `users` (
104 `user_id` int(11) NOT NULL,
105 `user_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
106 `user_password_hash` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
107 `user_email` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
108 `user_first_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
109 `user_last_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
110 `user_middle_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
111 `user_nickname` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
112 `user_gender` tinyint(1) NOT NULL,
113 `user_age` int(2) NOT NULL,
114 `user_birthday` date NOT NULL,
115 `user_address` text COLLATE utf8_unicode_ci NOT NULL,
116 `user_medical_condition` text COLLATE utf8_unicode_ci,
117 `user_mobile` text COLLATE utf8_unicode_ci NOT NULL,
118 `user_telephone` text COLLATE utf8_unicode_ci,
119 `user_emergency_mobile` text COLLATE utf8_unicode_ci NOT NULL,
120 `user_emergency_telephone` text COLLATE utf8_unicode_ci,
121 `user_emergency_contact_person` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
122 `user_active` tinyint(1) NOT NULL DEFAULT '0',
123 `user_account_type` tinyint(1) NOT NULL DEFAULT '1',
124 `user_has_avatar` tinyint(1) NOT NULL DEFAULT '0',
125 `user_rememberme_token` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
126 `user_creation_timestamp` bigint(20) DEFAULT NULL,
127 `user_last_login_timestamp` bigint(20) DEFAULT NULL,
128 `user_failed_logins` tinyint(1) NOT NULL DEFAULT '0',
129 `user_last_failed_login` int(10) DEFAULT NULL,
130 `user_activation_hash` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
131 `user_password_reset_hash` char(40) COLLATE utf8_unicode_ci DEFAULT NULL,
132 `user_password_reset_timestamp` bigint(20) DEFAULT NULL
133) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
134
135--
136-- Dumping data for table `users`
137--
138
139INSERT INTO `users` (`user_id`, `user_name`, `user_password_hash`, `user_email`, `user_first_name`, `user_last_name`, `user_middle_name`, `user_nickname`, `user_gender`, `user_age`, `user_birthday`, `user_address`, `user_medical_condition`, `user_mobile`, `user_telephone`, `user_emergency_mobile`, `user_emergency_telephone`, `user_emergency_contact_person`, `user_active`, `user_account_type`, `user_has_avatar`, `user_rememberme_token`, `user_creation_timestamp`, `user_last_login_timestamp`, `user_failed_logins`, `user_last_failed_login`, `user_activation_hash`, `user_password_reset_hash`, `user_password_reset_timestamp`) VALUES
140(10, 'kasakasa', '$2y$10$epadL0xrGY9HYGH7PNIaXO5C870V.DjpxJ9XKlqbe7UmiVIex20QG', 'kasakasa@gmail.com', '', '', '', NULL, 0, 0, '0000-00-00', '', '', '', '', '', '', '', 1, 1, 0, '0ae21d372bd069687e51a049d409df50c4017f293196077eca59cc79483463fc', 1442326184, 1442665020, 0, NULL, NULL, NULL, NULL),
141(13, 'kiko', '$2y$10$hXK74EcLqJOr7T6Kbxc2/OvsmxBznbun5i5A9sRYnXc128bg5HCUq', 'kiko@gmail.com', 'Kiko', 'Deo', 'Z', 'Meh', 1, 23, '1992-05-02', 'Pasay City', '', '09207090854', '1231234', '092212312334', '932122', 'Joseph', 1, 1, 0, NULL, 1442668235, NULL, 0, NULL, NULL, NULL, NULL);
142
143--
144-- Indexes for dumped tables
145--
146
147--
148-- Indexes for table `players`
149--
150ALTER TABLE `players`
151 ADD PRIMARY KEY (`player_id`),
152 ADD KEY `fk_players_team_id` (`team_id`),
153 ADD KEY `fk_players_user_id` (`user_id`);
154
155--
156-- Indexes for table `seasons`
157--
158ALTER TABLE `seasons`
159 ADD PRIMARY KEY (`season_id`);
160
161--
162-- Indexes for table `teams`
163--
164ALTER TABLE `teams`
165 ADD PRIMARY KEY (`team_id`),
166 ADD KEY `fk_teams_season_id` (`season_id`);
167
168--
169-- Indexes for table `users`
170--
171ALTER TABLE `users`
172 ADD PRIMARY KEY (`user_id`),
173 ADD UNIQUE KEY `user_name` (`user_name`),
174 ADD UNIQUE KEY `user_email` (`user_email`);
175
176--
177-- AUTO_INCREMENT for dumped tables
178--
179
180--
181-- AUTO_INCREMENT for table `players`
182--
183ALTER TABLE `players`
184 MODIFY `player_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
185--
186-- AUTO_INCREMENT for table `seasons`
187--
188ALTER TABLE `seasons`
189 MODIFY `season_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
190--
191-- AUTO_INCREMENT for table `teams`
192--
193ALTER TABLE `teams`
194 MODIFY `team_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=55;
195--
196-- AUTO_INCREMENT for table `users`
197--
198ALTER TABLE `users`
199 MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
200--
201-- Constraints for dumped tables
202--
203
204--
205-- Constraints for table `players`
206--
207ALTER TABLE `players`
208 ADD CONSTRAINT `fk_players_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`team_id`),
209 ADD CONSTRAINT `fk_players_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
210
211--
212-- Constraints for table `teams`
213--
214ALTER TABLE `teams`
215 ADD CONSTRAINT `fk_teams_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`season_id`);
216
217/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
218/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
219/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
220
221SELECT users.*,
222 players.*,
223 teams.*,
224 seasons.*
225 FROM users
226INNER JOIN players
227 ON users.user_id = players.user_id
228INNER JOIN teams
229 ON players.team_id = teams.team_id
230INNER JOIN seasons
231 ON teams.season_id = seasons.season_id
232 WHERE (users.user_email = "kiko@gmail.com"
233 AND seasons.season_id = (SELECT seasons.season_id
234 FROM seasons
235 WHERE seasons.season_active = 1));