[opgelost] query werkt. Maar niet helemaal zoals ik wil
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
CREATE TABLE anime (
id int unsigned not null auto_increment primary key,
title varchar(100) not null,
entitle varchar(100) not null,
episodes smallint unsigned not null,
licensed enum('ja', 'nee') not null default 'nee',
date_added datetime not null default '0000-00-00 00:00:00'
);
id int unsigned not null auto_increment primary key,
title varchar(100) not null,
entitle varchar(100) not null,
episodes smallint unsigned not null,
licensed enum('ja', 'nee') not null default 'nee',
date_added datetime not null default '0000-00-00 00:00:00'
);
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE TABLE anime_videos (
id bigint unsigned not null auto_increment primary key,
aid int unsigned not null,
title varchar(70) not null,
link varchar(255) not null,
views int unsigned not null,
type enum('tor', 'tube', 'google', 'divx') not null default 'tor',
date_added datetime not null default '0000-00-00 00:00:00'
);
id bigint unsigned not null auto_increment primary key,
aid int unsigned not null,
title varchar(70) not null,
link varchar(255) not null,
views int unsigned not null,
type enum('tor', 'tube', 'google', 'divx') not null default 'tor',
date_added datetime not null default '0000-00-00 00:00:00'
);
en
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
CREATE TABLE anime_images (
id tinyint unsigned not null,
aid int unsigned not null,
uid int unsigned not null,
type enum('groot', 'klein') not null default 'klein',
ext varchar(5) not null,
date_added datetime not null default '0000-00-00 00:00:00'
);
id tinyint unsigned not null,
aid int unsigned not null,
uid int unsigned not null,
type enum('groot', 'klein') not null default 'klein',
ext varchar(5) not null,
date_added datetime not null default '0000-00-00 00:00:00'
);
En heb ik deze sql 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
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
SELECT
t1.title AS anime_title,
t2.id,
t2.aid,
t2.title AS af_title,
t3.id AS imageid,
t3.ext
FROM
anime AS t1,
anime_videos AS t2,
anime_images AS t3
WHERE
t2.aid = t1.id
AND
t2.type = 'tor'
AND
t3.aid = t1.id
AND
t3.type = 'klein'
AND
t1.id = t2.aid
LIMIT 6
t1.title AS anime_title,
t2.id,
t2.aid,
t2.title AS af_title,
t3.id AS imageid,
t3.ext
FROM
anime AS t1,
anime_videos AS t2,
anime_images AS t3
WHERE
t2.aid = t1.id
AND
t2.type = 'tor'
AND
t3.aid = t1.id
AND
t3.type = 'klein'
AND
t1.id = t2.aid
LIMIT 6
Nouw doet ie het goed:) maar alleen zolang der in de tabel anime_images er een match is met anime.id dus zodra hier geen match tussen is of de tabel anime_images leeg is zegt ie dat er geen nieuwe afleveringen zijn. Terwijl er in de tabel anime_videos wel een record is die gewoon getoont kan worden op de website.
Hoe ken er dan voor zorgen dat als er geen plaatje is van die anime serie is toch staat dat er een nieuwe aflevring is?
Als er meer info nodig is vraag maar:-)
GR. mebus
Gewijzigd op 01/01/1970 01:00:00 door Mebus Hackintosh
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
t1.title AS anime_title,
t2.id,
t2.aid,
t2.title AS af_title,
t3.id AS imageid,
t3.ext
FROM
anime_videos AS t2,
anime AS t1
LEFT JOIN
anime_images AS t3
ON
t1.id = t3.aid
WHERE
t2.aid = t1.id
AND
t2.type = 'tor'
AND
t3.type = 'klein'
LIMIT 6
t1.title AS anime_title,
t2.id,
t2.aid,
t2.title AS af_title,
t3.id AS imageid,
t3.ext
FROM
anime_videos AS t2,
anime AS t1
LEFT JOIN
anime_images AS t3
ON
t1.id = t3.aid
WHERE
t2.aid = t1.id
AND
t2.type = 'tor'
AND
t3.type = 'klein'
LIMIT 6
GR. mebus
De query die ik nu heb:
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
anime.title AS anime_title,
anime_videos.id,
anime_videos.aid,
anime_videos.title AS af_title,
anime_images.id AS imageid,
anime_images.ext
FROM
anime JOIN anime_videos LEFT JOIN anime_images ON (anime_images.aid = anime.id)
WHERE
anime_videos.aid = anime.id
AND
anime_videos.type = 'tor'
AND
anime.id = anime_videos.aid
LIMIT 6
anime.title AS anime_title,
anime_videos.id,
anime_videos.aid,
anime_videos.title AS af_title,
anime_images.id AS imageid,
anime_images.ext
FROM
anime JOIN anime_videos LEFT JOIN anime_images ON (anime_images.aid = anime.id)
WHERE
anime_videos.aid = anime.id
AND
anime_videos.type = 'tor'
AND
anime.id = anime_videos.aid
LIMIT 6
Nogmaals hartstikke bedankt!
GR. mebus
Code (php)
1
2
2
WHERE anime_videos.aid = anime.id
AND anime.id = anime_videos.aid
AND anime.id = anime_videos.aid
Dit is dubbelop. 1 van de twee kun je natuurlijk weglaten...
GR. mebus