Editing table data within PHP after adding a new c

问题:

I am creating a website using PHP and MYSQL which has 3 pages; Insert, Update & Delete. The insert page adds a new column to a database table. In my update page I want to be able to view all data which is in the table where the new column has been added. How would I go about this? i.e if theres now 11 columns after the insert rather that 10 previous i want the update page to show

col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11

data | data | data | data | data | data | data | data | data | data | data

回答1:

Here's an example with a table of mine:

<table>
    <tr>

<?php
$mysqli = new mysqli("localhost", "user", "pass", "test");
/*
 * CREATE TABLE `test` (
 `col1` int(11) NOT NULL,
 `col2` int(11) NOT NULL
... // until col5
 */
$query = "SELECT column_name FROM information_schema.columns 
    WHERE table_name = 'test' AND table_schema = 'test'";
$result = $mysqli->query($query);

// INSERT INTO `test`.`test` (`col1`, `col2`, `col3`, `col4`, `col5`)
// VALUES ('10', '11', '12', '13', '14'), ('15', '16', '17', '18', '10');

$query2 = "SELECT * FROM test";
$result2 = $mysqli->query($query2);


while ($row = $result->fetch_assoc()) {

    ?>
        <td><?=$row['column_name'];?></td>

<?php
}
?>
        </tr>
        <tr>

        <?php
 while ($res = $result2->fetch_assoc()) {

    $count_columns = $result->num_rows;

        for ($i = 1; $i <= $count_columns; $i++) {
     ?>
        <td><?=$res['col'.$i.''];?></td>
        <?php
        }
        ?>
        </tr>
        <tr>
<?php

 }

---

Output:

col1    col2    col3    col4    col5
 10     11      12       13     14
 15     16      17       18     10

That's without knowing the column names and their count. It's enough they have the same prefix "col".

After adding one new column:

    ALTER TABLE `test` ADD `col6` INT NOT NULL 
    UPDATE test SET col6 = col1+1

The same code (without any change) produces:

col1    col2    col3    col4    col5    col6
 10     11      12       13     14      11
 15     16      17       18     10      16

P.S.: I'm still not encouraging this table structuring, where you need to add columns dynamicly

回答2:

Even adding columns (altering procedure) from PHP is bad practice for me, there might be different solution. You can retrieve the columns from information_schema:

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'yourTable';

$row['column_name']; //will output the actual result of columns for that table

Also for actual result, you can name them "col1, col2", so you can iterate through the results and recieve variable like

$row['col'.$i.''];

Then you can recieve results for every column, also their names.