<?php
//set up login constants
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_USER', 'YourUserName');
DEFINE ('DB_PASS', 'YourPassword');
DEFINE ('DB_NAME', 'CRI');
// Set up variable $dbcon for db-connection
$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// set chaset to utf8 for data transfer
mysqli_set_charset($dbcon,"utf8");
//#1 SQL
// DROP table export
$query = "DROP TABLE IF EXISTS export;";
$res_drop = mysqli_query($dbcon, $query);
//#2 SQL
// CREATE table export
if($res_drop){
$query = "CREATE TABLE export LIKE whole;";
$res_create = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#1";
exit;
}
//#3 SQL
// INSERT into export from whole
if($res_create){
$query = "INSERT INTO export SELECT * FROM whole;";
$res_export = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#2";
exit;
}
//#4 SQL
// SET random NULL
if($res_export){
$query = "UPDATE export SET random = NULL;";
$res_rand_null = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#3";
exit;
}
//#5 SQL
// SET random RAND() WHERE mobile = 1
if($res_rand_null){
$query = "UPDATE export SET random = RAND() WHERE mobile=1;";
$res_rand = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#4";
exit;
}
//#6 SQL
// REPLACE 'Aubrey' where fictional = 1 [Part 1]
if($res_rand){
$query = "UPDATE names SET random = RAND();";
$res_replace_1 = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#5";
exit;
}
//#7 SQL
// REPLACE 'Aubrey' where fictional = 1 [Part 2]
if($res_replace_1){
$query = "SET @randomName=(SELECT name FROM names WHERE random =(SELECT MIN(random)FROM names));";
$res_replace_2 = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#6";
exit;
}
//#8 SQL
// REPLACE 'Aubrey' where fictional = 1 [Part 3]
if($res_replace_2){
$query = "UPDATE export SET fragment = REPLACE(fragment, 'Aubrey', @randomName)WHERE fragment LIKE '%Aubrey%' AND fictional = 1;";
$res_replace_3 = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#7";
exit;
}
//#9 SQL
// SELECT statement: read into $res_select
if($res_replace_3){
$query = "SELECT * FROM export where pos >6 ORDER BY unit, random, pos;";
$res_select = mysqli_query($dbcon, $query);
}
else {
echo "Error at section: <br /> //#8";
exit;
}
//#10 PHP
// get data out of SELECT statement
$a_pos = 0;//set array-position-counter
if($res_select){
//while-loop: keep outputing to $load
while($load = mysqli_fetch_array($res_select)){
//split up data into single results (=one row of DB)
$res_id = $load ['id'];
$res_pos = $load ['pos'];
$res_fragment = $load ['fragment'];
$res_topic = $load ['topic'];
$res_subtopic = $load ['subtopic'];
$res_mobile = $load ['mobile'];
$res_heading = $load ['heading'];
$res_insertion = $load ['insertion'];
$res_unit = $load ['unit'];
$res_opp = $load ['opposition'];
$res_ori = $load ['orientation'];
$res_chapter = $load ['chapter'];
$res_lang = $load ['language'];
$res_fictional = $load ['fictional'];
$res_title = $load ['title'];
$res_intro = $load ['intro'];
$res_high = $load ['highlight'];
$res_random = $load ['random'];
//feed arrays: one array per column in DB
//–> data into specific positions in arrays
$array_id[$a_pos] = $res_id;
$array_pos[$a_pos] = $res_pos;
$array_fragment[$a_pos] = $res_fragment;
$array_topic[$a_pos] = $res_topic;
$array_subtopic[$a_pos] = $res_subtopic;
$array_mobile[$a_pos] = $res_mobile;
$array_heading[$a_pos] = $res_heading;
$array_insertion[$a_pos] = $res_insertion;
$array_unit[$a_pos] = $res_unit;
$array_opp[$a_pos] = $res_opp;
$array_ori[$a_pos] = $res_ori;
$array_chapter[$a_pos] = $res_chapter;
$array_lang[$a_pos] = $res_lang;
$array_fictional[$a_pos] = $res_fictional;
$array_title[$a_pos] = $res_title;
$array_intro[$a_pos] = $res_intro;
$array_high[$a_pos] = $res_high;
$array_random[$a_pos] = $res_random;
//set text-type in array_type
if($res_title == 'h1')
{$array_type[$a_pos] = 'book_title';}
elseif($res_title == 'h2')
{$array_type[$a_pos] = 'chapter_title';}
elseif($res_title == 'h3')
{$array_type[$a_pos] = 'small_title';}
elseif($res_title == 'h4')
{$array_type[$a_pos] = 'preface';}
elseif($res_intro == 1)
{$array_type[$a_pos] = 'intro';}
elseif($res_insertion == 0 && $res_mobile == 1 && $res_opp == 0 && $res_high == 0)
{$array_type[$a_pos] = 'running_n';}
elseif($res_insertion == 0 && $res_mobile == 1 && $res_opp == 0 && $res_high == 1 && $res_random <=0.4)
{$array_type[$a_pos] = 'running_h';}
elseif($res_insertion == 0 && $res_mobile == 1 && $res_opp == 0 && $res_high == 1 && $res_random >0.4)
{$array_type[$a_pos] = 'running_n';}
elseif($res_heading == 1)
{$array_type[$a_pos] = 'heading';}
elseif($res_insertion == 1)
{$array_type[$a_pos] = 'insertion';}//separate insertion by '_'
elseif(($res_opp == 1 && $res_chapter == 'GEFÜGE') || ($res_opp == 1 && $res_chapter == 'VERORTUNG')) {$array_type[$a_pos] = 'opposition';}
elseif($res_opp == 1 && $res_chapter == 'HIER' && $res_ori == 0)
{$array_type[$a_pos] = 'opposition';}
elseif($res_opp == 1 && $res_chapter == 'HIER' && $res_ori == 1)
{$array_type[$a_pos] = 'hier_op_1';}
elseif($res_opp == 1 && $res_chapter == 'HIER' && $res_ori == 2)
{$array_type[$a_pos] = 'hier_op_2';}
elseif($res_opp == 1 && $res_chapter == 'HIER' && $res_ori == 3)
{$array_type[$a_pos] = 'hier_op_3';}
elseif($res_opp == 1 && $res_chapter == 'LOVE' && $res_ori == '1')
{$array_type[$a_pos] = 'love_1';}
elseif($res_opp == 1 && $res_chapter == 'LOVE' && $res_ori == '2')
{$array_type[$a_pos] = 'love_2';}
elseif($res_opp == 1 && $res_chapter == 'LOVE' && $res_ori == '3')
{$array_type[$a_pos] = 'love_3';}
elseif($res_opp == 1 && $res_chapter == 'ICH;ICH' && $res_ori == '1')
{$array_type[$a_pos] = 'ich_1';}
elseif($res_opp == 1 && $res_chapter == 'ICH;ICH' && $res_ori == '2')
{$array_type[$a_pos] = 'ich_2';}
elseif($res_opp == 1 && $res_chapter == 'ICH;ICH' && $res_ori == '3')
{$array_type[$a_pos] = 'ich_3';}
elseif($res_chapter == 'ANMERKUNGEN'){$array_type[$a_pos] = 'notes';}
//increase array-position-counter
$a_pos ++;
}
}
//close database connection
mysqli_close($dbcon);
?>