I have an ALTER TABLE query in my php file where the name of the table is based on the $name variable.
e.g. $table = "Scores: ".$name;
I have substituted the $table variable into my INSERT statement and verifies that it does indeed work.
The scores_name table consists of integer fields (smallint), when a $POST value is empty, the number which is inserted into the table is 0 (by default obviously).
However, when a certain condition is met (in a non-empty field), the value for that particular row is intentionally set to be 0.
There are times when the $POST value should be empty, and therefore I want that particular row to be blank, so hence when it inserts as 0 instead of this, this causes obvious confusion.
The following is my alter statement, there are 5 game rows, so this alter query is triggered when the column for that particular game is empty:
if($game1(empty))
{
$query = "ALTER TABLE `$table` MODIFY COLUMN `game1` SMALLINT DEFAULT NULL";
$result = mysql_query($query) or die (' ' . mysql_error() );
}
No mysql error is returned, so therefore I'm assuming the ALTER statement is completing properly, however why is it still inserting 0, despite being modified to default to NULL?
Is there really no way to store a blank value within an integer-based table?
Consider the following...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (id INT NOT NULL AUTO_INCREMENT PRiMARY KEY,x INT DEFAULT NULL);
INSERT INTO my_table (x) VALUES (0),(''),(' '),(NULL);
INSERT INTO my_table (id) VALUES (0),(''),(' '),(NULL);
INSERT INTO my_table VALUES (DEFAULT,DEFAULT);
SELECT * FROM my_table;
+----+------+
| id | x |
+----+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
+----+------+
9 rows in set (0.00 sec)
There are times when the $POST value should be empty, and therefore I want that particular row to be blank, so hence when it inserts as 0 instead of this, this causes obvious confusion.
With the statement, I understand that you don't want to fill values into column but the default values as defined at column creation.
Possible ways to insert default values into columns are:
INSERTing.IF a condition is satisfied then use DEFAULT in place of value for the column in question.Example:
Say, your table is defined like the following:
create table tbl_so_q23869976( si smallint default null, col2 varchar(10) );
Let us see, how we can set defaults into si column.
Case 1: All is well, then:
$sql = "insert into tbl_so_q23869976( si, col2 ) values ( ?, ? )";
$stmt = $mysqli->prepare( @sql );
$stmt->bind_param( 'ds', 6, 'Ravin' ); // use proper variables in place of values
Case 2.1: If something is not matched, then:
/* use with 'default' to insert defaults */
$sql = "insert into tbl_so_q23869976( si, col2 ) values ( default, ? )";
$stmt = $mysqli->prepare( @sql );
$stmt->bind_param( 's', 'Ravin' ); // use proper variables in place of values
Case 2.2: If something is not matched, then:
/*just omit the column name into which you want to insert defaults */
$sql = "insert into tbl_so_q23869976( col2 ) values ( ? )";
$stmt = $mysqli->prepare( @sql );
$stmt->bind_param( 's', 'Ravin' ); // use proper variables in place of values