PHP db_fetchAll函数代码示例

/**
 * function get_conversation_to_display()
 * This function gets the conversation from the db to display/return to the user
 * @link http://blog.program-o.com/?p=1223
 * @param  array $convoArr - the conversation array
 * @return array $orderedRows - a list of conversation line
 **/
function get_conversation_to_display($convoArr)
{
    global $dbConn, $dbn, $bot_name, $unknown_user;
    $user_id = $convoArr['conversation']['user_id'];
    $bot_id = $convoArr['conversation']['bot_id'];
    $user_name = $convoArr['conversation']['user_name'];
    $user_name = !empty($user_name) ? $user_name : $unknown_user;
    $convoArr['conversation']['bot_name'] = $bot_name;
    if (empty($bot_name)) {
        $sql = "select `bot_name` from `bots` where `bot_id` = {$bot_id} limit 1;";
        $row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
        $bot_name = $row['bot_name'];
    }
    if ($convoArr['conversation']['conversation_lines'] != 0) {
        $limit = " LIMIT " . $convoArr['conversation']['conversation_lines'];
    } else {
        $limit = "";
    }
    $sql = "SELECT * FROM `{$dbn}`.`conversation_log`\n        WHERE\n        `user_id` = '" . $convoArr['conversation']['user_id'] . "'\n        AND `bot_id` = '" . $convoArr['conversation']['bot_id'] . "'\n        AND `convo_id` = '" . $convoArr['conversation']['convo_id'] . "'\n        ORDER BY id DESC {$limit} ";
    runDebug(__FILE__, __FUNCTION__, __LINE__, "get_conversation SQL: {$sql}", 3);
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    if (count($result) > 0) {
        foreach ($result as $row) {
            $allrows[] = $row;
        }
        $orderedRows = array_reverse($allrows, false);
    } else {
        $orderedRows = array('id' => NULL, 'input' => "", 'response' => "", 'user_id' => $convoArr['conversation']['user_id'], 'bot_id' => $convoArr['conversation']['bot_id'], 'timestamp' => "");
    }
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Found '" . count($result) . "' lines of conversation", 2);
    return $orderedRows;
}

/**
 * function intialise_censor()
 * A function to build session array containing the words from the censor list in the db
 **/
function initialise_censor($bot_id)
{
    global $dbConn, $dbn;
    //set in global config file
    $_SESSION['pgo_word_censor'] = array();
    $sql = "SELECT * FROM `{$dbn}`.`wordcensor` WHERE `bot_exclude` NOT LIKE '%[{$bot_id}]%'";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    foreach ($result as $row) {
        $index = $row['word_to_censor'];
        $value = $row['replace_with'];
        $_SESSION['pgo_word_censor'][$index] = $value;
    }
}

/**
 * Function buildSelOpts
 *
 *
 * @return string
 */
function buildSelOpts()
{
    global $bot_id, $bot_name, $msg;
    $sql = "SELECT DISTINCT filename FROM `aiml` where `bot_id` = {$bot_id} order by `filename`;";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    if (count($result) == 0) {
        $msg = "The chatbot '{$bot_name}' has no AIML categories to clear.";
        return false;
    }
    $out = "                  <!-- Start Selectbox Options -->\n";
    $optionTemplate = "                  <option value=\"[val]\">[val]</option>\n";
    foreach ($result as $row) {
        if (empty($row['filename'])) {
            $curOption = "                  <option value=\"\">{No Filename entry}</option>\n";
        } else {
            $curOption = str_replace('[val]', $row['filename'], $optionTemplate);
        }
        $out .= $curOption;
    }
    $out .= "                  <!-- End Selectbox Options -->\n";
    return $out;
}

