bd ripper
beperking invoegen
Het is nogal een ingewikkelde sql...
dus.. de 3 eerste die ook in _content_fontpage (b.id_content) staan mogen NIET worden weergegeven
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
<?
$query = "SELECT a.id, a.title, a.introtext, a.fulltext, a.sectionid, a.catid, a.$created as created, a.created_by, a.created_by_alias, a.hits, a.images"
. "\n FROM #__content AS a"
. "\n LEFT JOIN #__content_frontpage AS b ON b.content_id = a.id"
. "\n LEFT JOIN #__users AS c ON c.id = a.created_by"
. "\n LEFT JOIN #__sections AS d ON d.id = a.sectionid"
. "\n LEFT JOIN #__categories AS e ON e.id = a.catid"
. "\n WHERE (a.state = '1')"
. "\n AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= NOW())"
. "\n AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= NOW())"
. "\n AND (d.published = '1')"
. "\n AND (e.published = '1')"
. ($set_section_id_extra ? "\n AND (a.sectionid IN ($set_section_id_extra) )" : '')
. ($set_category_id_extra ? "\n AND (a.catid IN ($set_category_id_extra) )" : '')
. ($show_frontpage == "n" ? "\n AND (b.content_id IS NULL)" : '')
. ($show_frontpage == "only" ? "\n AND (b.content_id = a.id)" : '')
. ($set_article_id ? "\n AND (a.id IN ($set_article_id) )" : '')
. ($set_author_id ? "\n AND (a.created_by IN ($set_author_id) )" : '')
. ($set_author_name ? "\n AND (c.name IN ($set_author_name))" : '')
. ($set_author_alias ? "\n AND (a.created_by_alias IN ('$set_author_alias'))" : '')
. ($set_date_range <> NULL ? "\n AND (TO_DAYS(ADDDATE(NOW(), INTERVAL $mosConfig_offset HOUR)) - TO_DAYS(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)) <= '$set_date_range' )" : '')
. ($set_date_today == "y" ? "\n AND (TO_DAYS(ADDDATE(NOW(), INTERVAL $mosConfig_offset HOUR)) = TO_DAYS(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. (isset($set_date_month) ? "\n AND ($set_date_month = MONTH(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. (isset($set_date_year) ? "\n AND ($set_date_year = YEAR(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. ( $access ? "\n AND a.access <= '$my->gid'" : '' )
. "\n ORDER BY $order_by";
?>
$query = "SELECT a.id, a.title, a.introtext, a.fulltext, a.sectionid, a.catid, a.$created as created, a.created_by, a.created_by_alias, a.hits, a.images"
. "\n FROM #__content AS a"
. "\n LEFT JOIN #__content_frontpage AS b ON b.content_id = a.id"
. "\n LEFT JOIN #__users AS c ON c.id = a.created_by"
. "\n LEFT JOIN #__sections AS d ON d.id = a.sectionid"
. "\n LEFT JOIN #__categories AS e ON e.id = a.catid"
. "\n WHERE (a.state = '1')"
. "\n AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= NOW())"
. "\n AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= NOW())"
. "\n AND (d.published = '1')"
. "\n AND (e.published = '1')"
. ($set_section_id_extra ? "\n AND (a.sectionid IN ($set_section_id_extra) )" : '')
. ($set_category_id_extra ? "\n AND (a.catid IN ($set_category_id_extra) )" : '')
. ($show_frontpage == "n" ? "\n AND (b.content_id IS NULL)" : '')
. ($show_frontpage == "only" ? "\n AND (b.content_id = a.id)" : '')
. ($set_article_id ? "\n AND (a.id IN ($set_article_id) )" : '')
. ($set_author_id ? "\n AND (a.created_by IN ($set_author_id) )" : '')
. ($set_author_name ? "\n AND (c.name IN ($set_author_name))" : '')
. ($set_author_alias ? "\n AND (a.created_by_alias IN ('$set_author_alias'))" : '')
. ($set_date_range <> NULL ? "\n AND (TO_DAYS(ADDDATE(NOW(), INTERVAL $mosConfig_offset HOUR)) - TO_DAYS(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)) <= '$set_date_range' )" : '')
. ($set_date_today == "y" ? "\n AND (TO_DAYS(ADDDATE(NOW(), INTERVAL $mosConfig_offset HOUR)) = TO_DAYS(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. (isset($set_date_month) ? "\n AND ($set_date_month = MONTH(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. (isset($set_date_year) ? "\n AND ($set_date_year = YEAR(ADDDATE($created, INTERVAL $mosConfig_offset HOUR)))" : '')
. ( $access ? "\n AND a.access <= '$my->gid'" : '' )
. "\n ORDER BY $order_by";
?>
bv tabel met 21 id's, id 1-18 wordt weergeven...
Ook andere id's die NIET in die 2de tabel voorkomen mogen worden weergegeven.
een voorbeeld
TABEL 1
id onderwerp
1 ond1
2 ond2
" "
(tot bv 10)
TABEL 2
id_content
2
3
6
7
9
Dus dan krijg je een lijst waar id 9-7-6 niet bijstaan omdat dat de 3 hoogste id's zijn van tabel 2.
dus je ziet enkel:
10 - 8 - 5 - 4 - 3 - 2 - 1
Dat is eigenlijk de bedoeling en ik weet totaal niet hoe dit in zo'n sql te steken (of de php achteraf)...
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT id
FROM tabel1
WHERE NOT EXISTS
(
SELECT id
FROM tabel2
ORDER BY id DESC
LIMIT 3
)
FROM tabel1
WHERE NOT EXISTS
(
SELECT id
FROM tabel2
ORDER BY id DESC
LIMIT 3
)
. "\n WHERE NOT EXISTS(SELECT content_id FROM #__content_frontpage ORDER BY id DESC LIMIT 3)"
Staat er nu zo:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
<?
$query = "SELECT a.id, a.title, a.introtext, a.fulltext, a.sectionid, a.catid, a.$created as created, a.created_by, a.created_by_alias, a.hits, a.images"
. "\n FROM #__content AS a"
. "\n LEFT JOIN #__content_frontpage AS b ON b.content_id = a.id"
. "\n LEFT JOIN #__users AS c ON c.id = a.created_by"
. "\n LEFT JOIN #__sections AS d ON d.id = a.sectionid"
. "\n LEFT JOIN #__categories AS e ON e.id = a.catid"
. "\n WHERE NOT EXISTS(SELECT content_id FROM #__content_frontpage ORDER BY id DESC LIMIT 3)"
. "\n AND (a.state = '1')"
---
?>
$query = "SELECT a.id, a.title, a.introtext, a.fulltext, a.sectionid, a.catid, a.$created as created, a.created_by, a.created_by_alias, a.hits, a.images"
. "\n FROM #__content AS a"
. "\n LEFT JOIN #__content_frontpage AS b ON b.content_id = a.id"
. "\n LEFT JOIN #__users AS c ON c.id = a.created_by"
. "\n LEFT JOIN #__sections AS d ON d.id = a.sectionid"
. "\n LEFT JOIN #__categories AS e ON e.id = a.catid"
. "\n WHERE NOT EXISTS(SELECT content_id FROM #__content_frontpage ORDER BY id DESC LIMIT 3)"
. "\n AND (a.state = '1')"
---
?>
Gewijzigd op 01/01/1970 01:00:00 door maks