Recuperer une catégorie et tous se forums [Fermé]

Signaler
-
Messages postés
8
Date d'inscription
dimanche 25 mars 2007
Statut
Membre
Dernière intervention
9 mars 2017
-
Bonjour,

je dispose d'un forum tournant sous PunBB et qui est divisé en 4 catégories contenant plusieurs forums chacune.

Dans un soucis de simplification je voudrais récupérer dans une page, un seule catégorie et ses forums...
J'aurais besoin d'un coupe de main pour formuler le requette MYSQL qui me permettras de faire ce que je veux.

Par exemple j'ai une catégorie avec l'id, n°3 et je voudrai afficher tous les forums faisant partie de cette catégorie..

je pense pouvoir me débrouiller pour le reste, mais pour le requette a la base je comprends rien.

Voici la requette qui permets de récupérer toutes les catégories et tous les forums.
// Get list of forums and topics with new posts since last visit
if (!$forum_user['is_guest'])
{
	$query = array(
		'SELECT'	=> 't.forum_id, t.id, t.last_post',
		'FROM'		=> 'topics AS t',
		'JOINS'		=> array(
			array(
				'INNER JOIN'	=> 'forums AS f',
				'ON'			=> 'f.id=t.forum_id'
			),
			array(
				'LEFT JOIN'		=> 'forum_perms AS fp',
				'ON'			=> '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
			)
		),
		'WHERE'		=> '(fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>'.$forum_user['last_visit'].' AND t.moved_to IS NULL'
	);

	($hook = get_hook('in_qr_get_new_topics')) ? eval($hook) : null;
	$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

	$new_topics = array();
	while ($cur_topic = $forum_db->fetch_assoc($result))
		$new_topics[$cur_topic['forum_id']][$cur_topic['id']] = $cur_topic['last_post'];

	$tracked_topics = get_tracked_topics();
}

Et la requete qui permets de les afficher
// Print the categories and forums
$query = array(
 'SELECT' => 'c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster',
 'FROM'  => 'categories AS c',
 'JOINS'  => array(
  array(
   'INNER JOIN' => 'forums AS f',
   'ON'   => 'c.id=f.cat_id'
  ),
  array(
   'LEFT JOIN'  => 'forum_perms AS fp',
   'ON'   => '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
  )
 ),
 'WHERE'  => 'fp.read_forum IS NULL OR fp.read_forum=1',
 'ORDER BY' => 'c.disp_position, c.id, f.disp_position'
);


Si une ame charitable pouvait me donner un coup de main se serais sympas



4 réponses

Messages postés
32071
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
14 avril 2021
3 395
Bonjour,

En ajoutant dans le where :
AND c.id = 3 

ça ne marche pas ?
Merci de votre participation mais ça ne marche pas..
Messages postés
32071
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
14 avril 2021
3 395 > clyde
Pourrais tu nous fournir la structure de la table et quelques données de "test" ?
Voici la structure de la table catégories


