MySQL zoek query filter op waarde maar geen alle meta_value terug
Ik maak gebruik van deze query
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_title LIKE '%rad%'
OR IF(pm.meta_value IS NULL, 0, pm.meta_value LIKE '%rad%')
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_title LIKE '%rad%'
OR IF(pm.meta_value IS NULL, 0, pm.meta_value LIKE '%rad%')
wp_posts
| ID | post_title |
| 1 | radio |
| 2 | televisie |
wp_postmeta
| meta_id | post_id | meta_key | meta_value |
| 1 | 1 | price | 12.50 |
| 2 | 1 | _sku | 5876gh7 |
| 3 | 1 | thumb_id | 56 |
| 4 | 2 | price | 342.50 |
| 5 | 2 | _sku | tv784 |
| 6 | 2 | thumb_id | 78 |
Met deze query zoek ik in de post_title en in de meta_value kolom als kolom meta_key de waarde _sku bevat.
Zo kan ik zoeken op naam en artikelnummer.
De query zoals hierboven geeft als resultaat
- Radio
- 5876gh7
Maar ik wil ook de price en de thumb_id hebben uit de kolom meta_value.
Als ik het rood gemarkeerde weghaal
LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku') krijg ik alle meta_values terug maar dan word er ook in alle meta_values gezocht wat alleen mag als de meta_key _sku is.
Wat dien ik aan te passen in mijn query?
Gewijzigd op 24/05/2020 13:47:36 door Bryan De Baar
https://www.phphulp.nl/php/forum/topic/juiste-query-voor-heen-en-terug/103545/
Je condities komen zowel uit wp_posts alsook wp_postmeta. Specifiek, dit levert (in beide gevallen) bepaalde id's uit de wp_posts tabel op.
Dit levert mogelijk meerdere id's op (als je bijvoorbeeld op "7" zoekt krijg je beide posts terug omdat "7" in beide _sku's voorkomt), dus waarschijnlijk wil je ook sorteren op post_id, en dan op meta_id ofzo.
Het volgende werkt volgens mij:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT p.ID, p.post_title, m.meta_id, m.meta_key, m.meta_value
FROM wp_posts p
LEFT JOIN wp_postmeta m ON (m.post_id = p.ID)
WHERE EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id;
FROM wp_posts p
LEFT JOIN wp_postmeta m ON (m.post_id = p.ID)
WHERE EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id;
Maar dit is weer een vraagstuk over de wp_posts tabel, hier is recent een soortgelijke vraag over gesteld en mogelijk heeft WordPress hier out-of-the-box functionaliteit (interne link) voor.
Voordat je dit soort queries in elkaar loopt te hacken zou je eigenlijk moeten kijken of je dit met standaard WP functionaliteit op kunt lossen.
Dit ga ik even proberen :-). Hoewel het wel een Wordpress tabel is ail ik deze query buiten Wordpress gebruiken.
Vandaar dat ik niet de standaard wordpress functies gebruik.
Toevoeging op 24/05/2020 16:30:49:
Dit werkt inderdaad Thomas :)
Omdat ik de post_title nu even vaak terug krijg als de meta_value heb ik GROUP BY post_title geprobeerd omdat ik de post_title natuurlijk maar 1x hoef te hebben. Maar dan krijg ik maar 1 record terug.
Kan ik dit het beste oplossen met PHP en controleren of deze al bestaat of kan dit worden aangeven in de query?
Even de SELECT controleren op juiste WHERE.
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Winkel</title>
<meta name="viewport" content="user-scalable=1, width=device-width, initial-scale=1.0, maximum-scale=2.0, minimum-scale=1.0" />
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
<style>
html {
font-size: 13px;
}
div {
width: 600px;
padding: 1.000em;
border: 0.100em solid red;
margin: 1.000em auto;
font-family: futura;
font-size: 1.100em;
}
table, th, td {
border: 0.100em solid navy;
border-collapse: collapse;
}
table {
width: 100%;
}
th {
text-align: left;
background-color: #dcdc00;
}
th, td {
padding: 0.500em;
vertical-align: top;
}
tr:nth-child(odd) {
background: #dcdcdc;
}
tr:nth-child(even) {
background: #dcdcaa;
}
tr.afspraak {
background: ivory;
}
</style>
</head>
<body>
<div>
<?php
include ('config.php');
$query = "SELECT
p.post_title AS naam,
GROUP_CONCAT(pm.meta_key ORDER BY pm.meta_key separator ';') AS sleutel,
GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key separator ';') AS waarde
FROM
wp_posts p
LEFT JOIN
wp_postmeta pm
ON
p.id = pm.post_id
WHERE
p.post_title LIKE '%rad%' OR pm.post_id >= 1
GROUP BY
pm.post_id;";
$result = $db->query( $query );
?>
<table border="1">
<thead>
<caption>Winkel</caption>
<tr>
<?php
$velden = $result->fetch_fields();
foreach ( $velden as $veld )
{
?>
<th><?php echo ucfirst( $veld->name ); ?></th>
<?php
}
?>
</tr>
</thead>
<tbody>
<?php
while ( $row = $result->fetch_object() )
{
echo '<tr><td colspan="3"><pre>' . print_r( $row, TRUE ) . '</pre></td></tr>';
?>
<tr class='afspraak'>
<td><?php echo $row->naam; ?></td>
<td>
<?php
$sleutels = explode( ";", $row->sleutel );
//echo '<pre>' . print_r( $sleutels, TRUE ) . '</pre>';
foreach ( $sleutels as $sleutel )
{
echo $sleutel . '<br />';
}
?>
</td>
<td>
<?php
$waarden = explode( ";", $row->waarde );
//echo '<pre>' . print_r( $waarden, TRUE ) . '</pre>';
foreach ( $waarden as $waarde )
{
echo $waarde . '<br />';
}
?>
</td>
</tr>
<?php
}?>
</tbody>
<tfoot>
<tr>
<td colspan="5"></td>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Winkel</title>
<meta name="viewport" content="user-scalable=1, width=device-width, initial-scale=1.0, maximum-scale=2.0, minimum-scale=1.0" />
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
<style>
html {
font-size: 13px;
}
div {
width: 600px;
padding: 1.000em;
border: 0.100em solid red;
margin: 1.000em auto;
font-family: futura;
font-size: 1.100em;
}
table, th, td {
border: 0.100em solid navy;
border-collapse: collapse;
}
table {
width: 100%;
}
th {
text-align: left;
background-color: #dcdc00;
}
th, td {
padding: 0.500em;
vertical-align: top;
}
tr:nth-child(odd) {
background: #dcdcdc;
}
tr:nth-child(even) {
background: #dcdcaa;
}
tr.afspraak {
background: ivory;
}
</style>
</head>
<body>
<div>
<?php
include ('config.php');
$query = "SELECT
p.post_title AS naam,
GROUP_CONCAT(pm.meta_key ORDER BY pm.meta_key separator ';') AS sleutel,
GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key separator ';') AS waarde
FROM
wp_posts p
LEFT JOIN
wp_postmeta pm
ON
p.id = pm.post_id
WHERE
p.post_title LIKE '%rad%' OR pm.post_id >= 1
GROUP BY
pm.post_id;";
$result = $db->query( $query );
?>
<table border="1">
<thead>
<caption>Winkel</caption>
<tr>
<?php
$velden = $result->fetch_fields();
foreach ( $velden as $veld )
{
?>
<th><?php echo ucfirst( $veld->name ); ?></th>
<?php
}
?>
</tr>
</thead>
<tbody>
<?php
while ( $row = $result->fetch_object() )
{
echo '<tr><td colspan="3"><pre>' . print_r( $row, TRUE ) . '</pre></td></tr>';
?>
<tr class='afspraak'>
<td><?php echo $row->naam; ?></td>
<td>
<?php
$sleutels = explode( ";", $row->sleutel );
//echo '<pre>' . print_r( $sleutels, TRUE ) . '</pre>';
foreach ( $sleutels as $sleutel )
{
echo $sleutel . '<br />';
}
?>
</td>
<td>
<?php
$waarden = explode( ";", $row->waarde );
//echo '<pre>' . print_r( $waarden, TRUE ) . '</pre>';
foreach ( $waarden as $waarde )
{
echo $waarde . '<br />';
}
?>
</td>
</tr>
<?php
}?>
</tbody>
<tfoot>
<tr>
<td colspan="5"></td>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
Gewijzigd op 24/05/2020 16:47:01 door Adoptive Solution
https://www.phphulp.nl/php/forum/topic/groeperen-van-items/103526/
https://www.phphulp.nl/php/forum/topic/div-einde-toevoegen-nadata-alle-elementen-per-category-er-in-staan/103487/
Meerdere manieren om dit op te lossen, bijvoorbeeld:
- bouw eerst een hulparray in PHP-met de query-resultaten (zie tweede thread), of
- houd on-the-fly bij in welke rubriek je zat (eerste thread of zoals @Jop hierboven aangeeft), hierbij moet je dus wel sorteren op deze rubriek.
@Adoptive: alweer eerst een GROUP_CONCAT en dan een explode? :/
Quote:
Kan ik dit het beste oplossen met PHP en controleren of deze al bestaat of kan dit worden aangeven in de query?
Als je de price en thumb_id nodig hebt zoals je in je oorspronkelijke bericht aangeeft zul je deze informatie nog stees op moeten halen. Ik zou dit "gestructureerd" doen (en dit niet op één hoop gooien zodat de betekenis van de informatie min of meer verloren gaat) zodat je later deze data nog (aan de PHP-kant) aan kunt passen zonder de query te veranderen.
Het zou niet nodig moeten zijn dat je informatie die uit de database afkomstig is nog moet bewerken / "uit moet pakken" om deze te kunnen gebruiken. Deze zou direct gebruiksklaar, of op zijn minst "direct leesbaar", moeten zijn.
Gewijzigd op 24/05/2020 17:09:30 door Thomas van den Heuvel
Dit is zoiets als een oplossing verzinnen voor een probleem dat je zelf introduceert.
Het werkt verder prima daar niet van, maar ik zou het zo simpelweg niet aanpakken :).
Ik zou dan een array willen aanmaken en die dan terug sturen en met jQuery de opmaak willen maken.
Dat word dan een array gevuld met arrays?
Hoe kan ik dan per post_title met bijhorende meta_value's een array in de array maken?
Stel dat ik zoek op 7 en ik krijg beide terug:
Code (php)
1
2
3
4
2
3
4
while ( $row = $result->fetch_object() )
{
$sendback[] = $row;
}
{
$sendback[] = $row;
}
Deze had ik al geprobeerd opzich krijg ik het dan inderdaad in een array maar dan zit ik weer met die dubbele post_title.
Als je mijn aanpak gebruikte met de "dubbele data" in de records, kun je dit heel eenvoudig doen:
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
$data = array();
$query = '...'; // mijn eerdere query
$res = $db->query($query);
while ($row = $res->fetch_assoc()) {
// indien het subarray nog niet bestaat, maak deze aan
if (false === isset($data[$row['ID']])) {
// kopieer wp_posts specifieke data eenmalig bij creatie subarray
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
);
}
// nu kun je zonder zorgen alle overige key-value paren aan dit subarray toevoegen
// de isset-controle garandeert immers dat dit subarray bestaat
$data[$row['ID']][$row['meta_key']] = $row['meta_value'];
}
?>
$data = array();
$query = '...'; // mijn eerdere query
$res = $db->query($query);
while ($row = $res->fetch_assoc()) {
// indien het subarray nog niet bestaat, maak deze aan
if (false === isset($data[$row['ID']])) {
// kopieer wp_posts specifieke data eenmalig bij creatie subarray
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
);
}
// nu kun je zonder zorgen alle overige key-value paren aan dit subarray toevoegen
// de isset-controle garandeert immers dat dit subarray bestaat
$data[$row['ID']][$row['meta_key']] = $row['meta_value'];
}
?>
Gewijzigd op 26/05/2020 13:59:30 door Thomas van den Heuvel
Deze code werkt inderdaad prima. Echter heb ik nog een vraag.
In de kolom meta_key word per artikel nog een record aangemaakt met manage_stock en stock met in de meta_value de waarde yes voor manage_stock en een aantal voor stock.
| meta_id | post_id | meta_key | meta_value |
| 1 | 1 | price | 12.50 |
| 2 | 1 | _sku | 5876gh7 |
| 3 | 1 | thumb_id | 56 |
| 4 | 1 | manage_stock | yes |
| 5 | 1 | stock | 5 |
| 6 | 2 | price | 342.50 |
| 7 | 2 | _sku | tv784 |
| 8 | 2 | thumb_id | 78 |
| 9 | 2 | stock | 10 |
| 10 | 2 | manage_Stock | yes |
Als de record meta_key -> manage_stock meta_value-> yes is dan voeg de record meta_key -> stock de waarde van meta_value toe.
Omdat het systeem de ene keer de record manage_stock boven de record stock zet en de ene keer andersom dus de stock boven de manage stock zet kan ik dit niet oplossen met een if statement in de loop zoals ik onderstaand heb geprobeerd.
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
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
<?php
while($row = mysqli_fetch_assoc($result)){
if (false === isset($data[$row['ID']])) {
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
'price' => '-',
'stock' => 'geen voorraad'
);
}
if($row['meta_key'] == '_price' ){
if($row['meta_value'] !== ''){
$data[$row['ID']]['price'] = $row['meta_value'];
}else{
$data[$row['ID']]['price'] = '-';
}
}
if($row['meta_key'] == '_stock_status' ){
$data[$row['ID']]['stock_status'] = $row['meta_value'];
}
if($row['meta_key'] == '_manage_stock' ){
if($row['meta_value'] == 'yes'){
if($row['meta_key'] == '_stock' ){
$data[$row['ID']]['stock'] = $row['meta_value'];
}
}
}
}
?>
while($row = mysqli_fetch_assoc($result)){
if (false === isset($data[$row['ID']])) {
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
'price' => '-',
'stock' => 'geen voorraad'
);
}
if($row['meta_key'] == '_price' ){
if($row['meta_value'] !== ''){
$data[$row['ID']]['price'] = $row['meta_value'];
}else{
$data[$row['ID']]['price'] = '-';
}
}
if($row['meta_key'] == '_stock_status' ){
$data[$row['ID']]['stock_status'] = $row['meta_value'];
}
if($row['meta_key'] == '_manage_stock' ){
if($row['meta_value'] == 'yes'){
if($row['meta_key'] == '_stock' ){
$data[$row['ID']]['stock'] = $row['meta_value'];
}
}
}
}
?>
Moet ik dan nog een andere hulp array maken of nog een losse query om dit voor elkaar te krijgen?
Gewijzigd op 02/06/2020 23:22:08 door Bryan De Baar
Overigens, dit:
Code (php)
Is toch niet nodig? Overschrijf de default alleen als dat nodig is:
Code (php)
1
2
3
4
5
2
3
4
5
<?php
if ($row['meta_key'] == '_price' && $row['meta_value'] !== '') {
$data[$row['ID']]['price'] = $row['meta_value'];
}
?>
if ($row['meta_key'] == '_price' && $row['meta_value'] !== '') {
$data[$row['ID']]['price'] = $row['meta_value'];
}
?>
Houd het simpel.
Gewijzigd op 03/06/2020 00:09:32 door Thomas van den Heuvel
Maar als ik de manage_stock default op false heb staan en de record met stock word eerder geladen dan moet ik deze dan ergens in een variable zetten en als de manage_stock record is gelezen en yes is toevoegen aan array?
Code (php)
Deze code zal van de bovenstaande table alleen werken op post_id 1 en niet op post_id 2
Bryan De Baar op 03/06/2020 00:59:43:
Duidelijk Thomas,
Nou ja, niet helemaal dus :p.
Bryan De Baar op 03/06/2020 00:59:43:
Maar als ik de manage_stock default op false heb staan en de record met stock word eerder geladen dan moet ik deze dan ergens in een variable zetten en als de manage_stock record is gelezen en yes is toevoegen aan array?
Dat is precies wat ik in mijn vorige reactie voorstelde. Sla in plaats van "yes" simpelweg true op.
Als daar dan het moment is om stock weer te geven inspecteer je eerste manage_stock:
Code (php)
1
2
3
2
3
<?php
$stock = $whatever[$productId]['manage_stock'] ? $whatever[$productId]['stock'] : 'niet op voorraad';
?>
$stock = $whatever[$productId]['manage_stock'] ? $whatever[$productId]['stock'] : 'niet op voorraad';
?>
Of wat voor logica van toepassing is, ik weet niet onder welke voorwaarden je wat wilt weergeven.
Je zou dit alles ook generieker kunnen maken door een whitelist op te stellen voor de meta_keys waarin je geinteresseerd bent, en hier vervolgens ook "vertaalregels" aan kunnen hangen om de informatie nog wat te ordenen voordat je dit intern in een hulparray opslaat.
Gewijzigd op 03/06/2020 02:02:12 door Thomas van den Heuvel
Als je nu eerst een variabel aanmaakt voor _stock en _manage_stock en vervolgens een if statement toepast op deze?
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
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
<?php
while($row = mysqli_fetch_assoc($result)){
if (false === isset($data[$row['ID']])) {
//we zetten manage_stock (ms_on) op no als defautl
$ms_on = 'no';
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
'price' => '-',
//stock zetten we op 0 als default
'stock' => '0',
);
}
if ($row['meta_key'] == '_price' && $row['meta_value'] !== '') {
$data[$row['ID']]['price'] = $row['meta_value'];
}
//we slaan de meta_value van stock op in een variabel
if ($row['meta_key'] == '_stock' && $row['meta_value'] !== '') {
$stock = $row['meta_value'];
}
//we slaan de meta_value van _manage_stock op in een variable
if ($row['meta_key'] == '_manage_stock' && $row['meta_value'] !== '') {
$ms_on = $row['meta_value'];
}
//omdat beide variables nu gevuld zijn door de default of zijn overschreven door je resultaten kunnen we nu een if statement maken en dan stock in de array zetten
if($ms_on == 'yes'){
$data[$row['ID']]['stock'] = $stock;
}
}
?>
while($row = mysqli_fetch_assoc($result)){
if (false === isset($data[$row['ID']])) {
//we zetten manage_stock (ms_on) op no als defautl
$ms_on = 'no';
$data[$row['ID']] = array(
'id' => $row['ID'],
'post_title' => $row['post_title'],
'price' => '-',
//stock zetten we op 0 als default
'stock' => '0',
);
}
if ($row['meta_key'] == '_price' && $row['meta_value'] !== '') {
$data[$row['ID']]['price'] = $row['meta_value'];
}
//we slaan de meta_value van stock op in een variabel
if ($row['meta_key'] == '_stock' && $row['meta_value'] !== '') {
$stock = $row['meta_value'];
}
//we slaan de meta_value van _manage_stock op in een variable
if ($row['meta_key'] == '_manage_stock' && $row['meta_value'] !== '') {
$ms_on = $row['meta_value'];
}
//omdat beide variables nu gevuld zijn door de default of zijn overschreven door je resultaten kunnen we nu een if statement maken en dan stock in de array zetten
if($ms_on == 'yes'){
$data[$row['ID']]['stock'] = $stock;
}
}
?>
Gebruik (ook) op zijn minst Boolse waarden in plaats van een variabele met waarden als 'yes' en 'no' alsjeblieft, dit type bestaat niet voor niets...
@Jop dit werkt inderdaad :)
@Thomas helaas hanteert dit systeem de waarde yes ipv true. Ik ben dus gebonden aan yes.
Gewijzigd op 03/06/2020 21:37:47 door Thomas van den Heuvel