save boolean in mysql

Question

I have the following select in my form, which I need to save in my database:

<select name="openhours" class="form-control selectpicker" >
    <option value=" " >Open Sundays</option>
    <option>Yes</option>
    <option>No</option>
</select>

I made a boolean column in MySQL database. But first of all I cannot figure out if a boolean column still is a name for tinyint?

I have made the following insert function which is working perfectly fine. But how can I bind a boolean in my insert function?

<?php

if(isset($_POST["headline"], $_POST["description"])) {
    $head = trim($_POST["headline"]);
    $desc = trim($_POST["description"]);


    $stmt = $mysqli->prepare("INSERT INTO stores (headline, description) VALUES (?, ?)");
                    // bind variable
                    $stmt->bind_param('ss', $head, $desc);   // bind

                    // execute prepared statement 
                if ($stmt->execute()) {
                    $success = true;
                }

                // close statement
                $stmt->close();

                // close connection
                $mysqli->close();   

if($success) {
    echo "Insert Succesfull";
} else {
    echo "Failed: " .  $stmt->error;
  }
}
?>

Like: `` and `` ? How would I bind that in my prepared statement? — McDuck4, Apr 17 '17 at 20:28
@McDuck4 For a boolean value, I recommend ENUM type of field to use in mysql, rather than tinyint — Оzgur, Apr 17 '17 at 20:28
there's no POST array for `openhours`, nor matching inputs for the other POST arrays. — Funk Forty Niner, Apr 17 '17 at 20:31
@SloanThrasher values aren't always required for `` would have worked. — Funk Forty Niner, Apr 17 '17 at 20:33
Don't think about it by the hard way. Boolean means only two values or indeed, one value and any other value. I have tried many ways to store boolean descriptive storage. such as CHAR(1) with default Y or N then in your script you may check for `if ($it == 'Y'){}else{}` — SaidbakR, Apr 17 '17 at 20:36

Оzgur · Accepted Answer · 2017-04-17T20:35:09.207

2

You should add these lines to your code.

Give options a value

<option value="1">Yes</option>
<option value="0">No</option>

To get posted value

$oh = isset($_POST["openhours"])&&$_POST["openhours"]=="1"?1:0;

Update query

"INSERT INTO stores (headline, description,openhours) VALUES (?, ?,?)"

Bind it

$stmt->bind_param('ssi', $head, $desc, $oh);

edited Apr 17 '17 at 20:35

answered Apr 17 '17 at 20:31

Оzgur

432
2
10

Thank you for the answer. Is that all to set my booelan? I thought it was more complicated. – McDuck4 Apr 17 '17 at 20:33
@McDuck4 yes, it should update your db accordingly. If any problems just comment me. If works accept as answer please. – Оzgur Apr 17 '17 at 20:37

score 1 · Answer 2 · edited May 23 '17 at 12:18

You can Use TINYINT(1). BOOLEAN are synonyms of TINYINT(1) in MySQL . Zero is false, anything else is true

check the MySQL manual for this : https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

this answer should give you the right explanation of your issue : Which MySQL data type to use for storing boolean values

lastly make sure to give values to your select options thanks !

save boolean in mysql

2 Answers2