PHP Database Connectivity & AJAX Durgesh Kharb

UNIT IV: PHP Database Connectivity &

PHP with AJAX
NO. OF HOURS: 11 CHAPTER/BOOK REFERENCE: TB1 [8]

Prepared by: Durgesh Kharb

Contents

1 Overview 1

2 Introduction to MySQL 2
2.1 What is MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.2 Basic SQL Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

3 Connecting PHP to MySQL 3

4 MySQLi - Procedural Approach 3

4.1 Connecting and Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . 3
4.2 Inserting Data (Procedural) . . . . . . . . . . . . . . . . . . . . . . . . . 4

5 MySQLi - Object-Oriented Approach 4

6 Prepared Statements (MySQLi) 5

7 PHP Data Objects (PDO) 6

8 Checking Data Errors 6

9 PHP with AJAX 7

9.1 Steps to Create a PHP-AJAX Application . . . . . . . . . . . . . . . . . 7

10 Conclusion 8

1 Overview

This unit covers how PHP interacts with relational databases using:

• Relational Databases and SQL (MySQL)

• PHP Data Objects (PDO)

• MySQLi (Procedural and Object-Oriented)

Page 1
 PHP Database Connectivity & AJAX Durgesh Kharb

• Querying MySQL Database

• Prepared Statements and Error Checking

• PHP with AJAX — building interactive web apps

2 Introduction to MySQL

2.1 What is MySQL?

MySQL is an open-source Relational Database Management System (RDBMS)

that uses SQL (Structured Query Language) to manage data stored in tables.

2.2 Basic SQL Operations

1 -- Create database
2 CREATE DATABASE college ;
3

4 -- Use the database

5 USE college ;
6

7 -- Create a table
8 CREATE TABLE students (
9 id INT AUTO_INCREMENT PRIMARY KEY ,
10 name VARCHAR (100) ,
11 email VARCHAR (150) ,
12 age INT
13 );
14

15 -- Insert data
16 INSERT INTO students ( name , email , age )
17 VALUES ( ’ Amit Kumar ’ , ’ amit@example . com ’ , 21) ;
18

19 -- Retrieve data
20 SELECT * FROM students ;
21

22 -- Update data
23 UPDATE students SET age = 22 WHERE id = 1;
24

25 -- Delete data
26 DELETE FROM students WHERE id = 1;

Listing 1: Common SQL Commands

Expected Output:

Page 2
 PHP Database Connectivity & AJAX Durgesh Kharb

Query OK, 1 row affected

+----+------------+-------------------+-----+
| id | name | email | age |
+----+------------+-------------------+-----+
| 1 | Amit Kumar | amit@[Link] | 21 |
+----+------------+-------------------+-----+

3 Connecting PHP to MySQL

PHP can connect to MySQL using:

1. MySQLi (Improved MySQL extension)

2. PDO (PHP Data Objects)

Database credentials:

• Host: localhost

• Username: root

• Password: your password

• Database: college

4 MySQLi - Procedural Approach

4.1 Connecting and Retrieving Data

