Whats the best type of row!?

[matthew798]

Hey guys!

 

I have a system were an admin can create a user with certain permission. Now the PHP will check to see if these permissions are true or false and grant access to a page if said value is true!

 

What i want to know is

 

1: How dows a form transfer the state of a check box? (on-off, 0-1, yes-no???)

2: What is the best type of row to use for a simple checkbox in a mysql database (int, tinyint, ?set?, bool...)

3: What is the best way to achieve what i'm asking... If my way is just ridiculous, as it usually is, can someone point me in the right direction?

 

THANKS!!!

[JonnoTheDev]

Whatever you set the value attrib on the checkbox to is what is posted when checked. If not checked the post value is empty so:

 

checkbox name="x" value="1" />

 

If it is checked there will be a value of 1 in $_POST['x']. If not no value.

I would use an ENUM field type to store this data. You could have values of 1, 2 (I tend not to like using 0)

[matthew798]

great reply SUPER!!!

 

Only one thing. YOu said that if it isn't cecked there will be no value... Is that to say that there will be ABSOLUTELY no value and it will be NULL or there will just be a 0, or something...

 

Because i need to store the on-off state in a databse... Unless i could just say that if its on grant access else GFYS.. lol

 

 

[JonnoTheDev]

It will be an empty value so I would use an ENUM(1,2) 1 being off, and 2 being on. All you need is:

 

$value = strlen($_POST['checkboxName']) ? 2 : 1;

Replace 'checkboxName' with the name you give to the checkbox field. If the box is clicked the $value is set to 2. If not $value is set to 1. You can then use $value in your database insert query.

 

Simple.

[Zane]

It all depends on your development.....what way is best for YOU.

 

Me in particular though, if I have like umpteen billion checkbxes......I seem to have something against making upmteen billion columns in my database for each one..  So what I do is..

 

First of all, if the checkboxes are somewhat related...like you said, are permissions.  Then just create a column called permissions and I'd set the type to INT.  When you're reading back your checkboxes you need to have them set for a 1 or 0.

Now say you had 16 checkboxes...you would then have a 16-bit number to put into your permissions column

1111111111111111 -> meaning all of them where checked

 

Then when you're reading this info out of the database, you can use bitwise operators to read information about these permissions.

 

 

[matthew798]

very smart.... As for the post before, this is what i get when i try to make one of the rown an ENUM

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL' at line 1

 

So i tried deleting the row and creating a new one.... same thing...

[JonnoTheDev]

Yep zanus is correct. Used that method also. As stated use if you have many permission settings.

[matthew798]

ok, so 'ill drop the whole ENUM thing because it's likely i'll be adding more admin function, hence more permissions in the future so i imagine it's quicker and cleaner to use the "all-in-one-row" thingy. I'll ready into it, THANKS!!!!

[matthew798]

Yeah i don'y really understant the logic of bitwise operators...

 

I looked at your post and some stuff on google, but nothing really explains what a bitwise operator does, or at least nothing i can understand did... lol

 

I have a max of 6 permissions to set, would i be wasting my time by going with the ENUM idea? It seems a million times simpler. but i WOULD rather have someone explain the operators to me, always wanting to learn something new..

[JonnoTheDev]

If you use a varchar you can set the 6 permissions like this:

0,1,1,0,1,1

6 numbers - 0 is off, 1 is on.

 

Making them comma separated will be easiest.

 

Then when you get the record from the database you can create an array of the permissions using:

$permissions = explode(",", $databaseRow);

 

Using a print_r($permissions) will produce:

array(
[0] => 0,
[1] => 1,
[2] => 1,
[3] => 0,
[4] => 1,
[5] => 1,

}

And make it easy to test the values.

[matthew798]

<?php
$username = $_POST['username'];
$password = $_POST['password'];
$news = strlen($_POST['news']) ? 1 : 0;
$ticker = strlen($_POST['ticker']) ? 1 : 0;
$users = strlen($_POST['users']) ? 1 : 0;

mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