/**
 * function get_user_id()
 * A function to get the user id
 * @param  array $convoArr - the current state of the conversation array
 * @return array $convoArr
**/
function get_user_id($convoArr)
{
    //db globals
    global $dbConn, $dbn, $unknown_user;
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Getting user ID.', 2);
    //get undefined defaults from the db
    $sql = "SELECT * FROM `{$dbn}`.`users` WHERE `session_id` = '" . $convoArr['conversation']['convo_id'] . "' limit 1";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $count = count($result);
    if ($count > 0) {
        $row = $result[0];
        $convoArr['conversation']['user_id'] = $row['id'];
        // add user name, if set
        $convoArr['conversation']['user_name'] = !empty($convoArr['client_properties']['name']) ? $convoArr['client_properties']['name'] : !empty($row['user_name']) ? $row['user_name'] : $unknown_user;
        $convoArr['client_properties']['name'] = $convoArr['conversation']['user_name'];
        $msg = "existing";
    } else {
        $convoArr = intisaliseUser($convoArr);
        $msg = "new";
    }
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Getting {$msg} user id:" . $convoArr['conversation']['user_id'], 4);
    runDebug(__FILE__, __FUNCTION__, __LINE__, "get_user_id SQL: {$sql}", 3);
    return $convoArr;
}

/**
 * Function runSearch
 *
 *
 * @return mixed|string
 */
function runSearch()
{
    global $bot_id, $form_vars, $dbConn, $group;
    $groupSize = 10;
    $limit = ($group - 1) * $groupSize;
    $limit = $limit < 0 ? 0 : $limit;
    $search_fields = array('topic', 'filename', 'pattern', 'template', 'thatpattern');
    $searchTerms = array();
    $searchArguments = array($bot_id);
    $searchParams = array($bot_id);
    foreach ($search_fields as $index) {
        if (!empty($form_vars[$index])) {
            $searchParams[] = "%{$form_vars[$index]}%";
            array_push($searchArguments, "%" . trim($form_vars[$index]) . "%");
            array_push($searchTerms, "{$index} like ?");
        }
    }
    $searchTerms = !empty($searchTerms) ? implode(" AND ", $searchTerms) : "TRUE";
    $countSQL = "SELECT count(id) FROM `aiml` WHERE `bot_id` = ? AND ({$searchTerms})";
    $count = db_fetch($countSQL, $searchParams, __FILE__, __FUNCTION__, __LINE__);
    $total = $count['count(id)'];
    $limit = $limit >= $total ? $total - 1 - ($total - 1) % $groupSize : $limit;
    $order = isset($form_vars['sort']) ? $form_vars['sort'] . " " . $form_vars['sortOrder'] : "id";
    $sql = "SELECT id, topic, filename, pattern, template, thatpattern FROM `aiml` " . "WHERE `bot_id` = ? AND ({$searchTerms}) order by {$order} limit {$limit}, {$groupSize};";
    $result = db_fetchAll($sql, $searchParams, __FILE__, __FUNCTION__, __LINE__);
    $out = array("results" => $result, "total_records" => $total, "start_index" => 0, "page" => $limit / $groupSize + 1, "page_size" => $groupSize);
    return json_encode($out);
}

/**
 * function run_srai()
 * This function controls the SRAI recursion calls
 * @param array $convoArr - a reference to the existing conversation array
 * @param string $now_look_for_this - the text to search for
 * @return string $srai_parsed_template - the result of the search
 **/
