PHP do_sql函数代码示例

function do_cryptdb_sql($cdbh, $q)
{
    global $last_sth, $last_sql, $reccount, $out_message, $SQLq, $SHOW_T;
    $SQLq = $q;
    if (!do_multi_sql($q)) {
        $out_message = "Error: " . $cdbh->error;
    } else {
        if ($last_sth && $last_sql) {
            $SQLq = $last_sql;
            if (preg_match("/^select|show|explain|desc/i", $last_sql)) {
                if ($q != $last_sql) {
                    $out_message = "Results of the last select displayed:";
                }
                display_select($last_sth, $last_sql);
            } else {
                $reccount = mysql_affected_rows($cryptdbh);
                $out_message = "Done.";
                if (preg_match("/^insert|replace/i", $last_sql)) {
                    $out_message .= " Last inserted id=" . get_identity();
                }
                if (preg_match("/^drop|truncate/i", $last_sql)) {
                    do_sql($SHOW_T);
                }
            }
        }
    }
}

function weekly_planned_totals($athlete_id, $end_epoch)
{
    // Returns html output of weekly totals of training which is planned
    // but not yet done.
    // assuming date has been sent in epoch seconds format
    // need to calculate 7 days back so calc a week in seconds:
    // $end_epoch is probably Monday, we want Sunday so subtract 30mins to make sure
    $end_epoch = $end_epoch - 30 * 60;
    // and a week of seconds will take us back from 23:30 Sunday to 23:30 Sunday
    // previously so take off  an extra few hours hour
    $weeksecs = 60 * 60 * 24 * 7 - 360 * 60;
    $weekagoepoch = $end_epoch - $weeksecs;
    $weekstart = date("Y/n/j", $weekagoepoch);
    $weekend = date("Y/n/j", $end_epoch);
    $totals = array();
    $results = do_sql("SELECT sum(elapsed_time) FROM log \n    WHERE athlete_id={$athlete_id} AND exercise_type!='REST' \n    AND parent_session = 0 \n    AND start_date BETWEEN '{$weekstart}' AND '{$weekend}' \n    AND entry_type IN \n      ('plan', 'planned - not done', 'event - tentative', 'event - to enter', 'event - entered', 'event - enter by' ) \n    ");
    $row = pg_fetch_array($results, null, PGSQL_ASSOC);
    #$week_total = $row['sum'];
    $totals['Total To Do'] = $row['sum'];
    $results = do_sql("SELECT exercise_type, sum(elapsed_time) FROM log \n    WHERE athlete_id={$athlete_id} AND exercise_type!='REST' \n    AND parent_session = 0  \n    AND entry_type IN \n      ('plan', 'planned - not done', 'event - tentative', 'event - to enter', 'event - entered', 'event - enter by' ) \n    AND start_date BETWEEN '{$weekstart}' AND '{$weekend}' \n    GROUP BY exercise_type ");
    while ($row = pg_fetch_array($results, null, PGSQL_ASSOC)) {
        # $row now holds 1 exercise type
        $ex_type = $row['exercise_type'];
        $total_time = $row['sum'];
        #$totals = "$totals <nobr> &nbsp; &nbsp; <b>$ex_type:</b> $total_time</nobr>";
        $totals[$ex_type] = $total_time;
    }
    #$totals = "<b>Total To Do:</b>\n  $week_total <br>\n $totals \n";
    return $totals;
}

function get_column_names()
{
    # Get all log Column names
    $query = "SELECT * from log\n          WHERE session_id = 1 ";
    $result = do_sql($query);
    $details = pg_fetch_array($result, null, PGSQL_ASSOC);
    $columns = array_keys($details);
    return $columns;
}

function build_to_delete($id)
{
    $to_delete = array();
    $query = "SELECT session_id FROM log WHERE parent_session = {$id} ";
    $result = do_sql($query) or die('Query failed: ' . pg_last_error());
    #if ( pg_num_rows($result) == 0 ) { return $to_delete ; } ;
    while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        array_push($to_delete, $row['session_id']);
        // call self to cascade until all splits of splits are found
        $returned_array = build_to_delete($row['session_id']);
        foreach ($returned_array as $id) {
            array_push($to_delete, $id);
        }
    }
    return $to_delete;
}

