MySQL(i) - UNION ALL, Num_rows
Hier mijn 'query':
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
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
<?php
$qSearch = $objSql->query("
(SELECT
name, 'movie' AS section
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
UNION ALL
(SELECT
name, 'genre' AS section
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
UNION ALL
(SELECT
name, 'reek' AS section
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
ORDER BY
CASE WHEN
'".$_POST['rank']."' = 'alphabetical'
THEN
name
ELSE
section
END
");
[/code]
Nu krijg ik als de rank post section is genre, movie en reek onder elkaar.
Bijv:
[quote]Genre
Action
Urban
Movie
basblabl
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek
Alle Oz dingen!!![/quote]
Maar nu wil ik achter Genre, Movie en Reek tussen haakjes het aantal gevonden genres, movies of reeks.
Dit dus:
[quote]Genre (2)
Action
Urban
Movie (7)
basblabl
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek (1)
Alle Oz dingen!!![/quote]
Normaal gesproken wordt het aantal records met num_rows gedaan, maar dan krijg ik het totaal aantal gevonden records.
Iemand een idee?
M.v.g.
Dennis Mertens
$qSearch = $objSql->query("
(SELECT
name, 'movie' AS section
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
UNION ALL
(SELECT
name, 'genre' AS section
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
UNION ALL
(SELECT
name, 'reek' AS section
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%')
ORDER BY
CASE WHEN
'".$_POST['rank']."' = 'alphabetical'
THEN
name
ELSE
section
END
");
[/code]
Nu krijg ik als de rank post section is genre, movie en reek onder elkaar.
Bijv:
[quote]Genre
Action
Urban
Movie
basblabl
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek
Alle Oz dingen!!![/quote]
Maar nu wil ik achter Genre, Movie en Reek tussen haakjes het aantal gevonden genres, movies of reeks.
Dit dus:
[quote]Genre (2)
Action
Urban
Movie (7)
basblabl
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek (1)
Alle Oz dingen!!![/quote]
Normaal gesproken wordt het aantal records met num_rows gedaan, maar dan krijg ik het totaal aantal gevonden records.
Iemand een idee?
M.v.g.
Dennis Mertens
Ik ben even verder gegaan en ben nu bezig met multi_query, more_results en next_result.
Mijn query ziet er nu zo uit:
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$objSql->multi_query("
SELECT
name, 'genre' AS section
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
;
SELECT
name, 'movie' AS section
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
;
SELECT
name, 'reek' AS section
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
");
[/code]Helaas kan ik nu nog werken met ORDER BY name, zodat de resultaten van de verschillende queries door elkaar staan (alfabetisch).
$objSql->multi_query("
SELECT
name, 'genre' AS section
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
;
SELECT
name, 'movie' AS section
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
;
SELECT
name, 'reek' AS section
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
");
[/code]Helaas kan ik nu nog werken met ORDER BY name, zodat de resultaten van de verschillende queries door elkaar staan (alfabetisch).
Even onderbouwen. Want met COUNT bereik ik niks.
Edit
Query
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
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
<?php
$qSearch = $objSql->query("
(SELECT
name, 'movie' AS section, COUNT(*) AS total
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
UNION ALL
(SELECT
name, 'genre' AS section, COUNT(*) AS total
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
UNION ALL
(SELECT
name, 'reek' AS section, COUNT(*) AS total
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
ORDER BY
CASE WHEN
'".$objSql->real_escape_string($_POST['rank'])."'='alphabetical'
THEN
name
ELSE
section
END
");
[/code]
[b]Resultaat[/b]
[code]Genre (1)
Humor
Action
Movie (1)
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek (1)
Alle Oz dingen!!!
OZ!!!!![/code]
$qSearch = $objSql->query("
(SELECT
name, 'movie' AS section, COUNT(*) AS total
FROM
movie
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
UNION ALL
(SELECT
name, 'genre' AS section, COUNT(*) AS total
FROM
genre
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
UNION ALL
(SELECT
name, 'reek' AS section, COUNT(*) AS total
FROM
reek
WHERE
name LIKE '%".$objSql->real_escape_string($_POST['term'])."%'
GROUP BY
name)
ORDER BY
CASE WHEN
'".$objSql->real_escape_string($_POST['rank'])."'='alphabetical'
THEN
name
ELSE
section
END
");
[/code]
[b]Resultaat[/b]
[code]Genre (1)
Humor
Action
Movie (1)
OZ season 4
OZ season 1
OZ season 5
OZ season 2
OZ season 6
OZ season 3
Reek (1)
Alle Oz dingen!!!
OZ!!!!![/code]
Gewijzigd op 01/01/1970 01:00:00 door Dennis Mertens
Maar ik wil het zo compact mogelijk houden.
Maar iig bedankt voor je 'moeite'.