Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I write out data from SQL table using PhP (8.2)

I want to create a main page for my learning site (trying to learn stuff), but I have a problem with (I think) getting response from query.

HTML for getting the table what I want. Value in button is the table name from Database

<body>
  <div class="s">
        <form  action="table.php" method="POST">
          <ul>
           <!-- <li><input class="button" type="submit" name="ok" value="**table name**"></li> -->
                <li><input class="button" type="submit" name="ok" value="name"></li>
                <li><input class="button" type="submit" name="ok" value="exam"></li>
           </ul>
        </form>
  </div>
</body>

PhP

 if (isset($_POST["ok"])) 
    {
        $TableIWant = $_POST["ok"]; //Stores the name of the table
        $servername = "X";
        $username = "X";
        $password = "X";
        $dbname = "X";
        
        //Sql - gets column names?
        $sqlcol = "SELECT CONCAT('',GROUP_CONCAT(`COLUMN_NAME`),'') FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$dbname' AND `TABLE_NAME`= '$TableIWant'";
        //create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        
        echo "$sqlcol <br>"; // writes out (just for chechk)
        $column = $conn->query($sqlcol);  
        echo "$column <br>";

// after I get column names I go and get the data from database and make table, but I am stuck on getting the column names
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Well the solution It is not the best you can do but it works.

    <?php
if (isset($_POST["ok"])) {
// Get the table name from POST data
$TableIWant = $_POST["ok"];

// Database connection details
$servername = "servername";
$username = "username";
$password = "password";
$dbname = "dbname";

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

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

// SQL query to get concatenated column names
$sqlcol = "SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ') AS `columns`
           FROM `INFORMATION_SCHEMA`.`COLUMNS`
           WHERE `TABLE_SCHEMA` = '$dbname' AND `TABLE_NAME` = '$TableIWant'";

// Execute the query to get column names
$result = $conn->query($sqlcol);

if (!$result) {
    die("Query failed: " . $conn->error);
}

$row = $result->fetch_assoc();

if ($row) {
    // Split the concatenated column names into an array
    $columns = explode(", ", $row['columns']);

    // SQL query to fetch data from the specified table
    $sqlData = "SELECT * FROM `$TableIWant`"; // Query to fetch all data from the specified table

    $dataResult = $conn->query($sqlData); // Execute the query to get data

    if ($dataResult && $dataResult->num_rows > 0) {
        // Start the HTML table with centered alignment
        echo "<table border='1' style='margin: 0 auto; border: 1px solid black;'>";

        // Display the table header with column names
        echo "<tr>"; 
        foreach ($columns as $column) {
            echo "<th>$column</th>"; // Table headers for each column
        }
        echo "</tr>";

        // Display each row of data in the table
        while ($dataRow = $dataResult->fetch_assoc()) {
            echo "<tr>"; 
            foreach ($columns as $column) {
                echo "<td>" . $dataRow[$column] . "</td>"; // Output each data cell
            }
            echo "</tr>"; 
        }

        echo "</table>"; // End the table
    } else {
        echo "No data found in table '$TableIWant'.";
    }
} else {
    echo "No columns found for table '$TableIWant'.";
}

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

  2. 0

    This is a response to your answer, as opposed to your original question.

    Your current die statements for handling errors will not do anything. As of PHP 8.1.0, the default mysqli_driver::$report_mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, meaning an exception will be thrown if there is a connection or query error. For more detail on error handling, you might want to read this write up on error handling.

    Your "solution" uses an unnecessary separate info schema query to get the column names and it is interpolating variables directly into the SQL. Parameterising this is trivial, and I would suggest that it is even more important to do things "properly" while learning, as it helps you avoid forming bad habits.

    $sqlcol = "SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ') AS `columns`
           FROM `INFORMATION_SCHEMA`.`COLUMNS`
           WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?";
$columns = $conn->execute_query($sqlcol, [$dbname, $TableIWant])->fetch_column();

    Instead of the info schema query, you can retrieve the list of columns with mysqli_result::fetch_fields.

    // SQL query to fetch data from the specified table
$dataResult = $conn->query("SELECT * FROM `$TableIWant`");

// Get columns list from result set
$columns = $dataResult->fetch_fields();

    Now that $columns is an array of objects, you would change your column header iteration to:

    foreach ($columns as $column) {
    echo "<th>$column->name</th>";
}

    The query above is interpolating $TableIWant directly into the SQL, as the table name cannot be parameterised. Even though this is just for your "learning site", it is another good example of where you should implement some sensible checks. If this was public facing you definitely wouldn’t want the end user to grab all the data from any table they want, or to exploit the vulnerability by attempting a SQL Injection attack. So, to make sure the user can only access allowed tables you would have a whitelist, something like:

    $tableWhitelist = ['name', 'exam'];

if (isset($_POST['ok']) && in_array($_POST['ok'], $tableWhitelist)) {
    // do stuff
    $TableIWant = $_POST['ok'];
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search