PHP DB_fetch_row函数代码示例

function db($user, $password)
{
    $_SESSION['UserID'] = $user;
    $sql = "SELECT userid,\n\t\t\t\t\t\taccesslevel\n\t\t\t\tFROM www_users\n\t\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\t\tAND (password='" . CryptPass(DB_escape_string($password)) . "'\n\t\t\t\tOR  password='" . DB_escape_string($password) . "')";
    $Auth_Result = DB_query($sql, $_SESSION['db']);
    $myrow = DB_fetch_row($Auth_Result);
    if (DB_num_rows($Auth_Result) > 0) {
        $sql = 'SELECT tokenid FROM securitygroups
					WHERE secroleid =  ' . $_SESSION['AccessLevel'];
        $Sec_Result = DB_query($sql, $db);
        $_SESSION['AllowedPageSecurityTokens'] = array();
        if (DB_num_rows($Sec_Result) == 0) {
            return NoAuthorisation;
        } else {
            $i = 0;
            while ($myrow = DB_fetch_row($Sec_Result)) {
                $_SESSION['AllowedPageSecurityTokens'][$i] = $myrow[0];
                $i++;
            }
        }
        return $_SESSION['db'];
    } else {
        return NoAuthorisation;
    }
}

 function studentsRegisteredForSubject($subject, $period, $stream, $db)
 {
     $sql = "SELECT COUNT(*) FROM registered_students WHERE subject_id='{$subject}' AND period_id='{$period}' AND class_id='{$stream}'";
     $result = DB_query($sql, $db);
     $myrow = DB_fetch_row($result);
     return $myrow[0];
 }

function VerifyAreaCodeDoesntExist($AreaCode, $i, $Errors, $db)
{
    $Searchsql = "SELECT COUNT(areacode)\n\t\t\t\t\t FROM areas\n\t\t\t\t\t  WHERE areacode='" . $AreaCode . "'";
    $SearchResult = DB_query($Searchsql);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] > 0) {
        $Errors[$i] = AreaCodeNotSetup;
    }
    return $Errors;
}

function VerifyTaxProvinceId($TaxProvinceId, $i, $Errors, $db)
{
    $Searchsql = "SELECT COUNT(taxprovinceid)\n\t\t\t\t\t\tFROM taxprovinces\n\t\t\t\t\t\tWHERE taxprovinceid='" . $TaxProvinceId . "'";
    $SearchResult = DB_query($Searchsql);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] == 0) {
        $Errors[$i] = TaxProvinceIdNotSetup;
    }
    return $Errors;
}

function VerifyTaxProvinceId($TaxProvinceId, $i, $Errors, $db)
{
    $Searchsql = 'SELECT COUNT(taxprovinceid) FROM taxprovinces WHERE taxprovinceid="' . $TaxProvinceId . '"';
    $SearchResult = DB_query($Searchsql, $db);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] == 0) {
        $Errors[$i] = TaxProvinceIdNotSetup;
    }
    return $Errors;
}

 function studentTakesSubject($student, $subject, $period, $db)
 {
     $studentTakesThisSubject = 0;
     $sql = "SELECT student_id FROM registered_students WHERE subject_id='{$subject}' AND period_id='{$period}' AND student_id='{$student}'";
     $result = DB_query($sql, $db);
     $num = DB_fetch_row($result);
     if ($num[0] > 0) {
         $studentTakesThisSubject = 1;
     }
     return $studentTakesThisSubject;
 }

