Hulp bij search query
Ik kamp met een probleem in mijn search query en had graag wat hulp gehad.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
<?php
$q = $_POST['search'];
$query = "
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 '%$q%' AND ID = p.ID)
AND EXISTS (SELECT 1 FROM wp_posts WHERE post_type='product' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%$q%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id
";
?>
$q = $_POST['search'];
$query = "
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 '%$q%' AND ID = p.ID)
AND EXISTS (SELECT 1 FROM wp_posts WHERE post_type='product' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%$q%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id
";
?>
In de tabel wp_post word gezocht in de kolom post_title en in de kolom ID.
Dan is er nog de conditie als post_type is gelijk aan product.
Maar er word ook gekeken in de kolom _sku van tabel wp_postmeta.
Het probleem waar ik mee zit is dat er niet goed op de losse worden word gezocht.
Stel dat er een artikel is: Voetbal schoenen rood met zwart
Als ik vervolgens een post geef met de waarde voetbal rood dan schijnt er geen artikel te zijn.
Maar geef ik de waarde Voetbal schoenen rood dan krijg ik wel de juiste producten.
In principe moeten alle woorden die in de string zitten van links naar rechts, rechts naar links een resultaat geven.
Dus dat je ook resultaat krijgt op rood voetbal
Dus eigenlijk de string opsplitsen met explode?
En deze door de query halen?
Code (php)
1
$words = preg_split('/\\W+/',$q);
En dan dus nog een soort "score" berekenen. Dit kun je in SQL doen, of via SQL alleende grove filtering, en de score in PHP berekenen (ligt er een beetje aan wat het makkelijkst is, en hoeveel resultaten je verwacht - heb je 1000 of 1000000 posts).
Als ik de preg_split gebruik is het dan de bedoeling om een foreach aan te maken waarin de query staat?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
$words = preg_split('/\W+/',$_POST['search']);
foreach($words as $word){
$query = "
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 '%$word%' AND ID = p.ID)
AND EXISTS (SELECT 1 FROM wp_posts WHERE post_type='product' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%$word%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id
";
while($row = mysqli_fetch_assoc($result)){
//doe wat met je resultaten
//kan ik hier een score bereken voor de resultaten zoals je aangaf?
}
}
?>
$words = preg_split('/\W+/',$_POST['search']);
foreach($words as $word){
$query = "
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 '%$word%' AND ID = p.ID)
AND EXISTS (SELECT 1 FROM wp_posts WHERE post_type='product' AND ID = p.ID)
OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%$word%' AND post_id = p.ID)
ORDER BY p.ID, m.meta_id
";
while($row = mysqli_fetch_assoc($result)){
//doe wat met je resultaten
//kan ik hier een score bereken voor de resultaten zoals je aangaf?
}
}
?>
Code (php)
1
2
3
4
5
2
3
4
5
//stel $words = ['aap','noot','mies']
select * from tabel t
where (t.titel like '%aap%') or (t.meta like '%aap%')
or (t.titel like '%noot%') or (t.meta like '%noot%')
or (t.titel like '%mies%') or (t.meta like '%mies%')
select * from tabel t
where (t.titel like '%aap%') or (t.meta like '%aap%')
or (t.titel like '%noot%') or (t.meta like '%noot%')
or (t.titel like '%mies%') or (t.meta like '%mies%')
Vervolgens ga je daar "de beste" uit zoeken. Dat kan met PHP, of in SQL bijvoorbeeld:
Code (php)
1
2
3
4
2
3
4
order by if(t.titel like '%aap%',2,0) + if(t.meta like '%aap%',1,0) +
if(t.titel like '%noot%',2,0) + if(t.meta like '%noot%',1,0) +
if(t.titel like '%mies%',2,0) + if(t.meta like '%mies%',1,0) desc
limit 10
if(t.titel like '%noot%',2,0) + if(t.meta like '%noot%',1,0) +
if(t.titel like '%mies%',2,0) + if(t.meta like '%mies%',1,0) desc
limit 10
- 2 punten als het woord in de titel voorkomt
- 1 punt als het woord in de meta staat
- de post met de meeste punten bovenaan
- limiteer tot 10
(maar dit alles dus naar eigen inzicht)
En uiteraard is jouw situatie nog net iets complexer ;-p
Bonus
Code (php)
1
if(t.titel like '%aap%',2,0) + if(t.meta like '%aap%',1,0) + if(t.meta = 'aap',5,0)
= een exacte match op een woord extra punten (5) geven.
Je zou ook eens naar full text search kunnen kijken. Maar daar moet je vaak wel je DB op inrichten / voor aanpassen.
@rob het score systeem is inderdaad erg handig.
Ik ben begonnen met een simpele query met dit als resultaat
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
$connection = mysqli_connect('host', 'user', 'password', 'database');
$searchstrg = $_POST['search'];
$words = explode(" ", $searchstrg);
$totalwords = count($words);
$query = "SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[0]%' OR post_content LIKE '%$words[0]%')";
$ordercondition = "IF(post_title LIKE '%$words[0]%',2,0) + IF(post_content LIKE '%$words[0]%',1,0)";
if($totalwords > 1){
for($i=1;$i<$totalwords;$i++) {
$query .= "UNION SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[$i]%' OR post_content LIKE '%$words[$i]%')";
$ordercondition .= "+ IF(post_title LIKE '%$words[$i]%',2,0) + IF(post_content LIKE '%$words[$i]%',1,0)";
}
}
$query .= 'ORDER BY '.$ordercondition.' DESC LIMIT 10';
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result)){
echo $row['post_title'];
}
?>
$connection = mysqli_connect('host', 'user', 'password', 'database');
$searchstrg = $_POST['search'];
$words = explode(" ", $searchstrg);
$totalwords = count($words);
$query = "SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[0]%' OR post_content LIKE '%$words[0]%')";
$ordercondition = "IF(post_title LIKE '%$words[0]%',2,0) + IF(post_content LIKE '%$words[0]%',1,0)";
if($totalwords > 1){
for($i=1;$i<$totalwords;$i++) {
$query .= "UNION SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[$i]%' OR post_content LIKE '%$words[$i]%')";
$ordercondition .= "+ IF(post_title LIKE '%$words[$i]%',2,0) + IF(post_content LIKE '%$words[$i]%',1,0)";
}
}
$query .= 'ORDER BY '.$ordercondition.' DESC LIMIT 10';
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result)){
echo $row['post_title'];
}
?>
Dit levert de gewenste resultaten op maar nog zonder de 2e tabel.
Ik ben begonnen hieraan maar loop toch nog vast.
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
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
<?php
$connection = mysqli_connect('host', 'user', 'password', 'database');
$searchstrg = $_POST['search'];
$words = explode(" ", $searchstrg);
$totalwords = count($words);
$query = "
SELECT p.ID, p.post_title, pm.meta_key
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID)
WHERE post_type='product' AND (post_title LIKE '%$words[0]%' OR post_content LIKE '%$words[0]%')";
$ordercondition = "IF(post_title LIKE '%$words[0]%',2,0) + IF(post_content LIKE '%$words[0]%',1,0)";
if($totalwords > 1){
for($i=1;$i<$totalwords;$i++) {
$query .= "UNION SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[$i]%' OR post_content LIKE '%$words[$i]%')";
$ordercondition .= "+ IF(post_title LIKE '%$words[$i]%',2,0) + IF(post_content LIKE '%$words[$i]%',1,0)";
}
}
$query .= 'ORDER BY '.$ordercondition.' DESC LIMIT 10';
$result = mysqli_query($connection, $query);
?><table><?php
while($row = mysqli_fetch_assoc($result)){
if($row['ID'] !== $existid){
echo '<tr><td>'.$row['ID'].'</td></tr>';
}
if ($row['post_title'] !== $existtitle) {
echo '<tr><td>'.$row['post_title'].'</td></tr>';
}
echo '<tr><td>'.$row['meta_key'].'</td></tr>';
$existtitle = $row['post_title'];
$existid = $row['ID'];
}
?>
</table>
$connection = mysqli_connect('host', 'user', 'password', 'database');
$searchstrg = $_POST['search'];
$words = explode(" ", $searchstrg);
$totalwords = count($words);
$query = "
SELECT p.ID, p.post_title, pm.meta_key
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID)
WHERE post_type='product' AND (post_title LIKE '%$words[0]%' OR post_content LIKE '%$words[0]%')";
$ordercondition = "IF(post_title LIKE '%$words[0]%',2,0) + IF(post_content LIKE '%$words[0]%',1,0)";
if($totalwords > 1){
for($i=1;$i<$totalwords;$i++) {
$query .= "UNION SELECT * FROM wp_posts WHERE post_type='product' AND (post_title LIKE '%$words[$i]%' OR post_content LIKE '%$words[$i]%')";
$ordercondition .= "+ IF(post_title LIKE '%$words[$i]%',2,0) + IF(post_content LIKE '%$words[$i]%',1,0)";
}
}
$query .= 'ORDER BY '.$ordercondition.' DESC LIMIT 10';
$result = mysqli_query($connection, $query);
?><table><?php
while($row = mysqli_fetch_assoc($result)){
if($row['ID'] !== $existid){
echo '<tr><td>'.$row['ID'].'</td></tr>';
}
if ($row['post_title'] !== $existtitle) {
echo '<tr><td>'.$row['post_title'].'</td></tr>';
}
echo '<tr><td>'.$row['meta_key'].'</td></tr>';
$existtitle = $row['post_title'];
$existid = $row['ID'];
}
?>
</table>
De limit had ik graag alleen op de wp_post tabel gehad zodat er 10 producten geladen worden met wel alle meta_keys, want deze worden nu allemaal opgeteld en dan is je limit zo bereikt
Het zoeken naar de _sku wil ik ook graag nog inbouwen en dan ben ik helemaal blij :)
Gewijzigd op 20/06/2020 21:35:28 door Bryan De Baar