how to make table, alter column, and insert data into it at once in PHP?

Question

I want to send data from HTML to PHP and with POST and create table first with table value and alter add column with column value, and finally want to insert data into the column with data values when I send from HTML, but no matter I do it keeps having error.

Here is my HTML Code.

<form method="post" action="table2.php">
 Table<input type="text" name="table"><br>
 Column<input type="text" name="column"><br>
 Data<input type="text" name="data"><br>
 <input type="submit" value="submit">
</form>

and here is my PHP code,

<?php

include_once("login2.php");

$table = $_POST['table'];
$column = $_POST['column'];
$data = $_POST['data'];


$createTable = "create table ".$table."(
id int not null auto_increment, 
nickname varchar(50),
primary key(id)
)";

/*    Create Table   */

if(mysqli_query($conn, $createTable)){
echo "<br><br>";
echo "table created successfully";
$alterColumn = "alter table ".$table." add ".$column. "varchar(100)";
}else{
    echo "<br><br>";
    echo "Failed to create databases";
}


/*  Alter add Column into the Table   */

if(mysqli_query($conn, $alterColumn)){
    echo "<br><br>";
    echo "column created successfully";
    $insertValue = "insert into ".$table."(".$column.")values(".$data.");";
 }else{
      echo "<br><br>";
      echo "Failed to alter column";
    }

/* Insert data into the Column above  */

if(mysqli_query($conn, $insertValue)){
      echo "<br><br>";
      echo "Every process successfully done.";
}else{
      echo "<br><br>";
      echo "Failed to insert data"; 
   }
    
?>

Here are the errors that I have:

Warning: mysqli_query(): Empty query in C:\xampp\htdocs\practice\table2.php on line 30

Notice: Undefined variable: insertValue in C:\xampp\htdocs\practice\table2.php on line 41

Warning: mysqli_query(): Empty query in C:\xampp\htdocs\practice\table2.php on line 41

Can you write a functioning query in plain sql? Maybe start with that, and identify any discrepancies between that and your echoed query. — Strawberry, Jun 23 '17 at 07:07

score 1 · Answer 1 · edited Jun 23 '17 at 07:04

1

There is an issue with the alter query code

$alterColumn = "alter table ".$table." add ".$column. "varchar(100)";

change it to

$alterColumn = "alter table ".$table." add ".$column. " varchar(100)";

Added space after the $column variable

edited Jun 23 '17 at 07:04

Carl Binalla

5,393
5
27
46

answered Jun 23 '17 at 07:03

Vivek D.

101
7

OH Thank you so much! haha – James David Deann Jun 23 '17 at 13:18

Professor Abronsius · Answer 2 · 2017-06-23T07:22:47.110

A slightly different approach - seems to work in tests anyway. I believe it is always better to use backticks around table and column names - which also would have helped when a simple space was missing from alter command. The final insert statement required quotes around the actual data - though using prepared statements would have been a better solution.

<?php

    include_once("login2.php");

    $table = $_POST['table'];
    $column = $_POST['column'];
    $data = $_POST['data'];

    /*    Create Table   */
    $createTable = "create table `".$table."`(
        `id` int not null auto_increment, 
        `nickname` varchar(50),
        primary key(`id`)
        )";

    /* bitwise operator to test if everything is ok */
    $result = !empty( $createTable ) & mysqli_query( $conn, $createTable );
    $alterColumn = $result ? "alter table `".$table."` add `" . $column . "` varchar(100)" : false;
    echo $result ? 'Table created' : 'Failed to create table';


    /*  Alter add Column into the Table   */
    $result = !empty( $alterColumn ) & mysqli_query( $conn, $alterColumn );
    /* data needs to be quoted! */
    $insertValue = $result ? "insert into `".$table."` ( `".$column."` ) values ('".$data."');" : false;
    echo $result ? 'Column added' : 'Failed to add column';

    /* Insert data into the Column above  */
    $result = !empty( $insertValue ) & mysqli_query( $conn, $insertValue );
    echo $result ? "Every process successfully done." : "Failed to insert data";


?>

how to make table, alter column, and insert data into it at once in PHP?

2 Answers2