| ☰ See All Chapters |
PHP MySQLi query
The query function takes a valid SQL string and executes it directly against the database connection $conn.
Object oriented style
$result = $conn->query("SELECT * FROM `people`");
Object oriented style Example
Database script
CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6)); COMMIT; |
demo.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); }
//Create $insertSql1 = "INSERT INTO EMP (EMP_ID, NAME, SALARY) VALUES(100,'Manu Manjunatha', 123)";
if ($conn->query($insertSql1) === TRUE) { echo "</br>New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//------------------Create----------------- //For insert operations query() returns true if succefully inserted otherwise false. $insertSql2 = "INSERT INTO EMP (EMP_ID, NAME,SALARY) VALUES(101,'Test Name', 200)";
if ($conn->query($insertSql2) === TRUE) { echo "</br>New record created Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//-----------------Read------------------- //For read operation query() returns an associative array with column names as keys $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']; } }
//------------------Update----------------- //For update operations query() returns true if succefully inserted otherwise false.
$updateSql = "UPDATE EMP SET SALARY = '456' WHERE EMP_ID = '101'";
if ($conn->query($updateSql) === TRUE) { echo "</br>Record Updated Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//------------------Delete----------------- //For delete operations query() returns true if succefully deleted otherwise false.
$deleteSql = "DELETE FROM EMP WHERE EMP_ID = '101'";
if ($conn->query($deleteSql) === TRUE) { echo "</br>Record deleted Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
?> |
Procedural style
$result = mysqli_query($conn, "SELECT * FROM `people`");
Object oriented style Example
Database script
CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6)); COMMIT; |
demo.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()); }
//Create $insertSql1 = "INSERT INTO EMP (EMP_ID, NAME, SALARY) VALUES(100,'Manu Manjunatha', 123)";
if (mysqli_query($conn, $insertSql1) === TRUE) { echo "</br>New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//------------------Create----------------- //For insert operations query() returns true if succefully inserted otherwise false. $insertSql2 = "INSERT INTO EMP (EMP_ID, NAME,SALARY) VALUES(101,'Test Name', 200)";
if (mysqli_query($conn, $insertSql2) === TRUE) { echo "</br>New record created Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//-----------------Read------------------- //For read operation query() returns an associative array with column names as keys $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']; } }
//------------------Update----------------- //For update operations query() returns true if succefully inserted otherwise false.
$updateSql = "UPDATE EMP SET SALARY = '456' WHERE EMP_ID = '101'";
if (mysqli_query($conn, $updateSql) === TRUE) { echo "</br>Record Updated Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
//------------------Delete----------------- //For delete operations query() returns true if succefully deleted otherwise false.
$deleteSql = "DELETE FROM EMP WHERE EMP_ID = '101'";
if (mysqli_query($conn, $deleteSql) === TRUE) { echo "</br>Record deleted Successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }
?> |
All Chapters
