Erasmus Exchange in Ionian University 30 april – 3 May 2018

1. Erasmus Exchange in Ionian University 30 april – 3 May 2018

2. Internet TechnologiesMySQL connect to the Apache web server via PHP. Application: Registration on Site • Doru Anastasiu Popescu, associate professor, PhD • Faculty of Sciences, Physical Education and Informatics • Department of Mathematics and Informatics

3. MySQL Connection Using PHP Script • PHP + MySQL Database System • PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform) • PHP 5 and later can work with a MySQL database using: • MySQLi extension (the "i" stands for improved) • PDO (PHP Data Objects) Earlier versions of PHP used the MySQL extension. • PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database. You would require to call the PHP functions in the same way you call any other PHP function. • The PHP functions for use with MySQL have the following general format: mysql_function(value,value,...); The second part of the function name is specific to the function, usually a word that describes what the function does.

4. mysql_connect() PHP provides mysql_connect() function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success or FALSE on failure. Parameter & Description: • server Optional − The host name running the database server. If not specified, then the default value will be localhost • user • Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process. • passwd • Optional − The password of the user accessing the database. If not specified, then the default will be an empty password • new_link • Optional − If a second call is made to mysql_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.

5. mysql_connect() and mysql_close() • client_flags • Optional − A combination of the following constants: • MYSQL_CLIENT_SSL − Use SSL encryption. • MYSQL_CLIENT_COMPRESS − Use compression protocol. • MYSQL_CLIENT_IGNORE_SPACE − Allow space after function names. • MYSQL_CLIENT_INTERACTIVE − Allow interactive timeout seconds of inactivity before closing the connection. • Example • $msql=mysql_connect("localhost","root",""); • You can disconnect from the MySQL database anytime using another PHP function mysql_close(). This function takes a single parameter, which is a connection returned by the mysql_connect() function.

6. mysql_select_db() • The mysql_select_db() function sets the active MySQL database. • This function returns TRUE on success, or FALSE on failure. • Syntax • mysql_select_db(database,connection) • Parameter • database - Required. Specifies the database to select. • connection - Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() is used. Example $baza=mysql_select_db("enrolled");

7. mysql_errno() • The mysql_errno() function returns the error number of the last MySQL operation. • This function returns 0 (zero) if no error has occurred. Syntax mysql_errno(connection) Parameter connection - Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() is used.

8. mysql_error() • The mysql_error() function returns the error description of the last MySQL operation. • This function returns an empty string ("") if no error occurs. • Syntax mysql_error(connection) • Parameter • Connection - Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() is used. Example $result=mysql_query($interrogation); if(!$result) echo mysql_errno().":".mysql_error(); else echo "The table was created!";

9. mysql_query() • The mysql_query() function executes a query on a MySQL database. • This function returns the query handle for SELECT queries, TRUE/FALSE for other queries, or FALSE on failure. • Syntax mysql_query(query,connection) Parameter query - Required. Specifies the SQL query to send. connection - Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() is used. Example $interrogation="create table logindata(name char(30) primary key, password char(10))"; $result=mysql_query($interrogation); if(!$result) echo mysql_errno().":".mysql_error(); else echo "The table was created!";

10. mysql_fetch_array() • The mysql_fetch_array() function returns a row as an associative array and/or a numeric array. • This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows. • Syntax mysql_fetch_array(data,array_type) Parameter data - Required. Specifies which data pointer to use. The data pointer is the result from the mysql_query() function • array_type - Optional. Specifies what kind of array to return. Possible values: • MYSQL_ASSOC - Associative array • MYSQL_NUM - Numeric array • MYSQL_BOTH - Default. Both associative and numeric array

11. mysql_fetch_array() • Note: After the data is retrieved, this function moves to the next row. Each subsequent call to mysql_fetch_array() returns the next row. • Tip: Field names returned by this function are case-sensitive. Example while ($row = mysql_fetch_array($result))     echo $row[0].“ “. $row[1].”<BR/>”;  

12. require_once() • require_once() statement can be used to include a php file in another one, when you may need to include the called file more than once. If it is found that the file has already been included, calling script is going to ignore further inclusions. • If a.php is a php script calling b.php with require_once() statement, and does not find b.php, a.php stops executes causing a fatal error. • Syntax: • require_once('name of the called file with path');

13. Diagram for Application Registration on Site