function DisplayBOMItems($UltimateParent, $Parent, $Component, $Level, $db)
{
    global $ParentMBflag;
    // Modified by POPAD&T
    $sql = "SELECT bom.component,\n\t\t\t\tstockmaster.description,\n\t\t\t\tlocations.locationname,\n\t\t\t\tworkcentres.description,\n\t\t\t\tbom.quantity,\n\t\t\t\tbom.effectiveafter,\n\t\t\t\tbom.effectiveto,\n\t\t\t\tstockmaster.mbflag,\n\t\t\t\tbom.autoissue,\n\t\t\t\tstockmaster.controlled,\n\t\t\t\tlocstock.quantity AS qoh,\n\t\t\t\tstockmaster.decimalplaces\n\t\t\tFROM bom,\n\t\t\t\tstockmaster,\n\t\t\t\tlocations,\n\t\t\t\tworkcentres,\n\t\t\t\tlocstock\n\t\t\tWHERE bom.component='" . $Component . "'\n\t\t\tAND bom.parent = '" . $Parent . "'\n\t\t\tAND bom.component=stockmaster.stockid\n\t\t\tAND bom.loccode = locations.loccode\n\t\t\tAND locstock.loccode=bom.loccode\n\t\t\tAND bom.component = locstock.stockid\n\t\t\tAND bom.workcentreadded=workcentres.code\n\t\t\tAND stockmaster.stockid=bom.component";
    $ErrMsg = _('Could not retrieve the BOM components because');
    $DbgMsg = _('The SQL used to retrieve the components was');
    $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
    //echo $TableHeader;
    $RowCounter = 0;
    while ($myrow = DB_fetch_row($result)) {
        $Level1 = str_repeat('- ', $Level - 1) . $Level;
        if ($myrow[7] == 'B' or $myrow[7] == 'K' or $myrow[7] == 'D') {
            $DrillText = '%s%s';
            $DrillLink = '<div class="centre">' . _('No lower levels') . '</div>';
            $DrillID = '';
        } else {
            $DrillText = '<a href="%s&Select=%s">' . _('Drill Down');
            $DrillLink = htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?';
            $DrillID = $myrow[0];
        }
        if ($ParentMBflag != 'M' and $ParentMBflag != 'G') {
            $AutoIssue = _('N/A');
        } elseif ($myrow[9] == 0 and $myrow[8] == 1) {
            //autoissue and not controlled
            $AutoIssue = _('Yes');
        } elseif ($myrow[9] == 0) {
            $AutoIssue = _('No');
        } else {
            $AutoIssue = _('N/A');
        }
        if ($myrow[7] == 'D' or $myrow[7] == 'K' or $myrow[7] == 'A' or $myrow[7] == 'G') {
            $QuantityOnHand = _('N/A');
        } else {
            $QuantityOnHand = number_format($myrow[10], $myrow[11]);
        }
        printf('<td>%s</td>
				<td>%s</td>
			    <td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td class="number">%s</td>
				<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td class="number">%s</td>
				<td><a href="%sSelect=%s&SelectedComponent=%s">' . _('Edit') . '</a></td>
				<td>' . $DrillText . '</a></td>
				 <td><a href="%sSelect=%s&SelectedComponent=%s&delete=1&ReSelect=%s">' . _('Delete') . '</a></td>
				 </tr>', $Level1, $myrow[0], $myrow[1], $myrow[2], $myrow[3], $myrow[4], ConvertSQLDate($myrow[5]), ConvertSQLDate($myrow[6]), $AutoIssue, $QuantityOnHand, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow[0], $DrillLink, $DrillID, htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?', $Parent, $myrow[0], $UltimateParent);
    }
    //END WHILE LIST LOOP
}

function VerifyAreaCodeDoesntExist($AreaCode, $i, $Errors, $db)
{
    $Searchsql = 'SELECT COUNT(areacode)
					 FROM areas
					  WHERE areacode="' . $AreaCode . '"';
    $SearchResult = DB_query($Searchsql, $db);
    $answer = DB_fetch_row($SearchResult);
    if ($answer[0] > 0) {
        $Errors[$i] = AreaCodeNotSetup;
    }
    return $Errors;
}

function backup_tables($host, $user, $pass, $tables = '*', $db)
{
    //get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = DB_query('SHOW TABLES', $db);
        while ($row = DB_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
    //cycle through
    foreach ($tables as $table) {
        $result = DB_query('SELECT * FROM ' . $table, $db);
        $num_fields = DB_num_fields($result);
        $num_rows = DB_num_rows($result);
        $return .= 'DROP TABLE IF EXISTS ' . $table . ';';
        $row2 = DB_fetch_row(DB_query('SHOW CREATE TABLE ' . $table, $db));
        $return .= "\n\n" . $row2[1] . ";\n\n";
        $return .= 'INSERT INTO ' . $table . ' VALUES';
        for ($i = 0; $i < $num_fields; $i++) {
            $last = 0;
            while ($row = DB_fetch_row($result)) {
                $last = $last + 1;
                $return .= '(';
                for ($j = 0; $j < $num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n", "\\n", $row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j < $num_fields - 1 and isset($row[$j])) {
                        $return .= ',';
                    }
                }
                if ($last == $num_rows) {
                    $return .= ");\n";
                } else {
                    $return .= "),";
                }
            }
        }
        $return .= "\n\n\n";
    }
    //save file
    $handle = fopen('db-backup-' . time() . '-' . md5(implode(',', $tables)) . '.sql', 'w+');
    fwrite($handle, $return);
    fclose($handle);
    prnMsg(_(' back up successful'), 'success');
}

