function scriptCheckIfUnique($select, $errMsg)
{
$mysqli = connect_DB();
$message = "";
$result = $mysqli->query($select);
$numrows = $result->num_rows;
if ($numrows > 0) {
$message = $errMsg;
}
return $message;
}
<?php
require '../Core/SCV2_Core.php';
init_SCV2();
if (xsrf_guard()) {
if ($_POST['btnCancel']) {
header("location: " . HOME_PAGE);
exit;
}
}
$mysqli = connect_DB();
$mysqli->real_query("SELECT Table_ID, Table_Name, Remarks FROM `table` WHERE `Project_ID`='{$_SESSION['Project_ID']}' ORDER BY `Table_Name`");
drawHeader();
drawPageTitle('List View: Tables', $errMsg);
?>
<fieldset class="container">
<?php
drawButton('CANCEL');
?>
<a class='blue' href='CreateTables.php'>Create New Table</a> :: <a class=blue href=Import_Tables.php>Import Tables</a>
<table border=1 width=100% class="listView">
<tr class="listRowHead">
<td width="140">Operations</td>
<td>Table Name</td>
<td>Remarks</td>
</tr>
<?php
if ($result = $mysqli->use_result()) {
$a = 0;
$class = '';
while ($row = $result->fetch_assoc()) {
} else {
die($mysqli->error);
}
$mysqli->close();
$mysqli = connect_DB();
$mysqli->real_query("SELECT a.Field_Name FROM table_fields a, table_fields_list_source_link b WHERE a.Field_ID=b.Field_ID AND a.Field_ID='{$Field_ID}'");
if ($result = $mysqli->store_result()) {
$data = $result->fetch_assoc();
$Link_Field = $data['Field_Name'];
}
$mysqli->close();
$mysqli_validation_routines = connect_DB();
$mysqli_validation_routines->real_query("SELECT Validation_Routine \n FROM `table_fields_secondary_validation` \n WHERE Field_ID='{$Field_ID}'");
$mysqli_select_parameters = connect_DB();
$mysqli_select_parameters->real_query("SELECT b.Field_Name, a.Display \n FROM `table_fields_list_source_select` a, \n `table_fields` b \n WHERE a.Field_ID='{$Field_ID}' AND a.Select_Field_ID = b.Field_ID");
$mysqli_where_parameters = connect_DB();
$mysqli_where_parameters->real_query("SELECT b.Field_Name, a.Where_Field_Operand, a.Where_Field_Value, a.Where_Field_Connector \n FROM `table_fields_list_source_where` a, \n `table_fields` b \n WHERE a.Field_ID='{$Field_ID}' AND a.Where_Field_ID = b.Field_ID");
} elseif (xsrf_guard()) {
init_var($_POST['btnCancel']);
if ($_POST['btnCancel']) {
header('location: ListView_TableFields.php');
exit;
}
}
drawHeader();
drawPageTitle('Detail View: Table Field', $errMsg);
?>
<div class="container_mid">
<fieldset class="top">
View Table Field: <?php
echo $Field_Name;
<html>
<head>
<title>Setup DB</title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
<style>
body { font-family: verdana; padding: 5px; }
#info { background-color: #ddd; padding: 5px; }
</style>
</head>
<body>
<?php
include 'connect.php';
$dbh = connect_DB() or die('Cannot connect to DB!');
$dbdriver = '';
if ($dbenv[dbdriver] == 1) $dbdriver = 'SQLITE';
if ($dbenv[dbdriver] == 2) $dbdriver = 'MYSQL';
$html=<<<HTML
<div id="info">
/app/app_config.php<br/>
$dbdriver - Host: $dbenv[host] - DB: $dbenv[dbname] <p/>** For MySQL, Make sure you create DB first. <br/>** Verify SQL script file: prepare_tables_*.sql files. <p/>
</div>
HTML;
echo $html;
?>
<p/><h3>Recreate and Setup Database:</h3><p/>
<ul>
<li><a href="create_tables.php">Create Tables</a></li>
<li><a href="create_data.php">Create Data</a></li>
function rollback_field_from_relationship($Relation_ID)
{
//******************************************************************************
//*** START: ROLLBACK OF FIELD INFO UPON REMOVAL OF RELATIONSHIP ***************
//******************************************************************************
//We have to undo changes in the child field
$mysqli = connect_DB();
//Get the Child Field involved
$mysqli->real_query("SELECT Child_Field_ID\n FROM `table_relations`\n WHERE Relation_ID='{$Relation_ID}'");
if ($result = $mysqli->use_result()) {
while ($data = $result->fetch_assoc()) {
$Child_Field_ID = $data['Child_Field_ID'];
}
}
//Delete the SQL list settings (for 1-1 relationships)
$mysqli->real_query("DELETE FROM table_fields_list_source_select WHERE Field_ID='{$Child_Field_ID}'");
$mysqli->real_query("DELETE FROM table_fields_list_source_where WHERE Field_ID='{$Child_Field_ID}'");
//See what the attribute value is.
//- if "primary&foregin key", change back to "primary".
//- if "foreign key", change back to "none".
$mysqli->real_query("SELECT Attribute FROM table_fields WHERE Field_ID='{$Child_Field_ID}'");
if ($result = $mysqli->use_result()) {
while ($data = $result->fetch_assoc()) {
$Child_Field_Attribute = $data['Attribute'];
}
}
if ($Child_Field_Attribute == 'primary&foreign key') {
$new_attribute = 'primary key';
} else {
$new_attribute = 'none';
}
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("UPDATE table_fields SET Attribute=? WHERE Field_ID=?")) {
$stmt->bind_param("ss", $new_attribute, $Child_Field_ID);
$stmt->execute();
$stmt->close();
} else {
die($stmt->error);
}
//After undoing the attribute, we now have to undo the change to the control type (for 1-1 relationships).
//We need to look at this field's metadata to determine how to roll it back to default
$mysqli->real_query("SELECT Field_Name, Data_Type FROM table_fields WHERE Field_ID='{$Child_Field_ID}'");
if ($result = $mysqli->use_result()) {
while ($data = $result->fetch_assoc()) {
$Child_Field_Name = $data['Field_Name'];
$Child_Data_Type = $data['Data_Type'];
}
}
$arr_textarea_names = get_textarea_field_names();
if (in_array(strtoupper($Child_Field_Name), $arr_textarea_names)) {
$control_type = 'textarea';
} else {
switch ($Child_Data_Type) {
case 'text':
$control_type = 'textarea';
break;
case 'date':
$control_type = 'date controls';
break;
default:
$control_type = 'textbox';
}
}
if (strtoupper($Child_Field_Name) == 'ID') {
$label = 'ID';
} else {
$label = str_replace('_', ' ', $Child_Field_Name);
$label = ucwords($label);
//if field contains "Id" as a word somewhere in the middle, change to "ID"
$label = str_replace(' Id ', ' ID ', $label);
if (substr($label, 0, 3) == 'Id ') {
//Field name starts with 'Id', change to 'ID ' (e.g., field name was originally "id_number")
$label = 'ID ' . substr($label, 3);
}
if (substr($label, strlen($label) - 3) == ' Id') {
//Field name ends with 'Id', change to ' ID' (e.g., field name was originally "employee_id")
$label = substr($label, 0, strlen($label) - 3) . ' ID';
}
}
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("UPDATE table_fields SET Control_Type=?, Label=? WHERE Field_ID=?")) {
$stmt->bind_param("sss", $control_type, $label, $Child_Field_ID);
$stmt->execute();
$stmt->close();
} else {
die($stmt->error);
}
//******************************************************************************
//*** END: ROLLBACK OF FIELD INFO UPON REMOVAL OF RELATIONSHIP *****************
//******************************************************************************
}
$errMsg .= scriptCheckIfNull('Child Field Subtext', $Child_Field_Subtext);
if ($errMsg == '') {
//Check if chosen fields actually exist in parent
//--Get Table ID
$Table_ID = '';
$db_handle = connect_DB();
$db_handle->real_query("SELECT Table_ID\n FROM `table_fields`\n WHERE Field_ID = '{$Parent_Field_ID}'");
if ($result = $db_handle->use_result()) {
while ($row = $result->fetch_assoc()) {
$Table_ID = $row['Table_ID'];
}
$result->close();
}
//--Get the fields of this table
$arr_fields = array();
$db_handle = connect_DB();
$db_handle->real_query("SELECT Field_Name\n FROM `table_fields`\n WHERE Table_ID = '{$Table_ID}'\n ORDER BY Field_Name ");
if ($result = $db_handle->use_result()) {
while ($row = $result->fetch_assoc()) {
$arr_fields[] = $row['Field_Name'];
}
$result->close();
}
//--breakdown the chosen fields
$arr_chosen_fields = explode(',', $Child_Field_Subtext);
//--see if all chosen fields exist in the retreived parent fields
foreach ($arr_chosen_fields as $subtext) {
$subtext = trim($subtext);
if (in_array($subtext, $arr_fields)) {
//found, valid
} else {
function list_from_SQL_settings($Field_ID, $num_databases)
{
$mysqli = connect_DB();
$mysqli->real_query("SELECT b.Field_Name AS `Select_Field_Name`, a.Display, c.Table_Name, d.Database\n FROM table_fields_list_source_select a, table_fields b, `table` c, `database_connection` d\n WHERE a.Field_ID='{$Field_ID}' AND\n a.Select_Field_ID=b.Field_ID AND\n b.Table_ID = c.Table_ID AND\n c.DB_Connection_ID = d.DB_Connection_ID\n ORDER BY a.Auto_ID ASC");
if ($result = $mysqli->use_result()) {
$select_fields = array();
$select_tables = array();
$select_display = 'array(';
$select_value = '';
//We'll only accept one value for the select field's value, so we don't need an array
$order_by_fields = array();
while ($data = $result->fetch_assoc()) {
extract($data);
if (!in_array($Select_Field_Name, $select_fields)) {
if ($Display == "Yes") {
if ('Queried_' . $Select_Field_Name == $select_value) {
$Select_Field_Name = 'Queried_' . $Select_Field_Name;
$select_display .= "'{$Select_Field_Name}', ";
} else {
$select_display .= "'{$Select_Field_Name}', ";
$select_fields[] = array('Field' => $Select_Field_Name, 'Table' => $Table_Name);
}
$order_by_fields['Table'][] = $Table_Name;
$order_by_fields['Field'][] = $Select_Field_Name;
} else {
$select_value = 'Queried_' . $Select_Field_Name;
$Select_Field_Name = $Select_Field_Name . ' AS `' . $select_value . '`';
$select_fields[] = array('Field' => $Select_Field_Name, 'Table' => $Table_Name);
}
}
if ($num_databases > 1) {
if (!in_array($Database . '.' . $Table_Name, $select_tables)) {
$select_tables[] = $Database . '.' . $Table_Name;
}
} else {
if (!in_array($Table_Name, $select_tables)) {
$select_tables[] = $Table_Name;
}
}
}
$result->close();
$mysqli->close();
$select_display = substr($select_display, 0, strlen($select_display) - 2);
//remove last comma and space.
$select_display .= ')';
//close the array declaration.
} else {
die($mysqli->error);
}
$mysqli = connect_DB();
$mysqli->real_query("SELECT b.Field_Name AS `Where_Field_Name`, Where_Field_Operand, Where_Field_Value, Where_Field_Connector\n FROM table_fields_list_source_where a, table_fields b\n WHERE a.Field_ID='{$Field_ID}' AND\n a.Where_Field_ID=b.Field_ID");
if ($result = $mysqli->store_result()) {
if ($result->num_rows == 0) {
$where_fields = "NONE";
} else {
$where_fields = array();
while ($data = $result->fetch_assoc()) {
extract($data);
$where_fields[] = array('Field' => $Where_Field_Name, 'Operand' => $Where_Field_Operand, 'Value' => $Where_Field_Value, 'Connector' => $Where_Field_Connector);
}
$result->close();
$mysqli->close();
}
}
//****Create the query here.*********
//Set the SELECT clause (fields)
$select_query = 'SELECT ';
$num_fields = count($select_fields);
for ($b = 0; $b < $num_fields; $b++) {
$select_query .= $select_fields[$b]['Table'] . '.' . $select_fields[$b]['Field'] . ', ';
}
$select_query = substr($select_query, 0, strlen($select_query) - 2);
//removed last space and comma.
//Set the FROM clause (tables)
$select_query .= ' FROM ';
foreach ($select_tables as $table) {
$select_query .= "{$table}, ";
}
$select_query = substr($select_query, 0, strlen($select_query) - 2);
//removed last space and comma.
//Set the WHERE clause (conditions)
if ($where_fields != 'NONE') {
$select_query .= ' WHERE ';
foreach ($where_fields as $where) {
if ($where['Connector'] == 'NONE') {
$where['Connector'] = '';
}
$select_query .= $where['Field'] . $where['Operand'] . "'" . $where['Value'] . "' " . $where['Connector'] . ' ';
}
}
//Set the ORDER BY clause based on the list of fields shown, ASC, ranked in the order they are displayed
$select_query .= ' ORDER BY ';
$include_table_name = TRUE;
if (count($select_tables) == 1) {
//There is only one table involved, no need to use `table`.`field` format, just `field`.
$include_table_name = FALSE;
}
$select_query = substr($select_query, 0, strlen($select_query) - 2);
//removed last space and comma.
//********Aaaaand, we're done creating the query!**********
$settings = '';
$settings .= <<<EOD
'query' => "{$select_query}",
'list_value' => '{$select_value}',
'list_items' => {$select_display},
'list_separators' => array()
EOD;
//NOTE: The indentations above are necessary so the resulting file will have the items lined up nicely.
return $settings;
}
function drawTablePage($Page_ID, $dynamicList = FALSE, $name = 'Page_ID')
{
if ($dynamicList == TRUE) {
echo '<select name="' . $name . '[]">';
} else {
echo '<select name="' . $name . '">';
}
$db_handle = connect_DB();
$db_handle->real_query("SELECT Page_ID, Page_Name FROM page ORDER BY Page_Name");
if ($result = $db_handle->use_result()) {
while ($row = $result->fetch_assoc()) {
$mark = "";
if ($row['Page_ID'] == $Page_ID) {
$mark = 'selected';
}
printf("<option value='%s' {$mark}>%s</option>\n", $row['Page_ID'], $row['Page_Name']);
}
$result->close();
}
echo '</select>';
}
<?php
include_once 'config.php';
connect_DB();
$query = "DELETE FROM articles WHERE id_article = " . $_GET['id'];
$result = mysql_query($query);
if (!$result) {
die('ќшибка базы данных:' . mysql_error());
}
/*
My coment!!!
*/
$mysqli->real_query("SHOW TABLES");
if ($result = $mysqli->store_result()) {
for ($a = 0; $a < $result->num_rows; $a++) {
$data = $result->fetch_row();
if (!in_array($data[0], $SCV2_tables)) {
$tables_found[] = $data[0];
}
}
$ShowTables = TRUE;
} else {
die('Error getting tables: ' . $mysqli->error());
}
} else {
//Create a new database object, using SCV2 connection.
//We will use this object to insert records into SCV2 based on retrieved table and field info.
$SCV2_con = connect_DB();
foreach ($checkbox as $key => $current_table) {
//Check if a table with this name already exists and delete it if it does exist.
$result2 = $SCV2_con->query("SELECT Table_ID FROM `table` WHERE Table_Name = '{$current_table}' AND Project_ID='{$_SESSION['Project_ID']}'");
if ($result2->num_rows > 0) {
$param = $result2->fetch_array();
queryDeleteTable($param, $SCV2_con);
$result2->close();
}
$SCV2_con->query("OPTIMIZE TABLE `table_fields`");
//Get new Table_ID
$Table_ID = get_token();
$SCV2_con->real_query("INSERT INTO `table`(Table_ID, Project_ID, DB_Connection_ID, Table_Name, Remarks)\n VALUES('{$Table_ID}',\n '{$_SESSION['Project_ID']}',\n '{$DB_Connection_ID}',\n '{$current_table}',\n '')");
$add_file = 'add_' . $current_table . '.php';
$edit_file = 'edit_' . $current_table . '.php';
$detail_file = 'detailview_' . $current_table . '.php';
function get_subjects()
{
$con = connect_DB();
$result = mysqli_query($con, "SELECT * FROM `Subjects` ");
$subjects = array();
while ($row = mysqli_fetch_array($result)) {
$subjects[$row["ID"]] = $row["Name"];
}
return $subjects;
close_DB();
}
function multifield_setup($setup_type, $Field_Name, $Field_ID)
{
$setup_content = '';
if ($setup_type == 'Predefined List') {
$mysqli = connect_DB();
$mysqli->real_query("SELECT List_ID FROM table_fields_list WHERE Field_ID='{$Field_ID}'");
if ($result = $mysqli->store_result()) {
if ($result->num_rows > 0) {
$create_query = FALSE;
$data = $result->fetch_assoc();
extract($data);
//Now, get the items from the assigned list, and assign them to a string variable formatted
//as an array definition which will be written to the resulting module.
//Format:
//$arrayItems = array('PerLine' => TRUE,
// 'Items' => array('Yes','No'),
// 'Values'=> array('Yes','No'));
//Note that here, Items and Values arrays will contain pretty much the same thing - this can be modified later
//by the user so that the items displayed will not necessarily be the same as the value used.
//Also, the PerLine index is always TRUE right now since SCV2 doesn't yet allow users to specify it
//during field definitions (not yet supported by database and interface, a minor oversight we can correct
//easily later on.
$options = '';
//variable whose contents will be printed in the generated module (containing the $arrayItems var in the example)
$options_items = '';
//will correspond to the 'Items' index in the options array.
$options_values = '';
//will correspond to the 'Values' index in the options array.
$inner_db_handle = connect_DB();
$inner_db_handle->real_query("SELECT List_Item FROM table_fields_predefined_list_items WHERE List_ID='{$List_ID}'");
if ($inner_result = $inner_db_handle->use_result()) {
while ($data = $inner_result->fetch_assoc()) {
extract($data);
$List_Item = str_replace("'", "\\'", $List_Item);
//single quotes need escaping
$options_items .= "'{$List_Item}',";
$options_values .= "'{$List_Item}',";
}
} else {
die($inner_db_handle->error);
}
$options_items = substr($options_items, 0, strlen($options_items) - 1);
//We just removed the last comma.
$options_values = substr($options_values, 0, strlen($options_values) - 1);
//We just removed the last comma.
//$arrayItems should have a unique name, so prepend the field name to this variable's name.
$arrayItems = $Field_Name . '_array_options';
$options = <<<EOD
\${$arrayItems} = array('items' => array({$options_items}),
'values' => array({$options_values}));
EOD;
$setup_content .= <<<EOD
{$options}
EOD;
}
$result->close();
$mysqli->close();
} else {
die($mysqli_2->error);
}
} elseif ($setup_type == 'SQL Generated') {
$mysqli = connect_DB();
$mysqli->real_query("SELECT b.Field_Name AS `new_field`, a.Display, c.Table_Name \n FROM table_fields_list_source_select a, table_fields b, `table` c \n WHERE a.Field_ID='{$Field_ID}' AND \n a.Select_Field_ID=b.Field_ID AND \n b.Table_ID = c.Table_ID");
if ($result = $mysqli->use_result()) {
$select_fields = array();
$select_tables = array();
$select_display = 'array(';
$select_value = '';
//We'll only accept one value for the select field's value, so we don't need an array ^_^
while ($data = $result->fetch_assoc()) {
extract($data);
if (!in_array($new_field, $select_fields)) {
$select_fields[] = array('Field' => $new_field, 'Table' => $Table_Name);
}
if (!in_array($Table_Name, $select_tables)) {
$select_tables[] = $Table_Name;
}
if ($Display == "Yes") {
$select_display .= "'{$new_field}', ";
} else {
$select_value = $new_field;
}
}
$result->close();
$mysqli->close();
$select_display = substr($select_display, 0, strlen($select_display) - 2);
//remove last comma and space.
$select_display .= ');';
//close the array declaration.
} else {
die($mysqli->error);
}
$mysqli = connect_DB();
$mysqli->real_query("SELECT b.Field_Name, Where_Field_Operand, Where_Field_Value, Where_Field_Connector \n FROM table_fields_list_source_where a, table_fields b \n WHERE a.Field_ID='{$Field_ID}' AND \n \t a.Where_Field_ID=b.Field_ID");
if ($result = $mysqli->store_result()) {
if ($result->num_rows > 0) {
$where_fields = array();
while ($data = $result->fetch_assoc()) {
extract($data);
$where_fields[] = array('Field' => $Field_Name, 'Operand' => $Where_Field_Operand, 'Value' => $Where_Field_Value, 'Connector' => $Where_Field_Connector);
}
$result->close();
$mysqli->close();
} else {
$where_fields = NULL;
}
}
//****Create the query here.*********
//Set the SELECT clause (fields)
$select_query = 'SELECT ';
$num_fields = count($select_fields);
for ($b = 0; $b < $num_fields; $b++) {
$select_query .= $select_fields[$b]['Table'] . '.' . $select_fields[$b]['Field'];
if ($select_fields[$b]['Field'] == $select_value) {
$list_value = 'New_' . $select_fields[$b]['Field'];
$select_query .= ' AS `' . $list_value . '`';
}
$select_query .= ', ';
}
$select_query = substr($select_query, 0, strlen($select_query) - 2);
//removed last space and comma.
//Set the FROM clause (tables)
foreach ($select_tables as $table) {
$select_query .= ' FROM ';
}
$select_query .= "{$table}, ";
$select_query = substr($select_query, 0, strlen($select_query) - 2);
//removed last space and comma.
//Set the WHERE clause (conditions) if applicable.
if (is_null($where_fields)) {
//Nothing, no WHERE clause settings where found.
} else {
$select_query .= ' WHERE ';
foreach ($where_fields as $where) {
if ($where['Connector'] == 'NONE') {
$where['Connector'] = '';
}
$select_query .= $where['Field'] . $where['Operand'] . "'" . $where['Value'] . "' " . $where['Connector'];
}
}
//The names of the variables '$query_var', '$list_value_var', and '$list_items_var' need to be 'specialized' for this field,
//so that the script will work despite having many of this type of control, otherwise, many controls of the same type
//will end up depending on the same variable, which obviously won't work as expected.
$query_var = $Field_Name . '_query';
$list_value_var = $Field_Name . '_list_value';
$list_items_var = $Field_Name . '_list_items';
$setup_content .= <<<EOD
\${$query_var} = "{$select_query}";
\${$list_value_var} = "{$list_value}";
\${$list_items_var} = {$select_display}
EOD;
}
return $setup_content;
}
