function db_create_form_processing($connection, $table) { $connection = $connection; $table = $table; $display = $table; $query = ""; $form_is_empty = ""; function empty_form_test() { $filled_form_fields_count = ""; $form_is_empty = ""; foreach ($_POST as $key => $value) { if (!empty($value)) { $filled_form_fields_count += 1; } } if ($filled_form_fields_count > 1) { $form_is_empty = FALSE; } else { $form_is_empty = TRUE; } return $form_is_empty; } function construct_query($table) { $table = $table; $query = ""; $fields = ""; $columns = ""; $values = ""; // May want to re-factor global declaration. global $connection; if (!empty($_POST)) { /* //Debugging //echo "POST Debugging: <br/>"; //print_r($_POST); //Debugging */ $fields = $_POST; unset($fields['submit']); // Query Format Validation /* function format_validation($fields) { // Notice: global $connection; being used. May want to re-factor. function primary_validation($fields) { global $connection; // Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z. foreach ($fields as $key => $value) { // String test not working. if (is_string($value)) { $value = mysqli_real_escape_string($connection, $value); } } return $fields; } //function secondary_validation($fields) { // global $connection; //} $fields = primary_validation($fields); //secondary_validation($fields); return $fields; } $fields = format_validation($fields); */ // Query Creation Prep function input_count($fields) { $fields = $fields; $input_count = ""; foreach ($fields as $key => $value) { if (!empty($value)) { ++$input_count; } } return $input_count; } $input_count = input_count($fields); // Query Creation // mysqli_real_escape_string formats values with special characters so they can be stored in the database and not alter the format of the SQL query statement. foreach ($fields as $key => $value) { if (!empty($value) && $input_count > 1) { $value = mysqli_real_escape_string($connection, $value); $columns .= $key . ", "; $values .= "'" . $value . "'" . ", "; --$input_count; } elseif (!empty($value) && $input_count == 1) { $value = mysqli_real_escape_string($connection, $value); $columns .= $key; $values .= "'" . $value . "'"; } } $query = "INSERT INTO "; $query .= "{$table} ("; $query .= "{$columns}) "; $query .= "VALUES ("; $query .= "{$values})"; //Debugging //echo "<br/><br/>Query Debugging: <br/>" . $query . "</br>"; //Debugging } else { $query = ""; } return $query; } function run_query($connection, $query, $display, $form_is_empty) { $connection = $connection; $query = $query; $display = $display; $form_is_empty = $form_is_empty; function db_interfacing($connection, $query, $form_is_empty) { $connection = $connection; $query = $query; $form_is_empty = $form_is_empty; function send_query($connection, $query, $empty_form_test) { $connection = $connection; $form_is_empty = $empty_form_test; $query = $query; if ($form_is_empty == FALSE) { $result = mysqli_query($connection, $query); return $result; } } $result = send_query($connection, $query, $form_is_empty); return $result; } function report($result, $display, $form_is_empty) { //$result = $result; //$display = $display; //$form_is_empty = $form_is_empty; if ($result) { echo "Successfully added {$display}."; redirect_to("submission.php?display=personnel", 0); } elseif (!empty($_POST) && ($form_is_empty = TRUE)) { echo "Please enter data and click 'Add'."; } } $result = db_interfacing($connection, $query, $form_is_empty); report($result, $display, $form_is_empty); } $form_is_empty = empty_form_test(); $query = construct_query($table); run_query($connection, $query, $display, $form_is_empty); }
function construct_query($query, $not, $level, $argn = "", $oper = "") { global $corresp, $search, $corresp_op; //La requête commence-t-elle par and, or ou and not ? $pos = strpos($query, "and not"); if ($pos !== false && $pos == 0) { $ope = "ex"; } else { $pos = strpos($query, "or"); if ($pos !== false && $pos == 0) { $ope = "or"; } else { $pos = strpos($query, "and"); if ($pos !== false && $pos == 0) { $ope = "and"; } else { $ope = ""; } } } if ($ope != "") { //Si opérateur, recherche des arguments $arqs = array(); preg_match("/^" . ($ope == "ex" ? "and not" : $ope) . " arg" . $level . "!1\\((.*)\\) arg" . $level . "!2\\((.*)\\)\$/", $query, $args); //print "/^".$ope." arg".$level."!1\((.*)\) arg".$level."!2\((.*)\)$/"; //print_r($args); $return1 = construct_query($args[1], 0, $level + 1, 1, $ope); if ($oper && $return1) { $inter = "inter_" . ($level - 2 + $argn) . "_f_" . $return1; global ${$inter}; if (!${$inter}) { ${$inter} = $oper; } //print $inter."=".$$inter."<br />"; } $return2 = construct_query($args[2], 0, $level + 1, 2, $ope); if ($return2) { //print $level." ".$argn; if ($argn == "") { $argn = 2; } $inter = "inter_" . ($level - 1 + $argn) . "_f_" . $return2; global ${$inter}; if (!${$inter}) { ${$inter} = $ope; } //print $inter."=".$$inter."<br />"; } return; } else { $use = explode("=", $query); $idf = $corresp[$use[0]]; if (!$idf) { make_error(3, "1=" . $use[0]); } else { $search[] = "f_" . $idf; $vals = array(); $vals[0] = traite_val($use[1], $idf); $field = "field_" . (!$level ? 0 : $level - 2 + $argn) . "_f_" . $idf; global ${$field}; ${$field} = $vals; $op = "op_" . (!$level ? 0 : $level - 2 + $argn) . "_f_" . $idf; global ${$op}; ${$op} = $corresp_op[$use[0]]; return $idf; } } return; }
function construct_query($query, $not, $level, $argn = "") { //La requête commence-t-elle par and, or ou and not ? $pos = strpos($query, "and not"); if ($pos !== false && $pos == 0) { $ope = "and not"; } else { $pos = strpos($query, "or"); if ($pos !== false && $pos == 0) { $ope = "or"; } else { $pos = strpos($query, "and"); if ($pos !== false && $pos == 0) { $ope = "and"; } else { $ope = ""; } } } if ($ope != "") { //Si opérateur, recherche des arguments $arqs = array(); preg_match("/^" . $ope . " arg" . $level . "!1\\((.*)\\) arg" . $level . "!2\\((.*)\\)\$/", $query, $args); $return1 = construct_query($args[1], 0, $level + 1, 1); if ($ope == "and not") { $return2 = construct_query($args[2], 1, $level + 1, 2); } else { $return2 = construct_query($args[2], 0, $level + 1, 2); } if ($ope == "and not") { $ope = "and"; } $requete = "create temporary table r{$level} ENGINE=MyISAM "; if ($ope == "and") { $requete .= "select distinct {$return1}.notice_id from {$return1}, {$return2} where {$return1}.notice_id={$return2}.notice_id"; @pmb_mysql_query($requete); } else { $requete .= "select distinct notice_id from {$return1}"; @pmb_mysql_query($requete); $requete = "insert into r{$level} select distinct notice_id from {$return2} "; @pmb_mysql_query($requete); } $return = "r{$level}"; } else { $use = explode("=", $query); switch ($use[0]) { //Titre case 4: if ($not) { $requete = "select distinct notice_id from notices where (index_wew not like '%" . $use[1] . "%' )"; } else { $requete = "select distinct notice_id from notices where (index_wew like '%" . $use[1] . "%' )"; } break; //ISBN //ISBN case 7: if (isISBN($use[1])) { // si la saisie est un ISBN $code = formatISBN($use[1]); // si échec, ISBN erroné on le prend sous cette forme if (!$code) { $code = $use[1]; } } else { $code = $use[1]; } if ($not) { $requete = "select notice_id from notices where (code!='" . $code . "')"; } else { $requete = "select notice_id from notices where (code='" . $code . "')"; } break; // Auteur // Auteur case 1003: if ($not) { $requete = "create temporary table aut ENGINE=MyISAM select distinct responsability.responsability_notice as notice_id, index_author as auth from authors, responsability where responsability_author = author_id "; @pmb_mysql_query($requete); $requete = "select distinct notice_id from aut where auth not like '%" . $use[1] . "%'"; } else { $requete = "select distinct notice_id from responsability, authors, notices where index_author like '%" . $use[1] . "%' and author_id=responsability_author and notice_id=responsability_notice "; } break; default: make_error(3, "1=" . $use[0]); break; } $requete = "create temporary table r" . $level . "_" . $argn . " ENGINE=MyISAM " . $requete; @pmb_mysql_query($requete); $return = "r" . $level . "_" . $argn; } return $return; }
function search_resource($Q) { extract($Q); #echo '<pre>';print_r($_POST); if ($GLOBALS['s3db_info']['server']['db']['db_type'] == 'mysql') { $begin = ''; $intersect = 'and resource_id in'; $union = ') or resource_id in'; $end = ')'; $extra = ''; } else { $begin = '('; $intersect = ') intersect'; $union = ') union'; $end = ''; $extra = ')'; } $final_query = 'select distinct resource_id, resource_class_id, entity, notes, created_by, created_on from s3db_resource where resource_id in ' . $begin; $display_query = 'select distinct resource_id, resource_class_id, entity, notes, created_by, created_on from s3db_resource where resource_id in ' . $begin; $_SESSION['used_rule'] = ''; #echo $final_query;exit; //$_SESSION['rule_value_pairs'] = array(); #echo '<pre>';print_r($rules); //print_r($querable_rule); if (is_array($rules)) { $query_rule = array(); $used_rule = array(); $query = ''; //$found = False; foreach ($rules as $rule_id => $rule_info) { $sqlquery = construct_query(compact('rule_info', 'rule_value_pairs', 'db')); if ($sqlquery != '') { #echo 'ola'.$sqlquery;exit; $not_and_or = 'rule_' . $rule_info['rule_id']; if ($_POST[$not_and_or] == 'and' || $_POST[$not_and_or] == '') { $query .= construct_query(compact('rule_info', 'rule_value_pairs', 'db')) . ' ' . $intersect . ' '; } else { if ($_POST[$not_and_or] == 'or') { $query .= construct_query(compact('rule_info', 'rule_value_pairs', 'db')) . ' ' . $union . ' '; } } #else #$query.= construct_query($rule_info).' intersect '; //$query.= construct_query($queriable_rule[$i]); array_push($used_rule, $rule_info['rule_id']); array_push($query_rule, $rule_info); } } #echo $query;exit; if ($query != '') { //echo $query; $query = trim($query); if (strrpos($query, " " . $intersect) && substr($query, strrpos($query, " " . $intersect)) == ' ' . $intersect) { $query = substr($query, 0, strrpos($query, " " . $intersect)) . ')' . $extra; } else { if (strrpos($query, " " . $union) && substr($query, strrpos($query, " " . $union)) == ' ' . $union) { $query = substr($query, 0, strrpos($query, " " . $union)) . ')' . $extra; } else { $query .= ')'; } } #$query .= ')'; if ($query != '' && trim($orderBy) != '') { $query .= ' order by ' . $orderBy; } $final_query .= $query; $display_query .= $query; #echo $display_query; } else { $final_query = 'You need to specify some search criteria.'; //$display_query = 'All instance of resource <b>'.$queriable_rule[0]['subject'].'</b>'; $display_query = $final_query; } } #echo $final_query; $_SESSION['displayquery'] = $display_query; $db->query($final_query, __LINE__, __FILE__); while ($db->next_record()) { // $found_resources[] = Array('resource_id'=>$db->f('resource_id')); $found_resources[] = array('resource_id' => $db->f('resource_id'), 'entity' => $db->f('entity'), 'created_by' => $db->f('created_by'), 'created_on' => $db->f('created_on'), 'resource_class_id' => $db->f('resource_class_id'), 'notes' => $db->f('notes')); } //print_r($found_resources); //$found_instances = get_found_resources($found_resources); $found_instances = $found_resources; $found_instances['sqlquery'] = $display_query; $_SESSION['used_rule'] = $used_rule; $_SESSION['query_rule'] = $query_rule; return $found_instances; }
function construct_query($query, $not, $level, $argn = "") { //La requête commence-t-elle par and, or ou and not ? $pos = strpos($query, "and not"); if ($pos !== false && $pos == 0) { $ope = "and not"; } else { $pos = strpos($query, "or"); if ($pos !== false && $pos == 0) { $ope = "or"; } else { $pos = strpos($query, "and"); if ($pos !== false && $pos == 0) { $ope = "and"; } else { $ope = ""; } } } if ($ope != "") { $arqs = array(); preg_match("/^" . $ope . " arg" . $level . "!1\\((.*)\\) arg" . $level . "!2\\((.*)\\)\$/", $query, $args); $return1 = construct_query($args[1], 0, $level + 1, 1); if ($ope == "and not") { $return2 = construct_query($args[2], 1, $level + 1, 2); } else { $return2 = construct_query($args[2], 0, $level + 1, 2); } if ($ope == "and not") { $ope = "and"; } $requete = "create temporary table r{$level} "; if ($ope == "and") { $requete .= "select {$return1}.notice_id from {$return1}, {$return2} where {$return1}.notice_id={$return2}.notice_id"; @pmb_mysql_query($requete); } else { $requete .= "select notice_id from {$return1}"; @pmb_mysql_query($requete); $requete = "insert into r{$level} select {$return2}.notice_id from {$return2},{$return1} where {$return2}.notice_id!={$return1}.notice_id"; @pmb_mysql_query($requete); } $return = "r{$level}"; } else { $use = explode("=", $query); switch ($use[0]) { //Titre case 4: if ($not) { $requete = "select notice_id from notices where (tit1 not like '%" . $use[1] . "%' and tit2 not like '%" . $use[1] . "%' and tit3 not like '%" . $use[1] . "%' and tit4 not like '%" . $use[1] . "%')"; } else { $requete = "select notice_id from notices where (tit1 like '%" . $use[1] . "%' or tit2 like '%" . $use[1] . "%' or tit3 like '%" . $use[1] . "%' or tit4 like '%" . $use[1] . "%')"; } break; //ISBN //ISBN case 7: if (isISBN($use[1])) { // si la saisie est un ISBN $code = formatISBN($use[1]); // si échec, ISBN erroné on le prend sous cette forme if (!$code) { $code = $use[1]; } } else { $code = $use[1]; } if ($not) { $requete = "select notice_id from notices where (code!='" . $code . "')"; } else { $requete = "select notice_id from notices where (code='" . $code . "')"; } break; case 1003: if ($not) { $requete = "create temporary table aut1 select notice_id,concat(author_name,' ',author_rejete) as auth from notices left join authors on author_id=aut1_id"; @pmb_mysql_query($requete); $requete = "create temporary table aut2 select notice_id,concat(author_name,' ',author_rejete) as auth from notices left join authors on author_id=aut2_id"; @pmb_mysql_query($requete); $requete = "create temporary table aut3 select notice_id,concat(author_name,' ',author_rejete) as auth from notices left join authors on author_id=aut3_id"; @pmb_mysql_query($requete); $requete = "create temporary table aut4 select notice_id,concat(author_name,' ',author_rejete) as auth from notices left join authors on author_id=aut4_id"; @pmb_mysql_query($requete); $requete = "create temporary table aut select aut1.notice_id, concat(ifnull(aut1.auth,''),' ',ifnull(aut2.auth,''),' ',ifnull(aut3.auth,''),' ',ifnull(aut4.auth,'')) as auth from aut1, aut2, aut3, aut4 where aut2.notice_id=aut1.notice_id and aut3.notice_id=aut1.notice_id and aut4.notice_id=aut1.notice_id"; @pmb_mysql_query($requete); $requete = "select notice_id from aut where auth not like '%" . $use[1] . "%'"; } else { $requete = "select notice_id from notices,authors where (concat(author_name,' ',author_rejete) like '%" . $use[1] . "%' and (author_id=aut1_id or author_id=aut2_id or author_id=aut3_id or author_id=aut3_id))"; } break; // Sujet - Catégories // Sujet - Catégories case 21: if ($not) { $requete = "CREATE TEMPORARY TABLE cat SELECT DISTINCT notices_categories.notcateg_notice as notice_id, index_categorie as cat FROM categories, notices_categories WHERE notcateg_categorie = categ_id ORDER BY notices_categories.ordre_categorie"; @pmb_mysql_query($requete); $requete = "SELECT DISTINCT notice_id FROM cat WHERE cat not like '%" . $use[1] . "%'"; } else { $requete = "SELECT DISTINCT notice_id FROM notices_categories, categories, notices WHERE categ_id=notcateg_categorie AND notice_id=notcateg_notice AND index_categorie like '%" . $use[1] . "%'"; } break; default: make_error(3, "1=" . $use[0]); break; } $requete = "create temporary table r" . $level . "_" . $argn . " " . $requete; @pmb_mysql_query($requete); $return = "r" . $level . "_" . $argn; } return $return; }