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
– Bilas Sarker, Commented Jun 19, 2016 at 15:02

Atilla Arda Açıkgöz · Accepted Answer · 2016-06-19 14:23:57Z

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, 2016 at 14:23

Atilla Arda Açıkgöz

8303 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yonic Over a year ago

No, that's not what I'm asking. Besides, this MySql 5.5 document 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.

Community · Accepted Answer · 2017-05-23 10:29:01Z

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, 2017 at 10:29

CommunityBot

11 silver badge

answered Jun 19, 2016 at 15:54

Yonic

3173 silver badges15 bronze badges

1 Comment

ToVine Over a year ago

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...

Collectives™ on Stack Overflow

Setting MySQL value of a row to default in PHP

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related