compter le nombre de site
voici 02 tables et deux affichages:
table1: #__3d_categories
id categorie_fr
1 categorie1
2 categorie2
3 categorie3
table2: #__3d_properties
id category nam_site
1 1 site1
2 2 site2
3 3 site3
Affichage:
N° categories nombre_site
1 categorie1 1
2 categorie2 1
3 categorie3 1
On remplace la seconde table par:
table2: #__3d_properties
id category nam_site
1 1,2 site1
2 1,2,3 site2
3 2,3 site3
On a resultat comme suit:
N° categories nombre_site
1 categorie1 2
2 categorie2 3
3 categorie3 2
Aide moi pour obtenir ce resultat, il suffit de changer la requête sql mais je sais pas. aide moi
Code:
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
33
34
| <?php
function listcategorie( $option ) {
global $database, $mainframe;
//echo "sdfsdkfjhqsklf";
$limit = $mainframe->getUserStateFromRequest( "viewlistlimit", 'limit', 100 );
$limitstart = $mainframe->getUserStateFromRequest( "viewcli{$option}limitstart", 'limitstart', 0 );
# get the total number of records
$database->setQuery( "SELECT count(*) FROM #__3d_categories" );
$total = $database->loadResult();
echo $database->getErrorMsg();
require_once("includes/pageNavigation.php");
$pageNav = new mosPageNav( $total, $limitstart, $limit );
$sql = "SELECT c.*, count(DISTINCT p.id) as sites, sum(p.hits) as hits"
. "\nFROM #__3d_categories AS c"
. "\nLEFT JOIN #__3d_properties AS p ON c.id=p.category"
. "\nGROUP BY c.id"
. "\nORDER BY c.categorie_fr ASC"
. "\nLIMIT $pageNav->limitstart,$pageNav->limit";
$database->setQuery($sql);
if(!$result = $database->query()) {
echo $database->stderr();
return false;
}
$rows = $database->loadObjectList();
HTML_annu::listcategorie( $rows, $pageNav, $option );
}
?> |
