1 of 43

UNIT-5: �Interacting with MySQL using PHP�

2 of 43

PHP introduction

MySQL Versus MySQLi Functions
Connecting to MySQL with PHP
Working with MySQL Data
Creating an Online Address Book

3 of 43

MySQL

4 of 43

MySQL

MySQL is a RDBMS
MySQL is suitable for both small and large applications
MySQL supports standard SQL – Easy to use
MySQL compiles on a number of platforms [Operating Systems]
MySQL is free to download and use
MySQL is secure [ passwords ]
MySQL is fast and reliable

5 of 43

MySQL Versus MySQLi Functions�

MySQLi is also known as the improved version of MySQL
The ‘i’ in MySQLi stands for Improved.
MySQL extension is deprecated and will not be available in future PHP versions. It is recommended to use the MySQLi extension

6 of 43

MySQL Versus MySQLi Functions�

MySQLi	MySQL
MySQLi extension added in PHP 5.5	MySQL extension added in PHP version 2.0
The MySQLi supports prepared statements.	The MYSQL does not support prepared statements.
MySQLi supports transactions through API.	Transactions are handled by SQL queries only.
MySQLi provides both object-oriented and procedural interfaces.	MySQL provides procedural interface.
MySQLi extension is with enhanced security	MySQL extension lags in security

7 of 43

MySQL Functions�

mysql_connect($host_name,$user_name,$password);

mysql_select_db($dbname);

mysql_query("SQL statement",$con);

Example:

$con = mysql_connect("host", "username", "password");

mysql_select_db("database_name", $con);

8 of 43

MySQLi – connection�

<?php�$servername = "localhost";�$username = “gdctvr";�$password = “abc123";�$dbname = "myDB";�// Create connection�$conn = new mysqli($servername, $username, $password, $dbname);�// Check connection�if ($conn->connect_error) {� die("Connection failed: " . $conn->connect_error);�}�$sql = "INSERT INTO students VALUES (‘Ravi', ‘K', ‘ravi@gmail.com')";�if ($conn->query($sql) === TRUE) {� echo "New record created successfully";�} else {� echo "Error: " . $conn->error;�}�$conn->close();�?>

9 of 43

MySQLi Functions – Select Query�

$sql = "SELECT firstname, lastname FROM students";

�$result = $conn->query($sql);��if ($result->num_rows > 0) {� // output data of each row� while($row = $result->fetch_assoc()) {� echo $row["firstname"]. $row["lastname"];� }

10 of 43

Create a table for an Online Address Book

address_book

fname	lname	streetname	city	state	pincode	phone	emailid	notes

11 of 43

12 of 43

13 of 43

14 of 43

15 of 43

16 of 43

17 of 43

18 of 43

19 of 43

20 of 43

21 of 43

22 of 43

23 of 43

24 of 43

25 of 43

26 of 43

27 of 43

28 of 43

Create a form for adding a record in the address book

29 of 43

30 of 43

31 of 43

Insert a new record –Form

<h1 >Add an Entry in to the Address Book</h1>

<P><strong>First Name:</strong><br><input type="text" name="f_name" size=25 maxlength=25>

<P><strong>Street Name:</strong><br> <input type="text" name="s_name" size=25 maxlength=25>

<P><strong>State:</strong><br> <input type="text" name="state" size=25 maxlength=25>

<P><strong>Pincode:</strong><br>

<P><strong>Phone (Mobile/Land):</strong><br>

<P><strong>Notes:</strong><br> <textarea name="note" cols=35 rows=5 wrap=virtual></textarea>

</form>

32 of 43

33 of 43

Validate the form-data before adding a record

if (empty ($_POST["f_name"])|| empty ($_POST["l_name"])|| empty ($_POST["s_name"])||empty ($_POST["city"])||empty ($_POST["state"])||empty ($_POST["pin"])||empty ($_POST["phone"])||empty ($_POST["mail"])||empty ($_POST["note"]))

{

echo '<script>alert("Error! You did not fill all the fields")</script>’;

}

else

{

$fname = $_POST["f_name"]; $lname = $_POST["l_name"]; $sname = $_POST["s_name"]; $city = $_POST["city"]; $state = $_POST["state"]; $pin = $_POST["pin"]; $phone = $_POST["phone"]; $mail = $_POST["mail"]; $note = $_POST["note"];

}

34 of 43

35 of 43

36 of 43

Fetch the values from the form

if (empty ($_POST["f_name"])|| empty ($_POST["l_name"])|| empty ($_POST["s_name"])||empty ($_POST["city"])||empty ($_POST["state"])||empty ($_POST["pin"])||empty ($_POST["phone"])||empty ($_POST["mail"])||empty ($_POST["note"]))

{

echo '<script>alert("Error! You did not fill all the fields")</script>’;

}

else

{

$fname = $_POST["f_name"]; $lname = $_POST["l_name"]; $sname = $_POST["s_name"]; $city = $_POST["city"]; $state = $_POST["state"]; $pin = $_POST["pin"]; $phone = $_POST["phone"]; $mail = $_POST["mail"]; $note = $_POST["note"];

}

37 of 43

Get the values from the HTML form

else

{

$fname = $_POST["f_name"];

$lname = $_POST["l_name"];

$sname = $_POST["s_name"];

$city = $_POST["city"];

$state = $_POST["state"];

$pin = $_POST["pin"];

$phone = $_POST["phone"];

$mail = $_POST["mail"];

$note = $_POST["note"];

}

<h1 >Add an Entry in to the Address Book</h1>

<P><strong>First Name:</strong><br><input type="text" name="f_name" size=25 maxlength=25>

<P><strong>Street Name:</strong><br> <input type="text" name="s_name" size=25 maxlength=25>

<P><strong>State:</strong><br> <input type="text" name="state" size=25 maxlength=25>

<P><strong>Pincode:</strong><br>

<P><strong>Phone (Mobile/Land):</strong><br>

<P><strong>Notes:</strong><br> <textarea name="note" cols=35 rows=5 wrap=virtual></textarea>

</form>

38 of 43

Insert a new record - Syntax

INSERT INTO table_name (column1, column2, column3,...)�VALUES (value1, value2, value3,...)

Syntax rules to be followed:

PHP requires that the SQL query be quoted.
String values must be quoted within the SQL query.
Numbers should not be quoted.
NULL should not be quoted.

39 of 43

Insert a new record - Command

$sql = "INSERT INTO address_book (fname, lname, streetname,city,state, pincode, phone, emailid, notes) VALUES

('$fname','$lname', '$sname','$city', '$state','$pin','$phone','$mail','$note')";

1 of 43

2 of 43

3 of 43

4 of 43

5 of 43

6 of 43

7 of 43

8 of 43

9 of 43

10 of 43

11 of 43

12 of 43

13 of 43

14 of 43

15 of 43

16 of 43

17 of 43

18 of 43

19 of 43

20 of 43

21 of 43

22 of 43

23 of 43

24 of 43

25 of 43

26 of 43

27 of 43

28 of 43

29 of 43

30 of 43

31 of 43

32 of 43

33 of 43

34 of 43

35 of 43

36 of 43

37 of 43

38 of 43

39 of 43

40 of 43

41 of 43

42 of 43

43 of 43