Query resultaat duurd lang.
Nu Duurt het zeker +-10 seconden voor er resultaat komt.
En bij zoeken op barcode (product_model) laat hij het product 2x zien.
bij zoeken op naam laat hij hem 100x zien.
Kan iemand mij wijzen of ik iets fout doe ?
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
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
<?
if(isset($_POST['item_search']) and $_POST['item_search']!='')
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and
p.products_model like \"%$search%\" or
pd.products_name like \"%$search%\"
and pd.language_id=1
ORDER
by pd.products_name"; }
elseif(isset($_SESSION['current_item_search']))
{
$search=$_SESSION['current_item_search'];
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and
p.products_model like \"%$search%\" or
pd.products_name like \"%$search%\"
and pd.language_id=1
ORDER
by products_description.products_name";
}
else
{
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and pd.language_id=1 ORDER
by pd.products_name";
}
?>
if(isset($_POST['item_search']) and $_POST['item_search']!='')
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and
p.products_model like \"%$search%\" or
pd.products_name like \"%$search%\"
and pd.language_id=1
ORDER
by pd.products_name"; }
elseif(isset($_SESSION['current_item_search']))
{
$search=$_SESSION['current_item_search'];
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and
p.products_model like \"%$search%\" or
pd.products_name like \"%$search%\"
and pd.language_id=1
ORDER
by products_description.products_name";
}
else
{
$query="SELECT p.products_id,p.products_model,pd.products_name,p.products_price,tr.tax_rate
FROM
products as p,products_description as pd,tax_rates as tr,tax_class as tc
WHERE
p.products_id=pd.products_id and
p.products_tax_class_id=tc.tax_class_id and
tr.tax_class_id=tc.tax_class_id and pd.language_id=1 ORDER
by pd.products_name";
}
?>
En let dan eens op je AND en OR's in je WHERE statements. Daar gaat volgens mij iets fout.
En dan expliciet joinen.
Dit is je WHERE statement in je query, iets overzichtelijker geschreven:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
WHERE p.products_id=pd.products_id
AND p.products_tax_class_id=tc.tax_class_id
AND tr.tax_class_id=tc.tax_class_id
AND p.products_model like \"%$search%\"
OR pd.products_name like \"%$search%\"
AND pd.language_id=1
AND p.products_tax_class_id=tc.tax_class_id
AND tr.tax_class_id=tc.tax_class_id
AND p.products_model like \"%$search%\"
OR pd.products_name like \"%$search%\"
AND pd.language_id=1
Operator precedence bepaalt in welke volgorde operators worden geevalueerd. In dit geval zal de volgorde zijn =, LIKE, AND, OR
de = en LIKE spreken voor zich, het gaat om AND en OR. Om duidelijk te maken wat er nu gaat gebeuren zet ik haakjes om de statements welke als eerste worden bepaald:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
WHERE (
p.products_id=pd.products_id
AND p.products_tax_class_id=tc.tax_class_id
AND tr.tax_class_id=tc.tax_class_id
AND p.products_model like \"%$search%\"
) OR (
pd.products_name like \"%$search%\"
AND pd.language_id=1
)
p.products_id=pd.products_id
AND p.products_tax_class_id=tc.tax_class_id
AND tr.tax_class_id=tc.tax_class_id
AND p.products_model like \"%$search%\"
) OR (
pd.products_name like \"%$search%\"
AND pd.language_id=1
)
Je ziet dus dat alle rijen worden geselecteerd waarvan de id gelijk zijn en waarvan products_model LIKE de search waard is EN alle rijen waarvan de products_name LIKE de searchwaarde en de language gelijk is aan 1.
Iets verteld me dat dat niet is waar je naar op zoek bent. Met andere woorden, je hebt niet gekeken naar in welke volgorde de statements worden geevalueerd.
Nu heb ik 2 tips voor je.
1 - kijk naar operator precendence en daar waar de volgorde niet goed is gebruik haakjes om die statements heen om de juiste volgorde af te dwingen
2 - gebruik expliciete JOINS om je where statement een stuk overzichtelijker te maken. Dus in plaats van "FROM products as p,products_description as pd" doe je
"FROM products as p
LEFT JOIN products_description as pd ON p.products_id=pd.products_id"
Dan kan je namelijk die voorwaarde al uit je WHERE statement halen.