1

I use to insert values in php like this:

$sql = " INSERT INTO `table` (name, email) VALUES ('$name','$email') ";

considering that my table has the following columns: id, name, email, address, phone

in mysql 5.7 it won't let my do that query above anymore, cause it's says address don't have a default value.

I add a default value for it on mysql, but some fields dont allow default value, like blob, text...

my question is: Do I always have to describe every column of my table in my query so it can work? Like:

$sql = " INSERT INTO `table` (id,name, email, address, phone) 
VALUES ('', '$name','$email', '', '') ";
  • 3
    ***[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. [Escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! Learn [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 12 '17 at 16:11
  • 2
    Why concatenate the string instead of using [prepared statements](https://secure.php.net/manual/en/pdo.prepare.php) ? – Artemis Apr 12 '17 at 16:11
  • 1
    If you could have a default value for BLOB columns, what would it be? – Álvaro González Apr 12 '17 at 16:12
  • 2
    You don't have to describe every column for every query you make, but particular for `INSERT` queries, you have to make sure that the columns you *don't* specify either have a default value, or can be `NULL`. – Qirel Apr 12 '17 at 16:12
  • 1
    You don't need to insert id because if its set to auto increment / primary key – Mario Apr 12 '17 at 16:15

1 Answers1

0

You don't have to describe every column for every query you make, but particularly for INSERT queries, you have to make sure that the columns you don't specify either have a default value, or can be NULL.

For other queries, such as SELECT or UPDATE, you can choose just the ones you want without regard what the content is (of course same applies for UPDATE as INSERT, that columns which cannot be NULL, can't have a NULL-value in it).

You can alter your table such that the values you don't always insert either have a default value, or just set them to NULL by default (if you don't supply a value for that column upon inserting).

ALTER TABLE table_name 
CHANGE column_name column_name type DEFAULT NULL
Qirel
  • 25,449
  • 7
  • 45
  • 62