PHP reading from a database

Question

Answers ( 1 )

    0
    2024-01-11T18:05:25+00:00

    This task involves using PHP, a server-side scripting language, to retrieve data from a database.

    To accomplish this, you typically use PHP's database connection functions along with SQL queries. The most common types of databases accessed with PHP are MySQL and PostgreSQL. Below, I'll provide an example using MySQL, which is widely used with PHP.

    Connecting to a MySQL Database

    Before you can read from the database, you must establish a connection. PHP offers two main ways to connect to a MySQL database: using mysqli (MySQL Improved) or PDO (PHP Data Objects). Both have their advantages, but PDO offers more flexibility by allowing connections to various database types, not just MySQL.

    Using mysqli:

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

    Using PDO:

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    

    Reading Data from the Database

    After establishing a connection, you can use SQL queries to read data. Here’s a basic example to fetch all rows from a table named users.

    Using mysqli:

    $sql = "SELECT id, name, email 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";
    }
    $conn->close();
    

    Using PDO:

    $sql = "SELECT id, name, email FROM users";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    
    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    foreach(new RecursiveArrayIterator($stmt->fetchAll()) as $k=>$v) {
        echo $v['id']. " - Name: " . $v['name']. " - Email: " . $v['email']. "<br>";
    }
    
    $conn = null;
    

    In these examples, replace "localhost", "username", "password", and "myDB" with your actual database host, username, password, and database name. Also, modify the SQL query as per your specific database schema and requirements.

    Remember that database operations can be complex, and this is just a basic introduction. For more advanced operations, you may need to delve into more complex SQL queries, database security, and performance optimization.

Leave an answer