PHP INSERT boolean values into mysql

Question

I am trying to insert boolean values into mysql table through PHP Script.

Here is my code:

$id = "104"
  $bool1 = true
  $bool2 = false

$sql = "INSERT INTO Monday  SET Id = '$id' , morning = '$bool1' , night = '$bool2' " ;

//Store the result in $result
$result = $conn->prepare($sql) ;

// Get the result  in a Returnvalue var
if (!$result) {
    throw new Exception($result->error);
}
$result->bind_param("sii" ,$patientId , $morning , $night );
$returnValue = $result ->execute();
return $returnValue ;`

In MYSQL table structure

morning and night both are of type BOOLEAN (tinyint(1))

When I execute this code true and false both inserts value '0' .

yes thats because your structure of column is tinyint 1 that means only one value can be inserted, change the column to varchar(4) and check again — Exprator
– Exprator, Commented Jun 14, 2017 at 8:14
The code does what you have made it to. So I wonder a bit what the actual question is. Saying that, interpolating the variables into the query-string directly often is error prone. That might but must not be the cause of your issue. — hakre
– hakre, Commented Jun 14, 2017 at 8:15
TinyInt(1) only knows 0(false) and 1(true). so instead of inserting true or false insert 0 and 1. — Saad Suri
– Saad Suri, Commented Jun 14, 2017 at 8:15

RiggsFolly · Accepted Answer · 2017-06-14 08:42:52Z

1

Because morning and night are tinyint you should pass numbers(integers) 0=false and 1=true.

Also your mysqli_ code is a little odd, when using prepared parameterised queries you do not concatenate values into the query text, you use ? parameters

$id = "104"
$bool1 = 1
$bool2 = 0

$sql = "INSERT INTO Monday  SET Id = ? , morning = ? , night = ?" ;

$result = $conn->prepare($sql) ;

// Get the result  in a Returnvalue var
if (!$result) {
    throw new Exception($result->error);
}
$result->bind_param("sii" ,$patientId , $morning , $night );
$returnValue = $result ->execute();
return $returnValue;

You might also like to rethink your database design. Having a table for each day is very odd. Instead consider a table with a more generic name, and in it have a column called dayname or something like that.

So your table might look like this

id
dayname
morning
night

and a single table would hold all the days of the week

edited Jun 14, 2017 at 8:42

answered Jun 14, 2017 at 8:32

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

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

6 Comments

swift_USer Over a year ago

I did tried entering table name as a Variable but it failed."INSERT INTO '$dayOfWeek' SET Id = ? , morning = ? , night = ?"

RiggsFolly Over a year ago

No thats not what I ment! You cannot use a parameter for a table name or a column name

swift_USer Over a year ago

Actually I have 7 tables Monday , Tuesday , Wednesday .... etc . All 7 tables structure is same. I want this one query to apply for all 7 tables. How can I make this query dynamic ? Shall I give table name as variable $dayOfWeek which can have any value from Monday till Sunday.

RiggsFolly Over a year ago

No. Make ONE TABLE to hold all 7 days. That is what I have been trying to say

swift_USer Over a year ago

No I am not allowed to do so.

|

Collectives™ on Stack Overflow

PHP INSERT boolean values into mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related