CREATE TABLE IF NOT EXISTS `punbb_categories` (
  `id` int(10) unsigned NOT NULL,
  `cat_name` varchar(80) NOT NULL DEFAULT 'New Category',
  `disp_position` int(10) NOT NULL DEFAULT '0'
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
.

Je voudrais récupérer seulement la catégorie dont l'id =1

Ensuite la structure de la table Forums.
Je voudrais récupérer seulement les forums qui font partie de la catégorie 1

CREATE TABLE IF NOT EXISTS `punbb_forums` (
  `id` int(10) unsigned NOT NULL,
  `forum_name` varchar(80) NOT NULL DEFAULT 'New forum',
  `forum_desc` text,
  `redirect_url` varchar(100) DEFAULT NULL,
  `moderators` text,
  `num_topics` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `num_posts` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `last_post` int(10) unsigned DEFAULT NULL,
  `last_post_id` int(10) unsigned DEFAULT NULL,
  `last_poster` varchar(200) DEFAULT NULL,
  `sort_by` tinyint(1) NOT NULL DEFAULT '0',
  `disp_position` int(10) NOT NULL DEFAULT '0',
  `cat_id` int(10) unsigned NOT NULL DEFAULT '0',
  `last_post_subject` varchar(255) DEFAULT NULL,
  `fancy_simtopics_show` tinyint(1) NOT NULL DEFAULT '1',
  `fancy_simtopics_search` tinyint(1) NOT NULL DEFAULT '1',
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;


Pour ensuite afficher le tout dans une page...

Merci encore de votre intervention
J'ai oublié de vous fournir le code qui récupérè et affiche tous les forums.

// Get list of forums and topics with new posts since last visit
if (!$forum_user['is_guest'])
{
$query = array(
'SELECT' => 't.forum_id, t.id, t.last_post',
'FROM' => 'topics AS t',
'JOINS' => array(
array(
'INNER JOIN' => 'forums AS f',
'ON' => 'f.id=t.forum_id'
),
array(
'LEFT JOIN' => 'forum_perms AS fp',
'ON' => '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
)
),
'WHERE' => '(fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>'.$forum_user['last_visit'].' AND t.moved_to IS NULL'
);

($hook = get_hook('in_qr_get_new_topics')) ? eval($hook) : null;
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

$new_topics = array();
while ($cur_topic = $forum_db->fetch_assoc($result))
$new_topics[$cur_topic['forum_id']][$cur_topic['id']] = $cur_topic['last_post'];

$tracked_topics = get_tracked_topics();
}

// Setup main heading
$forum_page['main_title'] = forum_htmlencode($forum_config['o_board_title']);

($hook = get_hook('in_pre_header_load')) ? eval($hook) : null;

define('FORUM_ALLOW_INDEX', 1);
define('FORUM_PAGE', 'index');
require FORUM_ROOT.'header.php';

// START SUBST - <!-- forum_main -->
ob_start();

($hook = get_hook('in_main_output_start')) ? eval($hook) : null;

// Print the categories and forums
$query = array(
'SELECT' => 'c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster',
'FROM' => 'categories AS c',
'JOINS' => array(
array(
'INNER JOIN' => 'forums AS f',
'ON' => 'c.id=f.cat_id'
),
array(
'LEFT JOIN' => 'forum_perms AS fp',
'ON' => '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
)
),
'WHERE' => 'fp.read_forum IS NULL OR fp.read_forum=1',
'ORDER BY' => 'c.disp_position, c.id, f.disp_position'
);

($hook = get_hook('in_qr_get_cats_and_forums')) ? eval($hook) : null;
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

$forum_page['cur_category'] = $forum_page['cat_count'] = $forum_page['item_count'] = 0;

while ($cur_forum = $forum_db->fetch_assoc($result))
{
($hook = get_hook('in_forum_loop_start')) ? eval($hook) : null;

++$forum_page['item_count'];

if ($cur_forum['cid'] != $forum_page['cur_category']) // A new category since last iteration?
{
if ($forum_page['cur_category'] != 0)
echo "\t".'</div>'."\n";

++$forum_page['cat_count'];
$forum_page['item_count'] = 1;

$forum_page['item_header'] = array();
$forum_page['item_header']['subject']['title'] = '<strong class="subject-title">'.$lang_index['Forums'].'</strong>';
$forum_page['item_header']['info']['topics'] = '<strong class="info-topics">'.$lang_index['topics'].'</strong>';
$forum_page['item_header']['info']['post'] = '<strong class="info-posts">'.$lang_index['posts'].'</strong>';
$forum_page['item_header']['info']['lastpost'] = '<strong class="info-lastpost">'.$lang_index['last post'].'</strong>';

($hook = get_hook('in_forum_pre_cat_head')) ? eval($hook) : null;

$forum_page['cur_category'] = $cur_forum['cid'];

?> <div class="main-head">
<h2 class="hn"><span><?php echo forum_htmlencode($cur_forum['cat_name']) ?></span></h2>
</div>
<div class="main-subhead">
<p class="item-summary"><span><?php printf($lang_index['Category subtitle'], implode(' ', $forum_page['item_header']['subject']), implode(', ', $forum_page['item_header']['info'])) ?></span></p>
</div>
<div id="category<?php echo $forum_page['cat_count'] ?>" class="main-content main-category">
<?php

}

// Reset arrays and globals for each forum
$forum_page['item_status'] = $forum_page['item_subject'] = $forum_page['item_body'] = $forum_page['item_title'] = array();

// Is this a redirect forum?
if ($cur_forum['redirect_url'] != '')
{
$forum_page['item_body']['subject']['title'] = '<h3 class="hn"><a class="external" href="'.forum_htmlencode($cur_forum['redirect_url']).'" rel="nofollow noopener noreferrer" target="_blank" title="'.sprintf($lang_index['Link to'], forum_htmlencode($cur_forum['redirect_url'])).'"><span>'.forum_htmlencode($cur_forum['forum_name']).'</span></a></h3>';
$forum_page['item_status']['redirect'] = 'redirect';

if ($cur_forum['forum_desc'] != '')
$forum_page['item_subject']['desc'] = $cur_forum['forum_desc'];

$forum_page['item_subject']['redirect'] = '<span>'.$lang_index['External forum'].'</span>';

($hook = get_hook('in_redirect_row_pre_item_subject_merge')) ? eval($hook) : null;

if (!empty($forum_page['item_subject']))
$forum_page['item_body']['subject']['desc'] = '<p>'.implode(' ', $forum_page['item_subject']).'</p>';

// Forum topic and post count
$forum_page['item_body']['info']['topics'] = '<li class="info-topics"><span class="label">'.$lang_index['No topic info'].'</span></li>';
$forum_page['item_body']['info']['posts'] = '<li class="info-posts"><span class="label">'.$lang_index['No post info'].'</span></li>';
$forum_page['item_body']['info']['lastpost'] = '<li class="info-lastpost"><span class="label">'.$lang_index['No lastpost info'].'</span></li>';

($hook = get_hook('in_redirect_row_pre_display')) ? eval($hook) : null;
}
else
{
// Setup the title and link to the forum
$forum_page['item_title']['title'] = '<a href="'.forum_link($forum_url['forum'], array($cur_forum['fid'], sef_friendly($cur_forum['forum_name']))).'" rel="nofollow noopener noreferrer" target="_blank"><span>'.forum_htmlencode($cur_forum['forum_name']).'</span></a>';

// Are there new posts since our last visit?
if (!$forum_user['is_guest'] && $cur_forum['last_post'] > $forum_user['last_visit'] && (empty($tracked_topics['forums'][$cur_forum['fid']]) || $cur_forum['last_post'] > $tracked_topics['forums'][$cur_forum['fid']]))
{
// There are new posts in this forum, but have we read all of them already?
foreach ($new_topics[$cur_forum['fid']] as $check_topic_id => $check_last_post)
{
if ((empty($tracked_topics['topics'][$check_topic_id]) || $tracked_topics['topics'][$check_topic_id] < $check_last_post) && (empty($tracked_topics['forums'][$cur_forum['fid']]) || $tracked_topics['forums'][$cur_forum['fid']] < $check_last_post))
{
$forum_page['item_status']['new'] = 'new';
$forum_page['item_title']['status'] = '<small>'.sprintf($lang_index['Forum has new'], '<a href="'.forum_link($forum_url['search_new_results'], $cur_forum['fid']).'" rel="nofollow noopener noreferrer" target="_blank" title="'.$lang_index['New posts title'].'">'.$lang_index['Forum new posts'].'</a>').'</small>';

break;
}
}
}

($hook = get_hook('in_normal_row_pre_item_title_merge')) ? eval($hook) : null;

$forum_page['item_body']['subject']['title'] = '<h3 class="hn">'.implode(' ', $forum_page['item_title']).'</h3>';


// Setup the forum description and mod list
if ($cur_forum['forum_desc'] != '')
$forum_page['item_subject']['desc'] = $cur_forum['forum_desc'];

if ($forum_config['o_show_moderators'] == '1' && $cur_forum['moderators'] != '')
{
$forum_page['mods_array'] = unserialize($cur_forum['moderators']);
$forum_page['item_mods'] = array();

foreach ($forum_page['mods_array'] as $mod_username => $mod_id)
$forum_page['item_mods'][] = ($forum_user['g_view_users'] == '1') ? '<a href="'.forum_link($forum_url['user'], $mod_id).'" rel="nofollow noopener noreferrer" target="_blank">'.forum_htmlencode($mod_username).'</a>' : forum_htmlencode($mod_username);

($hook = get_hook('in_row_modify_modlist')) ? eval($hook) : null;

$forum_page['item_subject']['modlist'] = '<span class="modlist">'.sprintf($lang_index['Moderated by'], implode(', ', $forum_page['item_mods'])).'</span>';
}

($hook = get_hook('in_normal_row_pre_item_subject_merge')) ? eval($hook) : null;

if (!empty($forum_page['item_subject']))
$forum_page['item_body']['subject']['desc'] = '<p>'.implode(' ', $forum_page['item_subject']).'</p>';


// Setup forum topics, post count and last post
$forum_page['item_body']['info']['topics'] = '<li class="info-topics"><strong>'.forum_number_format($cur_forum['num_topics']).'</strong> <span class="label">'.(($cur_forum['num_topics'] == 1) ? $lang_index['topic'] : $lang_index['topics']).'</span></li>';
$forum_page['item_body']['info']['posts'] = '<li class="info-posts"><strong>'.forum_number_format($cur_forum['num_posts']).'</strong> <span class="label">'.(($cur_forum['num_posts'] == 1) ? $lang_index['post'] : $lang_index['posts']).'</span></li>';

if ($cur_forum['last_post'] != '')
$forum_page['item_body']['info']['lastpost'] = '<li class="info-lastpost"><span class="label">'.$lang_index['Last post'].'</span> <strong><a href="'.forum_link($forum_url['post'], $cur_forum['last_post_id']).'" rel="nofollow noopener noreferrer" target="_blank">'.format_time($cur_forum['last_post']).'</a></strong> <cite>'.sprintf($lang_index['Last poster'], forum_htmlencode($cur_forum['last_poster'])).'</cite></li>';
else
$forum_page['item_body']['info']['lastpost'] = '<li class="info-lastpost"><strong>'.$lang_common['Never'].'</strong></li>';

($hook = get_hook('in_normal_row_pre_display')) ? eval($hook) : null;
}

// Generate classes for this forum depending on its status
Messages postés
32071
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
14 avril 2021
3 395
Normalement :


// Get list of forums and topics with new posts since last visit
if (!$forum_user['is_guest'])
{
	$query = array(
		'SELECT'	=> 't.forum_id, t.id, t.last_post',
		'FROM'		=> 'topics AS t',
		'JOINS'		=> array(
			array(
				'INNER JOIN'	=> 'forums AS f',
				'ON'			=> 'f.id=t.forum_id'
			),
			array(
				'LEFT JOIN'		=> 'forum_perms AS fp',
				'ON'			=> '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
			)
		),
		'WHERE'		=> '(fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>'.$forum_user['last_visit'].' AND t.moved_to IS NULL  AND f.cat_id=1'
	);



// Print the categories and forums
$query = array(
	'SELECT'	=> 'c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster',
	'FROM'		=> 'categories AS c',
	'JOINS'		=> array(
		array(
			'INNER JOIN'	=> 'forums AS f',
			'ON'			=> 'c.id=f.cat_id'
		),
		array(
			'LEFT JOIN'		=> 'forum_perms AS fp',
			'ON'			=> '(fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].')'
		)
	),
	'WHERE'		=> 'fp.read_forum IS NULL OR fp.read_forum=1 AND f.cat_id=1',
	'ORDER BY'	=> 'c.disp_position, c.id, f.disp_position'
);


Messages postés
8
Date d'inscription
dimanche 25 mars 2007
Statut
Membre
Dernière intervention
9 mars 2017

Finalement je viens de me rendre compte que j’étais deja inscrit, du coup je me suis connecté.


Jordan, merci beaucoup de votre intervention, je vais passer a une autre solution, je savais que punbbb était assez spéciale au niveau des catégories.
Parce que ça ne fonctionne pas, la categorie que j'ai essayé d'afficher ne donne que trois forums alors que les autres sont complètes.
Je veux bien te passer un lien mais il faudra l'effacer de suite car je ne veux pas avec ce genre de demandes avec un lien.