function run_srai(&$convoArr, $now_look_for_this)
{
    global $srai_iterations, $error_response, $dbConn, $dbn;
    $bot_parent_id = $convoArr['conversation']['bot_parent_id'];
    $bot_id = $convoArr['conversation']['bot_id'];
    if ($bot_parent_id != 0 and $bot_parent_id != $bot_id) {
        $sql_bot_select = " (bot_id = '{$bot_id}' OR bot_id = '{$bot_parent_id}') ";
    } else {
        $sql_bot_select = " bot_id = '{$bot_id}' ";
    }
    $bot_id = $convoArr['conversation']['bot_id'];
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Checking for entries in the srai_lookup table.', 2);
    runDebug(__FILE__, __FUNCTION__, __LINE__, "google bot_id = {$bot_id}", 2);
    $lookingfor = $convoArr['aiml']['lookingfor'];
    //$now_look_for_this = strtoupper($now_look_for_this);
    $sql = "select `template_id` from `{$dbn}`.`srai_lookup` where `pattern` = '{$now_look_for_this}' and {$sql_bot_select};";
    runDebug(__FILE__, __FUNCTION__, __LINE__, "lookup SQL = {$sql}", 2);
    $row = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Result = ' . print_r($row, true), 2);
    $num_rows = count($row);
    if ($num_rows > 0) {
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Found {$num_rows} rows in lookup table: " . print_r($row, true), 2);
        $template_id = $row[0]['template_id'];
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Found a matching entry in the lookup table. Using ID# {$template_id}.", 2);
        $sql = "select `template` from `{$dbn}`.`aiml` where `id` = '{$template_id}';";
        $row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Row found in AIML for ID {$template_id}: " . print_r($row, true), 2);
        if (!empty($row)) {
            $template = add_text_tags($row['template']);
            try {
                $sraiTemplate = new SimpleXMLElement($template, LIBXML_NOCDATA);
            } catch (exception $e) {
                trigger_error("There was a problem parsing the SRAI template as XML. Template value:\n{$template}", E_USER_WARNING);
                $sraiTemplate = new SimpleXMLElement("<text>{$error_response}</text>", LIBXML_NOCDATA);
            }
            $responseArray = parseTemplateRecursive($convoArr, $sraiTemplate);
            $response = implode_recursive(' ', $responseArray, __FILE__, __FUNCTION__, __LINE__);
            runDebug(__FILE__, __FUNCTION__, __LINE__, "Returning results from stored srai lookup.", 2);
            return $response;
        }
    } else {
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'No match found in lookup table.', 2);
    }
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Nothing found in the SRAI lookup table. Looking for a direct pattern match for '{$now_look_for_this}'.", 2);
    $sql = "SELECT `id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` where `pattern` = :pattern and {$sql_bot_select} order by `id` asc;";
    $result = db_fetchAll($sql, array(':pattern' => $now_look_for_this), __FILE__, __FUNCTION__, __LINE__);
    $num_rows = count($result);
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Found {$num_rows} potential responses.", 2);
    $allrows = array();
    $i = 0;
    if ($num_rows > 0) {
        $tmp_rows = number_format($num_rows);
        runDebug(__FILE__, __FUNCTION__, __LINE__, "FOUND: ({$tmp_rows}) potential AIML matches", 2);
        //loop through results
        foreach ($result as $row) {
            $row['aiml_id'] = $row['id'];
            $row['score'] = 0;
            $row['track_score'] = '';
            $allrows[] = $row;
            $mu = memory_get_usage(true);
            if ($mu >= MEM_TRIGGER) {
                runDebug(__FILE__, __FUNCTION__, __LINE__, 'Current operation exceeds memory threshold. Aborting data retrieval.', 0);
                break;
            }
        }
    } else {
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
        $allrows[$i]['aiml_id'] = "-1";
        $allrows[$i]['bot_id'] = "-1";
        $allrows[$i]['pattern'] = "no results";
        $allrows[$i]['thatpattern'] = '';
        $allrows[$i]['topic'] = '';
    }
    //unset all irrelvant matches
    $allrows = unset_all_bad_pattern_matches($convoArr, $allrows, $now_look_for_this);
    $arCount = count($allrows);
    if ($arCount == 0) {
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
        $allrows[$i]['aiml_id'] = "-1";
        $allrows[$i]['bot_id'] = "-1";
        $allrows[$i]['pattern'] = "no results";
        $allrows[$i]['thatpattern'] = '';
        $allrows[$i]['topic'] = '';
    }
    //score the relevant matches
    $allrows = score_matches($convoArr, $allrows, $now_look_for_this);
    //get the highest
    $allrows = get_highest_scoring_row($convoArr, $allrows, $lookingfor);
    if (isset($allrows['aiml_id']) && $allrows['aiml_id'] > 0) {
        $aiml_id = $allrows['aiml_id'];
        $pattern = $allrows['pattern'];
        $sql = "select `template` from `{$dbn}`.`aiml` where `id` = :id limit 1;";
        $row = db_fetch($sql, array(':id' => $aiml_id), __FILE__, __FUNCTION__, __LINE__);
//.........这里部分代码省略.........

/**
 * function load_that(()
 * A function to load the previous bot responses into the convoArr['that'] array
 *
 * @link http://blog.program-o.com/?p=1283
 * @param  array $convoArr - the current state of the conversation array
 * @return array $convoArr (updated)
 */
function load_that($convoArr)
{
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading the THAT array.', 2);
    global $dbConn, $dbn, $remember_up_to, $bot_id;
    $remember_up_to = !empty($convoArr['conversation']['remember_up_to']) ? $convoArr['conversation']['remember_up_to'] : $remember_up_to;
    $user_id = $convoArr['conversation']['user_id'];
    $bot_id = !empty($convoArr['conversation']['bot_id']) ? $convoArr['conversation']['bot_id'] : $bot_id;
    $limit = $remember_up_to;
    $sql = "select `input`, `response` from `{$dbn}`.`conversation_log` where `user_id` = {$user_id} and `bot_id` = {$bot_id} order by `id` desc limit {$limit};";
    // desc
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Getting conversation log entries for the current user. SQL:\n{$sql}", 3);
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    if ($result) {
        $tmpThatRows = array();
        $tmpInputRows = array();
        $tmpThat = array();
        $tmpInput = array();
        $puncuation = array(',', '?', ';', '!');
        foreach ($result as $row) {
            $tmpThatRows[] = $row['response'];
            $tmpInputRows[] = $row['input'];
        }
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'Inputs returned:' . print_r($tmpInputRows, true), 1);
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading previous responses into the ~THAT~ array.', 4);
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'Responses returned:' . print_r($tmpThatRows, true), 1);
        $tmpThatRows = array_reverse($tmpThatRows);
        foreach ($tmpThatRows as $row) {
            $row = str_replace($puncuation, '.', $row);
            $tmpThat[] = explode('.', $row);
        }
        array_unshift($tmpThat, NULL);
        unset($tmpThat[0]);
        foreach ($tmpThat as $index => $value) {
            $value = implode_recursive(' ', $value, __FILE__, __FUNCTION__, __LINE__);
            $value = clean_that($value, __FILE__, __FUNCTION__, __LINE__);
            $convoArr = push_on_front_convoArr('that', $value, $convoArr);
        }
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading previous user inputs into the ~INPUT~ array.', 4);
        $tmpInputRows = array_reverse($tmpInputRows);
        foreach ($tmpInputRows as $row) {
            $row = str_replace($puncuation, '.', $row);
            $tmpInput[] = explode('.', $row);
        }
        array_unshift($tmpThat, NULL);
        unset($tmpThat[0]);
        foreach ($tmpInput as $index => $value) {
            $value = implode_recursive(' ', $value, __FILE__, __FUNCTION__, __LINE__);
            $value = clean_that($value, __FILE__, __FUNCTION__, __LINE__);
            $convoArr = push_on_front_convoArr('input', $value, $convoArr);
        }
    } else {
        runDebug(__FILE__, __FUNCTION__, __LINE__, 'Couldn\'t find any previous inputs or responses.', 4);
    }
    return $convoArr;
}

