When records are inserted into a table, a ‘SELECT’ statement is used to retrieve that record.
Here, this syntax is used to retrieve records from a single column of a table.
SELECT column_name (s) FROM table_name
Here this syntax is used to retrieve records from all the columns of a table.
SELECT * FROM table_name
Two parameters are usually given to the mysqli_query () function on the script below. The connection is given on one parameter and the string of MySQL statement is given on the other. This function is stored on $ result.
mysqli_fetch_row () :- this function number of rows; returns If the number of rows on the script is greater than 0 then only the statement of if will be executed.
Mysqli_fetch_assoc () :- in the given while condition when the number of rows is greater than 0, all results in this function; The iterations are done with loops in the associative array and when the rows are finished. Then this function returns NULL.
<?php
$conn = mysqli_connect("localhost", "root", "", "expertstutorials");
if($conn){
echo "Connected successfully.<br />";
}
else{
echo "Connection failed : ".mysqli_connect_error();
}
$select = "SELECT * FROM course";
$result = mysqli_query($conn, $select);
echo "<table>
<tr>
<th>id</th>
<th>Course Name</th>
<th>Course Year</th>
</tr>";
if(mysqli_num_rows($result) > 0){
while($rows = mysqli_fetch_assoc($result)){
echo "<tr>
<td>".$rows['id']."</td>
<td>".$rows['course_name']."</td>
<td>".$rows['course_year']."</td>
</tr>";
}
echo "</table>";
}
else{
echo "rows not found in table";
}
mysqli_close($conn);
?>
If you want to select a specific column,
SELECT id, student_name FROM student
Example for SELECT all COLUMNS using PDO
<?php
$server = "localhost";
$user = "root";
$password = "";
$db = "expertstutorials";
try{
$conn = new PDO("mysql:host=$server;dbname=$db", $user, $password);
echo "Connected successfully.";
$select = "SELECT * FROM course";
$result = $conn->query($select);
echo "<table>
<tr>
<th>id</th>
<th>Course Name</th>
<th>Course Year</th>
</tr>";
if($result->rowCount() > 0){
while($rows = $result->fetch()){
echo "<tr>
<td>".$rows['id']."</td>
<td>".$rows['course_name']."</td>
<td>".$rows['course_year']."</td>
</tr>";
}
echo "</table>";
}
else{
echo "rows not found in table";
}
}
catch(PDOException $e){
echo "Table Creation failed : " . $e->getMessage();
}
$conn = null;
?>