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...
sorteren op id, desc, en dan bij de LIMIT opgeven dat je records nummer 4 t/m 25000000000 wilt ophalen. Dat laatste nummer zal ruim voldoende zijn om al jouw records te pakken...
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)...
. "\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