PHP : MySQL Select Data

To retrieve data from a MySQL database table using PHP, you can use SQL SELECT queries. Below are examples using both mysqli and PDO to select data from a users table.

Using mysqli :

Here's an example of using mysqli to select all rows from the users table:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query to select data
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

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

Using PDO:

Here's the same example using PDO :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query to select data
    $sql = "SELECT * FROM users";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Set the resulting array to associative
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (count($result) > 0) {
        foreach ($result as $row) {
            echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
        }
    } else {
        echo "0 results";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an SQL SELECT * FROM users query to retrieve all rows from the users table.
• In the mysqli example, fetch_assoc() is used to fetch a row as an associative array.
• In the PDO example, fetchAll(PDO::FETCH_ASSOC) is used to fetch all rows into an associative array.
• The retrieved data is then outputted or processed as needed.

Important Notes:

• The SELECT * query retrieves all columns from the users table. You can specify specific columns if needed ( SELECT id, name, email FROM users ).
• Always verify that the connection is successful before executing queries.
• Ensure you handle exceptions, especially when using PDO.
• Close the connection after use to free up resources.

Running these scripts will select and output all rows from the users table in your MySQL database. Adjust the table name and column names as necessary for your specific database structure.