Javascript Disabled Detected

You currently have javascript disabled. Several functions may not work. Please re-enable javascript to access full functionality.

Reputation extension limited to 26 databases

Submitted mat206, May 03 2012 10:53 PM | Last updated May 03 2012 10:53 PM

In IP.Content's reputation.php extension, the following code assumes a MAX of 26 databases.. which we exceed. The problem occurs in the table prefixes line where you created an array using range( 'a', 'z' ).

$rep_log_joins = array(
                        array(
                                'select'    => "p.*, p.comment_record_id as repContentID",
                                'from'        => array( 'ccs_database_comments' => 'p' ),
                                'where'        => "r.type='comment_id' AND r.type_id=p.comment_id AND r.app='ccs'",
                                'type'        => 'left'
                            ),
                    );

$letters            = range( 'a', 'z' );
$idx                = 0;
$_possibilities        = array( "p.comment_user" );

foreach( $databases as $database )
{
    $letter    = $letters[ $idx ];
    $idx++;
    
    if( $letter == 'p' OR $letter == 'r' )
    {
        $letter    = $letters[ $idx ];
    }

    $_possibilities[]    = "{$letter}.primary_id_field";
    $rep_log_joins[]    = array(
                                'select'    => "{$letter}.primary_id_field as id_field_" . $database['database_id'],
                                'from'        => array( $database['database_database'] => $letter ),
                                'where'        => "r.type='record_id_{$database['database_id']}' AND r.type_id={$letter}.primary_id_field AND r.app='ccs'",
                                'type'        => 'left',
                             );
}

The sql error looks interesting.. check this out:


