Qeury hulp nodig
hoe zorg ik ervoor dat deze query niet alleen prijs naam en id zoekt maar ook in product model.
Tabel: products
veld: products_model
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
<? if(isset($_POST['item_search']) and $_POST['item_search']!='')
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT p.products_id,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.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,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.products_name like \"%$search%\" and pd.language_id=1
ORDER by products_description.products_name";
}
else
{
$query="SELECT p.products_id,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";
} ?>
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT p.products_id,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.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,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.products_name like \"%$search%\" and pd.language_id=1
ORDER by products_description.products_name";
}
else
{
$query="SELECT p.products_id,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";
} ?>
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
//voorbeeldje
$q = "
SELECT
a.naam,
b.banaan,
c.whoeij
FROM
namen a
JOIN bananan b on b.aap = a.aap,
test c
WHERE
overzicht = 'yep'
";
?>
//voorbeeldje
$q = "
SELECT
a.naam,
b.banaan,
c.whoeij
FROM
namen a
JOIN bananan b on b.aap = a.aap,
test c
WHERE
overzicht = 'yep'
";
?>
Ten tweede is dit vatbaar voor sql injection, je controleert niet wat de gebruiker invult, (mysql_real_escape_string())
Gewijzigd op 17/01/2012 11:34:03 door Jelle -
Tis een kassa systeem die ik heb geintergreerd met een een webwinkel
Toevoeging op 17/01/2012 11:42:59:
ik heb dit nu verandert maar nog geen succes
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
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
<?
if(isset($_POST['item_search']) and $_POST['item_search']!='')
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT p.products_id,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model 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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model like \"%$search%\" and pd.language_id=1
ORDER by products_description.products_name";
}
else
{
$query="SELECT p.products_id,pd.products_name,p.products_price,p.products_model,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_model 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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model 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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model like \"%$search%\" and pd.language_id=1
ORDER by products_description.products_name";
}
else
{
$query="SELECT p.products_id,pd.products_name,p.products_price,p.products_model,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_model 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";
}
?>
Gewijzigd op 17/01/2012 11:36:19 door bjorn cornelissen
Maar heb je mijn reactie gelezen? Maak eerst de query wat leesbaarder.
En als je iets wilt zoeken in meerdere velden, misschien een OR gebruiken (p.name LIKE 'something' OR p.model LIKE 'something')
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
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
<?
if(isset($_POST['item_search']) and $_POST['item_search']!='')
{
$search=$_POST['item_search'];
$_SESSION['current_item_search']=$search;
$query="SELECT
p.products_id,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model
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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model
like
\"%$search%\" and pd.language_id=1
ORDER
by products_description.products_name";
}
else
{
$query="SELECT
p.products_id,pd.products_name,p.products_price,p.products_model,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_model 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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model
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,pd.products_name,p.products_price,p.products_model,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.products_name and p.products_model
like
\"%$search%\" and pd.language_id=1
ORDER
by products_description.products_name";
}
else
{
$query="SELECT
p.products_id,pd.products_name,p.products_price,p.products_model,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_model 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";
}
?>
heb het nu zo staan
ziet er inderdaad beter uit
Gewijzigd op 17/01/2012 12:28:16 door bjorn cornelissen
tr.tax_class_id=tc.tax_class_id and
pd.products_name and p.products_model
like \"%$search%\" and pd.language_id=1
In een WHERE clause heb je een volledige vergelijking nodig. Dus zou het zoiets moeten zijn:
tr.tax_class_id=tc.tax_class_id and
pd.products_name like \"%$search%\" and
p.products_model like \"%$search%\" and
pd.language_id=1
Waarbij je ook nog zou moeten bedenken of het AND of OR moet zijn....
(en variabelen buiten quotes halen is ook altijd een goed idee)
Alvast bedankt!
Toevoeging op 18/01/2012 10:31:32:
Hmm hij laat wel resultaten zien zonder te zoeken maar zodra ik zoek gebeurt er niet en laat het gewoon alle producten zien
Code (php)
Gewijzigd op 18/01/2012 10:54:50 door Lendl Verschoor
met and niks inderdaad
Toevoeging op 18/01/2012 11:15:22:
Als je or gebruikt laat hij alleproducten zien en nog eens 4x alle producten
Toevoeging op 18/01/2012 11:19:13:
Kunne jullie niet even mijn code aanpassen zodat hij ook product_model kan vinden want dan kan ik tenminste even door. en zal later even terug kijken hoe het zat zit nu al 2 weken er mee vast
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
p.products_id,
p.products_price,
p.products_model
pd.products_name,
tr.tax_rate
FROM
products AS p
JOIN
products_description AS pd USING products_id
tax_class AS tc ON p.tax_class_id = tc.tax_class_id
tax_rates AS tr ON tc.tax_class_id = tr.tax_class_id
WHERE
p.products_model
LIKE '%$search%' AND pd.language_id=1
ORDER BY
pd.products_name
p.products_id,
p.products_price,
p.products_model
pd.products_name,
tr.tax_rate
FROM
products AS p
JOIN
products_description AS pd USING products_id
tax_class AS tc ON p.tax_class_id = tc.tax_class_id
tax_rates AS tr ON tc.tax_class_id = tr.tax_class_id
WHERE
p.products_model
LIKE '%$search%' AND pd.language_id=1
ORDER BY
pd.products_name