<?php
echo "FOOD";

$servername = "mysql";
$username = "root";
$password = "123";
$dbname = "foodmanager";

// checking connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// sql query with CREATE DATABASE
$sql = "CREATE DATABASE IF NOT EXISTS " . $dbname . " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

// Performs the $sql query on the server to create the database
if ($conn->query($sql) === TRUE) {
  echo 'Database ' . $dbname . ' successfully created' . "<br>";
}
else {
 echo ' Error: '. $conn->error;
}

//$conn->close();

/*echo "<pre>";
echo "DB:";
print_r ($conn);
echo "</pre>";*/

$sql = "SHOW DATABASES";
$conn = new mysqli($servername, $username, $password, $dbname) or die ('Error connecting to mysql: ' . mysqli_error($mysqli).'\r\n');
$result = $conn->query($sql);
if ($result === false) {
    throw new Exception("Could not execute query: " . $conn->error);
}

$db_names = array();
while($row = $result->fetch_array(MYSQLI_NUM)) { // for each row of the resultset
    $db_names[] = $row[0]; // Add db name to $db_names array
}
echo "<pre>";
echo "Database names: " . PHP_EOL . print_r($db_names, TRUE); // display array
echo "</pre>";

// sql code to create table
$sql = "CREATE TABLE IF NOT EXISTS employees(
        userId INT(4)  PRIMARY KEY,
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL
        )";

//$conn = new mysqli($servername, $username, $password, $dbname) or die ('Error connecting to mysql: ' . mysqli_error($conn).'\r\n');
$result = $conn->query($sql);
if ($conn->query($sql) === TRUE) {
    echo "Table employees created successfully <br>";
} else {
    echo "Error creating table: " . $conn->error . "<br>";
}

$sql = "INSERT INTO employees (firstname, lastname)
VALUES ('Анатолий', 'Варламов')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully <br>";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error . "<br>";
}

$sql = "CREATE TABLE IF NOT EXISTS menu(
    dishId INT(10) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    price INT(30) NOT NULL,
    date VARCHAR(30) NOT NULL
    )";

$result = $conn->query($sql);
if ($conn->query($sql) === TRUE) {
echo "Table menu created successfully". "<br>";
} else {
echo " Error creating table: " . $conn->error . "<br>";
}

$sql = "INSERT INTO menu (name, price, date)
VALUES ('chicken', '100', curdate())";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully" . "<br>";
} else {
    echo " Error: " . $sql . "<br>" . $conn->error . "<br>";
}

$sql ="SELECT * FROM menu WHERE name = 'chicken'" ;
$result=$conn->query($sql);
//$result->fetch();

echo "<pre>";
print_r ($result);
echo "</pre>";

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["dishId"] . "<br>" . " Name: " . $row["name"] ."<br>" . " Price: " . $row["price"] . "<br>" . " Date: " . $row["date"] . "<br>";
    }
}


$sql = 'DROP TABLES employees, menu, orders';
$result = $conn->query($sql);