bepaalde waarden van 1 rij tonen
Ik wil graag van reserveringen die zijn gedaan van een evenement laten zien. Maar van die reserveringen mogen alleen de Aanhef, Naam en Bedrijfsnaam getoond worden.
Ik heb drie tabellen:
tabel1:events_sessions
----------------------------------------------------------------------
session_id catid title
----------------------------------------------------------------------
1 3 Feest1
4 3 Feest2
2 3 Feest3
3 5 Feest4
5 5 Feest5
tabel2: events_registrations
----------------------------------------------------------------------
registration_id session_id userid fullname
----------------------------------------------------------------------
2 3 62 Raimond
3 3 22 Kim
2 2 62 Raimond
3 2 22 Kim
tabel3:events_registrations_fields
----------------------------------------------------------------------
field_id session_id name
----------------------------------------------------------------------
1 1 Bedrijfsnaam
2 1 Naam
4 2 Bedrijfsnaam
7 0 Aanhef
6 0 Bedrijfsnaam
8 0 Functie
9 0 Afdeling / Kamernummer
10 0 Adres
11 0 Postcode
12 0 Plaats
13 0 Telefoonnummer
14 0 Mobielnummer
15 0 Faxnummer
16 0 Eventuele vragen en/of opmerkingen
17 3 Bedrijfsnaam
18 3 Aanhef
19 3 Functie
20 3 Afdeling / Kamernummer
21 3 Adres
22 3 Postcode
23 3 Plaats
24 3 Telefoonnummer
25 3 Mobielnummer
26 3 Faxnummer
27 3 Eventuele vragen en/of opmerkingen
28 4 Bedrijfsnaam
29 4 Niets
30 4 Functie
31 4 Afdeling / Kamernummer
32 4 Adres
tabel4: events_registration_fields_values[
----------------------------------------------------------------------
value_id registration_id field_id value
----------------------------------------------------------------------
19 2 24 1234-56789
18 2 23 Woonplaats AAA
17 2 22 1000 AA
16 2 21 Straat 18
15 2 20
14 2 19
13 2 18 Dhr.
12 2 17 Mijn Bedrijf
20 2 25 06-12345678
21 2 26
22 2 27 Geen
23 3 17 Mijn bedrijf3
24 3 18 Mw.
25 3 19
26 3 20
27 3 21 Straat 20
28 3 22 1000 AB
29 3 23 Woonplaats BBB
30 3 24 1234-56788
31 3 25
32 3 26
33 3 27
34 4 4 Mijn Bedrijf
Nu wil ik dus per evenement de aanhef naam en bedrijfsnaam tonen.
Naam is geeen probleem en óf de Aanhef óf de bedrijfsnaam is ook geen probleeem, maar ik krijg ze niet beide aanhef en bedrijfsnaam.
De onderstaande code gebruik ik nu (ook in de bovenstaande tabellen staan meer kolommen, maar die zijn niet van belang)
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<?php
} // viewSession()
function displayRegistrations(&$row, $option, $customValues, $customFields) {
global $database, $mosConfig_live_site, $mosConfig_lang, $eSess;
/*
* Original Code from Michael Spredemann (scubaguy), which was based on
* Buddy Hack by Ford on http://www.mambojoe.com
* Revised by pcarr to conditionally deal with guests and community builder
*/
// query for people already registered
//$query = "SELECT * FROM #__events_registrations ";
//$query .= "WHERE session_id='".$row->session_id."' AND cancel_date='0000-00-00 00:00:00' ";
//$query .= "ORDER BY fullname ASC";
//$database->setQuery($query);
//$registrants = $database->loadObjectList();
$query = "SELECT reg.*, val.*, fiel.* FROM #__events_registrations AS reg"
. "\n LEFT JOIN #__events_registration_fields_values AS val ON val.registration_id = reg.registration_id"
. "\n LEFT JOIN #__events_registration_fields AS fiel ON fiel.field_id = val.field_id"
. "\n WHERE reg.session_id='".$row->session_id."' AND reg.status='0'"
. "\n AND ( fiel.name='Bedrijfsnaam'"
. "\n OR fiel.name='%Aanhef')";
$query .= "ORDER BY val.value ASC";
$database->setQuery($query);
$registrants = $database->loadObjectList();
$test = "SELECT reg.*, val.*, fiel.* FROM #__events_registrations AS reg"
. "\n LEFT JOIN #__events_registration_fields_values AS val ON val.registration_id = reg.registration_id"
. "\n LEFT JOIN #__events_registration_fields AS fiel ON fiel.field_id = val.field_id"
. "\n WHERE reg.session_id='".$row->session_id."' AND reg.status='0'"
. "\n AND fiel.name='Aanhef'";
$database->setQuery($test);
$aanhef = $database->loadObjectList();
if (count($registrants) > 0) {
echo "<h3>" . _ESESS_REG_LIST . "</h3>";
?>
<table cellpadding="4" cellspacing="0" border="0" class="contentpane">
<thead>
<tr >
<th colspan="2">Naam</th>
<th>Bedrijf</th>
</tr>
</thead>
<tbody>
<?php
foreach ($registrants as $registrant) {
if ($eSess["cbIntegrated"] && $eSess["showAvatar"]) {
// Load the avatar picture. For guests, display the appropriate no_picture file...
if ($registrant->userid) {
$query = "SELECT avatar FROM #__comprofiler WHERE user_id='".$registrant->userid."' AND avatarapproved=1";
$database->setQuery( $query );
$avatarFileName = $database->loadObjectList();
if ($avatarFileName[0]->avatar) {
/*
* Some photos may be from the gallery, while others may be user submitted
*/
if (strcmp("gallery", substr($avatarFileName[0]->avatar, 0, 7))) {
$avatarImgPath = $mosConfig_live_site . "/images/comprofiler/tn" . $avatarFileName[0]->avatar;
} else {
$avatarImgPath = $mosConfig_live_site . "/images/comprofiler/" . $avatarFileName[0]->avatar;
}
} else {
$avatarImgPath = $mosConfig_live_site . "/components/com_comprofiler/images/" . $mosConfig_lang . "/tnnophoto.jpg";
}
} else {
$avatarImgPath = $mosConfig_live_site . "/components/com_comprofiler/images/" . $mosConfig_lang . "/tnnophoto.jpg";
}
echo "<img src=\"" . $avatarImgPath . "\" alt=\"\" align=\"left\" />";
}
?>
<tr>
<td width="40px" align="left">
<?php // Display User Details
echo $aanhef;
//echo $registrant->comments . "<br clear=\"left\" /><br />";
?>
</td>
<td width="200px" align="left">
<?php // Display User Details
echo $registrant->fullname;
?>
</td width="200px">
<td>
<?php // Display User Details
//echo $customValues[array_search($customField->field_id,$customValueFieldIDs)]->value;
echo $registrant->value;
?>
</td>
</tr>
<?php
} //foreach ($registrants as $registrant)
} // if (count($registrants) > 0)
?>
</tbody>
</table>
} // viewSession()
function displayRegistrations(&$row, $option, $customValues, $customFields) {
global $database, $mosConfig_live_site, $mosConfig_lang, $eSess;
/*
* Original Code from Michael Spredemann (scubaguy), which was based on
* Buddy Hack by Ford on http://www.mambojoe.com
* Revised by pcarr to conditionally deal with guests and community builder
*/
// query for people already registered
//$query = "SELECT * FROM #__events_registrations ";
//$query .= "WHERE session_id='".$row->session_id."' AND cancel_date='0000-00-00 00:00:00' ";
//$query .= "ORDER BY fullname ASC";
//$database->setQuery($query);
//$registrants = $database->loadObjectList();
$query = "SELECT reg.*, val.*, fiel.* FROM #__events_registrations AS reg"
. "\n LEFT JOIN #__events_registration_fields_values AS val ON val.registration_id = reg.registration_id"
. "\n LEFT JOIN #__events_registration_fields AS fiel ON fiel.field_id = val.field_id"
. "\n WHERE reg.session_id='".$row->session_id."' AND reg.status='0'"
. "\n AND ( fiel.name='Bedrijfsnaam'"
. "\n OR fiel.name='%Aanhef')";
$query .= "ORDER BY val.value ASC";
$database->setQuery($query);
$registrants = $database->loadObjectList();
$test = "SELECT reg.*, val.*, fiel.* FROM #__events_registrations AS reg"
. "\n LEFT JOIN #__events_registration_fields_values AS val ON val.registration_id = reg.registration_id"
. "\n LEFT JOIN #__events_registration_fields AS fiel ON fiel.field_id = val.field_id"
. "\n WHERE reg.session_id='".$row->session_id."' AND reg.status='0'"
. "\n AND fiel.name='Aanhef'";
$database->setQuery($test);
$aanhef = $database->loadObjectList();
if (count($registrants) > 0) {
echo "<h3>" . _ESESS_REG_LIST . "</h3>";
?>
<table cellpadding="4" cellspacing="0" border="0" class="contentpane">
<thead>
<tr >
<th colspan="2">Naam</th>
<th>Bedrijf</th>
</tr>
</thead>
<tbody>
<?php
foreach ($registrants as $registrant) {
if ($eSess["cbIntegrated"] && $eSess["showAvatar"]) {
// Load the avatar picture. For guests, display the appropriate no_picture file...
if ($registrant->userid) {
$query = "SELECT avatar FROM #__comprofiler WHERE user_id='".$registrant->userid."' AND avatarapproved=1";
$database->setQuery( $query );
$avatarFileName = $database->loadObjectList();
if ($avatarFileName[0]->avatar) {
/*
* Some photos may be from the gallery, while others may be user submitted
*/
if (strcmp("gallery", substr($avatarFileName[0]->avatar, 0, 7))) {
$avatarImgPath = $mosConfig_live_site . "/images/comprofiler/tn" . $avatarFileName[0]->avatar;
} else {
$avatarImgPath = $mosConfig_live_site . "/images/comprofiler/" . $avatarFileName[0]->avatar;
}
} else {
$avatarImgPath = $mosConfig_live_site . "/components/com_comprofiler/images/" . $mosConfig_lang . "/tnnophoto.jpg";
}
} else {
$avatarImgPath = $mosConfig_live_site . "/components/com_comprofiler/images/" . $mosConfig_lang . "/tnnophoto.jpg";
}
echo "<img src=\"" . $avatarImgPath . "\" alt=\"\" align=\"left\" />";
}
?>
<tr>
<td width="40px" align="left">
<?php // Display User Details
echo $aanhef;
//echo $registrant->comments . "<br clear=\"left\" /><br />";
?>
</td>
<td width="200px" align="left">
<?php // Display User Details
echo $registrant->fullname;
?>
</td width="200px">
<td>
<?php // Display User Details
//echo $customValues[array_search($customField->field_id,$customValueFieldIDs)]->value;
echo $registrant->value;
?>
</td>
</tr>
<?php
} //foreach ($registrants as $registrant)
} // if (count($registrants) > 0)
?>
</tbody>
</table>
