|< 1 2 3 >| | 21 Einträge, 3 Seiten |
Quotein einem Suchdialog soll nach Feldern aus beiden Tabellen gesucht werden können.
Suchdialog:
| Dropdownlist v|-| Eingabefeld |-| Suchen-Button |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Tabelle "ansprechpartner"
id
firma_id
name
Tabelle "firma"
id
name
Abfrage:
SELECT DISTINCT b.name
FROM firma b INNER JOIN ansprechpartner a ON b.id = a.firma_id
WHERE a.name = "Name des gesuchten Ansprechpartners"
ORDER BY b.name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/usr/bin/perl
use warnings;
use strict;
my $test = { 112 => [19, 24, 57],
113 => [16, 7]
};
push @{$test->{114}}, 8, 31, 32;
for (keys(%$test)) {
print $_,"\n";
print "--", $_,"\n" for (@{$test->{$_}});
}
exit();
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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use CGI;
use HTML::Template;
use CGI::Carp qw(fatalsToBrowser);
my $q = new CGI;
my ($dbh, $sth);
my @firma_loop;
my @contact_loop;
my $daten;
my $aktion = $q->param('aktion') || '';
$dbh = DBI->connect ("DBI:mysql:host=192.168.42.6;database=cdmcrm",
"www-data", "www-data", {PrintError => 0, RaiseError => 1});
&fetch_IDs();
#&fetch_ent() if ($aktion eq 'Suchen');
&fetch_ent();
&html_fill();
$dbh->disconnect();
exit(0);
#
# Ab hier Subroutinen
#
sub fetch_IDs {
my $sth = $dbh->prepare (" SELECT firma.eid, pid
FROM firma
JOIN person
ON person.eid = firma.eid
WHERE Firma LIKE 'N%'
");
$sth->execute();
while (my ($eid, $pid) = $sth->fetchrow_array()) {
push @{$daten->{$eid}}, $pid;
}
$sth->finish();
# DEBUG
# for (keys(%$daten)) {
# print $_,"\n";
# print "--", $_,"\n" for (@{$daten->{$_}});
# }
}
sub html_fill {
# Parameter-loop fuer Suchformular fuellen
my @loop = (
{ value=>'Firma', selected=>'', label=>'Firma' },
{ value=>'Konzern', selected=>'', label=>'Konzern' },
{ value=>'PLZ', selected=>'', label=>'PLZ' },
{ value=>'Ort', selected=>'', label=>'Ort' }
);
# Vorlagen definieren und ausfuellen
my $template = HTML::Template->new(filename => 'selectlist2.tmpl');
$template->param( title => 'Suchformular',
url => $q->url(),
field1_loop => \@loop,
field2_loop => \@loop,
firma_loop => \@firma_loop );
print $template->output;
}
sub fetch_ent {
for (keys(%$daten)) {
my $eid = $_;
my $sth = $dbh->prepare (" SELECT Firma, Strasse, PLZ, Ort
FROM firma
WHERE eid = $eid
");
$sth->execute();
while (my ($firma, $strasse, $plz, $ort) = $sth->fetchrow_array()) {
my @inner_loop = &fetch_contact($eid);
my %row = (
firma => $firma,
strasse => $strasse,
plz => $plz,
ort => $ort,
contact_loop => \@inner_loop
);
push(@firma_loop, \%row);
}
$sth->finish();
}
}
sub fetch_contact {
my $eid = shift @_;
my @contact_loop;
for (@{$daten->{$eid}}) {
my $pid = $_;
my $sth = $dbh->prepare (" SELECT Name, Vorname, Telefon, Fax, Email
FROM person
WHERE pid = '$pid'
");
$sth->execute();
while (my ($nachname, $vorname, $telefon, $fax, $email) = $sth->fetchrow_array()) {
my %row = (
nachname => $nachname,
vorname => $vorname,
telefon => $telefon,
fax => $fax,
email => $email
);
push(@contact_loop, \%row);
}
$sth->finish();
}
return @contact_loop;
}
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
<TMPL_INCLUDE NAME="header.tmpl">
<form action="<TMPL_VAR NAME="url">" method="POST" target="">
<TMPL_INCLUDE NAME="search.tmpl">
</form>
<table>
<TMPL_LOOP name="firma_loop">
<tr>
<td>
<table>
<tr>
<td><TMPL_VAR name="firma"></td>
<td><TMPL_VAR name="strasse"></td>
<td><TMPL_VAR name="plz"></td>
<td><TMPL_VAR name="ort"></td>
</tr>
</table>
</td>
</tr>
<TMPL_LOOP name="contact_loop">
<tr>
<td>
<table>
<tr>
<td><TMPL_VAR name="nachname"></td>
<td><TMPL_VAR name="vorname"></td>
<td><TMPL_VAR name="telefon"></td>
<td><TMPL_VAR name="fax"></td>
<td><TMPL_VAR name="email"></td>
</tr>
</table>
</td>
</tr>
</TMPL_LOOP>
</TMPL_LOOP>
</table>
<TMPL_INCLUDE NAME="footer.tmpl">
|< 1 2 3 >| | 21 Einträge, 3 Seiten |