get the articles images etc... by category (from CatagoryIntersect.php tool by User:Duesentrieb)

00-CatScan readme.txt

CatScan modes:
cs  -- pages by category
ts  -- pages by template
al  -- all pages
ss  -- stubs := (having_less_than_x_bytes_1 || less_than_x_links_1 ) in mainspace
rc  -- recent changes
iul -- all images
cl  -- all categories

10 Articles in catagory.sql

--mode CS
SELECT page_id, 
       page_namespace, 
       page_title, 
       page_restrictions, 
       group_concat(DISTINCT c.cl_to SEPARATOR '|') as cats
  FROM page 
  JOIN categorylinks as c ON c.cl_from = page_id 
 WHERE c.cl_to in ('yoga,sports')
 GROUP BY page_id 
 ORDER BY page_title;

20 mode ls.php

      $links= (int)$filter;
  
      if ( $inverse ) { #more
          $having= 'links >= '.$links;
          $left= '';
      }
      else { #smaller
          $having= 'links < '.$links;
          $left= 'LEFT ';
      }
    
      $sql= "SELECT page_id, page_namespace, page_title, page_restrictions, 
                    group_concat(DISTINCT c.cl_to SEPARATOR '|') as cats, 
                    count(DISTINCT t.pl_title) as links
            FROM page 
            JOIN categorylinks as c ON c.cl_from = page_id 
            $left JOIN pagelinks as t on t.pl_from = page_id 
            WHERE c.cl_to in (".makeSQLSet($base).")
            AND pl_namespace = ".NS_MAIN."
            AND page_namespace = ".NS_MAIN."
            AND page_is_redirect = 0
            GROUP BY page_id 
            HAVING $having
            ORDER BY page_title";
  }

28 mode al.php

if ( $mode == 'al' ) $left= 'LEFT';
          
          $sql= "SELECT page_id, page_namespace, page_title, page_restrictions, 
                        group_concat(DISTINCT c.cl_to SEPARATOR '|') as cats, 
                        group_concat(DISTINCT if (t.tl_title in (".makeSQLSet($filter)."), t.tl_title, null) SEPARATOR '|') as tags
                FROM page 
                JOIN categorylinks as c ON c.cl_from = page_id 
                $left JOIN templatelinks as t on t.tl_from = page_id 
                WHERE c.cl_to in (".makeSQLSet($base).")
                GROUP BY page_id 
                $filterHaving
                ORDER BY page_title";
      }

30 images by catagory.sql

--Select images by catagory
--With parameterization for database, namesapace and catagories
SELECT page_id, page_namespace, page_title, page_restrictions,
  		  group_concat(DISTINCT c.cl_to SEPARATOR '|') as cats,
			  IF(local.img_size IS NULL,1,0) as on_commons,
        IF(local.img_width IS NOT NULL, local.img_width, commons.img_width) as img_width,
        IF(local.img_height IS NOT NULL, local.img_height, commons.img_height) as img_height,
        IF(local.img_size IS NOT NULL, local.img_size, commons.img_size) as img_size,
        IF(local.img_major_mime IS NOT NULL, local.img_major_mime, commons.img_major_mime) as img_major_mime,
        IF(local.img_minor_mime IS NOT NULL, local.img_minor_mime, commons.img_minor_mime) as img_minor_mime
   FROM page 
   JOIN categorylinks as c ON c.cl_from = page_id 
   LEFT JOIN image as local ON page_title = local.img_name
	 LEFT JOIN ".$commons->dbname.".image as commons ON page_title = commons.img_name
	WHERE page_namespace = ".NS_IMAGE." AND c.cl_to in ('yoga,sports')
	GROUP BY page_title 
	ORDER BY page_title;

40 used images by catagory.sql

--
--
--
--
SELECT page_namespace, page_title, page_restrictions, 
       group_concat(DISTINCT cat SEPARATOR '|') as cats,
       group_concat(DISTINCT art SEPARATOR '|') as used_in,
       if(local.img_size IS NULL,1,0) as on_commons,
       if(local.img_width IS NOT NULL, local.img_width, commons.img_width) as img_width,
       if(local.img_height IS NOT NULL, local.img_height, commons.img_height) as img_height,
       if(local.img_size IS NOT NULL, local.img_size, commons.img_size) as img_size,
       if(local.img_major_mime IS NOT NULL, local.img_major_mime, commons.img_major_mime) as img_major_mime,
       if(local.img_minor_mime IS NOT NULL, local.img_minor_mime, commons.img_minor_mime) as img_minor_mime
  FROM 
       ( 
         SELECT page_namespace, page_title, page_restrictions, 
                NULL as art,
                c.cl_to as cat
       	 	 FROM page 
		       JOIN categorylinks as c ON c.cl_from = page_id 
      	  WHERE page_namespace = ".NS_IMAGE." AND c.cl_to in (".makeSQLSet($base).")
      UNION
         SELECT ".NS_IMAGE." as page_namespace, il_to as page_title,
                NULL as page_restrictions, " . /* #FIXME: join with img-page to get page_restrictions field! */ "
	          		page_title as art,
          			c.cl_to as cat
      		FROM imagelinks
		      JOIN page ON il_from = page_id 
		      JOIN categorylinks as c ON c.cl_from = page_id 
		      WHERE c.cl_to in (".makeSQLSet($base).")
            AND page_namespace = ".NS_MAIN."
            AND page_is_redirect = 0
	    ) as img
      LEFT JOIN image as local ON page_title = local.img_name
	    LEFT JOIN ".$commons->dbname.".image as commons ON page_title = commons.img_name
      GROUP BY page_title 
      ORDER BY page_title;

85 mode cs.php

if ( $mode == 'cs' ) { 
          $sql= "SELECT page_id, page_namespace, page_title, page_restrictions, 
                        group_concat(DISTINCT c.cl_to SEPARATOR '|') as cats, 
                        group_concat(DISTINCT if (t.cl_to in (".makeSQLSet($filter)."), t.cl_to, null) SEPARATOR '|') as tags
                FROM page 
                JOIN categorylinks as c ON c.cl_from = page_id 
                JOIN categorylinks as t on t.cl_from = page_id 
                WHERE c.cl_to in (".makeSQLSet($base).")
                GROUP BY page_id 
                $filterHaving
                ORDER BY page_title";
}

99 makeSQLSet.php

//the helper function:
function makeSQLSet($titles) {
    global $db;
    
    if (!is_array($titles)) {
        $titles= preg_split('! *[;/\|] *!',$titles);
    }
    
    $s= '';
    foreach ($titles as $t) {
        if ($s) $s.= ', ';
        $t= wsfDBTitle($t);
        $s.= $db->addQuotes($t);
    }
    
    return $s;
}