/**
 * Function getBotList
 *
 *
 * @return string
 */
function getBotList()
{
    global $dbConn, $dbn, $bot_id;
    $botOptions = '';
    $sql = 'SELECT `bot_name`, `bot_id` FROM `bots` order by `bot_id`;';
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    foreach ($result as $row) {
        $bn = $row['bot_name'];
        $bi = $row['bot_id'];
        $sel = $bot_id == $bi ? ' selected="selected"' : '';
        $botOptions .= "                    <option{$sel} value=\"{$bi}\">{$bn}</option>\n";
    }
    return $botOptions;
}

/**
 * function load_spelling_list
 * Gets all missspelled words and their corrections from the DB, loading them into a session variable.
 *
 * @internal param $ (none)
 * @return void (void)
 */
function load_spelling_list()
{
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading the spellcheck list from the DB.', 2);
    global $dbConn, $dbn;
    $_SESSION['spellcheck'] = array();
    $sql = "SELECT `missspelling`, `correction` FROM `{$dbn}`.`spellcheck`;";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $num_rows = count($result);
    if ($num_rows > 0) {
        foreach ($result as $row) {
            $missspelling = IS_MB_ENABLED ? mb_strtolower($row['missspelling']) : strtolower($row['missspelling']);
            $correction = $row['correction'];
            $_SESSION['spellcheck'][$missspelling] = $correction;
        }
    }
}

