Ern Ern - 7 months ago 17
PHP Question

How to add new table and column by form input

I wanted to add New table in my database and this table should have 5 columns but it should be done by filling up a form in the website form Let's say I'm going to add Travel Order table with it's five columns. Now i have this code in my form and it gives me this error: Call to undefined method mysqli::mysqli_real_escape_string()

<input type="text" class="form-control1" onKeyPress= "return lettersOnly(event)" name= "categoryname" id="categoryname" placeholder="Category Name..." maxlength="30" reqiured>
this for the table name.

<input type="text" class="form-control1" onKeyPress= "return lettersOnly(event)" name="firstattrib" id="firstattrib" placeholder="..." maxlength="30">
this for one of the column. (please don't mind my naming of variables)

`

$connect = mysqli_connect("localhost","root","","doctrack_db");

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

// sql to create table
$sql = "CREATE TABLE ".$categoryname." (
id INT(6) UNSIGNED AUTO_INCREMENT NOT NULL,
file VARCHAR(30) NOT NULL,
)";

$firstattrib = $connect->mysqli_real_escape_string($_POST['firstattrib']);


//query to add coulumns to table
$query = 'ALTER TABLE ' .$categoryname . '
ADD COLUMN '. $firstattrib .' VARCHAR(30) TINYINT NOT NULL DEFAULT \'0\' AFTER file';

if ($connect->query($sql)&$connect->query($query) === TRUE) {
echo "alert('Category created successfully!!')";
} else {
echo "alert('Error creating Category!!')" . $connect->error;
}

$connect->close();
?>`

Can you please tell me which part i got wrong or is it wrong as a whole?

Answer

try this

    <?php
    // Create connection
    mysql_connect("localhost","root","","test") OR die("Server Connection error");
    mysql_select_db("test") OR die("DB error");

    $category = "category"; 

    // sql to create table
    $sql = "CREATE TABLE ".$category." (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,  
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL,
        email VARCHAR(50),
        reg_date TIMESTAMP
    )";

    if(mysql_query($sql)){
        $sql1 = "INSERT INTO ".$category." (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
        mysql_query($sql1);
    }
?>

and it doesn't matter when you create table, what matter is when you inserting data into table have to has exist.