<?php
	$servername = "127.0.0.1";
	$username = "root";
	$password = "";
	$dbname = "kontr";

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

	}

	// sql to create table
	$sql = "CREATE TABLE IF NOT EXISTS books (
		isbn INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		title VARCHAR(30),
		published_year date,
		genre VARCHAR(50),
		author VARCHAR(50)
	)";

	if ($conn->query($sql)) {
		//echo "Table books created successfully";
	} else {
		//echo "Error creating table: " . $conn->error;
	}

	if(isset($_GET['add'])){

		$sql = "INSERT INTO books (title, published_year, genre, author)
		VALUES ('".$_GET['title']."', '".$_GET['published_year']."', '".$_GET['genre']."', '".$_GET['author']."')";

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

		$conn->close();
	}

	if(isset($_GET['search'])){

		$sql = "select * from books where genre like '".$_GET['genre']."'";


		if ($conn->query($sql)) {
			$result = $conn->query($sql);

			if ($result->num_rows > 0) {
				while($row = $result->fetch_assoc()) {
					echo "isbn: ".$row['isbn']." author: ".$row['author']."<br>";
				}
			} else {
				echo "0 results";
			}

		} else {
			echo "Error: " . $sql . "<br>" . $conn->error;
		}

		$conn->close();
	}

	if(isset($_GET['change'])){
		$sql = "UPDATE books SET title='".$_GET['title']."' WHERE isbn=".$_GET['isbn'];

		if ($conn->query($sql)) {
			echo "Record updated successfully";
			$sql = "select * from books where isbn=".$_GET['isbn'];


			if ($conn->query($sql)) {
				$result = $conn->query($sql);

				if ($result->num_rows > 0) {
					while($row = $result->fetch_assoc()) {
						echo "isbn: ".$row['isbn']." Title: ".$row['title']."<br>";
					}
				} else {
					echo "0 results";
				}

			} else {
				echo "Error: " . $sql . "<br>" . $conn->error;
			}
		} else {
			echo "Error updating record: " . $conn->error;
		}
	}

	if(isset($_GET['sort'])){
		$sql = "select * from books order by author ascxx";


		if ($conn->query($sql)) {
			$result = $conn->query($sql);

			if ($result->num_rows > 0) {
				while($row = $result->fetch_assoc()) {
					echo "isbn: ".$row['isbn']." author: ".$row['author']."<br>";
				}
			} else {
				echo "0 results";
			}

		} else {
			echo "Error: " . $sql . "<br>" . $conn->error;
		}

		$conn->close();
	}



?>
<h2>Add book</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<label for="t" >Title</label>
	<input id="t" type="text" name="title"></input>
	<label for="p" >Pulished Year</label>
	<input id="p" type="date" name="published_year"></input>
	<label for="g" >Genre</label>
	<input id="g" type="text" name="genre"></input>
	<label for="a" >Author</label>
	<input id="a" type="text" name="author"></input>
	<button type="submit" name="add">add</button>
</form>
<h2>Get book by Genre</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<label for="gs">Genre</label>
	<input id="gs" type="text" name="genre"></input>
	<button type="submit" name="search">search</button>
</form>
<h2>Change book Title</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<label for="i" >ISBN</label>
	<input id="i" type="number" name="isbn"></input>
	<label for="ti" >Title</label>
	<input id="ti" type="text" name="title"></input>
	<button type="submit" name="change">change</button>
</form>
<h2>Sort book by Author</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<button type="submit" name="sort">sort</button>
</form>