14. Files of web application btab.php - script for creating the enrolled database and the logindata table from it. The login data table contains the following fields: name and password The btab.php script will be launched in executions once, at the beginning of the application’s usage. main.php –is used for selecting from 2 choices one: the login or registration on the web site. For the registration on the web site the following script is launched in execution: singup.php, and for the login identification.php. singup.php –allows the insertion of the data for registration. These data are took over by the script: enroll.php enroll.php – cheks if the data already exists in the table and if the password is introduced correct. If so the data is introduced in the table. identification.php –takes over the data for the registration. The took over data is checked using the verif.php script. verif.php –is used to check if the took over data are in the table and if so the registration is realised and the access to the secure page is given.

15. 1. Creating the database and the table btab.php <?php $msql=mysql_connect("localhost","root",""); if(!$msql){ echo "No connection to MySQL was made"; exit; } $result=mysql_query("CREATE DATABASE enrolled"); if(!$result){ echo "Failed to create the database!"; exit; } else echo "The database was created! <BR/>";

16. 1. Creating the database and the table continuation $baza=mysql_select_db("enrolled"); $interrogation="create table logindata(name char(30) primary key, password char(10))"; $result=mysql_query($interrogation); if(!$result) echo mysql_errno().":".mysql_error(); else echo "The table was created!"; ?>

17. 2. Start web application main.php <html> <body> <h2> Welcome to the site ...</h2> <a href="http://localhost:8888/identification.php"> Identification</a> <br><br> <a href="http://localhost:8888/singup.php"> Sign up </a> </body> </html>

18. 3. Sending data to the server singup.php <html> <body> <form action="enroll.php" method="post"> Name<br> <input name="name" type="text"><br> Password<br> <input name="password1" type="password"><br> Password again<br> <input name="password2" type="password"><br> <input type="submit", value="Sign up!"> </form> </body> </html>

19. 4. Entering data into table logindata from database enrolled enroll.php <?php $msql=mysql_connect("localhost","root",""); if(!$msql){ echo "No connection to MySQL was made"; exit; } $db=mysql_select_db("enrolled"); if(!$db){ echo mysql_errno().":".mysql_error(); exit; } $name=$_POST['name']; if(strcmp($name,"")==0){ echo "You have not written name"; require_once("singup.php"); exit; }

20. 4. Entering data into table logindata from database enrolled continuation $password1=$_POST['password1']; $password2=$_POST['password2']; if(strcmp($password1,$password2)!=0){ echo "incorrect password"; require_once("singup.php"); exit; } $request="insert into logindata (name,password) values ('$name','$password1')"; $res=mysql_query($request); if(!$res){ echo "Name that exists in the database!"; require_once("singup.php"); }else echo "OK! You have been entered in our database."; ?>

21. 5. Sending data to the server for verification identification.php <html> <body> <form action="verif.php" method="post"> Name<br> <input name="name" type="text"><br> Password<br> <input name="password" type="password"><br> <input type="submit" value="Sign me!"> </form> </body> </html>

22. 6. Verification data verif.php <?php $msql=mysql_connect("localhost","root",""); if(!$msql){ echo "It could not make a connection to MySQL!"; exit; } $bd=mysql_select_db("enrolled"); if(!$bd){ echo mysql_errno().":".mysql_error(); exit; }

23. 6. Verification data continuation $name=$_POST['name']; $password=$_POST['password']; $request="select name from logindata where name='$name' and password='$password'"; $result=mysql_query($request); $row=mysql_fetch_array($result); if($row["name"]!=$name){ echo "Your name and/or password are incorrect"; require_once("identification.php"); }else{ echo "Welcome ".$row["name"]."! <br/>"; require_once("result.php"); } ?>

24. 7. The web page for which login was made result.php <html> <body> <h1> This is the restricted web page! </body> </html>

25. Laboratory task PHP-MySQL • Check the presented application • Develop a web application for presenting the student’s timetable from a faculty. For registration the required data will be: email, name, year of study, specialty. When the registration on site will be realised a web page with the suitable timetable will open with the suitable year of study and specialty. The application will contain a script for creating a table with the information about the timetable.

26. Bibliography PHP-MySQL https://www.w3schools.com/php/ https://www.tutorialspoint.com

27. Thank you! Questions Email: dopopan@yahoo.com WEB SITE: http://www.dopopan.ro