connectie met database en server voor een subquery
Quote:
Hallo,
Heb een sql select query met een subquery, mijn vraag hoe kan ik de connectie maken met db1 en server1 in de subquery weet iemand dat? graag uw hulp!
Heb een sql select query met een subquery, mijn vraag hoe kan ik de connectie maken met db1 en server1 in de subquery weet iemand dat? graag uw hulp!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS voornaam, db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email
FROM db2.`tablename` e
LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133'
WHERE db2.ce.email NOT IN(SELECT db1.email_addresses.email_address
FROM db1.tablename)
AND db2.a.value IN (SELECT db1.accounts_cstm.custid_c FROM db1.accounts_cstm )
GROUP BY db2.ce.email";
$result = mysql_query($sql,????) or die(mysql_error());
?>
$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS voornaam, db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email
FROM db2.`tablename` e
LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133'
WHERE db2.ce.email NOT IN(SELECT db1.email_addresses.email_address
FROM db1.tablename)
AND db2.a.value IN (SELECT db1.accounts_cstm.custid_c FROM db1.accounts_cstm )
GROUP BY db2.ce.email";
$result = mysql_query($sql,????) or die(mysql_error());
?>
Gewijzigd op 20/03/2014 14:23:13 door Murtada Helo
Gewijzigd op 20/03/2014 17:01:41 door Ger van Steenderen
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$guery="SELECT email_addresses.email_address FROM db1.tablename";
$result = mysql_query($guery,$link1) or die(mysql_error());
$select=array();
$row=mysql_fetch_array($result);
$select[]=$row["email_address"];
$select= implode(", ", $select);
$sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS voornaam, db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email
FROM db2.`tablename` e
LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133'
WHERE db2.ce.email NOT IN($select)
GROUP BY db2.ce.email";
$result = mysql_query($sql,$link2) or die(mysql_error());
?>
$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$guery="SELECT email_addresses.email_address FROM db1.tablename";
$result = mysql_query($guery,$link1) or die(mysql_error());
$select=array();
$row=mysql_fetch_array($result);
$select[]=$row["email_address"];
$select= implode(", ", $select);
$sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS voornaam, db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email
FROM db2.`tablename` e
LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133'
WHERE db2.ce.email NOT IN($select)
GROUP BY db2.ce.email";
$result = mysql_query($sql,$link2) or die(mysql_error());
?>
Weet iemand nog de oplossing hiervoor?
Gewijzigd op 20/03/2014 19:16:03 door Murtada Helo
Dan nog even een tip, je geeft een alias op een tabel:
Code (php)
1
... FROM db2.tablename e ....
Dan kan je volstaan met e.entity_id en hoef je niet nog eens de FQN te gebruiken
Wat je doet is niet fout maar overbodig.
Gewijzigd op 20/03/2014 19:37:16 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
$link1 =mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$query="SELECT email_addresses.email_address FROM db1.tablename";
$result = mysql_query($query,$link1) or die(mysql_error());
$select=array();
$row=mysql_fetch_array($result);
$select[]=$row["email_address"];
$select = "('" . implode ("','", $select) . "')";
mysql_query("set sql_big_selects=1",$link2);
$sql="SELECT `e`.`entity_id` , a.value AS klantnr, b.value AS voornaam, c.value AS achternaam, `t`.`value` AS telfnr, ce.email
FROM `tablename` e
LEFT OUTER JOIN customer_entity_varchar a ON a.entity_id = e.parent_id AND a.attribute_id = '133'
WHERE ce.email NOT IN($select)
GROUP BY ce.email";
$result = mysql_query($sql,$link2) or die(mysql_error());
?>
$link1 =mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$query="SELECT email_addresses.email_address FROM db1.tablename";
$result = mysql_query($query,$link1) or die(mysql_error());
$select=array();
$row=mysql_fetch_array($result);
$select[]=$row["email_address"];
$select = "('" . implode ("','", $select) . "')";
mysql_query("set sql_big_selects=1",$link2);
$sql="SELECT `e`.`entity_id` , a.value AS klantnr, b.value AS voornaam, c.value AS achternaam, `t`.`value` AS telfnr, ce.email
FROM `tablename` e
LEFT OUTER JOIN customer_entity_varchar a ON a.entity_id = e.parent_id AND a.attribute_id = '133'
WHERE ce.email NOT IN($select)
GROUP BY ce.email";
$result = mysql_query($sql,$link2) or die(mysql_error());
?>
Gewijzigd op 21/03/2014 11:33:14 door Murtada Helo
Spring in met tab of spaties, gebruik meer enters.
Verwijder die lelijke backticks (`) en wees ervan bewust dat de mysql_* functies die je nu gebruikt 'deprecated' zijn wat zoals betekend dat ze op de lijst staan om te verdwijnen. Er wordt aangeraden gebruik te maken van de mysqli_ functies of PDO.
En bouw foutafhandeling in en gebruik geen die().
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
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
<?php
$link1 = mysqli_connect('host1','username','password','db1');
$link2 = mysqli_connect('host2','username','password','db2');
$query = "
SELECT
email_addresses.email_address
FROM
db1.tablename
";
$result = mysqli_query($link1, $query);
if($result) {
$select = array();
while($row = mysqli_fetch_assoc($result)) {
$select[] = $row["email_address"];
}
$select = "('" . implode ("','", $select) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
//mysqli_query($moonen_link, "set sql_big_selects=1"); //???
$sql = "
SELECT
e.entity_id
,a.value AS klantnr
,b.value AS voornaam
,c.value AS achternaam
,t.value AS telfnr
,ce.email
FROM
tablename e
LEFT OUTER JOIN
customer_entity_varchar a ON a.entity_id = e.parent_id
AND a.attribute_id = 133
WHERE
ce.email NOT IN($select)
GROUP BY
ce.email
";
$result = mysqli_query($link2, $query);
if($result) {
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link2);
}
?>
$link1 = mysqli_connect('host1','username','password','db1');
$link2 = mysqli_connect('host2','username','password','db2');
$query = "
SELECT
email_addresses.email_address
FROM
db1.tablename
";
$result = mysqli_query($link1, $query);
if($result) {
$select = array();
while($row = mysqli_fetch_assoc($result)) {
$select[] = $row["email_address"];
}
$select = "('" . implode ("','", $select) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
//mysqli_query($moonen_link, "set sql_big_selects=1"); //???
$sql = "
SELECT
e.entity_id
,a.value AS klantnr
,b.value AS voornaam
,c.value AS achternaam
,t.value AS telfnr
,ce.email
FROM
tablename e
LEFT OUTER JOIN
customer_entity_varchar a ON a.entity_id = e.parent_id
AND a.attribute_id = 133
WHERE
ce.email NOT IN($select)
GROUP BY
ce.email
";
$result = mysqli_query($link2, $query);
if($result) {
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link2);
}
?>
Gewijzigd op 21/03/2014 10:03:05 door Michael -
Ook zit er nou foutafhandeling in dus als je je code uitvoert zul je zien wat er mis gaat.
Sowieso zie ik al niet waar b,c,t,ce vandaan komen,en die value kan ook niet kloppen. Dus dit zal al mis gaan en email_addresses.email_address klopt ook niet, maar omdat je hier geen join gebruikt kun je ook gewoon email_address gebruiken zonder tabel ervoor.
Deze fouten zou je in ieder geval al kunnen oplossen.
Gewijzigd op 21/03/2014 10:22:02 door Michael -
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
<?php
$select = "('" . implode ("','", $select) . "')";
$sql = "
...
WHERE
ce.email NOT IN($select)
...
";
?>
$select = "('" . implode ("','", $select) . "')";
$sql = "
...
WHERE
ce.email NOT IN($select)
...
";
?>
Dit levert dus op:
Code (php)
1
2
2
WHERE
ce.email NOT IN(('een waarde', 'een andere waarde'))
ce.email NOT IN(('een waarde', 'een andere waarde'))
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
107
108
109
110
111
112
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
<?php
$link1 = mysqli_connect('server1','user1','ww1','db1');
$link2 = mysqli_connect('server2','user2','ww2','db2');
$query1 = "
SELECT
custid_c
FROM
accounts_cstm
LEFT JOIN
accounts ON accounts.id = accounts_cstm.id_c
WHERE
accounts_cstm.custid_c IS NOT NULL
AND accounts.id = accounts_cstm.id_c
ORDER by
accounts_cstm.custid_c
";
$result = mysqli_query($link1, $query1);
if($result) {
$klantnr = array();
while($row = mysqli_fetch_assoc($result)) {
$klantnr[] = $row["custid_c"];
}
$klantnr = "('" . implode ("','", $klantnr) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
$query2 = "
SELECT
email_addresses.email_address
FROM
accounts
LEFT JOIN
accounts_cstm ON accounts_cstm.id_c = accounts.id
LEFT JOIN
accounts_contacts ON accounts_contacts.account_id = accounts.id
LEFT JOIN
contacts ON contacts.id = accounts_contacts.contact_id
LEFT JOIN
email_addr_bean_rel ON email_addr_bean_rel.bean_id = contacts.id
LEFT JOIN
email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id
LEFT JOIN
contacts_cstm ON contacts_cstm.id_c = contacts.id
WHERE
email_addresses.email_address IS NOT NULL
";
$results = mysqli_query($link1, $query2);
if($results) {
$email = array();
while($row = mysqli_fetch_assoc($results)) {
$email[] = $row["email_address"];
}
$email = "('" . implode ("','", $email) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
//mysqli_query($link2, "set sql_big_selects=1"); //???
$sql = "
SELECT
a.value AS klantnr
,b.value AS voornaam
,c.value AS achternaam
,t.value AS telfnr
,ce.email
FROM
customer_address_entity e
LEFT OUTER JOIN
customer_entity_varchar a ON a.entity_id = e.parent_id
AND a.attribute_id = 133
LEFT OUTER JOIN
customer_entity_varchar b ON b.entity_id = e.parent_id
AND b.attribute_id = '5'
LEFT OUTER JOIN
customer_entity_varchar c ON c.entity_id = e.parent_id
AND c.attribute_id = '7'
LEFT OUTER JOIN
customer_address_entity_varchar t ON t.entity_id = e.entity_id
AND t.attribute_id = '30'
LEFT OUTER JOIN
customer_entity ce ON ce.entity_id = a.entity_id
WHERE
ce.email NOT IN($email)
AND a.value IN ($klantnr)
GROUP BY
ce.email
";
$resultaat = mysqli_query($link2, $sql);
if($resultaat) {
$klantnr_magento=array();
$email_magento=array();
$vnaam=array();
$anaam=array();
$phone=array();
while($row = mysqli_fetch_assoc($results)) {
$klantnr_magento[]= $row["klantnr"];
$vnaam[]= $row["voornaam"];
$anaam[]= $row["achternaam"];
$phone[]= $row["telfnr"];
$email_magento[]= $row["email"];
}
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link2);
}
?>
$link1 = mysqli_connect('server1','user1','ww1','db1');
$link2 = mysqli_connect('server2','user2','ww2','db2');
$query1 = "
SELECT
custid_c
FROM
accounts_cstm
LEFT JOIN
accounts ON accounts.id = accounts_cstm.id_c
WHERE
accounts_cstm.custid_c IS NOT NULL
AND accounts.id = accounts_cstm.id_c
ORDER by
accounts_cstm.custid_c
";
$result = mysqli_query($link1, $query1);
if($result) {
$klantnr = array();
while($row = mysqli_fetch_assoc($result)) {
$klantnr[] = $row["custid_c"];
}
$klantnr = "('" . implode ("','", $klantnr) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
$query2 = "
SELECT
email_addresses.email_address
FROM
accounts
LEFT JOIN
accounts_cstm ON accounts_cstm.id_c = accounts.id
LEFT JOIN
accounts_contacts ON accounts_contacts.account_id = accounts.id
LEFT JOIN
contacts ON contacts.id = accounts_contacts.contact_id
LEFT JOIN
email_addr_bean_rel ON email_addr_bean_rel.bean_id = contacts.id
LEFT JOIN
email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id
LEFT JOIN
contacts_cstm ON contacts_cstm.id_c = contacts.id
WHERE
email_addresses.email_address IS NOT NULL
";
$results = mysqli_query($link1, $query2);
if($results) {
$email = array();
while($row = mysqli_fetch_assoc($results)) {
$email[] = $row["email_address"];
}
$email = "('" . implode ("','", $email) . "')";
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link1);
}
//mysqli_query($link2, "set sql_big_selects=1"); //???
$sql = "
SELECT
a.value AS klantnr
,b.value AS voornaam
,c.value AS achternaam
,t.value AS telfnr
,ce.email
FROM
customer_address_entity e
LEFT OUTER JOIN
customer_entity_varchar a ON a.entity_id = e.parent_id
AND a.attribute_id = 133
LEFT OUTER JOIN
customer_entity_varchar b ON b.entity_id = e.parent_id
AND b.attribute_id = '5'
LEFT OUTER JOIN
customer_entity_varchar c ON c.entity_id = e.parent_id
AND c.attribute_id = '7'
LEFT OUTER JOIN
customer_address_entity_varchar t ON t.entity_id = e.entity_id
AND t.attribute_id = '30'
LEFT OUTER JOIN
customer_entity ce ON ce.entity_id = a.entity_id
WHERE
ce.email NOT IN($email)
AND a.value IN ($klantnr)
GROUP BY
ce.email
";
$resultaat = mysqli_query($link2, $sql);
if($resultaat) {
$klantnr_magento=array();
$email_magento=array();
$vnaam=array();
$anaam=array();
$phone=array();
while($row = mysqli_fetch_assoc($results)) {
$klantnr_magento[]= $row["klantnr"];
$vnaam[]= $row["voornaam"];
$anaam[]= $row["achternaam"];
$phone[]= $row["telfnr"];
$email_magento[]= $row["email"];
}
} else {
echo 'Er is een fout opgetreden' . mysqli_error($link2);
}
?>
maar krijg nog steeds de zelfde foutmelding
Er is een fout opgetreden Operand should contain 1 column(s)
Gewijzigd op 21/03/2014 11:34:00 door Erwin H
Deze regel
$klantnr = "('" . implode ("','", $klantnr) . "')";
Vervangen door
$klantnr = "'" . implode ("','", $klantnr) . "'";