Zoekfunctie
Ik ben bezig om een zoekfunctie te maken met PHP en mySQL.
Er word in meerdere kolommen gezochte en dat doe ik met deze code
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
<?php
global $wpdb;
$strng = 'XO';
$table = $wpdb->prefix . "posts";
$query = "SELECT ID, post_title FROM $table WHERE (ID LIKE '%$strng%' OR post_title LIKE '%$strng%')";
$connection = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$result = mysqli_query($connection, $query);
?>
global $wpdb;
$strng = 'XO';
$table = $wpdb->prefix . "posts";
$query = "SELECT ID, post_title FROM $table WHERE (ID LIKE '%$strng%' OR post_title LIKE '%$strng%')";
$connection = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$result = mysqli_query($connection, $query);
?>
De variable $strng word straks doormiddel van $_POST gevuld.
Ik krijg nu altijd het resultaat dat ik wil hebben maar wil nog iets toevoegen maar niet precies weet hoe ik dit kan realiseren.
Er zijn twee kolommen "meta_key" en "meta_value".
In de meta_key staat als waarde _sku en in de meta_value staat een bepaalde waarde.
Op die waarde wil ik ook kunnen zoeken maar alleen als in dezelfde record in de meta_key _sku is.
Hoe voeg ik die conditie nu precies toe in de query?
Let op #1: $strng wordt nu niet ge-escaped, je kunt dus allerlei ongein uithalen in deze query.
Let op #2: ook al escape je $strng dan blijven het underscore karakter (_) en het percentage karakter (%) hun betekenis houden als wildcards, dus als je dit gedrag niet wilt houden en/of je letterlijk de karakters "_" en "%" wilt kunnen matchen zul je deze apart moeten escapen met een \ (backslash).
Mogelijk zou je een IF-statement kunnen gebruiken?
Code (php)
1
$query = "... WHERE ... OR ... OR IF(meta_key = '_sku', meta_value LIKE '".$strng."%', 1)";
Let op #1: alleen een % aan het einde, zodat gekeken wordt of je een substring van een SKU hebt ingevuld
Let op #2: de "else" case bevat een 1 (true), als de meta_key verschilt van '_sku' is dit OR-deel altijd goed
Let op #3: nog niet geescaped
Ik ben even het stoeien geweest maar krijg het nog niet helemaal voor elkaar.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
<?php
$con = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$strng = mysqli_real_escape_string($con, $_POST['s']);
$posts = $wpdb->prefix . "posts";
$postmeta = $wpdb->prefix . "postmeta";
$query = "SELECT ID, post_title FROM $posts as p JOIN $postmeta as pm WHERE p.ID LIKE '%$strng%' OR p.post_title LIKE '%$strng%' IF(pm.meta_key = '_sku', pm.meta_value LIKE '%$strng%') AND p.post_type = 'product'";
?>
$con = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$strng = mysqli_real_escape_string($con, $_POST['s']);
$posts = $wpdb->prefix . "posts";
$postmeta = $wpdb->prefix . "postmeta";
$query = "SELECT ID, post_title FROM $posts as p JOIN $postmeta as pm WHERE p.ID LIKE '%$strng%' OR p.post_title LIKE '%$strng%' IF(pm.meta_key = '_sku', pm.meta_value LIKE '%$strng%') AND p.post_type = 'product'";
?>
Je hebt beide nodig voor het gewenste resultaat denk ik.
Controleer nogmaals mijn voorbeeld.
Je wilt een zoekfunctie bouwen die in meerde kolommen zoekt?
Mag ik vragen waarom je een INNER / LEFT / RIGHT of FULL OUTER JOIN wilt gebruiken? Deze zie ik niet terug in je eerste vraag.
Klopt inderdaad :)
De JOIN wil ik toepassen omdat de kolommen meta_key en meta_value in een andere tabel staan, dit was ik inderdaad vergeten in mijn vraag.
De zoekresultaten moeten de ID's en posttitels bevatten, dit heb ik tot zover aan het draaien maar kom er niet goed uit met die 2e tabel.
Als een gebruiker het sku nummer invult dan moet alsnog de ID en de posttitel worden teruggegeven van de andere tabel, vandaar dat ik een INNER JOIN heb gebruikt.
De situatie is precies als volgt.
Het betreft een Wordpress database die ik aanroep, omdat ik de info niet op mijn Wordpressite wil maar op een andere site maak ik geen gebruik van de Wordpresstools.
De tabel posts heeft de kolom ID en post_title.
De tabel post_meta heeft de kolom post_id, meta_key en meta_value.
De kolom ID en post_id zijn dus altijd gelijk, hier moet ik dus een koppeling maken tussen de twee tabellen als ik het goed heb.
Als Wordpress een post aanmaakt dan word er voor elke meta een nieuwe record aangemaakt in de post_meta tabel.
Er zijn in deze tabel dus per post meerdere records bijvoorbeeld
| post_id | meta_key | meta_value |
| 1700 | price | 20.00 |
| 1700 | _sku | 782736 |
| 1700 | type | simple |
Daarom is er een IF-statement nodig om alleen te filteren op _sku en dan de bijbehorende waarde van kolom meta_value.
In feit wil ik dan dus zoeken op 3 verschillende kolommen in 2 verschillende tabellen.
Met als uitkomst de ID en de post_title.
Ik ben wat aan het stoeien geweest maar krijg het niet voor elkaar met de 2e tabel en het if statement.
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
$output = '';
$con = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$keywords = mysqli_real_escape_string($con, $_POST['s']);
if(strlen($keywords) < 3){
echo 'Fill in 3 or more characters';
return;
}
$posts = $wpdb->prefix . "posts";
$postmeta = $wpdb->prefix . "postmeta";
$query = " SELECT ID, post_title
FROM $posts as p
INNER JOIN $postmeta as pm ON p.ID = pm.post_id
WHERE p.post_type = 'product' AND p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR IF(pm.meta_key = '_sku', pm.meta_value LIKE '%$keywords%', 1)
ORDER BY post_title";
$result = mysqli_query($con, $query);
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_array($result)){
$output .= '<table>';
$output .= '<tr><td>'.$row['ID'].'</td><td>'.$row['post_title'].'</td></tr>';
$output .= '</table>';
}
echo $output;
}else{
echo 'Data Not Found';
}
?>
$output = '';
$con = mysqli_connect($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);
$keywords = mysqli_real_escape_string($con, $_POST['s']);
if(strlen($keywords) < 3){
echo 'Fill in 3 or more characters';
return;
}
$posts = $wpdb->prefix . "posts";
$postmeta = $wpdb->prefix . "postmeta";
$query = " SELECT ID, post_title
FROM $posts as p
INNER JOIN $postmeta as pm ON p.ID = pm.post_id
WHERE p.post_type = 'product' AND p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR IF(pm.meta_key = '_sku', pm.meta_value LIKE '%$keywords%', 1)
ORDER BY post_title";
$result = mysqli_query($con, $query);
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_array($result)){
$output .= '<table>';
$output .= '<tr><td>'.$row['ID'].'</td><td>'.$row['post_title'].'</td></tr>';
$output .= '</table>';
}
echo $output;
}else{
echo 'Data Not Found';
}
?>
Dan zou je wellicht beter een soort van (LEFT) JOIN kunnen gebruiken misschien.
Neem in de (LEFT) JOIN-conditie op dat de meta_key gelijk moet zijn aan _sku, en als je daar resultaat hebt (meta_key verschilt van NULL) kijk dan of de meta_value matcht met je keywords?
Het is misschien handig om te weten of er altijd (precies) één _sku meta_key hoort bij een post?
Ik was dat stukje helemaal vergeten in het vraagstuk, OEPS.
Er is inderdaad altijd maar één _sku per post :)
Dus ik voeg een LEFT JOIN toe aan
Code (php)
1
2
3
4
5
6
2
3
4
5
6
<?php
$query = " SELECT ID, post_title
FROM $posts
WHERE post_type = 'product' AND ID LIKE '%$keywords%' OR post_title LIKE '%$keywords%'
ORDER BY post_title";
?>
$query = " SELECT ID, post_title
FROM $posts
WHERE post_type = 'product' AND ID LIKE '%$keywords%' OR post_title LIKE '%$keywords%'
ORDER BY post_title";
?>
Het volgende zou dan ongeveer moeten werken denk ik:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT p.ID, p.post_title
FROM $posts p
INNER JOIN $postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_type = 'product'
AND (p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR pm.meta_value LIKE '%$keywords%')
ORDER BY p.post_title
FROM $posts p
INNER JOIN $postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_type = 'product'
AND (p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR pm.meta_value LIKE '%$keywords%')
ORDER BY p.post_title
Het loont ook altijd de moeite om expliciete haken om combinaties van AND en OR te zetten, ten einde verwarring/dubbelzinnigheid te voorkomen.
A AND (B OR C) is namelijk iets compleet anders dan (A AND B) OR C. Wanneer je A AND B OR C gebruikt dan zal dat equivalent zijn aan het eerste statement vanwege de associativiteit van AND en OR.
Zie ook de volgende waarheidstabel:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
| A B C | (A AND B) OR C | A AND (B OR C) |
|---------+------------------+------------------|
| 0 0 0 | 0 | 0 |
| 0 0 1 | 1 | 0 | *
| 0 1 0 | 0 | 0 |
| 0 1 1 | 1 | 0 | *
| 1 0 0 | 0 | 0 |
| 1 0 1 | 1 | 1 |
| 1 1 0 | 1 | 1 |
| 1 1 1 | 1 | 1 |
|---------+------------------+------------------|
| 0 0 0 | 0 | 0 |
| 0 0 1 | 1 | 0 | *
| 0 1 0 | 0 | 0 |
| 0 1 1 | 1 | 0 | *
| 1 0 0 | 0 | 0 |
| 1 0 1 | 1 | 1 |
| 1 1 0 | 1 | 1 |
| 1 1 1 | 1 | 1 |
Gewijzigd op 03/04/2020 23:24:50 door Thomas van den Heuvel
Duidelijk antwoord waar ik iets mee kan :)
Ik heb altijd artikelnummers ingevuld waardoor _sku altijd aanwezig is maar als ik een niet artikel aanmaak dan is er geen _sku aanwezig in de kolom meta_key.
Hierdoor vind hij nooit dat artikel.
Ik ben dus even terug gegaan naar je eerste antwoord en heb dit gemaakt
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
$query = " SELECT p.ID, p.post_title
FROM $posts p
INNER JOIN $postmeta pm ON pm.post_id = p.ID
WHERE p.post_type = 'product'
AND p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR IF(meta_key = '_sku', pm.meta_value LIKE '%$keywords%', 1)
ORDER BY p.post_title";
?>
Maar krijg nu heel erg veel records terug wat niet helemaal de bedoeling is, zelfs records die geen van de keywords bevat.
$query = " SELECT p.ID, p.post_title
FROM $posts p
INNER JOIN $postmeta pm ON pm.post_id = p.ID
WHERE p.post_type = 'product'
AND p.ID LIKE '%$keywords%' OR p.post_title LIKE '%$keywords%' OR IF(meta_key = '_sku', pm.meta_value LIKE '%$keywords%', 1)
ORDER BY p.post_title";
?>
Maar krijg nu heel erg veel records terug wat niet helemaal de bedoeling is, zelfs records die geen van de keywords bevat.
Dan moet je dus terug naar de constructie met een LEFT JOIN, waarbij je de tabellen verbindt met de extra conditie pm.meta_key = '_sku'. Mocht dit bijbehorende record er (nog) niet zijn dan zijn alle kolommen uit de $postmeta tabel gelijk aan NULL, maar krijg je dus wel altijd precies één resultaat (per post id), of er nu een _sku is of niet.
Je krijgt in de bovenstaande query waarschijnlijk heel veel records terug omdat je die twee tabellen enkel met de post_id = ID conditie aan elkaar fietst, dan krijg je waarschijnlijk voor elk record in $postmeta wat overeenkomen met het post ID een record terug, en dat is niet wat je wilt...
En die "1" moet waarschijnlijk bij nader inzien een 0 zijn, anders staat er immers ... OR ... OR 1, en dat is altijd waar :p.
Dus, ten overvloede, met wat aanpassingen ten opzichte van mijn vorige query, en een extra controle of meta_value wel een waarde heeft, zou dit beter moeten werken:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT p.ID, p.post_title
FROM $posts p
LEFT JOIN $postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_type = 'product'
AND (
p.ID LIKE '%$keywords%' OR
p.post_title LIKE '%$keywords%' OR
IF(pm.meta_value IS NULL, 0, pm.meta_value LIKE '%$keywords%')
)
ORDER BY p.post_title
FROM $posts p
LEFT JOIN $postmeta pm ON (pm.post_id = p.ID AND pm.meta_key = '_sku')
WHERE p.post_type = 'product'
AND (
p.ID LIKE '%$keywords%' OR
p.post_title LIKE '%$keywords%' OR
IF(pm.meta_value IS NULL, 0, pm.meta_value LIKE '%$keywords%')
)
ORDER BY p.post_title
Let hierbij dus ook nog steeds op de haken om de OR-statements, want die zijn in jouw bovenstaande query alweer verdwenen.
Gewijzigd op 04/04/2020 21:07:41 door Thomas van den Heuvel