function log_logout($athlete_id)
{
    // writes logouts to a log file which can be viewed by users
    $logfile = "/var/log/tlog/logins.log";
    $results = do_sql("SELECT name,surname,country FROM athlete WHERE athlete_id={$athlete_id}");
    while ($row = pg_fetch_array($results)) {
        $name = $row['name'];
        if ($row['surname'] . "X" != "X") {
            $surname = $row['surname'];
        }
        if ($row['country'] . "X" != "X") {
            $country = " from " . $row['country'];
        }
    }
    $loghandle = fopen($logfile, "a") or die("cannot open {$logfile}");
    $date = date("Y/m/d H:i");
    $line = "LOGGED OUT {$date} - {$name} {$surname} ({$athlete_id}) {$country}\n";
    fwrite($loghandle, $line);
    fclose($loghandle);
}

<?php

session_start();
$_SESSION['athlete_id'];
$_SESSION['encpass'];
$_SESSION['login'];
$login = $_POST["login"];
$password = $_POST["password"];
include_once "logit.php";
include_once "sql_functions.php";
logit("Login {$login} start");
$query = "SELECT athlete_id, encpass FROM athlete WHERE login = '{$login}'";
#$result = pg_query($query) or die('Query failed: ' . pg_last_error());
$result = do_sql($query);
$row = pg_fetch_array($result, null, PGSQL_ASSOC);
$athlete_id = $row['athlete_id'];
$encpass = $row['encpass'];
# Check whether any rows were retrieved, ie. whether the login exists
$num_rows = pg_num_rows($result);
if ($num_rows == 0) {
    header("Location:index.php?message=Login Incorrect");
    logit("Login {$login} incorrect");
    exit("Incorrect Login");
} elseif ($num_rows > 1) {
    logit("Login {$login} : multiple occurences of login found ");
    header("Location:index.php?message=Login problem - multiple occurrences detected.");
    exit("Login problem - multiple occurrences detected.'");
}
# create sha1 encrypted version of entered password
$received_encpass = sha1($password);
# Compare this with the encrypted password in the db.

        if ($_POST['bid'] != "" && $_SESSION['id'] != '') {
            // deleting a blog
            $send = '';
            $res = check_vote_on_post($_POST['bid'], $mysqli);
            $where['votes_blog_id'] = $_POST['bid'];
            $where['votes_people_id'] = $_SESSION['id'];
            if ($res) {
                do_sql('votes', $send, 'delete', $mysqli, $where);
            } else {
                do_sql('votes', $where, 'insert', $mysqli, $where['votes_blog_id'] . ' AND ' . $where['votes_people_id']);
            }
        }
        break;
    case 'send_response':
        if ($_POST['bid'] != "" && $_SESSION['id'] != '' && $_POST['sendResponse'] != "") {
            // deleting a blog
            $send['response_blog_id'] = $_POST['bid'];
            $send['response_people_id'] = $_SESSION['id'];
            $send['response_text'] = htmlentities($_POST['sendResponse']);
            do_sql('response', $send, 'insert', $mysqli, $send);
        }
        break;
    case 'approvePeople':
        if ($_POST['pid'] != "") {
            $where['people_id'] = $_POST['pid'];
            $send['people_addedBy'] = '0';
            echo do_sql('people', $send, 'update', $mysqli, $where);
        }
        break;
}
echo 1;

                     */
                    if ($_POST['cryptdb_describe_table']) {
                        $q = "describe " . $DB['db'] . "." . $_POST['cryptdb_describe_table'];
                        do_sql($q);
                    }
                } else {
                    if (isset($_REQUEST['refresh'])) {
                        check_xss();
                        do_sql('show databases');
                    } elseif (preg_match('/^show\\s+(?:databases|status|variables|process)/i', $SQLq)) {
                        check_xss();
                        do_sql($SQLq);
                    } else {
                        $err_msg = "Select Database first";
                        if (!$SQLq) {
                            do_sql("show databases");
                        }
                    }
                }
            }
        }
        $time_all = ceil((microtime_float() - $time_start) * 10000) / 10000;
        print_screen();
    }
} else {
    $_SESSION['logoff'] = false;
    print_cfg();
}
function print_header()
{
    global $err_msg, $VERSION, $DB, $CRYPTDB, $dbh, $self, $is_sht, $xurl, $SHOW_T;

function get_session_owner($session_id)
{
    // Gets the id of the athlete who owns this session
    $query = "SELECT athlete_id FROM log WHERE session_id = {$session_id} ";
    $result = do_sql($query) or die('Query failed: ' . pg_last_error());
    $row = pg_fetch_array($result, null, PGSQL_ASSOC);
    return $row['athlete_id'];
    // END OF FUNCTION
}

function do_sht()
{
    global $SHOW_T;
    $cb = $_REQUEST['cb'];
    if (!is_array($cb)) {
        $cb = array();
    }
    $sql = '';
    switch ($_REQUEST['dosht']) {
        case 'exp':
            $_REQUEST['t'] = join(",", $cb);
            print_export();
            exit;
        case 'drop':
            $sq = 'DROP TABLE';
            break;
        case 'trunc':
            $sq = 'TRUNCATE TABLE';
            break;
        case 'opt':
            $sq = 'OPTIMIZE TABLE';
            break;
    }
    if ($sq) {
        foreach ($cb as $v) {
            $sql .= $sq . " {$v};\n";
        }
    }
    if ($sql) {
        do_sql($sql);
    }
    do_sql($SHOW_T);
}

function update_log_row($athlete_id, $details, $post_data)
{
    $columns = array();
    $columns = validate_form_data($details, $post_data);
    $session_id = $columns['session_id'];
    // Find out who owns this session and check that this user
    // has permission to edit the owner's log
    $session_owner = get_session_owner($session_id);
    if ($athlete_id == $session_owner) {
    } elseif ($athlete_id != $session_owner && check_share_permission($session_owner, "edit log {$athlete_id}")) {
        $athlete_id = $session_owner;
    } else {
        echo "You do not have permission to edit this athlete's log<br>";
        return false;
    }
    # Build insert query
    $query = "UPDATE log SET ";
    #foreach ($details as $column){
    foreach (array_keys($columns) as $column) {
        $entry = $columns[$column];
        $query = "{$query} {$column} = '{$entry}' ,";
    }
    # remove final comma from query
    $query = substr($query, 0, strlen($query) - 1);
    $query = "{$query} WHERE athlete_id = {$athlete_id} AND session_id = {$session_id} ";
    #echo "DEBUG Entry update query:<br>$query";
    # Update session using build UPDATE query
    $result = do_sql($query) or die('Query failed: ' . pg_last_error());
    ## END OF FUNCTION
}

function jump($action)
{
    switch ($action) {
        case "go":
            do_sql($_REQUEST['SQLfield']);
            // don't need stripslashes here?
            break;
    }
    return;
}

function display_menubar()
{
    if (!isset($_SESSION['athlete_id'])) {
        echo <<<ENDHTML
\t<H2>Your session has expired</H2>
\t<FONT size=1>Please <a href=index.php >return to the <u>login page</u> and log in again.<a></FONT><br>
ENDHTML;
        exit;
    }
    $login = $_SESSION['login'];
    $athlete_id = $_SESSION['athlete_id'];
    require_once "access_check.php";
    $filename = basename(__FILE__);
    #access_check( $filename ) ;
    // Connect to DB
    include_once "sql_functions.php";
    #$dbconn = pg_connect("host=localhost dbname=training_diary user=athlete password=ironman")
    # or die('Could not connect: ' . pg_last_error());
    $query = "SELECT name from athlete WHERE athlete_id = {$athlete_id}";
    #$result = pg_query($query) or die('Query failed: ' . pg_last_error());
    $result = do_sql($query);
    $row = pg_fetch_array($result, null, PGSQL_ASSOC);
    $firstname = $row['name'];
    $query = "SELECT function FROM user_access WHERE athlete_id = '{$athlete_id}'";
    $result = do_sql($query) or die('Query failed: ' . pg_last_error());
    $allowed = pg_fetch_all_columns($result, 0);
    echo <<<HTMLEND

<TABLE>
<TR><TD align=right class=fitnessmad  >
<b><font size=3 color=#885555 >
<i> FitnessMad.Net </i>
</FONT><br/>
<font color=#AA5555 >
Training Log
</FONT>
</b>
</TD><TD class=mainmenu align=right valign=bottom >
&nbsp;&nbsp;&nbsp; 
You are logged in {$firstname}<br>
</TD>
</TR>
</TABLE>

<HR>
HTMLEND;
    echo <<<ENDHTML

  <TABLE><TR><TD>
  <UL class="nav">

ENDHTML;
    // Assume we want to jump to today's date, won't do anything if
    // this is not in the date range selected.
    $jumpto = "anchorD" . date("Ymd");
    // View My Log
    if (in_array("view_log.php", $allowed)) {
        echo <<<HTMLEND

  <LI class=menuheader ><a href="view_log.php#{$jumpto}"> View My Log</a> 
  </LI>

HTMLEND;
    }
    // Add Log Entry
    $date = date("d/m/Y");
    if (in_array("add_log_entry.php", $allowed)) {
        echo <<<HTMLEND

  <LI class=menuheader >
    <!-- <a href="javascript:launchRemote('add_log_entry.php#{$jumpto}')" >  -->
    <a href="javascript:launchRemote('add_log_entry.php?start_date={$date}')" >
    Add Log entry</a>
  </LI>

HTMLEND;
    }
    // Start dropdown menu for Configuration options
    echo <<<CONFIGMENU

    <li class=menuheader ><a >Configuration</a>
    <ul>

CONFIGMENU;
    if (in_array("configure_view.php", $allowed)) {
        echo <<<CONFIGMENU

\t<li><a href="configure_view.php">
\t\tConfigure View</a></li>

CONFIGMENU;
    }
    if (in_array("configure_log_entry.php", $allowed)) {
        echo <<<CONFIGMENU

\t<li><a href="configure_log_entry.php">
\t\tConfigure Log Entry Fields</a></li>

CONFIGMENU;
    }
//.........这里部分代码省略.........

ENDHTML;
// Cannot directly read "/var/lib/php5" because the dir is not traversable by
// www-data.
// There is a perl script in /usr/local/bin run by the /etc/cron.d/www which
// writes a list of athlete ids which it finds in the session files every minute
// to /var/log/tlog/loggedin
$file = "/var/log/tlog/current_logins";
$handle = fopen($file, "r");
while ($line = fgets($handle)) {
    $ldata = explode(",", $line);
    // Athlete_id is the second field in the line:
    $athids[] = $ldata[1];
}
fclose($handle);
foreach ($athids as $id) {
    $results = do_sql("SELECT name,surname,country FROM athlete WHERE athlete_id={$id}");
    while ($row = pg_fetch_array($results)) {
        $name = $row['name'];
        if ($row['surname'] . "X" != "X") {
            $surname = $row['surname'];
        }
        if ($row['country'] . "X" != "X") {
            $country = " from " . $row['country'];
        }
        echo "<b>{$name} {$surname}</b>{$country} is logged in.<br>\n";
    }
}
echo <<<ENDHTML
</BODY>
</HTML>
ENDHTML

                $send['app_people_id'] = $_SESSION['id'];
                do_sql('approval', $send, 'insert', $mysqli);
                // add_score
                $sql = "UPDATE position SET position_approvalScore=position_approvalScore+" . $_SESSION['score'] . " WHERE position_id='" . $pos_id . "';";
                if ($mysqli->query($sql) === false) {
                    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR);
                } else {
                    $affected_rows = $mysqli->affected_rows;
                }
                echo '<p>score added';
            }
            // update top role
            // fetching user id
            $getUID = $mysqli->prepare('SELECT position_people_id FROM position WHERE position_id=? ') or die('Couldn\'t check the userid');
            $getUID->bind_param('s', $pos_id);
            $getUID->execute();
            $getUID->store_result();
            $getUID->bind_result($user_id);
            while ($getUID->fetch()) {
                $top_role = get_top_role($user_id, $mysqli);
            }
            echo '<p>got top_role : ' . $top_role . " and user id : " . $user_id;
            // now updating table with top_role
            $upd['people_id'] = $user_id;
            $upd['people_topCode'] = $top_role;
            do_sql('people', $upd, 'update', $mysqli);
            echo '<p>update added';
        }
        break;
}
echo 1;