Location: PHPKode > projects > PowerMovieList > powermovielist-0_14_beta/convert-structure.php
<?
$FILE_SELF = "convert-structure.php";
include("application.php");

RequestLogin(PML_LoginStyle_AccessDenied, PML_Rights_SuperAdmin);

include("top.html");

@set_time_limit(120);
ignore_user_abort(true);

//todo reserved words for tables and fields

$strSql = "SELECT ID, Name FROM $CFG[Prefix]lists WHERE Name!='Index' AND Name!='' ORDER BY ID";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$lists = array();
while($row = mysql_fetch_assoc($result)) {
    $lists[] = $row;
}

$strSql = "SHOW TABLES";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$tables = array();
while($row = mysql_fetch_row($result)) {
    $tables[] = $row[0];
}

$list = false;
for($i=0;$i<count($lists);$i++)
{
    if(!in_array("$CFG[Prefix]movies_".strtolower($lists[$i]['Name']), $tables)) {
        $list = $lists[$i];
    }
}

if($list===false) {
    if(in_array("$CFG[Prefix]movieprop", $tables)) {
        echo "<h1>renaming the old tables into *_OLD</h1>\n";
        echo "<p>They won't be needed anymore, you can delete them safe.</p>\n";
        $strSql = "ALTER TABLE $CFG[Prefix]movieprop RENAME $CFG[Prefix]movieprop_OLD";
        pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
        $strSql = "ALTER TABLE $CFG[Prefix]movielink RENAME $CFG[Prefix]movielink_OLD";
        pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
        $strSql = "ALTER TABLE $CFG[Prefix]moviepropval RENAME $CFG[Prefix]moviepropval_OLD";
        pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
        echo "<h1>convertion finished!</h1>";
    } else {
        echo "<p>Everything is allready converted to the new structure.</p>";
    }
    include("bottom.html");
    exit;
}

unset($tables);
unset($lists);

$table = "$CFG[Prefix]movies_".strtolower($list['Name']);


echo "<h1>creating structure for $list[Name]</h1>\n";
if(!preg_match('#^[a-zA-Z0-9_]+$#', $table)) {
    echo "<p>Invalid Table-Name: $table    - <b>stopped</b>!!!!!!";
    die();
}
/*
if(in_array($table, $tables)) {
    $strSql = "DROP TABLE $table";
    pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
    echo "<p>Deleted table $table</p>";
}*/

$strSql = "CREATE TABLE $table (
    MovieID BIGINT NOT NULL PRIMARY KEY,
    DateAdded datetime NOT NULL default '0000-00-00 00:00:00',
    DownloadCount int(11) NOT NULL default '0',
    UserAddedID INT DEFAULT '0' NOT NULL)";
pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
echo "<p>created {$table}</p>";

$strSql = "SELECT ID, Name, PropType FROM $CFG[Prefix]prop WHERE ListID='$list[ID]' ORDER BY SortOrder";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$props = array();
while($row = mysql_fetch_assoc($result)) {
    $props[$row['ID']] = $row;
}

$strSql = "SHOW FIELDS FROM $table";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$fields = array();
while($row = mysql_fetch_row($result)) {
    $fields[] = $row[0];
}

//get the fields from pml_movielink - if somebody hacked the script and added
//additional fields we need them!
//default is this:
//array("link"=>"TINYTEXT", "text"=>"TINYTEXT", "size"=>"SMALLINT", "dloadcount"=>"INT");
$strSql = "SHOW FIELDS FROM $CFG[Prefix]movielink";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$downloadLinkFileTypes = array();
while($row = mysql_fetch_row($result)) {
    if($row[0]=="MovieID") continue;
    if($row[0]=="PropID") continue;
    $downloadLinkFileTypes[strtolower($row[0])] = $row[1];
}

foreach($props as $prop)
{
    $field = $prop['Name'];
    if(in_array($field, $fields)) {
        echo "<p>Field does allready exist: $field    - <b>Field ignored</b>!!!!!!";
        continue;
    }
    if(!preg_match('#^[a-zA-Z0-9]+$#', $field)) {
        echo "<p>Invalid Field-Name: $field    - <b>Field ignored</b>!!!!!!";
        continue;
    }

    switch($prop['PropType']) {
        case PML_PropType_Text:
            $fieldType = "TEXT";
            break;
        case PML_PropType_Date:
            $fieldType = "DATE";
            break;
        case PML_PropType_Boolean:
            $fieldType = "INT";
            break;
        case PML_PropType_ListBox:
            $fieldType = "INT";
            break;
        case PML_PropType_Textfield:
            $fieldType = "TEXT";
            break;
        case PML_PropType_Url:
            $fieldType = "TINYTEXT";
            break;
        case PML_PropType_UrlCached:
            $fieldType = "TINYTEXT";
            break;
        case PML_PropType_FileUpload:
            $fieldType = "TINYTEXT";
            break;
        case PML_PropType_UserRating:
            $fieldType = "FLOAT";
            break;
        case PML_PropType_DownloadLink:
        case PML_PropType_DownloadLinkFileUpload:
            $fieldType = array();
            foreach($downloadLinkFileTypes as $name=>$type) {
                $fieldType[$field."_".strtolower($name)] = $type;
            }                
            break;
        case PML_PropType_StaticText:
        case PML_PropType_DateAdded:
        case PML_PropType_DisplayNr:
        case PML_PropType_Clicks:
        case PML_PropType_UserAdded:
        case PML_PropType_Comments:
            $fieldType = "";
            break;
        case PML_PropType_AutoIncrement:
            $fieldType = "INT";
            break;
        case PML_PropType_ListBoxMulti:
            $fieldType = "TEXT";
            break;
        default:
            $fieldType = "";
            echo "<p>Unknown PropType for $field</p>";
            break;
    }
    if($fieldType=="") continue; //nothing todo
    if(!is_array($fieldType)) {
        $strSql = "ALTER TABLE $table ADD `$field` $fieldType NOT NULL";
        pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
    }
    else
    {
        foreach($fieldType as $f=>$type) {
            $strSql = "ALTER TABLE $table ADD `$f` $type NOT NULL";
            pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
        }
    }
}

//select movies
$strSql = "SELECT * FROM $CFG[Prefix]movies WHERE $CFG[Prefix]movies.ListID=$list[ID]";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $strSql = "INSERT INTO $table SET MovieID='{$row['ID']}', DateAdded='{$row['DateAdded']}', DownloadCount='{$row['DownloadCount']}', UserAddedId='{$row['UserAddedID']}'";
    pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);

}
$strSql = "INSERT INTO $table SET MovieID='0', DateAdded='', DownloadCount='0', UserAddedId='0'";
pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);


$strSql = "SELECT ID, Name, PropType FROM $CFG[Prefix]prop WHERE ListID='$list[ID]' ORDER BY SortOrder";
$result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
$props = array();
while($row = mysql_fetch_assoc($result)) {
    $props[$row['ID']] = $row;
}

foreach($props as $Prop)
{
    if($Prop['PropType'] > 100) continue;

    switch($Prop['PropType']) {
        case PML_PropType_UserRating:
            //select the votes:
            $strSql = "SELECT MovieID, AVG(Vote) AS Vote FROM $CFG[Prefix]votes WHERE PropID='$Prop[ID]' GROUP BY MovieID";
            $result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $strSql = "UPDATE $table SET `$Prop[Name]`='".addslashes($row['Vote'])."' WHERE MovieID='$row[MovieID]'";
                pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            }
            break;
        case PML_PropType_ListBoxMulti:
            //select the moviepropval's
            $strSql = "SELECT MovieID, moviepropval.PropValID FROM $CFG[Prefix]moviepropval AS moviepropval, $CFG[Prefix]propval AS propval
                       WHERE propval.PropID='$Prop[ID]'
                       AND propval.ID=moviepropval.PropValID";
            $result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            $ListBoxMultiData = array();
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $ListBoxMultiData[$row['MovieID']][] = $row['PropValID'];
            }
            foreach($ListBoxMultiData as $MovieID=>$data) {
                $data = "_".implode("_", $data)."_";
                $strSql = "UPDATE $table SET `$Prop[Name]`='".addslashes($data)."' WHERE MovieID='$MovieID'";
                pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            }
            unset($ListBoxMultiData);
            break;
        case PML_PropType_ListBox:
        case PML_PropType_Boolean:
            //select the moviepropval's
            $strSql = "SELECT MovieID, moviepropval.PropValID FROM $CFG[Prefix]moviepropval AS moviepropval, $CFG[Prefix]propval AS propval
                       WHERE propval.PropID='$Prop[ID]'
                       AND propval.ID=moviepropval.PropValID";
            $result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);

            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $strSql = "UPDATE $table SET `$Prop[Name]`='".addslashes($row['PropValID'])."' WHERE MovieID='$row[MovieID]'";
                pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            }
            break;
        case PML_PropType_DownloadLink:
        case PML_PropType_DownloadLinkFileUpload:
            //select the movielink's
            $strSql = "SELECT * FROM $CFG[Prefix]movielink WHERE PropID='$Prop[ID]'";
            $result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);

            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $strSql = "UPDATE $table SET ";
                foreach($row as $fld=>$val) {
                    if($fld=="MovieID") continue;
                    if($fld=="PropID") continue;
                    $fld = strtolower($fld);
                    $strSql .= "`$Prop[Name]_$fld`='".addslashes($val)."', ";
                }
                $strSql = substr($strSql, 0, -2);
                $strSql .= " WHERE MovieID=$row[MovieID]";
                pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            }
            break;
        default:
            //select the movieprop's
            $strSql = "SELECT MovieID, Property FROM $CFG[Prefix]movieprop WHERE PropID='$Prop[ID]'";
            $result = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $strSql = "UPDATE $table SET `$Prop[Name]`='".addslashes($row['Property'])."' WHERE MovieID='$row[MovieID]'";
                pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
            }
            break;
    }
}


echo "<p>wrote all entried</p>";    


echo "<br><p><b><a href=\"$FILE_SELF\">continue converting next list</a></b></p>\n";


include("bottom.html");

?>
PHPKode Newsletter X is easy to install and configure on the most servers. Try now!
Return current item: PowerMovieList