PHP for fetching a column from a MySQL result

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create a query
$sql = "SELECT name, email FROM users";

// Execute the query
$result = $conn->query($sql);

// Get the column index by name
$index = $result->field_index('name');

// Fetch the column values
while ($row = $result->fetch_array()) {
    echo $row[$index] . "\n";
}

// Close the connection
$conn->close();

This PHP code demonstrates how to fetch a specific column from a MySQL result. Let’s break down what each part of the code does:

  • Database Connection: We start by establishing a connection to the MySQL database using the mysqli extension.

  • Query Execution: We create a query ($sql) to select the name and email columns from the users table. The query() method is used to execute the query and store the result in the $result variable.

  • Column Index: To fetch a specific column, we need to determine its index within the result. The field_index() method is used to get the index of the name column. This technique is helpful when dealing with a result set with many columns.

  • Looping and Fetching: We iterate over the result rows using a while loop. The fetch_array() method is utilized to retrieve each row as an associative array.

  • Column Value Printing: Within the loop, we access the name column value using the index we obtained earlier. The result is printed for each row.

  • Connection Closing: After processing the results, we close the database connection using the close() method.

In summary, this code effectively demonstrates how to fetch a specific column from a MySQL result. It uses a combination of the mysqli extension for database connectivity, SQL for data retrieval, and PHP for manipulating the result set, making it a powerful and flexible approach for working with MySQL data in PHP applications.