Tinyint used for true/false

Question

I created a table person with two tinytint fields to decide the role of a person. The roles are instructor and faculty. A person can be one or both. I preferred to use tinyint since I can possibly set 1 to true and 0 to false. The question is how can I do so and submit the values into mysql table person. SITE

The 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`),
);

HTML

<?php

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

        //Need help here
}

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

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

</form>

Use either [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). Make sure you know what mysql injection is, and how you properly prevent it (see for example [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)). Please first try to get it to work yourself. If you still have a question after trying something, edit your question with what you tried so far. — Sumurai8, Nov 16 '13 at 07:03

score 0 · Answer 1 · answered Nov 16 '13 at 07:06

You need to check PDO for PHP. PDO is an object that allows you to compile MYSQL methods.

http://php.net/manual/en/book.pdo.php

After you construct your PDO object, you can prepare the query and then simply insert or update.

You can define the faculty or instructor by checking the radio buttons;

$var = 0;
$option = $_POST['role'];
if($option=='faculty')
   $var = 1
else
   $var = 0

and then simply insert with PDO or update

Sid · Answer 2 · 2013-12-03T09:56:51.947

0

Evalute the roles of the person, then you can make use of the following code:

$con=mysqli_connect("Mysql_host","DB_username","DB_password","DB_name");

$contact_role = 0;
$instructor_role = 0;
foreach($_POST['role'] as $role){
if($role == "faculty")
    $contact_role = 1;
if($role == "instructor")
    $instructor_role = 1;
}

$personName = mysqli_real_escape_string($_POST['person_name']);

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_query($con,"INSERT INTO person (contact_role, instructor_role, person_name) VALUES ('$contact_role', '$instructor_role', '$presonName')");

mysqli_close($con);

To make this more secure and protect against mysql injections, you can use prepared statements.

edited Dec 03 '13 at 09:56

answered Nov 16 '13 at 07:07

Sid

480
1
6
19

1

-1 You are telling him to put `$_POST['person_name']` directly into an SQL statement, knowing full well that it's a bad idea? How hard is it to to just use `mysqli_real_escape_string`? – Shea Nov 16 '13 at 07:11
That is why i have mentioned after that to make use of prepared statements. – Sid Nov 16 '13 at 07:12
It just don't sit right to have a novice programmer even think about using that. – Shea Nov 16 '13 at 07:15
1

I admit that i was wrong and i have fixed it now. – Sid Nov 16 '13 at 07:15
+1 yes, how do you make the evaluation? – Code_Ed_Student Nov 16 '13 at 07:23

score 0 · Accepted Answer · answered Nov 16 '13 at 07:39

First thing first:

You must not use same name for input. Also, as you are using checkbox group use array. You might use something like this:

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>

You need to use mysqli or mysql pdo to read/write to mysql database. Here is how database connection is done.

$connect = mysqli_connect("HOST","DB_USERNAME","DB_PASSWORD","DB_NAME");
if (mysqli_connect_errno()) {
    die("Unable to connect to database. Error: ". mysqli_connect_error());
}

Your table fields are tinyint where as your form values are string. You need to implement logic to get relative int values.

if(isset($_POST['submit'])){
    $person1_role1 = empty($_POST['role1'][0]) ? 0 : 1;
    $person1_role2 = empty($_POST['role1'][1]) ? 0 : 1;

    // Do the same for person2

    // grab other fields (person_name1, person_name2)

    // Query insert statement
}

Tinyint used for true/false

3 Answers3