/**
 * Returns an HTML form for selecting a chatbot from the database
 *
 *
 * @return string
 */
function getChangeList()
{
    global $dbConn, $template;
    $bot_id = isset($_SESSION['poadmin']['bot_id']) ? $_SESSION['poadmin']['bot_id'] : 0;
    $botId = $bot_id;
    $sql = "SELECT * FROM `bots` ORDER BY bot_name";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $options = '                <option value="new">Add New Bot</option>' . "\n";
    foreach ($result as $row) {
        $options .= "<!-- bot ID = {$row['bot_id']}, {$botId} -->\n";
        if ($botId == $row['bot_id']) {
            $sel = ' selected="selected"';
        } else {
            $sel = '';
        }
        $bot_id = $row['bot_id'];
        $bot_name = $row['bot_name'];
        $options .= "                <option value=\"{$bot_id}\"{$sel}>{$bot_name}</option>\n";
    }
    $options = rtrim($options);
    $form = $template->getSection('ChangeBot');
    $form = str_replace('[options]', $options, $form);
    return $form;
}

/**
 * Function updateBot
 *
 *
 * @return string
 */
function updateBot()
{
    global $bot_id, $bot_name, $post_vars;
    $msg = "";
    if (!empty($post_vars['newEntryName'])) {
        $newEntryNames = $post_vars['newEntryName'];
        $newEntryValues = $post_vars['newEntryValue'];
        $sql = "Insert into `botpersonality` (`id`, `bot_id`, `name`, `value`) values (null, {$bot_id}, :name, :value);";
        $params = array();
        foreach ($newEntryNames as $index => $key) {
            $value = $newEntryValues[$index];
            if (empty($value)) {
                continue;
            }
            $params[] = array(':name' => $key, ':value' => $value);
        }
        $rowsAffected = db_write($sql, $params, true, __FILE__, __FUNCTION__, __LINE__);
        if ($rowsAffected > 0) {
            $msg = empty($msg) ? "Bot personality added. \n" : $msg;
        } else {
            $msg = 'Error updating bot personality.';
        }
    }
    $sql = "SELECT * FROM `botpersonality` where `bot_id` = {$bot_id};";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $rows = array();
    $insertParams = array();
    $updateParams = array();
    foreach ($result as $row) {
        $name = $row['name'];
        $value = $row['value'];
        $rows[$name] = array('id' => $row['id'], 'value' => $value);
    }
    $insertSQL = "Insert into `botpersonality` (`id`, `bot_id`, `name`, `value`) values (null, {$bot_id}, :name, :value);";
    $updateSQL = "update `botpersonality` set `value` = :value where `id` = :id;";
    $exclude = array('bot_id', 'func', 'action', 'newEntryName', 'newEntryValue');
    $values = '';
    foreach ($post_vars as $key => $value) {
        if (in_array($key, $exclude)) {
            continue;
        }
        if (!isset($rows[$key])) {
            $insertParams[] = array(':name' => $key, ':value' => $value);
        } else {
            $oldValue = $rows[$key]['value'];
            if ($value != $oldValue) {
                $curId = $rows[$key]['id'];
                $updateParams[] = array(':value' => $value, ':id' => $curId);
            }
        }
    }
    if (empty($insertParams) && empty($updateParams)) {
        return 'No changes found.';
    }
    $affectedRows = !empty($updateParams) ? db_write($updateSQL, $updateParams, true, __FILE__, __FUNCTION__, __LINE__) : 0;
    $affectedRows += !empty($updateParams) ? db_write($insertSQL, $insertParams, true, __FILE__, __FUNCTION__, __LINE__) : 0;
    if ($affectedRows > 0) {
        $msg = 'Bot Personality Updated.';
    } else {
        $msg = "Something went wrong! Affected rows = {$affectedRows}.";
    }
    return $msg;
}

