Mekanic Mekanic -4 years ago 119
PHP Question

Can I partially populate a table instead of populating all columns in a table?

I'm a newbie to php and mysql and I'm reading from a book. I'm trying to insert some information into a database but I realize that the table only populates when the query below and the database contains exactly the same columns.

For example this works:

$query2 = "INSERT INTO mismatch_user (username, password, join_date) VALUES ('$username', SHA('$password1'), NOW())";

+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | NO | | | |
| password | varchar(40) | NO | | | |
| join_date | date | NO | | NULL | |


When I run this query on this table, it works with no problem.

But if I add more columns to the table then run the same query it doesn't work.

For example this doesn't work:

$query2 = "INSERT INTO mismatch_user (username, password, join_date) VALUES ('$username', SHA('$password1'), NOW())";

+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | NO | | | |
| password | varchar(40) | NO | | | |
| join_date | date | NO | | NULL | |
| first_name | varchar(40) | NO | | | |
| last_name | varchar(40) | NO | | | |
| gender | varchar(1) | NO | | | |
| birthdate | date | NO | | NULL | |
| city | varchar(40) | NO | | | |
| state | varchar(2) | NO | | | |
| picture | varchar(40) | NO | | | |
+------------+-------------+------+-----+---------+----------------+


Everything runs successfully, I checked the error log there were no errors but the user still wasn't created in the database. Is this a legit problem or is this problem specific to my set up?

PS: The whole website only works when I connect with 127.0.0.1, If I use localhost in place of 127.0.0.1 nothing works but except for my problem above with the query issue everything works.

If it helps my versions and OS is:

PHP version: PHP 5.6.29

MySql version: mysql Ver 14.14 Distrib 5.7.16

OS: MacOS Sierra version 10.12.1

All the help is greatly appreciated.

Answer Source

When you look at your database structure and see 'NO' under 'NULL' that means that NULL values are not allowed.

So, when you try to insert data into your table, and it does not see data for columns that are not allowed to be empty it freaks out and dies.

Change your database to allow NULL values for everything except username, password and join date and it should work.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download