sapnesh naik sapnesh naik - 1 year ago 77
MySQL Question

Error adding column: You have an error in your SQL syntax;

Here is my initial createScheme.php


<form action="tableData.php" method="post">
Scheme Name: <input type="text" name="scheme"><br><br>
No of Members: <input type="text" name="memberCount"><br><br>
No. of Months: <input type="text" name="monthCount"><br><br>
EMI Amount: <input type="text" name="amount"><br><br>
Start Date:<input type="text" name="sDate"><br><br>
<!--E-mail: <input type="text" name="email"><br>-->
<input type="submit">


And here is my tableData.php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "gold";
$table = $_POST['scheme'];
$member = $_POST['memberCount'];
$month = $_POST['monthCount'];
$amount = $_POST['amount'];
$ldate= $_POST['sDate'];
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());

// sql to create table
$sql = "CREATE TABLE " .$table." (
contact_no VARCHAR(15) NOT NULL,
amount DECIMAL(20,3),
month VARCHAR(10) NOT NULL)";

if (mysqli_query($conn, $sql)) {
echo "Table created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
$i = 1;
while ($i <= $month)
$zdate = date('Y/m/d', strtotime( "+".$i." month", strtotime($ldate)))."<br>";
$column = $zdate;
echo $column."<br>";
$sql="ALTER TABLE ".$table." ADD ".$column." VARCHAR(55)";
if (mysqli_query($conn, $sql)) {
echo "Table altered successfully";
} else {
echo "Error adding column: " . mysqli_error($conn);


But upon execution I get this error :

> Error adding column: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2016/12/08
VARCHAR(55)' at line 1

Note: I'm trying to create a database for a scheme management. Which involves customer details and date as column name, incremented one month from given date.

Input date is 2016/07/08

Edit: I understand problems might occur with using / in column name so I did

$column= str_replace("/", "_", $zdate);

My test echo column statement prints all dates with / replaced with _. I still have the same error.
I think name isn't the issue here.

Edit2:I'm positive the issue isn't with date format just replaced / with m, so the date now is 2016m07m08 still the same error!.

Please help me solve this, thank you

Answer Source

Finally solved. Knew it had to be a silly mistake. Knowing how to use quotes in sql queries can save you a lot of hassle. this helped Here's what I did,

replacing " with back ticks ` for cumnn variable

$sql="ALTER TABLE ".$table." ADD `$cumnn`VARCHAR(100) NOT NULL ";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download