Setting MySQL value of a row to default in PHP

Question

So I have a PHP form to add a row to a database. If any of the unrequired fields are empty, the default value for that field should be used.

I tried to implement this with MySQLi like this:

$required = $_POST["required"];
$unrequired = isset($_POST["unrequired"])?$_POST["unrequired"]:"DEFAULT(`Unrequired`)";

$sql = $mysqli->prepare("INSERT INTO `table` (`Required`,`Unrequired`) VALUES (?,?)");
$sql->bind_param("is",$required,$unrequired);
$sql->execute();

But when I try to get the value of the unrequired field using SELECT unrequired FROM table WHERE required = 33, I get DEFAULT(`Unrequired`) instead of the default value of the column for varchar columns, and 0 for int and double columns.

Is this problem caused by PHP, or MySQL?

NOTE: Some of the unrequired fields are nullable and some are not. Those which aren't nullable have a set default value. int and double fields' set default value is 1, the rest are nullable.

What does it DEFAULT(`Unrequired`)? It should be returned DEFAULT(`Unrequired`) and it will be inserted in your table. You can set default value instead of DEFAULT(`Unrequired`). — Bilas Sarker, Jun 19 '16 at 15:02

score 0 · Answer 1 · answered Jun 19 '16 at 14:23

0

Can't you just set default value in your database structure. Below you can see an example;

CREATE TABLE example_table
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255), DEFAULT 'unrequired'
City varchar(255) DEFAULT 'Unrequired'
)

When you create your table like that you are good to go. You can also alter your table

ALTER TABLE example_table
ALTER City SET DEFAULT 'Unrequired'

answered Jun 19 '16 at 14:23

Atilla Arda Açıkgöz

827
2
15
29

No, that's not what I'm asking. Besides, [this MySql 5.5 document](https://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_default) mentions that an error results if the column does not have a default value. What I'm asking is how to set a row field's value to the respective column's default value. – Yonic Jun 19 '16 at 14:30

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

Alright, I think I figured out the problem.

Since I am using prepared statements, every parameter that I put in the reslting SQL query statement will be converted into safe strings, ints or doubles, so I suppose expressions like DEFAULT(column) will be re-interpreted as "DEFAULT(column)", and will be considered as strings.

Although not a very safe solution, I've considered using not prepared statements, that is concat every parameter and using DEFAULT like in here. To make the queries safer, I'll use real_escape_string() for strings.

edited May 23 '17 at 10:29

Community

1
1

answered Jun 19 '16 at 15:54

Yonic

325
3
14

Make sure you use `mysqli_real_escape_string()` instead, as it should be the newest and most specific function (I think?). PHP makes me a bit sad in that way, there are a lot of similar functions that do *almost* the same, but some are more correct than others even though they appear to be the same, and there's not always very intuitive to find the best one... – ToVine Jun 20 '16 at 18:29

Setting MySQL value of a row to default in PHP

2 Answers2