XAMPP Installation

1. XAMPP Installation SWE381: Web Application Development

2. XAMPP • Simple, lightweight Apache distribution that makes it extremely easy for developers to create a local web server for testing and deployment purposes. • Allows you to visualize and manage your databases from your browser using the phpMyAdmin application

3. XAMPP installation • URL: https://www.apachefriends.org/index.html

4. XAMPP installation

5. XAMPP installation

6. XAMPP installation

7. XAMPP installation

8. XAMPP installation

9. XAMPP installation

10. XAMPP installation

11. XAMPP Control Panel

12. XAMPP Control Panel

13. Accessing phpMyAdmin • In your browser: • http://localhost/phpmyadmin/ • phpMyAdmin is a database • management tool for MySQL • compatible databases. 

14. Database manipulation in phpMyAdmin • Using phpMyAdmin you can: • Create databases • Create tables • Set relations between tables • Access and manipulate data

15. Example 1 • Database Name: Bookstore • Book (BookID, title, author, language, edition, price, publisherID) • Publisher (ID, Name, address )

16. Create a database

17. Create a table

18. Book table

19. Publisher table

20. Setting up relations

21. SELECT

22. INSERT

23. INSERT

24. DELETE

25. DELETE

26. UPDATE

27. UPDATE

28. Add a user to access the DB

30. Exercise 2 • Database Name: WebProject • Teacher (ID, name, password) • Course (ID, Name, teacher, semester )

31. Create a database

32. Create a table CREATE TABLE `webProject`.`teacher` ( `id` INT NOT NULL , `name` VARCHAR(200) NOT NULL , `password` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB; CREATE TABLE `webProject`.`course` ( `id` INT NOT NULL , `name` VARCHAR(200) NOT NULL , `teacherID` INT NOT NULL , `semester` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`), INDEX (`teacher`)) ENGINE = InnoDB;

33. Setting up relation ALTER TABLE `course` ADD FOREIGN KEY (`teacher`) REFERENCES `teacher`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

34. Add a user to access the DB CREATE USER 'mywebProj'@'localhost' IDENTIFIED WITH mysql_native_password;GRANT USAGE ON *.* TO 'mywebProj'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;SET PASSWORD FOR 'mywebProj'@'localhost' = '***';GRANT ALL PRIVILEGES ON `webProject`.* TO 'mywebProj'@'localhost';

35. Insert INSERT INTO `teacher` (`id`, `name`, `password`) VALUES ('1', 'sadeem', '11111'), ('2', 'noura', ‘2222') INSERT INTO `course` (`id`, `name`, `teacherID`, `semester`) VALUES ('1', 'swe381', '2', 'fall2016'), ('2', 'swe381', '2', 'spring2017'), ('3', 'swe481', '1', 'fall2016'), ('4', 'swe481', '1', 'spring2017')

36. Select What does this select statement do? SELECT course.name FROM course INNER JOIN teacher ON teacher.id=course.teacherID and teacher.name='sadeem'

37. Delete What does this delete statement do? Delete from course where name='swe381' and semester='fall2016'

38. Drop • Dropping table course Drop table course;

39. Evaluation • 1. create tables of below database with the relation • Database Name: Restaurants • Restaurant (ID, name, license , owner) • Branch (ID, name, location, manager, phoneNo, restaurantID) • 2. insert values • 3. select all branches of “pizza hut” • 4. delete “domino’s” restaurant