Storing tinyint values in mysql

Question

I have two input fields where a user can enter a name of a person and then choose their role, faculty or instructor. For the roles I am using tinyint to determine whether it is true or false (0 or 1). I am having difficulties saving the values to mysql db. When submitting the query nothing is being stored. EXAMPLE

<?php

if(isset($_POST['submit'])){

$con = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);


$person1_role1 = empty($_POST['role1'][1]) ? 0 : 1;

$person2_role2 = empty($_POST['role2'][1]) ? 0 : 1;

$person_name1 = $_POST['person_name1'];
$person_name2 = $_POST['person_name2'];

$option = $_POST['role'];

if($option == 'faculty'){
    mysqli_query($con,"INSERT INTO person (contact_role, person_name) VALUES ($person1_role1, $person_name1)");
} elseif ($option == 'instructor') {
    mysqli_query($con,"INSERT INTO person (instructor_role, person_name) VALUES ($person2_role2, $person_name2)");
}

print_r($_POST);

}

?>
<form action="test2.php" method="POST">
<b>Select the role for the person</b>
</br>
</br>
Name:<input type="text" name="person_name1">
     <input type="checkbox" name="role1[]" value="faculty">faculty
     <input type="checkbox" name="role1[]" value="instructor">instructor<br><br>
Name:<input type="text" name="person_name2">
     <input type="checkbox" name="role2[]" value="faculty">faculty
     <input type="checkbox" name="role2[]" value="instructor">instructor<br><br>

<input value="SAVE" name="submit" type="submit">

</form>

Table

CREATE TABLE IF NOT EXISTS `person` (
  `person_id` int(11) NOT NULL auto_increment,
  `faculty_role` tinyint(1) NOT NULL,
  `instructor_role` tinyint(1) NOT NULL,
  `person_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`person_id`),
);

Why are you using checkboxes if a person can have only one role? Why not a <select> or <input type="radio" />. And you're using $_POST['role'] but as you can see in your print_r($_POST) that key doesn't exists. — Peter van der Wal
– Peter van der Wal, Commented Nov 16, 2013 at 18:28
@davidstrachan can it be 1, 1 if the person is both faculty or instructor? — Code_Ed_Student
– Code_Ed_Student, Commented Nov 16, 2013 at 18:41

Peter van der Wal · Accepted Answer · 2013-11-17 11:02:02Z

2

You use $option = $_POST['role'] but 'role' isn't in your POST-data, see your print_r($_POST). So $option is neither 'faculty' nor 'instructor' so none of your INSERT-queries is being called.

You say a person can have both roles, but you use two INSERT-queries. Thats odd. Following would make more sense:

$person1_faculty = in_array($_POST['role1'], 'faculty') ? 1 : 0;
$person1_instructor = in_array($_POST['role1'], 'instructor') ? 1 : 0;
$person1_name = mysqli_real_escape_string($_POST['person_name1']);
mysqli_query(
    $con,
    "INSERT INTO person (faculty_role, instructor_role, person_name) VALUES " .
    "(" . $person1_faculty . ", " . $person1_instructor . ", '" . $person1_name . "')"
);

Your $_POST['role1'] is a array, check with in_array if faculty and/or instructor are in it. And use mysqli_real_escape_string to prevent SQL-injection.

edited Nov 17, 2013 at 11:02

answered Nov 16, 2013 at 18:31

Peter van der Wal

12k2 gold badges24 silver badges29 bronze badges

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

4 Comments

Code_Ed_Student Over a year ago

+1 Oh Alright I see, How can I make $option = $_POST['role'] part of the post?

Peter van der Wal Over a year ago

Check edited answer. You don't add 'role' to your $_POST-data but you should rebuild your PHP-logic. Besides that, start with adding a single user, with a single role (I think that's already difficult enough for you), if that works start expanding your code. Take small steps ;)

Code_Ed_Student Over a year ago

ALright, I made the changes but nothing is being inserted. Yes, you are right, once i get this working I will need to go back and work on my php logic.

Peter van der Wal Over a year ago

Sorry, my fault. Got a small error in building the query (happens when you're used to an different db-technique at work). Change ", " . $person1_name . ")" to ", '" . $person1_name . "')".

Collectives™ on Stack Overflow

Storing tinyint values in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related