function db($user, $password)
{
    $_SESSION['UserID'] = $user;
    $sql = "SELECT userid\n\t\t\tFROM www_users\n\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\tAND (password='" . CryptPass(DB_escape_string($password)) . "'\n\t\t\tOR  password='" . DB_escape_string($password) . "')";
    $Auth_Result = DB_query($sql, $_SESSION['db']);
    $myrow = DB_fetch_row($Auth_Result);
    if (DB_num_rows($Auth_Result) > 0) {
        return $_SESSION['db'];
    } else {
        return NoAuthorisation;
    }
}

function GetExpiryDate($StockID, $LocCode, $BundleRef)
{
    global $db;
    $SQL = "SELECT expirationdate \n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid = '" . $StockID . "'\n\t\t\t\tAND loccode = '" . $LocCode . "'\n\t\t\t\tAND serialno = '" . $BundleRef . "'";
    $Result = DB_query($SQL);
    if (DB_num_rows($Result) == 0) {
        return '0000-00-00';
    } else {
        $myrow = DB_fetch_row($Result);
        return ConvertSQLDate($myrow[0]);
    }
}

function ValidBundleRef($StockID, $LocCode, $BundleRef)
{
    global $db;
    $SQL = "SELECT quantity\n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid='" . $StockID . "'\n\t\t\t\tAND loccode ='" . $LocCode . "'\n\t\t\t\tAND serialno='" . $BundleRef . "'";
    $Result = DB_query($SQL, $db);
    if (DB_num_rows($Result) == 0) {
        return 0;
    } else {
        $myrow = DB_fetch_row($Result);
        return $myrow[0];
        /*The quantity in the bundle */
    }
}

function CheckForRecursiveGroup($ParentGroupName, $GroupName, $db)
{
    $ErrMsg = _('An error occurred in retrieving the account groups of the parent account group during the check for recursion');
    $DbgMsg = _('The SQL that was used to retrieve the account groups of the parent account group and that failed in the process was');
    do {
        $sql = "SELECT parentgroupname FROM accountgroups WHERE groupname='" . $GroupName . "'";
        $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
        $myrow = DB_fetch_row($result);
        if ($ParentGroupName == $myrow[0]) {
            return true;
        }
        $GroupName = $myrow[0];
    } while ($myrow[0] != '');
    return false;
}

 function Offer($Supplier)
 {
     /*Constructor function initialises a new purchase offer object */
     global $db;
     $this->LineItems = array();
     $this->total = 0;
     $this->LinesOnOffer = 0;
     $this->SupplierID = $Supplier;
     $sql = "SELECT suppname,\n\t\t\t\t\temail,\n\t\t\t\t\tcurrcode\n\t\t\t\tFROM suppliers\n\t\t\t\tWHERE supplierid='" . $this->SupplierID . "'";
     $result = DB_query($sql, $db);
     $myrow = DB_fetch_row($result);
     $this->SupplierName = $myrow[0];
     $this->EmailAddress = $myrow[1];
     $this->CurrCode = $myrow[2];
 }

function CheckForRecursiveGroup($ParentGroupName, $GroupName, $db)
{
    /* returns true ie 1 if the group contains the parent group as a child group
    ie the parent group results in a recursive group structure otherwise false ie 0 */
    $ErrMsg = _('An error occurred in retrieving the account groups of the parent account group during the check for recursion');
    $DbgMsg = _('The SQL that was used to retrieve the account groups of the parent account group and that failed in the process was');
    do {
        $sql = "SELECT parentgroupname\n\t\t\t\tFROM accountgroups\n\t\t\t\tWHERE groupname='" . $GroupName . "'";
        $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
        $myrow = DB_fetch_row($result);
        if ($ParentGroupName == $myrow[0]) {
            return true;
        }
        $GroupName = $myrow[0];
    } while ($myrow[0] != '');
    return false;
}