MySQL SET DEFAULT returns error: query is empty

Question

I have an existing MySQL (version 5.6.12) table called 'users' and am trying to set the column defaults via php (version 5.4.12), for example:

$query = "ALTER TABLE users ALTER username SET DEFAULT NULL";
$result = mysqli_query($query);

However this generates a SQL error (which results in the message 'query is empty'). 'username' is a VARCHAR(200) type column.

Using a quoted literal (such as 'John') for the default value results in the same error. (Just for kicks, though I am using MySQL I have also tried modifying the query according to SQL/MS Access or Oracle syntax but it still doesn't work).

What am I doing wrong?

EDIT: Problem solved. The above query is fine and nothing is wrong.

I made an incredibly dumb error and forgot $ sign in front of $query, i.e. my code was written $result = mysqli_query(query);

(Just so you know, I am a programmer with decades of experience.)

I guess the command should look like : `ALTER TABLE users MODIFY....` — 1000111, Nov 14 '16 at 10:31
It does not work for you because mysqli_query() requires two parameters.... Look into manual — nospor, Nov 14 '16 at 10:33
@nospor Only for the procedural style, not object oriented: See `http://php.net/manual/en/mysqli.query.php` — Stefan, Nov 14 '16 at 11:28
@1000111 And ALTER is correct: See for example https://www.tutorialspoint.com/mysql/mysql-alter-command.htm — Stefan, Nov 14 '16 at 11:34
@nospor How can you tell? I'm only showing code relevant to my question...? — Stefan, Nov 14 '16 at 13:03
@Stefan `$result = mysqli_query(query);` this is procedural style... Are we playing hide and seek or what? — nospor, Nov 14 '16 at 13:25

score 0 · Answer 1 · 2016-11-14T10:37:01.957

0

Change query like below

$query = "ALTER TABLE users MODIFY Column username VARCHAR(200) NULL DEFAULT NULL";

or

 $query = "ALTER TABLE users CHANGE Column username username VARCHAR(255) NULL DEFAULT NULL";

edited Nov 14 '16 at 10:37

answered Nov 14 '16 at 10:31

score 0 · Answer 2 · answered Nov 14 '16 at 10:47

0

I use this one for my website

$query = "ALTER TABLE 'users' CHANGE 'username' 'username' VARCHAR( 100 ) NULL DEFAULT NULL";
$result = mysqli_query($query);

answered Nov 14 '16 at 10:47

Jack Siro

677
10
29

score 0 · Answer 3 · answered Nov 14 '16 at 11:42

0

Problem solved. The query is actually fine and nothing is wrong.

I made an incredibly dumb error and forgot $ sign in front of $query, i.e. my code was written $result = mysqli_query(query);

(Just so you know, I am a programmer with decades of experience.)

answered Nov 14 '16 at 11:42

Stefan

3,850
2
26
39

dont brag just say you made a mistake and have realized it – Jack Siro Nov 14 '16 at 14:32
@JacksonSiro I was demonstrating that even after many years of experience I am still dumb enough to make such a mistake. Obviously you had trouble understanding this. – Stefan Nov 14 '16 at 18:40
Okay sir. Programmers need a 'binoculous' – Jack Siro Nov 15 '16 at 05:52

score -1 · Answer 4 · answered Nov 14 '16 at 10:42

-1

In MySQL you use backticked [`] quoted literals, don't use the quote sign [']. On the other side, every DDBB engine uses its slightly different flavour of SQL. Most of them anyway are able to interpret common subsets as described by the various active standards SQL89, SQL92, SQL99... and so on.

In short, your SQL is wrong, in regards to what MySQL expects it to be. You'll have to build your SQL skills on top of trial and error by working with different SQL engines, until you can give each of these engines the SQL variant they expect. Just don't give up and insist on the query. Simplify it, running shorter strings until you find one that works, then refine it by adding smaller pieces until you reach your objective.

answered Nov 14 '16 at 10:42

Daniel J.

308
2
12

This doesn't help me much at all. – Stefan Nov 14 '16 at 11:33
Kindly: help yourself first. I'm trying to put you into context, so that you can solve your problem on your own. Analyzing every byte on your query and giving you the right one is making your homework. – Daniel J. Nov 15 '16 at 08:05
There is nothing you said in this post that I and most everybody else don't already know. It is way too general when my problem is a very specific problem. In fact, it is almost insulting because of the assumptions you make - it is like you are telling a little kid he should just problem solve his problem. Zero help given. – Stefan Nov 15 '16 at 08:49
Nobody knows on advance what is your level of knowledge in this specific subject. Being it higher or lower does not make you better or worse, so I don't see how my answer could be considered insulting. Anyhow, I see you followed my advice and helped yourself in the end. – Daniel J. Nov 18 '16 at 10:01
Your answer amounts to telling someone to figure it out for themselves by trial and error. Seriously? You think that is helpful? Of course that's insulting. Any idiot knows that, so you are in effect saying I am less than an idiot. The comments you made about backticked literals are, firstly, irrelevant, and secondly, also not necessarily true. The are only needed if you use special caharacters for table and field names, and it might be better to use normal quotes because bacticks are not ANSI-SQL compliant. – Stefan Nov 19 '16 at 07:29

MySQL SET DEFAULT returns error: query is empty

4 Answers4