[Mediawiki-l] Any help on sophisticated MySQL-Statemanet

Stefan Werthmann werthmann at psycholutions.de
Thu Apr 14 14:54:02 UTC 2011


Hey! Wow!

yes it's very blaming. Your code looks mutch more better!

The $e was just for convenience.

Actually I need something like this as end result:

for($i=1;$i<count($pidsArray);$i++):
            $qString = "SELECT cl_to,count(*) as cl_count from
".$wgDBprefix."categorylinks where cl_from='".$pidsArray[$i]."' group by
cl_to having cl_count >0";
            $wgRes   = $db->query($qString);
            while($row = $db->fetchObject($wgRes)):
                $categories[$i]['cl_to']=$row->cl_to;
                $categories[$i]['cl_count']=$row->cl_count; 
            endwhile;
        endfor;   

Is there a way to modify your code in this direction?

-----Ursprüngliche Nachricht-----
Von: mediawiki-l-bounces at lists.wikimedia.org
[mailto:mediawiki-l-bounces at lists.wikimedia.org] Im Auftrag von Daniel
Friesen
Gesendet: Donnerstag, 14. April 2011 16:35
An: MediaWiki announcements and site admin list
Betreff: Re: [Mediawiki-l] Any help on sophisticated MySQL-Statemanet

This will only get a  series of cl_to's. I'm not sure what that $e is for,
or what the /dev/null style fetchObject call is for, and I'm not sure what
data output you're expecting. So I'll need more information if you want
other information in the rows, a count, or a grouping of things together.

$res = $db->select(
	array('categorylinks', 'imagelinks', 'page'),
	array( 'cl_to' ),
	array(
		'page_id' =>  $pidsArray,
		'page_namespace' =>  NS_FILE,
	),
	__METHOD__,
	array(),
	array(
		'imagelinks' =>  array( 'JOIN', 'il_from=cl_from' ),
		'page' =>  array( 'JOIN', 'page_title=il_to' ),
	)
);
foreach ( $res as $row ) {
	$row->cl_to;
	// [...]
}

(Side-note; Uuuuughhh... A $wg that's not global, raw sql queries, unescaped
SQL concatenation (including what looks like an SQL injection vector),
hardcoded NS number, for() where foreach() is usable, $arr[$i] instead of
$arr[]... that code is........................ <facepalm/>)

~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]

On 11-04-14 06:34 AM, Stefan Werthmann wrote:
> Hi all!
>
>
> Could someone translate this to a single sophisticated mysql-statement 
> (perhaps with a count() on the categories-result)?
> The code below retrieves category links from pages (articles) 
> containing specific files.
> At the beginning I only have page ids from the filepages.
>
> for($i=1;$i<count($pidsArray);$i++):
>              $qString = "SELECT page_title FROM ".$wgDBprefix."page 
> where page_id='".$pidsArray[$i]."' and page_namespace=6";/* File:
namespace */
>              $wgRes   = $db->query($qString);
>              while($row = $db->fetchObject($wgRes)):
>                  $imagePages[$i]=$row->page_title;
>              endwhile;
>          endfor;
>
>
>          $e=0;
>          for($i=1;$i<  count($imagePages);$i++):
>              $qString = "SELECT il_from FROM ".$wgDBprefix."imagelinks 
> where
> il_to='".$imagePages[$i]."'";/* File: namespace */
>              $wgRes   = $db->query($qString);
>              $row = $db->fetchObject($wgRes);
>              while($row = $db->fetchObject($wgRes)):
>                      $imageLinks[$e]=$row->il_from;
>              endwhile;
>          endfor;
>
>            for($i=0;$i<count($imageLinks);$i++):
>              $qString = "SELECT cl_to FROM 
> ".$wgDBprefix."categorylinks where cl_from='".$imageLinks[$i]."'";
>              $wgRes   = $db->query($qString);
>              while($row = $db->fetchObject($wgRes)):
>                  $categories[$i]=$row->cl_to;
>              endwhile;
>          endfor;
>
>
> c u stevie

_______________________________________________
MediaWiki-l mailing list
MediaWiki-l at lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l




More information about the MediaWiki-l mailing list