Introduction ination for developing dynam forms a powerful combinal e oA Th ern of HF wh 9 rt 2 eye Onan Omen an database: Bart wer Me On the other hand, MySQL is a popular open-source relation} jatabases, an‘ database management system amd it offers et In this chapter, we will discuss the interaction between PHP and MySQl. to bulld web applications ively. We can create dynamic websites that respon it store, retrieve, and manipulate data effective] : aes ‘actions and deliver personalized content by utilizing PHP's capabilities to execute Mysqy, queries, handle database connections, and process query results. Pfs + MySQL Improved Extension (mysqli) is @ PHP extension that provides an interface to the Mysq, database. ‘+ Itisan enhanced version of the original MySQL extension (mysql) and offers improved security and functionality. . + The mysqli extension supports features such as prepared statements, transactions, multiple statements, and more, making it a preferred choice for interacting with MySQL databases in PHP applications. + Itprovides a set of functions to connect to a MySQL database, execute queries, fetch results, handle ‘errors, manage transactions, and close connections efficientiy. fficient storage and retrieval of data, BIE Pa ana wySQi Functions PHP provides a variety of functions to interact with MySQL databases to perform tasks such as connecting to a database, executing queries, fetching results, and managing data efficiently. Let us discuss some commonly used PHP functions for MySQL database operations: * (Hoey Connecting to a MySQL Database Establishing a connection to a MySQL database is a fundamental step in developing web applications that interact with data. Connecting to MySQL from a programming language like PHP enables developers to access, manipulate, and retrieve data dynamically. The PHP functions such as mysqli_ connect() and mysqli_select_db() can establish secure connections to MySQL servers and select specific databases for their applications. 1, mysqli_connect() : The mysqli_connect() is a PHP function used to estab! to a MySQL database server, stablish a connection $connection = mysqli_connect($server, $username, $password, $database); + $connection: This variable is used to store the connection object returned by the mysqli_connect() function. It represents the connection to the MySQL database serverom | + mysqli_connect(Sserver, sus eonnect() function call, where. Me + Sserver: Represents. the : ™ connection will be mac, 'YSQL server hostname or IP address to which the + Susername: Refers to th, e when connecting tothe slay ‘database username ised for authentication + Spassword: Indicat yord: tes the pass authentication purposes, No"d a8S0rIated with the provided username for SSE Connecting to a MySQL. Database Using mysqit Sconnection: The MySQL database connection object returned by mysali.connect() «+ Sdatabase_name: The name ofthe database to selec ‘Selecting a Specific Database using mysali.select_dbQ "; } J SELECT query {gquery = "SELECT * FROM person”; = nysqli_query($connection, $query); gresult Jj creck 1f query was executed successfully Hf (Sresult) { // Process the query result while ($row = mysqli_fatch_ossoc($result)) { // Process each row of the result echo “ID: * . $row[‘id'] . "
"; echo “Name: " . $row['name’] . “
: echo "Age: " . $row['age'] . "cbrs" echo "
"; e j else { echo “Error executing query: " . mysqli_error($connection); } // Close connection mysqli_close($connection) ; >» 1, The code establishes a connection toa MySQL database ‘using mysqli_connect() with credentials for the server, username, password, and database name 2 Ttexecutes a SELECT query to retrieve all records from a table named "person" using mysqli_ query0 and processes the query result by fetching each row with mysqli fetch assoct), 3. For each row in the result set, it displays the ID, name, and age values using echo statements. 4. Ithe query execution is successful, the data is displayed; otherwise, an error message with the ‘specific error is shown using mysqli_error(). 5. The code closes the database connection using mysqli_close() to release resources. ‘mysqli_multi_query(): mysqli_multi_query()isa PHP function that allows executing multiple SQL queries in a single call. mysq1i_multi_query($connection, $queries); + Sconnection : MySQL database connection object returned by "mysqli_connect()'. * Squeries : String containing multiple SQL queries separated by semicolons,[Eee | ting Multipl ‘Queries using mysali_multiaueryO cut nyse ; "s * | aysqli_connect_error())5 + // Multiple SELECT queries queries ~ "SELECT * FROM users; SELECT * FROM orders"; Af (mysqli_multi_query($connection, $queries)) { do { if ($result = mysqli_store_result($connection)) { while ($row = mysqli fetch assoc($result)) { // Process each query result // Example: echo $row['column_nane' ]; + mysqli_free_result($result); } } while (mysqli_next_result($connection)); yelse { ; echo “Error executing multiple queries: " . mysqli_error($connection); /I Close connection mysqli_close($connection) ; > = The | ee aes function is used to execute multiple SELECT queries ("SELECT * * Inside th if (rysai ru FROM orders:") with the established database connection. result by iteratin query(connection,quertes))" block, the code processes each queTY ig over the sal fon ose ate ee¥l set using mysal store result), mysal fetch assoe), a| Fhe mysqlisnext result function is used to mov ‘execution loop. «, ifthere is an error in executing the multiple queries, an e if pmo dn ae ror message along with the specific + The connection to the MySQL servers closed using mysali.cose() to the next result set in the multiple query arieving Query Results ag data from the result set of a SQL. query is a crucial aspect of database interaction in web , PHP provides functions like ‘mysqli_fetch_array()', 'mysqli_fetch_assoc()', and ‘mysqli_ sw0 to fetch rows from the result set in different formats, 1, nysqlifetch_arrayQ : The mysqli fetch array() is a PHP function that fetches a result row from the result set as an associative array, a numeric array, or both. mysqli_fetch_array($result, $result_type); + $result : Result set returned by a query execution. + Sresult_type : Optional parameter specifying the type of array to return (MYSQLI_ASSOC, MYSQLILNUM, MYSQLI_BOTH). om if ‘ (SU ae ew Acctere array ng mventtoch aed <2php $servername ~ “localhost”; $username = "root"; $password = “"; $database = “mydatabase"; $connection = mysqli_connect($servername, $username, $password, $database); Af (!$connection) { die("Connection failed: “ . mysqli_connect_error()); + $query = “SELECT * FROM Person”; $result = mysqli_query($connection, $query); if (mysqli_num_rows($result) > @) { while ($row = mysqli_fetch_erray($result, MYSQLI_ASSOC)) { // Process the row data as an associative array echo "ID: * . $row[‘id'] . "
"; echo “Name: " . $row{'name’] . "
"; ‘echo “Age: “ . $row{‘age'] . “”} else { af ‘echo “No records found."5 > mysqli_close($connection); > Pesteeon I conned to the MySQI- database and selects all records from the Person table * The above code connects tothe It as ‘etches each row from the result set as an associative array using mysqli fetch array 1LI_ASSOC option. aa eee ese each ow by accessing te clunn Vie Ung sectg «+ Inside the while loop, “narne’, age’ ‘them. array keys (id, ‘name’, age’) and displays ¢ «= Ifthere are no records in the resul Set, t outputs a message indicating no records found, "The mysqli fetch assoc() is a PHP function that fetches a result row mysqli_fetch_assoc(): from the result set as an associative array. [-symtex J mysqli_fetch_assoc($result); = Sresult : Result set returned by a query execution. Fetching Data as an Associative Array using mysqli_fetch_assoc() Br) sccy apd grocest_ ech, (9&2 an associative array | [nse (Seo = mysqii_fetch_assoc(sresult)) { | vy process the row date $row{'id'} . "
"5 echo “ID: echo "Name: - $row{‘nane’] - "ebr>"5 scho “Age: " = $rowE'age’} - "
" echo “
"3 t peise { ‘echo “NO records found."5 } y ase ed { no “Error executing query: " - nysali_error($connection) 5 , jj etose connection fysqii_close($connection); > 1 “+ The script establi « Itexecutes aSQL query to selectall «= Ifthe query is successful, It fetches eae processes the row data as an: « Thescript outputs the id, name, and age values for each row. 1 ifthere are no records in the result set displays a message indicating no records found. « Ifthere is an error during query ‘execution, it outputs the error message. 4. mysqli fetch row(): The mysqli.fetch_rowO) isa PHP function that fetches a result row from. the result set as a numeric array. Gehes a connection to the MySQL database using! the provided credentials. data from a table named Person. sh row from the result set ‘ising mysqli-fetch_assocO) and array. mysqli_fetch_row($result); + $result : Result set returned by ‘a query execution. Fetching Data as a Numeric Array using’ See ‘php Sservername = “localhost”; $username = “root $password = "75 vs : Sdatabase = "mydatabase” 1 é$passwor A. Gonnect(sservernamer $username, Pi Feonnection = mysal if (1$connection) { & nysqia_conmect_error())5 die("Connection failed: } $query = $result - mysqli geLEcT * FROM Person”s ; query($connection, $query)? esult) > 0) ¢ ni fetch_row($result)) row data as a numeric array if (mysqli_num_rows ($r while ($row = mysql // Process the n echo “rp: * . Srow(@] . “"5 echo echo “ } }else { echo "No records found."; } mysqli_close($connection); ?> = The above code executes a SELECT query to fetch all data froma table named Person. + Ifthere are rowsin the result set, itloops through each row using mysqli fetch_row to fetch the data as a numeric array. + It then processes and displays the data using numeric indices (0, 1, 2) for id, name, and age respectively. + Ifthere are no records in the result set, it outputs a message indicating no records found. + Finally, the script closes the database connection using mysqli close. [HMR Counting Returned Records When working with MySQL databases in PHP, han processing and displaying data in web nee: ere aaa Teas a and mysqli affected rows() to manage and interpret the results of database operations. i 74 Bae Toe. i the yea ana ow isa PHP function that is used to determine the oun ber. set obtained from a SELECT query execution, When this function is with the result set pl ‘95 @ parameter, it returns the number of rows present in that result mysqli_num_rows( $result); : Sresult: Result set returned by a SELECT. query execution, Returns: The number of rows in the result set,+ The number of rows in the tag ; . Pee ee pareaete aso result set by comparing them if records are found, it displays “Displaying search results: and iterate display the name of each person. eS i Ifo records are found, it outputs "No records found: E ‘The databace connection is closed at the end using mysqli.close(Sconnection) resources 5 - .. mysqli_affected_rows(): The PHP function mysqli_affected_rows() is utilized number of rows impacted by the most recent INSERT, UPDATE, or DELETE query the database connection. This function returns the count of affected rows for performed. mysqli_affected_rows(Sconnection); ‘+ Sconnection : It represents the variable that holds the connection to the MySQL server. + Returns: The number of rows affected by the last query. ’ t // DELETE query $query = "DELETE FROM person WHERE id=1"; mysqli_query($connection, $query) ; // Check the number of rows affected by the DELETE query echo “Number of rows affected: " . mysqil_aftacted_ rows (Sconnection); // Close connection en ~~ mysqli_close($connection) ; >+ The DELETE query deletes a record from the "person" table where the ID i 1 + The mysali_query(Sconnection, $query) function is used to execute the DELETE query. + After executing the DELETE que Ty, mysqli_affected_rows($connection) is used to get the number of rows affected by the deletion operation. Secaneri seinen & + The code then displays the number of rows affected by the DELETE operation. + The database connection is closed using mysqliclose(Sconnection) to release resources. ting records in a database involves modifying existing data in a table. This is commonly done jing an SQL UPDATE statement. The UPDATE statement allows to change the values of one or more _jolumns in existing rows based on specified conditions, PHP, we can execute an UPDATE query using the mysqli_query() function provided by the MySQI stension. After executing the UPDATE query, we can check the number of rows affected by the update peration using mysqli_affected_rows(). Updating Records "; } J/ Update query $query = "UPDATE person SET name="Swati', age=3@ WHERE id=1"; mysqli_query($connection, $query); J/ Check the number of rows affected by the UPDATE query ‘echo “Number of rows affected: ” . mysqli_affected_rows($connection); // Close connection nysqli_close($connection) ; >