web analytics
May 19, 2024

How to connect to a MySQL database and retrieve data from MySQL tables using PHP

<?php

// Connect to the database
$host = "hostname";
$user = "username";
$password = "password";
$dbname = "dbname";

$conn = mysqli_connect($host, $user, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Select data from two tables
$sql = "SELECT * FROM table1 JOIN table2 ON table1.id = table2.id LIMIT 4";
$result = mysqli_query($conn, $sql);

// Output data
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - table1_col: " . $row["table1_col"]. " - table2_col: " . $row["table2_col"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Also, you can use below example for adding, deleting and editing the data in tables and rows.



<?php
// Insert data into table
$sql = "INSERT INTO table (col1, col2) VALUES ('value1', 'value2')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Update data in table
$sql = "UPDATE table SET col1 = 'new_value' WHERE id = 1";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Delete data from table
$sql = "DELETE FROM table WHERE id = 1";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Please note that this code is just an example and may not run as is. You will need to replace the placeholder values with your own database credentials, table names, and column names. Also, make sure to handle security issues like SQL injection, etc.