Forum CIGIEMA

Crazy · [↑]

La requête SQL zarbie que j'ai faite est celle-ci :

$result = $db->query('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, t.subject, t.id AS tid FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'topics AS t ON (t.last_post_id=f.last_post_id OR NULL) LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE fp.read_forum IS NULL OR fp.read_forum=1 ORDER BY c.disp_position, c.id, f.disp_position', TRUE) OR ERROR('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

C'est celle qui permet de récupérer les infos à afficher colonne de droite (dernier message) sur la page d'accueil du Forum.

Au suivant...

Alain AKA Arma

Crazy · [↑]

Je complète enfin le Post précédent que je viens d'exhumer.
En effet, la requête d'extraction des données est bien mais il manque le principe d'utilisation.
Ce code est celui qui est appliqué à ce Forum et il pourra être très facilement intégré à tout Script de Forum PunBB.

Dans /index.php,

1)

Rechercher :

Code :

// Print the categories and forums

A la ligne suivante...
Remplacer :

Code :

$result = $db->query('
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 '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp
ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].')
WHERE fp.read_forum IS NULL OR fp.read_forum=1
ORDER BY c.disp_position, c.id, f.disp_position', true) or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

Par :

Code :

$result = $db->query('
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, t.subject, t.id AS tid
FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'topics AS t
ON (t.last_post_id=f.last_post_id OR NULL) LEFT JOIN '.$db->prefix.'forum_perms AS fp
ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].')
WHERE fp.read_forum IS NULL OR fp.read_forum=1
ORDER BY c.disp_position, c.id, f.disp_position', true) or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

2)

Rechercher :

Code :

	// If there is a last_post/last_poster.

Dans le "if ($cur_forum['last_post'] != '')"...
Remplacer :

Code :

		$last_post = '<a href="'.makeurl("p", $cur_forum['last_post_id'], format_time($cur_forum['last_post'])).'#p'.$cur_forum['last_post_id'].'">'.format_time($cur_forum['last_post']).'</a> <span class="byuser">'.$lang_common['by'].' '.pun_htmlspecialchars($cur_forum['last_poster']).'</span>';

Par :

Code :

		if (strlen($cur_forum['subject']) > 40) $cur_forum['subject'] = substr($cur_forum['subject'], 0, 36).'...';
		$last_post = '<a href="'.makeurl("t", $cur_forum['tid'], $cur_forum['subject']).'" style="text-decoration:none;color:black">'.$cur_forum['subject'].'</a><br><a href="'.makeurl("p", $cur_forum['last_post_id'], format_time($cur_forum['last_post'])).'#p'.$cur_forum['last_post_id'].'" rel="nofollow">'.format_time($cur_forum['last_post']).'</a> <span class="byuser">'.$lang_common['by'].' '.pun_htmlspecialchars($cur_forum['last_poster']).'</span>';

Mettre des accolades ({}) au besoin (il y a deux lignes d'instructions).

Alain AKA Arma

Crazy · [↑]

Une autre requête assez complexe, tirée du nouveau Script V10Rxx de l'Annuaire Cigiema.
Voici juste le code SQL :

Code :

SELECT c.id, c.cat_name AS cat_name, count(c2.id) AS count1, count(s.id) AS count2 FROM $T_categories AS c
LEFT JOIN $T_categories AS c2 ON c2.id_cat_mere = c.id LEFT JOIN $T_sites AS s ON s.id_cat = c.id
WHERE c.id_cat_mere = '0' AND c.activation = '1' AND s.activation = '1'
GROUP BY cat_name ORDER BY count1 DESC, count2 DESC;

Pour :

Pour 'coins arrondis et ombres' --> http://forum.cigiema.fr/t2338-Cadres-av … s-CSS.html

Enjoy !

Alain AKA Arma

Annonces

#1 [↑] [↓]08/12/2006 10:14:55

Qui écrit du SQL bien zarbi ?

#2 [↑] [↓]06/12/2009 09:53:42

Re: Qui écrit du SQL bien zarbi ?

Code :

Code :

Code :

Code :

Code :

Code :

#3 [↑] [↓]02/09/2011 11:03:58

Re: Qui écrit du SQL bien zarbi ?

Code :

Pied de page des forums