Cours: Conception et programmation de sites web dynamiques CPSWD

1. Cours: Conception et programmation de sites web dynamiquesCPSWD Programmation Web coté Server avec PHP/MySQL 2me partie (English)

2. Content • MySQL server installation • Introduction to web databases • WorkShop: Using PHP with MySQL

3. MySQL server installation

4. Testing whether or not MySQL is working To test whether or not MySQL is working, you can execute the following commands: C:\mysql\bin\mysqlshow C:\mysql\bin\mysqlshow mysql C:\mysql\bin\mysqladmin version status proc

5. Attending the loose ends of the default configuration • Setting your PATH • Deleting the anonymous user • Setting the root password

6. Attending the loose ends of the default configuration • Setting your PATH

7. Attending the loose ends of the default configuration • Setting your PATH Double-clickingPATHwillallowyou to editit.

8. Attending the loose ends of the default configuration • Setting your PATH Add a semicolon to the end of your current path to separate your new entry from the previous one then add c:\mysql\bin.

9. Attending the loose ends of the default configuration • Setting your PATH When you click OK, your addition will be stored in the machine’s registry. Next time you restart the machine, you will be able to type mysql rather than C:\mysql\bin\mysql.

10. Attending the looseends of the default configuration • Deleting the anonymous user c:\mysql\bin\mysql use mysql delete from user where User=''; quit c:\mysql\bin\mysqladmin reload The anonymous user is now gone.

11. Attending the loose ends of the default configuration • Setting the root password Even the superuser account, root, has no password yet.To set this user’s password type these lines: c:\mysql\bin\mysqladmin -u root password your_password

12. Introduction to web databases

13. Using a relational database allows you to quickly and easily answer queries about what are the options of studies at UP, about the students, which options is chosen by most students, etc.. This information can help who visits the web site of the university and improve the site to attract and keep more users

14. Relational database concepts and terminology • Web database design • Web database architecture

15. Relational Database Concepts Tables Relational databases are made up of relations, more commonly called tables. A table is exactly what it sounds like: a table of data.

16. Relational Database Concepts Rows Each row in the table represents a different customer. Because of the tabular format, they all have the same attributes. Rows are also called records or tuples.

17. Relational Database Concepts Keys Weneed to have a way of identifyingeachspecific record. Namesusuallyaren’t a very good way of doingthis - if you have a commonname, you’llprobablyunderstandwhy. The identifyingcolumn in a table iscalled the keyor the primarykey.

18. Relational Database Concepts Relationships Foreign keys represent a relationship between data in two tables. Three basic kinds of relationships exist in a relational database.

19. Relational Database Concepts Schemas The complete set of the table designs for a database is called the database schema.

20. Relational Database Concepts Relationships They are classified according to the number of things on each side of the relationship. Relationships can be either one-to-one, one-to-many, or many-to-many.

21. Relational Database Concepts Relationships A one-to-one relationship means that there is one of each thing in the relationship. In a one-to-many relationship, one row in one table is linked to many rows in another table.

22. Relational Database Concepts Relationships In a many-to-many relationship, many rows in one table are linked to many rows in another table.

23. How to Design Your Web Database • Think About the Real World Objects You Are Modeling. • Avoid Storing Redundant Data. • Use Atomic Column Values. • Choose Sensible Keys. • Think About the Questions You Want to Ask the Database. • Avoid Designs with Many Empty Attributes.

24. Web Database Architecture The client/server relationship between a Web browser and Web server requires communication. The basic Web database architecture consists of the Web browser,Web server, scripting engine, and database server.

25. Using PHP with MySQL

26. Connection to a database <?php require("connections/cn.php")?> <?php $db_host="localhost"; $db_user="doncurt"; $db_password="passwd"; $db_name="test"; ?>

27. Connection to a database $mysql_cn = @mysql_connect($db_host, $db_user, $db_password) or die("La connection n'a pas été établie"); mysql_select_db($db_name);

28. Query a database (Insert) $sql_query = "insert into Tb_UPSt(Id_St,nom,age,carriere) values('NULL','$nom','$age','$carriere')"; mysql_query($sql_query);

29. Query a database (Select) $sql_query = "select * from Tb_UPSt"; $result = mysql_query($sql_query);

30. Displaying data For ($i=0;$i<=mysql_num_rows($result);$i++) { $dis_nom=mysql_result($result,$i, "nom " ); $dis_age=mysql_result($result,$i, "age " ); $dis_carriere=mysql_result($result,$i, "carriere" ); }

31. Conclusion We have seen the installation procedure of MySQL under a windows environment and we covered the most important concepts of web databases. We are ready to use some PHP codes with MySQL to query our databases. This will be done in the Lab following this section.

32. Literature • http://www.commentcamarche.net • PHP and MySQL web development 2nd edition, Luke Melling and Laura Thompson.