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

Question

Here is my initial createScheme.php

<html>
<body>

<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">
</form>

</body>
</html>

And here is my tableData.php

<?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." (
book_no VARCHAR(10) NOT NULL PRIMARY KEY, 
name VARCHAR(50) NOT NULL,
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)";
    $conn->query($sql);
    $i++;
}
if (mysqli_query($conn, $sql)) {
echo "Table altered successfully";
} else {
    echo "Error adding column: " . mysqli_error($conn);
}

mysqli_close($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

You cann't add table field with name "2016/07/08", it's ridiculous. But if you want, you should replace slashes with underscore, for example - 2016_07_08 — Suleiman, Jul 08 '16 at 07:41
@IceJOKER a *column* like 2016_07_08 **is even more ridiculuos** — Your Common Sense, Jul 08 '16 at 07:42
You should change your database scheme, making only **one column** for date. — Your Common Sense, Jul 08 '16 at 07:43
I have no other option, it's clients requirements. I did try using _ but date function with y_m_d gives me corrupt output. It only accepts / or -. So I need to use anyone of those to increment date — Sapnesh Naik, Jul 08 '16 at 07:45
@YourCommonSense you're right , pay attention to - "But if you want" — Suleiman, Jul 08 '16 at 07:45
Just did $column= str_replace("/", "_", $zdate); my test echo column statement prints all dates with / replaced. But I still have the same error I think column name isn't the issue here. — Sapnesh Naik, Jul 08 '16 at 08:04
@sapneshnaik as a dev you should be talking with your client and explain to them why this shouldn't be done this way and not just say _it's client requirements' it will lead to long term effort with database maintenance.. — Matt, Jul 08 '16 at 08:08
@Matt yes but I think it's not an issue with the name anymore. See the edit at the end in my original post — Sapnesh Naik, Jul 08 '16 at 08:11
@sapneshnaik Yes it might not be a name issue etc but you really shouldn't be doing it like this.. Having a column per date means when you come to order things by date you can't ... You should have 1 column for date as _Your Common Sense_ said and multiple rows. Otherwise later down the line when the client says _order my boo numbers by date_ you'll go .. ah nope no can do. — Matt, Jul 08 '16 at 08:14
*"but date function with y_m_d gives me corrupt output"* – Wut? That's... unusual, to say the least. — deceze, Jul 08 '16 at 08:14
And I'll chime in: don't let the client dictate your database design. *You* are the database designer (presumably), *you* know how to work with relational databases (presumably, maybe not); the client doesn't know, that's why they hire you. — deceze, Jul 08 '16 at 08:16
@deceze the output defaults to 1970 no matter what start date I give when using y_m_d — Sapnesh Naik, Jul 08 '16 at 08:18
Whenever I hear the words "client requirements" I instantly think of another word: "pushback". I don't tell my dentist how to fix my teeth - imagine if I did, and even worse - imagine if he followed my instructions!? — Darragh Enright, Jul 08 '16 at 08:18
@Matt it's my first real client project. I can't figure out how to implement your suggestion of one date column. It has to like this bookno, name, details, date1,date2,date3..,date'n'. Whenever a customer pays that months amount the respective date feild for that customer should have a tick or some indication. That's all I want. The date as column name is very necessary in that way. It would enable the owner to tick the box for that particular month — Sapnesh Naik, Jul 08 '16 at 08:22
You would want a date for when the amount was paid, if it was split payments maybe have a start of payment date and fully paid date but never have a column per date with a _tick_. You then write code specifically so a user can tick a tickbox and on the backend, if this tickbox is ticked then put the date into the _date_ column and if you require a `paid` column you can do that and also insert into there. You need to design a database such that it is normalized thus easy to order and search etc — Matt, Jul 08 '16 at 08:26
I'm positive the issue isn't with date format just replaced / with m, so the date now is 2016m07m08 still the same error!. — Sapnesh Naik, Jul 08 '16 at 08:28
for debugging..change the name to something simple like `date1` `date2` `date3` etc and then you can echo out your sql queries you are attempting, paste them into the database and see what error the database itself throws. — Matt, Jul 08 '16 at 08:30
@Matt it was all working when the name was just date1, date2. The database was created successfully. — Sapnesh Naik, Jul 08 '16 at 08:33

score 0 · Accepted Answer · edited May 23 '17 at 11:44

0

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 ";

edited May 23 '17 at 11:44

Community

1
1

answered Jul 08 '16 at 14:48

Sapnesh Naik

11,011
7
63
98

carte-sd · Answer 2 · 2016-08-06T15:42:24.880

0

I had the same problem I think MySQL no longer allows us to omit grave accents (Alt Gr + 7 key on a french keyboard).

alter table personnages
add column force tinyint unsigned not null default 20 after nom; # This doesn't work anymore

alter table `personnages`
add column `force` tinyint unsigned not null default 20 after `nom`; # This works

edited Aug 06 '16 at 15:42

answered Aug 06 '16 at 09:10

carte-sd

21
4

I just found out what is the problem. Somes words are reserved to mysql, in my query, the word 'force' is problematic. It does the same error with words like 'if' or 'for', you must escape them. – carte-sd Aug 14 '16 at 07:29

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

2 Answers2