| ☰ See All Chapters |
PHP Loop through MySQLi results
PHP makes it easy to get data from your results and loop over it using a while statement. When it fails to get the next row, it returns false, and your loop ends. Below functions can be used to fetch data from results.
Procedural style | Object oriented style | Description |
mysqli_fetch_assoc | fetch_assoc | Returns associative array with column names as keys |
mysqli_fetch_object | fetch_object | Returns stdClass object with column names as variables |
mysqli_fetch_array | fetch_array | Returns associative array with column names as keys. Also this is numeric array, can get the data using numeric string key, key being starts from “0”. |
mysqli_fetch_row | fetch_row | Returns numeric string array, number of elements is same as number of columns, index starts from “0”. |
Procedural style example
Database script
CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6)); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES (103,'Manu',3000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(104,'Tyagraj',4000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(105,'Likitha',5000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'Advith',6000); COMMIT;
|
index.php
<?php
$servername = "localhost"; $username = "root"; $password = "pass"; $dbname = "study";
// Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
$readSql = "SELECT * FROM EMP"; $result = mysqli_query($conn, $readSql); if ($result != null) { while($row = mysqli_fetch_assoc($result)) { echo '</br>Employee ID: '.$row['EMP_ID'].' Name: ' .$row['NAME'].' Salary: '. $row['SALARY']; } }
$result = mysqli_query($conn, $readSql); if ($result != null) { while($row = mysqli_fetch_object($result)) { echo '</br>Employee ID: '.$row->EMP_ID.' Name: ' .$row->NAME.' Salary: '. $row->SALARY; } }
$result = mysqli_query($conn, $readSql); if ($result != null) { while($row = mysqli_fetch_array($result)) { echo '</br>Employee ID: '.$row['0'].' Name: ' .$row['1'].' Salary: '. $row['2']; echo '</br>Employee ID: '.$row['EMP_ID'].' Name: ' .$row['NAME'].' Salary: '. $row['SALARY']; } }
$result = mysqli_query($conn, $readSql); if ($result != null) { while($row = mysqli_fetch_row($result)) { echo '</br>Employee ID: '.$row['0'].' Name: ' .$row['1'].' Salary: '. $row['2']; } }
?> |
Object oriented style example
Database script
CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6)); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES (103,'Manu',3000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(104,'Tyagraj',4000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(105,'Likitha',5000); INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'Advith',6000); COMMIT;
|
index.php
<?php
$servername = "localhost"; $username = "root"; $password = "pass"; $dbname = "study";
// Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }
$readSql = "SELECT * FROM EMP"; $result = $conn->query($readSql);
if ($result != null) { while($row = $result->fetch_assoc()) { echo '</br>Employee ID: '.$row['EMP_ID'].' Name: ' .$row['NAME'].' Salary: '. $row['SALARY']; } }
$result = $conn->query($readSql); if ($result != null) { while($row = $result->fetch_object()) { echo '</br>Employee ID: '.$row->EMP_ID.' Name: ' .$row->NAME.' Salary: '. $row->SALARY; } }
$result = $conn->query($readSql); if ($result != null) { while($row = $result->fetch_array()) { echo '</br>Employee ID: '.$row['0'].' Name: ' .$row['1'].' Salary: '. $row['2']; echo '</br>Employee ID: '.$row['EMP_ID'].' Name: ' .$row['NAME'].' Salary: '. $row['SALARY']; } } $result = $conn->query($readSql); if ($result != null) { while($row = $result->fetch_row()) { echo '</br>Employee ID: '.$row['0'].' Name: ' .$row['1'].' Salary: '. $row['2']; } }
?> |
All Chapters