/**
 * Function getCheckboxes
 *
 *
 * @return string
 */
function getCheckboxes()
{
    global $bot_id, $bot_name, $msg;
    $sql = "SELECT DISTINCT filename FROM `aiml` where `bot_id` = {$bot_id} order by `filename`;";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    if (count($result) == 0) {
        $msg = "The chatbot '{$bot_name}' has no AIML categories to download. Please select another bot.";
        return false;
    }
    $out = "";
    $checkboxTemplate = <<<endRow
                    <div class="cbCell">
                      <input id="[file_name_id]" name="filenames[]" type="checkbox" class="cbFiles" value="[file_name]">
                      <label for="[file_name_id]">&nbsp;[file_name]</label>
                    </div>
endRow;
    $rowCount = 0;
    foreach ($result as $row) {
        if (empty($row['filename'])) {
            $row['filename'] = 'unnamed_AIML.aiml';
        }
        $file_name = $row['filename'];
        $file_name_id = str_replace('.', '_', $file_name);
        $curCheckbox = str_replace('[file_name]', $file_name, $checkboxTemplate);
        $curCheckbox = str_replace('[file_name_id]', $file_name_id, $curCheckbox);
        $out .= $curCheckbox;
        $rowCount++;
    }
    return rtrim($out);
}

/**
 * Function runSpellSearch
 *
 *
 * @return string
 */
function runSpellSearch()
{
    global $dbConn, $template, $post_vars;
    $i = 0;
    $search = trim($post_vars['search']);
    $sql = "SELECT * FROM `spellcheck` WHERE `missspelling` LIKE '%{$search}%' OR `correction` LIKE '%{$search}%' LIMIT 50";
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $htmltbl = '<table>
                  <thead>
                    <tr>
                      <th class="sortable">missspelling</th>
                      <th class="sortable">Correction</th>
                      <th>Action</th>
                    </tr>
                  </thead>
                <tbody>';
    foreach ($result as $row) {
        $i++;
        $misspell = strtoupper($row['missspelling']);
        $correction = strtoupper($row['correction']);
        $id = $row['id'];
        $group = round($id / 50);
        $action = "<a href=\"index.php?page=spellcheck&amp;action=edit&amp;id={$id}&amp;group={$group}#{$id}\"><img src=\"images/edit.png\" border=0 width=\"15\" height=\"15\" alt=\"Edit this entry\" title=\"Edit this entry\" /></a>\n                    <a href=\"index.php?page=spellcheck&amp;action=del&amp;id={$id}&amp;group={$group}#{$id}\" onclick=\"return confirm('Do you really want to delete this missspelling? You will not be able to undo this!')\";><img src=\"images/del.png\" border=0 width=\"15\" height=\"15\" alt=\"Edit this entry\" title=\"Edit this entry\" /></a>";
        $htmltbl .= "<tr valign=top>\n                            <td>{$misspell}</td>\n                            <td>{$correction}</td>\n                            <td align=center>{$action}</td>\n                        </tr>";
    }
    $htmltbl .= "</tbody></table>";
    if ($i >= 50) {
        $msg = "Found more than 50 results for '<b>{$search}</b>', please refine your search further";
    } elseif ($i == 0) {
        $msg = "Found 0 results for '<b>{$search}</b>'. You can use the form below to add that entry.";
        $htmltbl = "";
    } else {
        $msg = "Found {$i} results for '<b>{$search}</b>'";
    }
    $htmlresults = "<div id=\"pTitle\">{$msg}</div>" . $htmltbl;
    return $htmlresults;
}

/**
 * Parses the AIML <set> tag, storing it's value in the database
 *
 * @param array $convoArr
 * @param SimpleXMLElement $element
 * @param string $parentName
 * @param int $level
 * @return string
 */