1 <? php
2 $conn = mysqli_connect ( " localhost " , " root " , " your_password " , "
college " ) ;
3

4 if (! $conn ) {
5 die ( " Connection failed : " . m y s q l i _ c o n n e c t _ e r r o r () ) ;
6 }
7

8 $sql = " SELECT id , name , email FROM students " ;

9 $result = mysqli_query ( $conn , $sql ) ;
10

11 if ( mysqli_num_rows ( $result ) > 0) {

12 while ( $row = my sq li _f et ch _a ss oc ( $result ) ) {
13 echo $row [ ’ id ’] . " - " . $row [ ’ name ’] . " - " . $row [ ’
email ’] . " <br > " ;
14 }

Page 3
 PHP Database Connectivity & AJAX Durgesh Kharb

15 } else {
16 echo " No records found . " ;
17 }
18

19 mysqli_close ( $conn ) ;
20 ?>

Listing 2: MySQLi Procedural - Connect and Query

Expected Output:

1 - Amit Kumar - amit@[Link]

2 - Rahul Singh - rahul@[Link]

4.2 Inserting Data (Procedural)

1 <? php
2 $conn = mysqli_connect ( " localhost " , " root " , " your_password " , "
college " ) ;
3

4 $name = " Riya Sharma " ;

5 $email = " riya@example . com " ;
6 $age = 20;
7

8 $sql = " INSERT INTO students ( name , email , age )

9 VALUES ( ’ $name ’, ’ $email ’, $age ) " ;
10

11 if ( mysqli_query ( $conn , $sql ) ) {

12 echo " Record inserted successfully . " ;
13 } else {
14 echo " Error : " . mysqli_error ( $conn ) ;
15 }
16

17 mysqli_close ( $conn ) ;
18 ?>

Listing 3: MySQLi Procedural - Insert

Expected Output:

Record inserted successfully.

5 MySQLi - Object-Oriented Approach

Page 4
 PHP Database Connectivity & AJAX Durgesh Kharb

1 <? php
2 $mysqli = new mysqli ( " localhost " , " root " , " your_password " , "
college " ) ;
3

4 if ( $mysqli - > connect_error ) {

5 die ( " Connection failed : " . $mysqli - > connect_error ) ;
6 }
7

8 $result = $mysqli - > query ( " SELECT name , email FROM students " ) ;
9 while ( $row = $result - > fetch_assoc () ) {
10 echo $row [ ’ name ’] . " - " . $row [ ’ email ’] . " <br > " ;
11 }
12

13 $result - > free () ;

14 $mysqli - > close () ;
15 ?>

Listing 4: MySQLi Object-Oriented - Connect and Query

Expected Output:

Amit Kumar - amit@[Link]

Riya Sharma - riya@[Link]

6 Prepared Statements (MySQLi)

Prepared statements help avoid SQL injection attacks.

1 <? php
2 $conn = new mysqli ( " localhost " , " root " , " your_password " , " college
");
3

4 $stmt = $conn - > prepare ( " INSERT INTO students ( name , email , age )
VALUES (? , ? , ?) " ) ;
5 $stmt - > bind_param ( " ssi " , $name , $email , $age ) ;
6

7 $name = " Arjun Mehta " ;

8 $email = " arjun@example . com " ;
9 $age = 23;
10 $stmt - > execute () ;
11

12 echo " Record inserted successfully . " ;

13 $stmt - > close () ;
14 $conn - > close () ;

Page 5
 PHP Database Connectivity & AJAX Durgesh Kharb

15 ?>

Listing 5: MySQLi Prepared Statement

Expected Output:

Record inserted successfully.

7 PHP Data Objects (PDO)

PDO provides a database-independent interface for PHP.

1 <? php
2 try {
3 $pdo = new PDO ( " mysql : host = localhost ; dbname = college " , " root " ,
" your_password " ) ;
4 $pdo - > setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMO DE_EXC EPTION )
;
5

6 $stmt = $pdo - > query ( " SELECT id , name FROM students " ) ;
7 while ( $row = $stmt - > fetch ( PDO :: FETCH_ASSOC ) ) {
8 echo $row [ ’ id ’] . " - " . $row [ ’ name ’] . " <br > " ;
9 }
10 } catch ( PDOException $e ) {
11 echo " Connection failed : " . $e - > getMessage () ;
12 }
13 ?>

Listing 6: Connecting and Querying with PDO

Expected Output:

1 - Amit Kumar
2 - Riya Sharma
3 - Arjun Mehta

8 Checking Data Errors

PHP provides several functions to handle errors:

• mysqli error($conn)

• mysqli errno($conn)

• PDO’s try-catch exception block

Page 6
 PHP Database Connectivity & AJAX Durgesh Kharb

1 <? php
2 $conn = mysqli_connect ( " localhost " , " root " , " wrong_pass " , "
college " ) ;
3

4 if ( mysql i _ c o n n e c t _ e r r n o () ) {
5 echo " Failed to connect : " . m y s q l i _ c o n n e c t _ e r r o r () ;
6 exit () ;
7 }
8 ?>

Listing 7: Error Checking Example

Expected Output:

Failed to connect: Access denied for user ’root’@’localhost’

9 PHP with AJAX

AJAX (Asynchronous JavaScript and XML) allows sending and receiving data asyn-
chronously between a web page and the server without reloading.

9.1 Steps to Create a PHP-AJAX Application

1. Create an HTML page with JavaScript (AJAX code)

2. Create a PHP file to process requests and return data

1 <! DOCTYPE html >

2 < html >
3 < head >
4 < title > AJAX Example </ title >
5 < script >
6 function fetchStudent () {
7 var xhr = new XMLHttpRequest () ;
8 xhr . open ( " GET " , " getStudent . php " , true ) ;
9 xhr . onre ad ys ta tec ha ng e = function () {
10 if ( xhr . readyState == 4 && xhr . status == 200) {
11 document . getElementById ( " result " ) . innerHTML = xhr .
responseText ;
12 }
13 };
14 xhr . send () ;
15 }
16 </ script >

Page 7
 PHP Database Connectivity & AJAX Durgesh Kharb

17 </ head >

18 < body >
19 < h3 > AJAX Example </ h3 >
20 < button onclick = " fetchStudent () " > Fetch Student Data </ button >
21 < div id = " result " > </ div >
22 </ body >
23 </ html >

Listing 8: HTML + JavaScript (AJAX Example)

PHP file: [Link]

1 <? php
2 $conn = mysqli_connect ( " localhost " , " root " , " your_password " , "
college " ) ;
3 $result = mysqli_query ( $conn , " SELECT name , email FROM students
LIMIT 1 " ) ;
4 $row = mysq li _f et ch _a ss oc ( $result ) ;
5 echo " Name : " . $row [ ’ name ’] . " <br > Email : " . $row [ ’ email ’ ];
6 mysqli_close ( $conn ) ;
7 ?>

Expected Browser Output:

Name: Amit Kumar

Email: amit@[Link]

10 Conclusion

In this unit, we learned:

• Basics of MySQL and SQL commands.

• PHP connectivity using MySQLi (Procedural and OO).

• PDO for secure and flexible database interaction.

• Prepared statements to prevent SQL injection.

• Error handling using exceptions and error functions.

• Building dynamic, asynchronous web applications with PHP and AJAX.

Page 8