Laten zien als...
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
<?php
// variabelen initialiseren:
// $username ="uw_gerbuikersnaam";
// $password = "uw_wachtwoord";
$host="localhost";
$dbnaam="barroc-it";
$db=mysql_connect($host) or die (mysql_error());
mysql_select_db($dbnaam, $db) or die (mysql_error());
?>
// variabelen initialiseren:
// $username ="uw_gerbuikersnaam";
// $password = "uw_wachtwoord";
$host="localhost";
$dbnaam="barroc-it";
$db=mysql_connect($host) or die (mysql_error());
mysql_select_db($dbnaam, $db) or die (mysql_error());
?>
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
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
<?php
$result = mysql_query("SELECT customer_id, Company_name, Address1, Contact_person, Telephone_number1 FROM customers");
while ($row = mysql_fetch_array($result)){
$ID = $row["customer_id"];
$company = $row["Company_name"];
$Address1 = $row["Address1"];
$Contact_person = $row["Contact_person"];
$Telephone_number1 = $row["Telephone_number1"];
echo("
<tr>
<td width=\"10\">
<td valign=\"top\" class=\"tekst\"> $company </td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Address1</font></td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Contact_person</font></td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Telephone_number1</font></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"klant_zien.php?ID=$ID\" class=\"webmail\"><img src=\"../images/adres.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"project_zien.php?ID=$ID\" class=\"webmail\"><img src=\"../images/overzicht.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"new_project.php?ID=$ID\" class=\"webmail\"><img src=\"../images/offer.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"klant_aan.php?ID=$ID\"
class=\"webmail\"><img src=\"../images/aanpassen.jpg\" border=\"0\"></a></td>
<td width=\"10\"></td>
</tr>
");
}
?>
$result = mysql_query("SELECT customer_id, Company_name, Address1, Contact_person, Telephone_number1 FROM customers");
while ($row = mysql_fetch_array($result)){
$ID = $row["customer_id"];
$company = $row["Company_name"];
$Address1 = $row["Address1"];
$Contact_person = $row["Contact_person"];
$Telephone_number1 = $row["Telephone_number1"];
echo("
<tr>
<td width=\"10\">
<td valign=\"top\" class=\"tekst\"> $company </td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Address1</font></td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Contact_person</font></td>
<td valign=\"top\" class=\"leftborder_td\"><font class=\"tekst\">$Telephone_number1</font></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"klant_zien.php?ID=$ID\" class=\"webmail\"><img src=\"../images/adres.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"project_zien.php?ID=$ID\" class=\"webmail\"><img src=\"../images/overzicht.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"new_project.php?ID=$ID\" class=\"webmail\"><img src=\"../images/offer.jpg\" border=\"0\"></a></td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\"><a href=\"klant_aan.php?ID=$ID\"
class=\"webmail\"><img src=\"../images/aanpassen.jpg\" border=\"0\"></a></td>
<td width=\"10\"></td>
</tr>
");
}
?>
Het gaat erom dat de status uit de tabel 'offers' staat op 'offer recieved', alleen dan wil ik dat de klant in dit overzicht staat. De rest van de gegevens worden gehaald uit de tabel 'customers'. Weet iemand wat ik moet doen zodat dit gaat werken?
Alvasy bedankt
Gewijzigd op 01/01/1970 01:00:00 door Guido Piket
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
c.customer_id,
c.Company_name,
c.Adress1,
etc....
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
o.customer_id is de foreign key uit de offers tabel op de customers tabel.
c.customer_id,
c.Company_name,
c.Adress1,
etc....
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
o.customer_id is de foreign key uit de offers tabel op de customers tabel.
Want wat moet ik nu veranderen? En in wat?
Voortaan trouwens geen/zo min mogelijk hoofdletters in variabelen gebruiken, is alleen maar lastig.
Ik ben er even mee bezig.
Guido Piket schreef op 06.06.2007 18:34:
Dit is de query zoals hij er ongeveer uit komt te zien. Je zult hem zelf nog wel even moeten aanpassen naar je eigen situatie, dus de juiste tabelnamen en kolomnamen invoeren...Ik ben pas net begonnen met php, heb dus niet echt veel verstand ervan.
Want wat moet ik nu veranderen? En in wat?
Want wat moet ik nu veranderen? En in wat?
Ik heb m al in je code gezet en je code even op orde gebracht:
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
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
<?php
//Pas hieronder je variabelen aan:
// Vervang gebruiker door je database username
$user = "gebruiker";
// Vervang wachtwoord door je database wachtwoord
$pass = "wachtwoord";
// Laat localhost staan
$host = "localhost";
// Laat barroc-it staan indien het de database is die je wilt gebruiken
$database = "barroc-it";
//Connect met database
mysql_connect($host, $user, $pass) or die (mysql_error());
mysql_select_db($database) or die (mysql_error());
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
");
while ($row = mysql_fetch_array($result)){
$id = $row["customer_id"];
$company = $row["company_name"];
$address1 = $row["address1"];
$contact_person = $row["contact_person"];
$telephone_number1 = $row["telephone_number1"];
echo("
<table>
<tr>
<td width=\"10\">
</td>
<td valign=\"top\" class=\"tekst\">
" . $company . "
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $address1 . "</font>
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $contact_person . "</font>
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $telephone_number1 . "</font>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"klant_zien.php?id=" . $id . "\" class=\"webmail\"><img src=\"images/adres.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"project_zien.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/overzicht.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"new_project.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/offer.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"klant_aan.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/aanpassen.jpg\" border=\"0\"></a>
</td>
<td width=\"10\">
</td>
</tr>
</table>
");
}
?>
//Pas hieronder je variabelen aan:
// Vervang gebruiker door je database username
$user = "gebruiker";
// Vervang wachtwoord door je database wachtwoord
$pass = "wachtwoord";
// Laat localhost staan
$host = "localhost";
// Laat barroc-it staan indien het de database is die je wilt gebruiken
$database = "barroc-it";
//Connect met database
mysql_connect($host, $user, $pass) or die (mysql_error());
mysql_select_db($database) or die (mysql_error());
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
");
while ($row = mysql_fetch_array($result)){
$id = $row["customer_id"];
$company = $row["company_name"];
$address1 = $row["address1"];
$contact_person = $row["contact_person"];
$telephone_number1 = $row["telephone_number1"];
echo("
<table>
<tr>
<td width=\"10\">
</td>
<td valign=\"top\" class=\"tekst\">
" . $company . "
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $address1 . "</font>
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $contact_person . "</font>
</td>
<td valign=\"top\" class=\"leftborder_td\">
<font class=\"tekst\">" . $telephone_number1 . "</font>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"klant_zien.php?id=" . $id . "\" class=\"webmail\"><img src=\"images/adres.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"project_zien.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/overzicht.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"new_project.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/offer.jpg\" border=\"0\"></a>
</td>
<td valign=\"top\" align=\"center\" class=\"leftborder_td\">
<a href=\"klant_aan.php?id=" . $id . "\"class=\"webmail\"><img src=\"images/aanpassen.jpg\" border=\"0\"></a>
</td>
<td width=\"10\">
</td>
</tr>
</table>
");
}
?>
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
");
?>
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
");
?>
Verranderen in
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
GROUP BY
c.customer_id
");
?>
$result = mysql_query("
SELECT
c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
GROUP BY
c.customer_id
");
?>
In zo'n geval als dit zul je DISTINCT moeten gebruiken:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
DISCTINCT c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
DISCTINCT c.customer_id,
c.company_name,
c.adress1,
c.contact_person,
c.telephone_number
FROM
customers AS c,
offers AS o
WHERE
o.customer_id = c.customer_id
AND
o.status = 'offer received'
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit