Location: PHPKode > projects > News Publishing Content Management System > news-publishing-content-management-system/jpowered/newscms/admin/include/dbutils.php
<?php
/* ---------------------------------------------------------------- */
/* New Publishing  v2.3                                             */
/* by JPowered.com                                                  */
/* http://www.jpowered.com                                          */
/* ---------------------------------------------------------------- */
/*                                                                  */
/* Database Utility Functions                                       */
/*                                                                  */
/* Version History                                                  */
/* ---------------                                                  */
/* v2.3 - Pat OBrien - 15th May 2006                                */
/*      - Initial Release                                           */
/*                                                                  */
/*                                                                  */
/*                                                                  */
/* Function List                                                    */
/* -------------                                                    */
/* systemError($errorMessage)                                       */
/*    generate system error page                                    */
/*                                                                  */
/* testSession()                                                    */
/*    checks for an existing session and return the userid of that  */
/*    session. If no sesssion exists then 0 is returned.            */
/*                                                                  */
/* ---------------------------------------------------------------- */

/*
Table Name and Record definitions
*/
$tabledef["userdetails"][0] = "userid";
$tabledef["userdetails"][1] = "accesslevel";
$tabledef["userdetails"][2] = "username";
$tabledef["userdetails"][3] = "password";
$tabledef["userdetails"][4] = "email";
$tabledef["userdetails"][5] = "name";
$tabledef["userdetails"][6] = "lastlogin";

$tabledef["categories"][0] = "categoryid";
$tabledef["categories"][1] = "parentcategoryid";
$tabledef["categories"][2] = "shortname";
$tabledef["categories"][3] = "name";
$tabledef["categories"][4] = "sortorder";
$tabledef["categories"][5] = "description";
$tabledef["categories"][6] = "templateid";
$tabledef["categories"][7] = "status";
$tabledef["categories"][8] = "nviews";
$tabledef["categories"][9] = "lastresetdate";

$tabledef["articles"][0]  = "newsid";
$tabledef["articles"][1]  = "headline";
$tabledef["articles"][2]  = "categoryid";
$tabledef["articles"][3]  = "filename";
$tabledef["articles"][4]  = "datecreated";
$tabledef["articles"][5]  = "createdbyuserid";
$tabledef["articles"][6]  = "lastmodifieddate";
$tabledef["articles"][7]  = "lastmodifiedbyuserid";
$tabledef["articles"][8]  = "status";
$tabledef["articles"][9]  = "nviews";
$tabledef["articles"][10] = "lastresetdate";
$tabledef["articles"][11] = "templateid";

$tabledef["templates"][0] = "templateid";
$tabledef["templates"][1] = "status";
$tabledef["templates"][2] = "templatefile";

$tabledef["system"][0] = "systemid";
$tabledef["system"][1] = "paramname";
$tabledef["system"][2] = "paramvalue";

// Report all errors except E_NOTICE
error_reporting(E_ALL ^ E_NOTICE);

/*
General db functions


*/


function encryptRecord($record) {

    // remove the new line character
    $record = str_replace("\r\n", "", $record);
    $record = str_replace("\n", "", $record);

    stripslashes($record);

    $record = strrev($record);
    $record = base64_encode($record)."\n";

    return $record;
}


function decryptRecord($record) {

    $record = base64_decode($record);
    $record = strrev($record);

    // remove the new line character
    $record = str_replace("\r\n", "", $record);
    $record = str_replace("\n", "", $record);

    stripslashes($record);

    return $record;
}



function getRecordByID($tablename,$id) {

    global $tabledef;
    global $datadir;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";

    // open the file
    if (!file_exists($filename)) {return false;}
    $handle = fopen($filename, "rb");
    if (!$handle) {return false;}

    // find the record with this id
    $found = false;
    $tablerecord[$tabledef[$tablename]["0"]] = false;
    while (!$found && !feof($handle) ) {
        $record = fgets($handle);

        $record = decryptRecord($record);
        $elements = explode("#@#", $record);

        if (trim($id) == trim($elements[1])) {

            if (strcmp($elements[0],"D")<>0) {
                $found = true;
                for ($i=0;$i<count($tabledef[$tablename]);$i++) {
                    $tablerecord[$tabledef[$tablename][$i]] = $elements[$i+1];
                }
            }


        }

    }

    // close the file
    fclose($handle);


    // return the result
    if ($found) {
        return $tablerecord;
    }
    else {
        return false;
    }

}

function getRecordByColname($tablename,$tablecolumn,$value) {

    global $tabledef;
    global $datadir;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";

    // open the file
    if (!file_exists($filename)) {return false;}
    $handle = fopen($filename, "rb");
    if (!$handle) {return false;}

    // establish which column in the table
    // we are looking at
    $colindex = -1;
    for ($i=0;$i<count($tabledef[$tablename]);$i++) {
        if (strcmp($tabledef[$tablename][$i],$tablecolumn) == 0) {
            $colindex = $i;
        }
    }

    // if column not found then return
    if ($colindex<0) {return false;}

    // find the record where the column matches the value
    $found = false;
    $tablerecord[$tabledef[$tablename]["0"]] = false;
    while (!$found && !feof($handle) ) {
        $record = fgets($handle);

        $record = decryptRecord($record);
        $elements = explode("#@#", $record);

        if (strcmp($value,$elements[$colindex+1])==0) {

            if (strcmp($elements[0],"D")<>0) {
                $found = true;
                for ($i=0;$i<count($tabledef[$tablename]);$i++) {
                    $tablerecord[$tabledef[$tablename][$i]] = $elements[$i+1];
                }

            }


        }

    }

    // close the file
    fclose($handle);

    // return the result
    if ($found) {
        return $tablerecord;
    }
    else {
        return false;
    }




}



function addRecord($tablename,$valuearray) {

    global $tabledef;
    global $datadir;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";

    // open the file
    $handle = fopen($filename, "a+b");
    if (!$handle) {return false;}

    // read in the entire file
    $records = array();
    while (!feof($handle)) {
        $records[] = fgets($handle);
    }

    $highestID = getHighestID($records);

    $tablerecord[$tabledef[$tablename][0]] = $highestID + 1;
    $thisid = $tablerecord[$tabledef[$tablename][0]];

    // build and encrypt the new record
    $record = " #@#".$tablerecord[$tabledef[$tablename][0]];

    for ($i=0;$i<count($valuearray);$i++) {
        $record .= "#@#".$valuearray[ $tabledef[$tablename][$i+1] ];
    }
    $record .= "#@#\n";

    $record = encryptRecord($record);

    // add the new record to the end of the file
    fwrite($handle, $record);

    // close the file
    fclose($handle);


    return $thisid;

}

// get the highest id of any record
function getHighestID($records) {

    $highestID = 0;

	foreach ($records as $record) {

		$record = decryptRecord($record);
		$elements = explode("#@#", $record);

		if (strlen($elements[1])>0) {

			$thisID = (int)$elements[1];

			if ($thisID > $highestID) {
				$highestID = $thisID;
			}
		}
	}

	return $highestID;
}

// Remove duplicate IDs from a table
function repairTable($lines) {

	$newLines = array();
	$IDs      = array();

	foreach ($lines as $line) {

		$thisID = 0;

		$record = decryptRecord($line);
		$elements = explode("#@#", $record);

		if (strlen($elements[1])>0) {
			$thisID = (int)$elements[1];
		}

		if ($thisID > 0 && !in_array($thisID,$IDs)) {
			$newLines[] = $line;
			$IDs[] = $thisID;
		}

	}

	return $newLines;
}


function updateRecord($tablename,$valuearray) {

    global $tabledef;
    global $datadir;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";
    if (!file_exists($filename)) {return false;}


    // load the file contents into an array
    $lines = file($filename);
    if (!$lines) {return false;}

    // Repair the table removing any duplicates
    $lines = repairTable($lines);


    // re-open the file so it is cleared
    $handle = fopen($filename,"w");
    if (!$handle) {return false;}

    // write the contents back to the file
    foreach($lines as $line) {

        $line = decryptRecord($line);
        $elements = explode("#@#", $line);

        if (trim($valuearray[ $tabledef[$tablename][0] ]) == trim($elements[1])) {
            $line = " ";
            for ($i=0;$i<count($valuearray);$i++) {
                $line .= "#@#".$valuearray[ $tabledef[$tablename][$i] ];
            }
            $line .= "#@#\n";
        }

        $line = encryptRecord($line);

        fwrite($handle, $line);

    }

    // close the file
    fclose($handle);



    return true;
}



function deleteRecord($tablename,$id) {

    global $tabledef;
    global $datadir;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";
    if (!file_exists($filename)) {return false;}

    // load the file contents into an array
    $lines = file($filename);
    if (!$lines) {return false;}

    // re-open the file so it is cleared
    $handle = fopen($filename,"w");
    if (!$handle) {return false;}

    // write the contents back to the file setting the
    // record of $id to deleted
    foreach($lines as $line) {

        $line = decryptRecord($line);
        $elements = explode("#@#", $line);

        if (trim($id) == trim($elements[1])) {
            $line   = "D";
            for ($i=1;$i<count($elements);$i++) {
                $line .= "#@#".$elements[$i];
            }
            $line .= "#@#\n";
        }
        $line = encryptRecord($line);
        fwrite($handle, $line);

    }

    // close the file
    fclose($handle);



    return true;
}






/*
Mutliple Record Return Functions
*/

function getAllRecords($tablename,$sortcolumn,$sorttype) {

    global $tabledef;
    global $datadir;
    global $sortcolumnname;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";

    // open the file
    if (!file_exists($filename)) {return false;}
    $handle = fopen($filename, "rb");
    if (!$handle) {return false;}


    // read all the records into the table array
    $recordcount = 0;
    $tablerecord[$recordcount][$tabledef[$tablename]["0"]] = false;

    while (!feof($handle) ) {
        $record = fgets($handle);
        $record = decryptRecord($record);
        $elements = explode("#@#", $record);

        if (strcmp($elements[0],"D")<>0 && $elements[1]>0) {
            for ($i=0;$i<count($tabledef[$tablename]);$i++) {
                $tablerecord[$recordcount][$tabledef[$tablename][$i]] = $elements[$i+1];
            }
            $recordcount++;
        }

    }


    // sort the result
    $sortcolumnname = $sortcolumn;
    switch ($sorttype) {
        case "stringasc":
            usort($tablerecord, "strcompareasc");
        break;

        case "stringdesc":
            usort($tablerecord, "strcomparedesc");
        break;

        case "numberasc":
            usort($tablerecord, "numcompareasc");
        break;

        case "numberdesc":
            usort($tablerecord, "numcomparedesc");
        break;

        case "dateasc":
            usort($tablerecord, "datecompareasc");
        break;

        case "datedesc":
            usort($tablerecord, "datecomparedesc");
        break;


    }


    // close the file
    fclose($handle);


    // return the result
    return $tablerecord;


}



function getAllRecordsColname($tablename,$tablecolumn,$value,$sortcolumn,$sorttype) {

    global $tabledef;
    global $datadir;
    global $sortcolumnname;

    // check tablename is valid
    if (strlen($tabledef[$tablename][0]) < 1) {return false;}

    // construct the filename for this table
    $filename = $datadir.$tablename.".jdb";

    // open the file
    if (!file_exists($filename)) {return false;}
    $handle = fopen($filename, "rb");
    if (!$handle) {return false;}


    // establish which column in the table
    // we are looking at
    $colindex = -1;
    for ($i=0;$i<count($tabledef[$tablename]);$i++) {
        if (strcmp($tabledef[$tablename][$i],$tablecolumn) == 0) {
            $colindex = $i;
        }
    }

    // if column not found then return
    if ($colindex<0) {return false;}

    // read all the records into the table array
    $recordcount = 0;
    $tablerecord[$recordcount][$tabledef[$tablename]["0"]] = false;

    while (!feof($handle) ) {
        $record = fgets($handle);
        $record = decryptRecord($record);
        $elements = explode("#@#", $record);

        if (strcmp($value,$elements[$colindex+1])==0) {
            if (strcmp($elements[0],"D")<>0 && $elements[1]>0) {
                for ($i=0;$i<count($tabledef[$tablename]);$i++) {
                    $tablerecord[$recordcount][$tabledef[$tablename][$i]] = $elements[$i+1];
                }
                $recordcount++;
            }

        }
    }


    // sort the result
    $sortcolumnname = $sortcolumn;
    switch ($sorttype) {
        case "stringasc":
            usort($tablerecord, "strcompareasc");
        break;

        case "stringdesc":
            usort($tablerecord, "strcomparedesc");
        break;

        case "numberasc":
            usort($tablerecord, "numcompareasc");
        break;

        case "numberdesc":
            usort($tablerecord, "numcomparedesc");
        break;

        case "dateasc":
            usort($tablerecord, "datecompareasc");
        break;

        case "datedesc":
            usort($tablerecord, "datecomparedesc");
        break;

    }


    // close the file
    fclose($handle);


    // return the result
    return $tablerecord;


}









/*
Sort Functions
*/

function strcompareasc($a, $b) {
    global $sortcolumnname;
    return strcmp($a[$sortcolumnname], $b[$sortcolumnname]);
}

function strcomparedesc($a, $b) {
    global $sortcolumnname;
    return -strcmp($a[$sortcolumnname], $b[$sortcolumnname]);
}

function numcompareasc($a, $b) {
    global $sortcolumnname;
    if ($a[$sortcolumnname] == $b[$sortcolumnname]) {
        return 0;
    }
    return ($a[$sortcolumnname] < $b[$sortcolumnname]) ? -1 : 1;
}

function numcomparedesc($a, $b) {
    global $sortcolumnname;
    if ($a[$sortcolumnname] == $b[$sortcolumnname]) {
        return 0;
    }
    return ($a[$sortcolumnname] < $b[$sortcolumnname]) ? 1 : -1;
}

function datecompareasc($a, $b) {
    global $sortcolumnname;

    $date1 = strtotime($a[$sortcolumnname]);
    $date2 = strtotime($b[$sortcolumnname]);

    if ($date1 == $date2) {
        return 0;
    }
    return ($date1 < $date2) ? -1 : 1;
}

function datecomparedesc($a, $b) {
    global $sortcolumnname;

    $date1 = strtotime($a[$sortcolumnname]);
    $date2 = strtotime($b[$sortcolumnname]);

    if ($date1 == $date2) {
        return 0;
    }
    return ($date1 < $date2) ? 1 : -1;
}





?>
Return current item: News Publishing Content Management System