What is the best way to retrieve the column titles, types, and lengths from a SQL table in PHP?

To retrieve the column titles, types, and lengths from a SQL table in PHP, you can use the following SQL query to fetch the information schema for the table. This query will provide you with all the necessary details about the columns in the table, such as the column name, data type, and maximum length. You can then loop through the result set to extract and display this information. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

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

// SQL query to retrieve column information
$sql = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'your_table_name'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Column Name: " . $row["COLUMN_NAME"]. " - Data Type: " . $row["DATA_TYPE"]. " - Max Length: " . $row["CHARACTER_MAXIMUM_LENGTH"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

PHP SQL database column metadata data types

Related Questions