mysql_query("INSERT INTO users
(username, password, permission) VALUES('$username', '$password', '$news,$ticker,$users')");
?>

 

Is this correct?

[Zane]

you wouldn't be wasting your time with it, although when you wanted to add more permissions it could become a hassle...

 

it would just be a continuous script of IFs or just a really long switch statement of all the possible permissions.

 

it's just the same as the MySQL permissions almost...you can't give someone the GRANT permission without them also having the SELECT, INSERT, DELETE yada yada.....so you're switch would have to check for the "GRANT" permission....if it's checked then you've eliminated a lot of  checking, but what if they don't have the GRANT permission.......some permission have more precedence than others, like DELETE and UPDATE.

you can't give someone those without giving them SELECT and INSERT as well. 

 

If you have only 6 permissions then you only need 3 bits.  No it's not a 50% rule either.

 

64,32,16,8,4,2,1

0 ,0 ,0 ,0 ,0 ,0 ,0

 

6 is less than 8, so you'll only need the first three to make 6 permissions....because 111 is 7 permissions (you'll never use it if you only have 6)

but you will use 101....that's you're highest permission in a 6 permission world.

 

First you may need to order your checkboxes/permissions in order of  precedence so that if you happen to have a permission in your table 101

then you can assume they have all the other permissions as well.

 

you can also set up CONSTANTS to make things easier......

 

 

EDIT: if you're not a deadline, it's a fun project to play with.  But if you're in a hurry I'd say to go with what works easiest

[discomatt]

Why not store these numbers as an integer?

 

0,1,1,0,1,1 could be written as 011011 instead, which in binary is the integer 27. With some made up permission, bitwise works like this:

 

Basic user ( int 0, binary 000000 )

Can view private forum ( int 1, binary 000001 )

Can edit all posts ( int 2, binary 000010 )

Can delete all posts ( int 4, binary 000100 )

Can move posts ( int 8, binary 001000 )

Can create groups ( int 16, binary 010000 )

Can administrate forums ( int 32, binary 100000 )

 

Now, to give a certain user permission, you jsut add the integers and use bitwise operators to check if the values exist!

 

Take this script for example

<?php

$permissions['user1'] = 2+4+8; # user1 can edit delete and move posts
$permissions['user2'] = 1+2; # user2 can view private forum and edit posts

function deletePost( $perms ) {
if ( $perms & 4 ) # '4' is the integer required to delete posts
	return 'User is allowed to delete the post';
else
	return 'Permission denied';
}

# In action

echo "user1 attempt to delete: " . deletePost( $permissions['user1'] ) . "<br />";

echo "user2 attempt to delete: " . deletePost( $permissions['user2'] ) . "<br />";

?>

 

Et voila! Extremely customizable, can be stored in an int field ( small footprint ) and you don't need any conversions for the data to be usable!

 

[discomatt]

Or use zanus' approach, depending on how customizable you need the permissions to be

[matthew798]

<?php
session_start();
$username = 'matthew798';
include 'dbconnect.php';

$q = mysql_query("SELECT permission FROM users WHERE username = '$username'")
or die(mysql_error()); 

$permissions = explode(",", $q);

print_r($permissions);

if ( $permissions['0'] == 0 ){
die('You do not have permission to access this administrative function');
}
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<LINK REL=StyleSheet HREF="stylemain.css" TYPE="text/css" MEDIA=screen />
</head>

<body bgcolor="black">

<table border="0">
  <tr>
    <td><form action="newsprocess.php" method="POST">
      <span class='headline'>Title</span><br />
      <input name="title" type="text" class='textinputhead' />
<br /><br />
<span class='headline'>Body</span><br />
<textarea name="body" type="text" class='textinputbody' /></textarea><br /><br />
      <input type="submit" value='Post News' class='textinputsubmit'/>
    </form></td>

  </tr>
</table>

</body>
</html>

 

I keep getting this.

DatabaseArray ( [0] => Resource id #4 )

 

 

1: Now a few things i want you to look at, the IF statement. If the value IS NOT 0, then it should just continue with the rest of the page, right?

2: Why isnt it returning all the permission values in the array? (the value in the DB is "1,1,1", i checked)

3: All i have to do is session_start() and it loads all $_SESSION variables i had when they logged in right?