MySQL query komt nu 4 uit ipv 2
De gegevens uit rij2 kan ik niet vrijgeven ivm privacy :P
Volgens mij is dat hier ook niet interessant..
$week is 44
$year = 2010
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$query = mysql_query("SELECT * FROM ".$tblRooster." AS rooster JOIN ".$tblLeden." AS leden WHERE rooster.week = '".$week."' AND rooster.jaar = '".$year."' ORDER BY leden.rang DESC") or die("Fout: Er konden geen gegevens opgehaald worden.");
if(mysql_num_rows($query) > 0){
while($geg = mysql_fetch_assoc($query)){
// Uur en minten weergeven: date("H:i", strtotime($geg["dagStart"]));
echo "<tr onMouseOver=\"this.style.backgroundColor='#E8E8E8'\" onMouseOut=\"this.style.backgroundColor='#FFFFFF'\">";
// Werknemer
echo "<td class=\"naam\">".$geg["naam"]."</td>";
// Alle dagen echon
foreach($days as $day){
if(!empty($geg[$day."Start"]) && !empty($geg[$day."Eind"])){
echo "<td><a href=\"#\" title=\"ajax:fileinfo.php?day=".$day."&week=".$week."&year=".$year."&exclude=".$geg["werknemerID"]."\">".date("H:i", strtotime($geg[$day."Start"]))." - ".date("H:i", strtotime($geg[$day."Eind"]))."</a></td>";
} else {
echo "<td> </td>";
}
}
// Totaal aantal uren voorschotelen
echo "<td>35</td>";
echo "</tr>";
}
}
if(mysql_num_rows($query) > 0){
while($geg = mysql_fetch_assoc($query)){
// Uur en minten weergeven: date("H:i", strtotime($geg["dagStart"]));
echo "<tr onMouseOver=\"this.style.backgroundColor='#E8E8E8'\" onMouseOut=\"this.style.backgroundColor='#FFFFFF'\">";
// Werknemer
echo "<td class=\"naam\">".$geg["naam"]."</td>";
// Alle dagen echon
foreach($days as $day){
if(!empty($geg[$day."Start"]) && !empty($geg[$day."Eind"])){
echo "<td><a href=\"#\" title=\"ajax:fileinfo.php?day=".$day."&week=".$week."&year=".$year."&exclude=".$geg["werknemerID"]."\">".date("H:i", strtotime($geg[$day."Start"]))." - ".date("H:i", strtotime($geg[$day."Eind"]))."</a></td>";
} else {
echo "<td> </td>";
}
}
// Totaal aantal uren voorschotelen
echo "<td>35</td>";
echo "</tr>";
}
}
De tabelstructuur is:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--
-- Tabelstructuur voor tabel `leden`
--
CREATE TABLE `leden` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pass` varchar(40) NOT NULL,
`naam` varchar(100) NOT NULL,
`adres` varchar(200) NOT NULL,
`postcode` varchar(10) NOT NULL,
`woonplaats` varchar(100) NOT NULL,
`land` varchar(100) NOT NULL,
`tel` varchar(15) NOT NULL,
`mtel` varchar(15) NOT NULL,
`email` varchar(200) NOT NULL,
`rang` varchar(2) NOT NULL,
`opmerkingen` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
CREATE TABLE `rooster` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`werknemerID` int(100) NOT NULL,
`week` int(2) NOT NULL,
`jaar` year(4) NOT NULL,
`maandagStart` time DEFAULT NULL,
`maandagEind` time DEFAULT NULL,
`dinsdagStart` time DEFAULT NULL,
`dinsdagEind` time DEFAULT NULL,
`woensdagStart` time DEFAULT NULL,
`woensdagEind` time DEFAULT NULL,
`donderdagStart` time DEFAULT NULL,
`donderdagEind` time DEFAULT NULL,
`vrijdagStart` time DEFAULT NULL,
`vrijdagEind` time DEFAULT NULL,
`zaterdagStart` time DEFAULT NULL,
`zaterdagEind` time DEFAULT NULL,
`zondagStart` time DEFAULT NULL,
`zondagEind` time DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
-- Tabelstructuur voor tabel `leden`
--
CREATE TABLE `leden` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pass` varchar(40) NOT NULL,
`naam` varchar(100) NOT NULL,
`adres` varchar(200) NOT NULL,
`postcode` varchar(10) NOT NULL,
`woonplaats` varchar(100) NOT NULL,
`land` varchar(100) NOT NULL,
`tel` varchar(15) NOT NULL,
`mtel` varchar(15) NOT NULL,
`email` varchar(200) NOT NULL,
`rang` varchar(2) NOT NULL,
`opmerkingen` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
CREATE TABLE `rooster` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`werknemerID` int(100) NOT NULL,
`week` int(2) NOT NULL,
`jaar` year(4) NOT NULL,
`maandagStart` time DEFAULT NULL,
`maandagEind` time DEFAULT NULL,
`dinsdagStart` time DEFAULT NULL,
`dinsdagEind` time DEFAULT NULL,
`woensdagStart` time DEFAULT NULL,
`woensdagEind` time DEFAULT NULL,
`donderdagStart` time DEFAULT NULL,
`donderdagEind` time DEFAULT NULL,
`vrijdagStart` time DEFAULT NULL,
`vrijdagEind` time DEFAULT NULL,
`zaterdagStart` time DEFAULT NULL,
`zaterdagEind` time DEFAULT NULL,
`zondagStart` time DEFAULT NULL,
`zondagEind` time DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
En de rijen zijn:
Code (php)
1
2
3
2
3
INSERT INTO `rooster` (`id`, `werknemerID`, `week`, `jaar`, `maandagStart`, `maandagEind`, `dinsdagStart`, `dinsdagEind`, `woensdagStart`, `woensdagEind`, `donderdagStart`, `donderdagEind`, `vrijdagStart`, `vrijdagEind`, `zaterdagStart`, `zaterdagEind`, `zondagStart`, `zondagEind`) VALUES
(1, 1, 44, 2010, NULL, NULL, '18:30:00', '21:00:00', '18:00:00', '21:00:00', NULL, NULL, '16:00:00', '21:00:00', '17:50:00', '21:00:00', NULL, NULL),
(5, 2, 44, 2010, '18:00:00', '21:00:00', '17:50:00', '21:00:00', NULL, NULL, '15:00:00', '21:00:00', '18:00:00', '21:00:00', NULL, NULL, NULL, NULL);
(1, 1, 44, 2010, NULL, NULL, '18:30:00', '21:00:00', '18:00:00', '21:00:00', NULL, NULL, '16:00:00', '21:00:00', '17:50:00', '21:00:00', NULL, NULL),
(5, 2, 44, 2010, '18:00:00', '21:00:00', '17:50:00', '21:00:00', NULL, NULL, '15:00:00', '21:00:00', '18:00:00', '21:00:00', NULL, NULL, NULL, NULL);
Veldnamen moet je niet af laten hangen van een aantal.
kan ik niet met de huidige situatie sorteren op rang?
Je geeft in je query geeft verbinding aan tussen de twee tabellen.
Maar het gaat erom dat ik ze op rang sorteren..