function parse_set_tag(&$convoArr, $element, $parentName, $level)
{
    runDebug(__FILE__, __FUNCTION__, __LINE__, 'Parsing the SET tag.', 2);
    global $dbConn, $dbn, $user_name, $remember_up_to;
    $var_value = tag_to_string($convoArr, $element, $parentName, $level, 'element');
    $bot_id = $convoArr['conversation']['bot_id'];
    $user_id = $convoArr['conversation']['user_id'];
    $var_name = (string) $element->attributes()->name;
    $var_name = $var_name == '*' ? $convoArr['star'][1] : $var_name;
    for ($n = 2; $n <= $remember_up_to; $n++) {
        $var_name = $var_name == "*{$n}" ? $convoArr['star'][$n] : $var_name;
    }
    $vn_type = gettype($var_name);
    runDebug(__FILE__, __FUNCTION__, __LINE__, "var_name = {$var_name} and is type: {$vn_type}", 4);
    if ($var_name == 'name') {
        $user_name = $var_value;
        $escaped_var_value = $var_value;
        $sql = "UPDATE `{$dbn}`.`users` set `user_name` = '{$escaped_var_value}' where `id` = {$user_id};";
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Updating user name in the DB. SQL:\n{$sql}", 3);
        $sth = $dbConn->prepare($sql);
        $sth->execute();
        $numRows = $sth->rowCount();
        $sql = "select `user_name` from `{$dbn}`.`users` where `id` = {$user_id} limit 1;";
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Checking the users table to see if the value has changed. - SQL:\n{$sql}", 3);
        $row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
        $rowCount = count($row);
        if ($rowCount != 0) {
            $tmp_name = $row['user_name'];
            runDebug(__FILE__, __FUNCTION__, __LINE__, "The value for the user's name is {$tmp_name}.", 4);
        }
    } else {
        $convoArr['client_properties'][$var_name] = $var_value;
    }
    $lc_var_name = IS_MB_ENABLED ? mb_strtolower($var_name) : strtolower($var_name);
    if ($lc_var_name == 'topic') {
        $convoArr['topic'][1] = $var_value;
    }
    $sql = "select `value` from `{$dbn}`.`client_properties` where `user_id` = {$user_id} and `bot_id` = {$bot_id} and `name` = '{$var_name}';";
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Checking the client_properties table for the value of {$var_name}. - SQL:\n{$sql}", 3);
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $rowCount = count($result);
    /** @noinspection PhpSillyAssignmentInspection */
    $var_name = $var_name;
    $var_name = str_replace("'", "\\'", $var_name);
    /** @noinspection PhpSillyAssignmentInspection */
    $var_value = $var_value;
    $var_value = str_replace("'", "\\'", $var_value);
    if ($rowCount == 0) {
        $sql = "insert into `{$dbn}`.`client_properties` (`id`, `user_id`, `bot_id`, `name`, `value`)\n      values (NULL, {$user_id}, {$bot_id}, '{$var_name}', '{$var_value}');";
        runDebug(__FILE__, __FUNCTION__, __LINE__, "No value found for {$var_name}. Inserting {$var_value} into the table.", 4);
    } else {
        $sql = "update `{$dbn}`.`client_properties` set `value` = '{$var_value}' where `user_id` = {$user_id} and `bot_id` = {$bot_id} and `name` = '{$var_name}';";
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Value found for {$var_name}. Updating the table to  {$var_value}.", 4);
    }
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Saving to DB - SQL:\n{$sql}", 3);
    $sth = $dbConn->prepare($sql);
    $sth->execute();
    $rowCount = $sth->rowCount();
    $response = $var_value;
    $convoArr['client_properties'][$var_name] = $var_value;
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Value for {$var_name} has ben set. Returning {$var_value}.", 4);
    return $response;
}

/**
 * function find_aiml_matches()
 * This function builds the sql to use to get a match from the tbl
 * @param array $convoArr - conversation array
 * @return array $convoArr
 **/