mySQL query error: SELECT p.comment_user as author_id, r.*,p.*, p.comment_record_id as repContentID,a.primary_id_field as id_field_31,b.primary_id_field as id_field_1,c.primary_id_field as id_field_26,d.primary_id_field as id_field_3,e.primary_id_field as id_field_15,f.primary_id_field as id_field_12,g.primary_id_field as id_field_28,h.primary_id_field as id_field_25,i.primary_id_field as id_field_7,j.primary_id_field as id_field_16,k.primary_id_field as id_field_29,l.primary_id_field as id_field_5,m.primary_id_field as id_field_24,n.primary_id_field as id_field_4,o.primary_id_field as id_field_10,q.primary_id_field as id_field_8,q.primary_id_field as id_field_30,s.primary_id_field as id_field_2,s.primary_id_field as id_field_11,t.primary_id_field as id_field_14,u.primary_id_field as id_field_13,v.primary_id_field as id_field_22,w.primary_id_field as id_field_20,x.primary_id_field as id_field_21,y.primary_id_field as id_field_18,z.primary_id_field as id_field_19,.primary_id_field as id_field_17,.primary_id_field as id_field_23,.primary_id_field as id_field_27 FROM reputation_index r LEFT JOIN ccs_database_comments p ON ( r.type='comment_id' AND r.type_id=p.comment_id AND r.app='ccs' ) LEFT JOIN ccs_custom_database_31 a ON ( r.type='record_id_31' AND r.type_id=a.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_1 b ON ( r.type='record_id_1' AND r.type_id=b.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_26 c ON ( r.type='record_id_26' AND r.type_id=c.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_3 d ON ( r.type='record_id_3' AND r.type_id=d.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_15 e ON ( r.type='record_id_15' AND r.type_id=e.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_12 f ON ( r.type='record_id_12' AND r.type_id=f.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_28 g ON ( r.type='record_id_28' AND r.type_id=g.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_25 h ON ( r.type='record_id_25' AND r.type_id=h.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_7 i ON ( r.type='record_id_7' AND r.type_id=i.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_16 j ON ( r.type='record_id_16' AND r.type_id=j.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_29 k ON ( r.type='record_id_29' AND r.type_id=k.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_5 l ON ( r.type='record_id_5' AND r.type_id=l.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_24 m ON ( r.type='record_id_24' AND r.type_id=m.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_4 n ON ( r.type='record_id_4' AND r.type_id=n.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_10 o ON ( r.type='record_id_10' AND r.type_id=o.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_8 q ON ( r.type='record_id_8' AND r.type_id=q.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_30 q ON ( r.type='record_id_30' AND r.type_id=q.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_2 s ON ( r.type='record_id_2' AND r.type_id=s.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_11 s ON ( r.type='record_id_11' AND r.type_id=s.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_14 t ON ( r.type='record_id_14' AND r.type_id=t.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_13 u ON ( r.type='record_id_13' AND r.type_id=u.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_22 v ON ( r.type='record_id_22' AND r.type_id=v.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_20 w ON ( r.type='record_id_20' AND r.type_id=w.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_21 x ON ( r.type='record_id_21' AND r.type_id=x.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_18 y ON ( r.type='record_id_18' AND r.type_id=y.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_19 z ON ( r.type='record_id_19' AND r.type_id=z.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_17 ON ( r.type='record_id_17' AND r.type_id=.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_23 ON ( r.type='record_id_23' AND r.type_id=.primary_id_field AND r.app='ccs' ) LEFT JOIN ccs_custom_database_27 ON ( r.type='record_id_27' AND r.type_id=.primary_id_field AND r.app='ccs' ) WHERE r.app='ccs' AND r.type='comment_id' and r.id > 0 ORDER BY r.id ASC LIMIT 0,50

Status:	Fixed
Version:	2.3.1
Fixed In:	2.3.2

4 Comments

mat206

May 03 2012 11:05 PM

Didn't test this.. but what if you did something like this:


$rep_log_joins = array(
                                                array(
                                                                'select'        => "p.*, p.comment_record_id as repContentID",
                                                                'from'          => array( 'ccs_database_comments' => 'p' ),
                                                                'where'         => "r.type='comment_id' AND r.type_id=p.comment_id AND r.app='ccs'",
                                                                'type'          => 'left'
                                                        ),
                                        );

$letters                        = range( 'a', 'z' );
$idx                            = 0;
$_possibilities         = array( "p.comment_user" );

foreach( $databases as $database )
{
        $letter = implode('', array_fill(0, intval($idx/count($letters))+1, $letters[ $idx % count($letters) ]));
        $idx++;
        
        if( $letter == 'p' OR $letter == 'r' )  // I don't understand what this is for
        {
                $letter = $letters[ $idx ];
        }

        $_possibilities[]       = "{$letter}.primary_id_field";
        $rep_log_joins[]        = array(
                                                                'select'        => "{$letter}.primary_id_field as id_field_" . $database['database_id'],
                                                                'from'          => array( $database['database_database'] => $letter ),
                                                                'where'         => "r.type='record_id_{$database['database_id']}' AND r.type_id={$letter}.primary_id_field AND r.app='ccs'",
                                                                'type'          => 'left',
                                                         );
}

The idea of this line is:

        $letter = implode('', array_fill(0, intval($idx/count($letters))+1, $letters[ $idx % count($letters) ]));
        $idx++;

if $idx >= 0 && $idx <= 25 then $letter = 'a' through 'z'
if $idx >= 26 && $idx <= 52 then $letter = 'aa' through 'zz'
etc

This code is untested though..

mat206

May 03 2012 11:10 PM

Okay, this fixes it.. very simple:

foreach( $databases as $database )
{
$letter = implode('', array_fill(0, intval($idx/count($letters))+1, $letters[ $idx % count($letters) ]));

    $idx++;
    
    if( $letter == 'p' OR $letter == 'r' )
    {
        $letter    = $letter . 'bz';  // Give an alias nothing else will use
    }

    $_possibilities[]    = "{$letter}.primary_id_field";
    $rep_log_joins[]    = array(
                                'select'    => "{$letter}.primary_id_field as id_field_" . $database['database_id'],
                                'from'        => array( $database['database_database'] => $letter ),
                                'where'        => "r.type='record_id_{$database['database_id']}' AND r.type_id={$letter}.primary_id_field AND r.app='ccs'",
                                'type'        => 'left',
                             );
}

bfarber

May 04 2012 10:11 AM

For the record, the 'p' and 'r' exceptions in the loop are because we already alias r as "reputation_index" and p as "custom_database_comments", so can't reuse them in the loop.

bfarber

May 04 2012 11:29 AM

I'm using your sample code, as it appears to be fine and works in my testing.

Reputation extension limited to 26 databases

4 Comments

Sign In