<?php
// error_reporting(E_ALL ^ E_WARNING);
include 'settings.php';
/*
$usr = "";
$pass = "";
$dbName = ""; //set as unique dbName
$ip = "localhost";
$connection = null;
$debugMode = TRUE; // TRUE: printWrongQueries; FALSE: doNotPrintThem
$wrongQuery = "";
$isConnected = FALSE;
//$port = "3306";
FUNCTION USAGE EXPLANATION:
=================
0 parameter functions:
connectTo(): starts MySQL connection with config data
closeConnection(): closes the connection
printWrongQuery(): if $debugMode is TRUE, prints last wrongQuery SQL code
init(): creates database, tables, views and inserts constant data
=================
1 parameter functions:
executeQuery(queryCode): returns query result or FALSE if failure
hasRows(queryResult): returns TRUE if result has rows, or FALSE if it hasn't
doesDbExist(db): returns TRUE if there is such database, or FALSE if isn't
doesTableExist(table): returns TRUE if there is such table in current DB, or FALSE if
isn't
createDb(db): creates a db if it is possible
changeTableIntoStringWithCommas(array): creates string with array elements separated by commas
changeTableIntoStringWithCommasAndQuotes(array): creates string with array elements in quotes separated
by commas
changeTableIntoStringWithAnd(array): creates string with array elements separated by ' AND ' phrase
useDb(db): switches active database to db
=================
multi-parameter functions:
createTable(name, col1 properties [, col2 properties, [, ...]]):
creates table with selected name and columns properties list
createOrReplaceView(table, col1Name [, col2Name [, ...]]):
creates or replaces a view on selected table name
insertRecordIntoTableIfNotExist(table, val1 [, val2 [, ...]]):
inserts a new record into table, if there is no such record
*/
function connectTo()
{
global $usr;
global $pass;
global $dbName;
global $ip;
global $connection;
global $isConnected;
if ($isConnected == TRUE)
return TRUE;
$connection = new mysqli($ip, $usr, $pass, ""); // empty dbName
if (mysqli_connect_errno())
exit("Błąd połączenia z bazą:<br>Kod błędu: " . mysqli_connect_errno() . json_encode($connection) ."<br>");
$isConnected = TRUE;
$connection->query("SET NAMES 'utf8'");
$connection->query("SET NAMES `utf8` COLLATE `utf8_polish_ci`");
return TRUE;
}
function closeConnection()
{
global $isConnected;
global $connection;
if ($isConnected == TRUE)
if ($connection->close() == FALSE)
{
$isConnected = FALSE;
exit("Błąd rozłączenia<br>");
}
}
function executeQuery($query)
{
connectTo();
global $debugMode;
global $wrongQuery;
global $connection;
global $dbName;
useDb($dbName);
$result = $connection->query($query);
if ($result == FALSE && $debugMode == TRUE)
$wrongQuery = "Błąd w zapytaniu: " . $query . "<br>";
return $result;
}
function executeMultiQuery($multiQuery)
{
connectTo();
global $debugMode;
global $wrongQuery;
global $connection;
global $dbName;
useDb($dbName);
$result = $connection->multi_query($multiQuery);
if ($result == FALSE && $debugMode == TRUE)
$wrongQuery = "Błąd w zapytaniu: " . $multiQuery . "<br>";
return $result;
}
function useDb($db)
{
$result = executeQuery("USE " . $db . ";");
if($result == FALSE)
{
printWrongQuery();
exit("Błąd w funckji useDb()<br>");
}
//$result->close();
}
function printWrongQuery()
{
global $debugMode, $wrongQuery;
if ($debugMode == TRUE)
echo $wrongQuery;
}
function hasRows($table)
{
connectTo();
if ($table->num_rows > 0)
return TRUE;
return FALSE;
}
function doesDbExist($db)
{
$result = executeQuery("SHOW DATABASES LIKE '" . $db . "';");
if ($result == FALSE)
{
// $result->close();
return FALSE;
}
else if (hasRows($result))
{
// $result->close();
return TRUE;
}
else
{
// $result->close();
return FALSE;
}
}
function doesTableExist($table)
{
$result = executeQuery("SHOW TABLES LIKE '" . $table . "';");
if ($result == FALSE)
{
// $result->close();
return FALSE;
}
else if (hasRows($result))
{
// $result->close();
return TRUE;
}
else
{
// $result->close();
return FALSE;
}
}
function createDb($db)
{
if (doesDbExist($db) == FALSE)
{
$result = executeQuery("CREATE DATABASE " . $db . ";");
if ($result == FALSE)
{
printWrongQuery();
exit("Wystąpił błąd podczas tworzenia nowej bazy<br>");
}
// $result->close();
}
}
function changeTableIntoStringWithCommas($table)
{
if (count($table) == 0)
return "";
$result = "";
foreach ($table as $val)
$result .= $val . ", ";
$result = substr($result, 0, strlen($result) - 2);
return $result;
}
function changeTableIntoStringWithCommasAndQuotes($table)
{
if (count($table) == 0)
return "";
$result = "";
foreach ($table as $val)
$result .= "'" . $val . "', ";
$result = substr($result, 0, strlen($result) - 2);
return $result;
}
function changeTableIntoStringWithAnd($table)
{
if (count($table) == 0)
return "";
$result = "";
foreach ($table as $val)
$result .= $val . " AND ";
$result = substr($result, 0, strlen($result) - 5);
return $result;
}
function createTable() // various number of parameters:
// tableName, field1, field2, field3, ...
{
if (func_num_args() < 2)
exit("Błędna liczba parametrów funkcji createTable()<br>");
$str = changeTableIntoStringWithCommas(func_get_args());
$firstComma = strpos($str, ",");
$tableName = substr($str, 0, $firstComma);
$rest = substr($str, $firstComma + 1);
if (doesTableExist($tableName) == FALSE)
{
$result = executeQuery("CREATE TABLE " . $tableName . " (" . $rest . ");");
if ($result == FALSE)
{
printWrongQuery();
exit("Błąd tworzenia tabeli<br>");
}
$result->close();
}
}
/*
function doesRecordExist()
{
if (func_num_args() < 2)
exit("Błędna liczba parametrów funkcji doesRecordExist()<br>");
$str = changeTableIntoStringWithAnd(func_get_args());
$firstAnd = strpos($str, " AND ");
$tableName = substr($str, 0, $firstAnd);
$rest = substr($str, $firstAnd + 5);
$result = executeQuery("SELECT EXISTS(SELECT * FROM " . $tableName . " WHERE " . $rest . ");");
if ($result == FALSE)
return FALSE;
else if (hasRows($result))
return TRUE;
else
return FALSE;
}
*/
function insertRecordIntoTableIfNotExist()
{
if (func_num_args() < 2)
exit("Błędna liczba parametrów funkcji insertRecordIntoTableIfNotExist()<br>");
$str = changeTableIntoStringWithCommasAndQuotes(func_get_args());
$firstComma = strpos($str, ",");
$tableName = substr($str, 0, $firstComma);
$tableName = substr($tableName, 1, strlen($tableName) - 2);
$rest = substr($str, $firstComma + 1);
$result = executeQuery("SELECT " . $rest . " MINUS SELECT * FROM " . $tableName . "_v;");
if ($result != FALSE)
if (hasRows($result))
{
// $result->close();
return;
}
$result = executeQuery("INSERT INTO " . $tableName . " VALUES(NULL, " . $rest . ");");
if ($result == FALSE)
{
printWrongQuery();
exit("Błąd podczas dodawania nowego rekordu w funkcji insertRecordIntoTableIfNotExist()<br>");
}
// $result->close();
}
function createOrReplaceView()
{
if (func_num_args() < 3)
exit("Błędna liczba parametrów funkcji createOrReplaceView()<br>");
$str = changeTableIntoStringWithCommas(func_get_args());
$firstComma = strpos($str, ",");
$tableName = substr($str, 0, $firstComma);
$rest = substr($str, $firstComma + 1);
$result = executeQuery("CREATE OR REPLACE VIEW " . $tableName . "_v " . " AS SELECT " . $rest . " FROM " . $tableName . ";");
if ($result == FALSE)
{
printWrongQuery();
exit("Błąd przy tworzeniu widoku w funkcji createOrReplaceView()<br>");
}
// $result->close();
}
function printHeaderRowOfTable($table)
{
echo "<tr>";
for ($i = 1; $i < count($table); $i++)
echo "<td>" . $table[$i] . "</td>";
echo "</tr>";
}
function printTable()
{
global $connection;
if (func_num_args() < 2)
exit("Błędna liczba parametrów funkcji printTable()<br>");
$result = func_get_arg(0);
if ($result != FALSE)
{
if ($connection->field_count + 1 != func_num_args())
exit("Błędna liczba parametrów funkcji printTable()<br>");
echo "<table>";
printHeaderRowOfTable(func_get_args());
while ($row = $result->fetch_row())
{
echo "<tr>";
foreach ($row as $field)
echo "<td>" . $field . "</td>";
echo "</tr>";
}
echo "</table>";
// $result->close();
}
else
exit("Wystąpił błąd w funkcji printTable() - nieprawidłowe zapytanie<br>");
}
function init()
{
global $dbName;
echo " 1 ";
createDb($dbName);
echo " 2 ";
useDb($dbName);
echo " 3 ";
createTable("movies", // table name changed from 'movieTitles' to 'movies'
"id INT(6) AUTO_INCREMENT PRIMARY KEY NOT NULL",
"title VARCHAR(255) NOT NULL",
"producer int NOT NULL",
"director int NOT NULL",
"country int NOT NULL",
"dateOfProd smallint NOT NULL",
"duration int NOT NULL",
"TV tinyint NOT NULL",
"cinema tinyint NOT NULL",
"DVD tinyint NOT NULL",
"VOD tinyint NOT NULL",
"otherThanCinema tinyint NOT NULL",
"contractDate DATE NOT NULL",
"company int NOT NULL",
"UNIQUE (title)");
echo " 3 ";
createTable("producers",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"name VARCHAR(255) NOT NULL");
createTable("directors",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"nameAndSurname VARCHAR(255) NOT NULL");
createTable("countries",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"country VARCHAR(255) NOT NULL",
"taxAtSource TINYINT NOT NULL");
createTable("companies",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"name VARCHAR(255) NOT NULL");
createTable("cinemas",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"name VARCHAR(255) NOT NULL",
"city int NOT NULL");
createTable("cities",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"name VARCHAR(255) NOT NULL");
createTable("shows",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"dateFrom DATE NOT NULL",
"dateTo DATE NOT NULL",
"cinema int NOT NULL",
"title int NOT NULL",
"setId int NOT NULL",
"comments VARCHAR(255) NOT NULL",
"invoice int NOT NULL");
createTable("sets",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"name VARCHAR(255) NOT NULL",
"amount decimal(9, 2) NOT NULL");
createTable("invoices",
"id INT AUTO_INCREMENT PRIMARY KEY NOT NULL",
"number VARCHAR(255) NOT NULL",
"company int NOT NULL",
"amount decimal(9, 2) NOT NULL");
$countries = array(
array("Albania", "5"),
array("Algieria", "10"),
array("Arabia Saudyjska", "10"),
array("Armenia", "10"),
array("Australia", "10"),
array("Austria", "5"),
array("Azerbejdżan", "10"),
array("Bangladesz", "10"),
array("Belgia", "5"),
array("Białoruś", "0"),
array("Bośnia i Hercegowina", "20"),
array("Bułgaria", "5"),
array("Chile", "15"),
array("Chiny", "10"),
array("Chorwacja", "10"),
array("Cypr", "5"),
array("Czarnogóra", "10"),
array("Czeska Republika", "10"),
array("Dania", "5"),
array("Egipt", "12"),
array("Estonia", "10"),
array("Etiopia", "10"),
array("Filipiny", "15"),
array("Finlandia", "5"),
array("Francja", "0"),
array("Grecja", "10"),
array("Gruzja", "8"),
array("Guernsey", "20"),
array("Hiszpania", "10"),
array("Holandia", "5"),
array("Indie", "15"),
array("Indonezja", "10"),
array("Iran", "10"),
array("Irlandia", "10"),
array("Islandia", "10"),
array("Izrael", "10"),
array("Japonia", "0"),
array("Jersey", "20"),
array("Jordania", "10"),
array("Kanada", "5"),
array("Katar", "5"),
array("Kazachstan", "10"),
array("Kirgistan", "10"),
array("Korea Płd.", "5"),
array("Kuwejt", "15"),
array("Liban", "5"),
array("Litwa", "10"),
array("Luksemburg", "5"),
array("Łotwa", "10"),
array("Macedonia", "10"),
array("Malezja", "8"),
array("Malta", "5"),
array("Maroko", "10"),
array("Meksyk", "10"),
array("Mołdawia", "10"),
array("Mongolia", "5"),
array("Niemcy", "5"),
array("Nigeria", "10"),
array("Norwegia", "5"),
array("Nowa Zelandia", "10"),
array("Pakistan", "20"),
array("Polska", "0"),
array("Portugalia", "10"),
array("Republika Płd. Afryki", "10"),
array("Rosja", "10"),
array("Rumunia", "10"),
array("Serbia", "10"),
array("Singapur", "5"),
array("Słowacka Rep.", "5"),
array("Słowenia", "10"),
array("SriLanka", "10"),
array("Stany Zjednoczone", "5"),
array("Syria", "18"),
array("Szwajcaria", "0"),
array("Szwecja", "5"),
array("Tadżykistan", "10"),
array("Tajlandia", "15"),
array("Tunezja", "12"),
array("Turcja", "10"),
array("Ukraina", "10"),
array("Urugwaj", "15"),
array("Uzbekistan", "10"),
array("Węgry", "10"),
array("Wielka Brytania", "5"),
array("Wietnam", "15"),
array("Włochy", "10"),
array("Wyspa Man", "20"),
array("Zambia", "10"),
array("Zjedn. Emiraty Arabskie", "5"),
array("Zimbabwe", "10"),
array("inne", "20"));
foreach ($countries as $val)
insertRecordIntoTableIfNotExist("countries", $val[0], $val[1]);
$files = scandir("./queries");
foreach ($files as $fileName)
if ($fileName != "." && $fileName != "..")
executeMultiQuery(file_get_contents("./queries/" . $fileName));
}
?>
Wywołuję z zewnątrz funkcję init()
. Wywala się po echo " 1 ";
Wywołuję stąd:
<?php
function createUserAndDb($connection)
{
$result = $connection->query("CREATE USER '" . $_REQUEST["usr"] . "'@'" . $_REQUEST["addr"] . "';");
if ($result != FALSE)
{
echo "dodano użytkownika<br>";
//$result->close();
$result = $connection->query("CREATE DATABASE " . $_REQUEST["db"] . " DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
if ($result != FALSE)
{
echo "stworzono bazę<br>";
//$result->close();
$result = $connection->query("GRANT ALL PRIVILEGES ON " . $_REQUEST["db"] . ".* To '" . $_REQUEST["usr"] . "'@'" . $_REQUEST["addr"] . "' IDENTIFIED BY '" . $_REQUEST["pass"] ."'");
if ($result != FALSE)
{
echo "nadano uprawnienia<br>";
return TRUE;
}
}
}
return FALSE;
}
$connection2 = new mysqli($_REQUEST["addr"], $_REQUEST["usrRoot"], $_REQUEST["passRoot"], "");
$newSettingsFileString = sprintf(file_get_contents("settings_install.php"),
$_REQUEST["usr"], $_REQUEST["pass"], $_REQUEST["db"], $_REQUEST["addr"]);
if (createUserAndDb($connection2) == FALSE)
echo "Błąd podczas dodawania użytkownika lub bazy";
else
{
file_put_contents("settings.php", $newSettingsFileString);
include "akomodacja.php";
$connection2->close();
init();
echo "OK1234";
}
?>