function find_aiml_matches($convoArr)
{
    global $dbConn, $dbn, $error_response, $use_parent_bot;
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Finding the aiml matches from the DB", 4);
    $i = 0;
    //TODO convert to get_it
    $bot_id = $convoArr['conversation']['bot_id'];
    $bot_parent_id = $convoArr['conversation']['bot_parent_id'];
    $default_aiml_pattern = $convoArr['conversation']['default_aiml_pattern'];
    #$lookingfor = get_convo_var($convoArr,"aiml","lookingfor");
    $convoArr['aiml']['lookingfor'] = str_replace('  ', ' ', $convoArr['aiml']['lookingfor']);
    $lookingfor = trim(strtoupper($convoArr['aiml']['lookingfor']));
    //get the first and last words of the cleaned user input
    $lastInputWord = get_last_word($lookingfor);
    $firstInputWord = get_first_word($lookingfor);
    //get the stored topic
    $storedtopic = get_topic($convoArr);
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Stored topic = '{$storedtopic}'", 4);
    //get the cleaned user input
    $lastthat = isset($convoArr['that'][1][1]) ? $convoArr['that'][1][1] : '';
    //build like patterns
    if ($lastthat != '') {
        $thatPatternSQL = " OR " . make_like_pattern($lastthat, 'thatpattern');
        $thatPatternSQL = rtrim($thatPatternSQL, ' OR');
    } else {
        $thatPattern = '';
        $thatPatternSQL = '';
    }
    //get the word count
    $word_count = wordsCount_inSentence($lookingfor);
    if ($bot_parent_id != 0 and $bot_parent_id != $bot_id) {
        $sql_bot_select = " (bot_id = '{$bot_id}' OR bot_id = '{$bot_parent_id}') ";
    } else {
        $sql_bot_select = " bot_id = '{$bot_id}' ";
    }
    if (!empty($storedtopic)) {
        $topic_select = "AND (`topic`='' OR `topic`='{$storedtopic}')";
    } else {
        $topic_select = '';
    }
    if ($word_count == 1) {
        //if there is one word do this
        $sql = "SELECT `id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` WHERE\n  {$sql_bot_select} AND (\n  `pattern` = '_' OR\n  `pattern` = '*' OR\n  `pattern` = '{$lookingfor}' OR\n  `pattern` = '{$default_aiml_pattern}'\n  {$thatPatternSQL}\n  ) {$topic_select} order by `topic` desc, `pattern` asc, `thatpattern` asc,`id` asc;";
    } else {
        //otherwise do this
        $sql_add = make_like_pattern($lookingfor, 'pattern');
        $sql = "SELECT `id`, `bot_id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` WHERE\n  {$sql_bot_select} AND (\n  `pattern` = '_' OR\n  `pattern` = '*' OR\n  `pattern` = '{$lookingfor}' OR {$sql_add} OR\n  `pattern` = '{$default_aiml_pattern}'\n  {$thatPatternSQL}\n  ) {$topic_select}\n  order by `topic` desc, `pattern` asc, `thatpattern` asc,`id` asc;";
    }
    runDebug(__FILE__, __FUNCTION__, __LINE__, "Core Match AIML sql: {$sql}", 3);
    $result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
    $num_rows = count($result);
    if ($result && $num_rows > 0) {
        $tmp_rows = number_format($num_rows);
        runDebug(__FILE__, __FUNCTION__, __LINE__, "FOUND: ({$num_rows}) potential AIML matches", 2);
        $tmp_content = date('H:i:s') . ": SQL:\n{$sql}\nRows = {$tmp_rows}\n\n";
        //loop through results
        foreach ($result as $row) {
            $row['aiml_id'] = $row['id'];
            $row['bot_id'] = $bot_id;
            $row['score'] = 0;
            $row['track_score'] = '';
            $allrows[] = $row;
            $mu = memory_get_usage(true);
            if ($mu >= MEM_TRIGGER) {
                runDebug(__FILE__, __FUNCTION__, __LINE__, 'Current operation exceeds memory threshold. Aborting data retrieval.', 0);
                break;
            }
        }
    } else {
        runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
        $allrows[$i]['aiml_id'] = "-1";
        $allrows[$i]['bot_id'] = $bot_id;
        $allrows[$i]['pattern'] = "no results";
        $allrows[$i]['thatpattern'] = '';
        $allrows[$i]['topic'] = '';
    }
    return $allrows;
}