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;
}
