Jump to content


Photo

multiple numbers in mysql column


  • Please log in to reply
6 replies to this topic

#1 ibda12u

ibda12u
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 06 March 2006 - 09:59 PM

I have a question. I am using a php login script that checks an access level in a table. This access level is just a number in a column called level in the table.

What I am wondering is, is there a way to input more than 1 number in the level column? IF so, is there something I can insert in between them, so that sql can tell that it's not just 1 number, but seperate numbers?



#2 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 06 March 2006 - 10:01 PM

You could probably do something like seperate them with comma's (make sure the table type is set to text) then use the explode() function in PHP to put them in an array then use the in_array() function with an if statement?

Not sure if that's the direction you're wanting to go or not.

#3 ibda12u

ibda12u
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 06 March 2006 - 10:13 PM

[!--quoteo(post=352258:date=Mar 6 2006, 04:01 PM:name=Gaia)--][div class=\'quotetop\']QUOTE(Gaia @ Mar 6 2006, 04:01 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
You could probably do something like seperate them with comma's (make sure the table type is set to text) then use the explode() function in PHP to put them in an array then use the in_array() function with an if statement?

Not sure if that's the direction you're wanting to go or not.
[/quote]

I think that's the direction I'm looking for. I'm not super strong with explode and in_array yet (something to read up on. But I guess I'd like my code to at all the numbers in the array, and then allow access based on that.
Ex. suppose a page has an access lvl requiring 12.
My user has a 1,12,34 all in his level field.
So I could explode that data into an array. Then have my access script check to see if 12 is in the array?

#4 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 06 March 2006 - 10:19 PM

[!--quoteo(post=352271:date=Mar 6 2006, 05:13 PM:name=ibda12u)--][div class=\'quotetop\']QUOTE(ibda12u @ Mar 6 2006, 05:13 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I think that's the direction I'm looking for. I'm not super strong with explode and in_array yet (something to read up on. But I guess I'd like my code to at all the numbers in the array, and then allow access based on that.
Ex. suppose a page has an access lvl requiring 12.
My user has a 1,12,34 all in his level field.
So I could explode that data into an array. Then have my access script check to see if 12 is in the array?
[/quote]

That would work.. Of course, you lose some SQL functionality this way as the database will have no idea that there are multiple values in that column.

The proper "SQL way" to do this is to create an additional table with the mappings.
--
[a href=\"http://blog.godshell.com\" target=\"_blank\"]XenoPhage[/a]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Something mysterious is formed, born in the silent void. Waiting alone and unmoving, it is at once still and yet in constant motion. It is the source of all programs. I do not know its name, so I will call it the Tao of Programming.[/quote]

#5 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 06 March 2006 - 10:19 PM

Exactly.

When you retrieve the data, it would be like 1,12,34...when you explode it, it will end up in an array.

$foo = array (
    [0] => 1,
    [1] => 12,
    [2] => 34
);

Then with the in_array function you just wrap an if statement around it.

if ( in_array('1',$foo) ) {
    echo '1 was found in the array';
} else {
    echo '1 was not found in the array';
}

Hope that helps some :).

EDIT: And yea, as Xeno said, you could create a new MySQL table listing the permissions and do it from a MySQL perspective.

#6 ibda12u

ibda12u
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 06 March 2006 - 10:24 PM

[!--quoteo(post=352280:date=Mar 6 2006, 04:19 PM:name=Gaia)--][div class=\'quotetop\']QUOTE(Gaia @ Mar 6 2006, 04:19 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Exactly.

When you retrieve the data, it would be like 1,12,34...when you explode it, it will end up in an array.

$foo = array (
    [0] => 1,
    [1] => 12,
    [2] => 34
);

Then with the in_array function you just wrap an if statement around it.

if ( in_array('1',$foo) ) {
    echo '1 was found in the array';
} else {
    echo '1 was not found in the array';
}

Hope that helps some :).

EDIT: And yea, as Xeno said, you could create a new MySQL table listing the permissions and do it from a MySQL perspective.
[/quote]

Hmm.. I'm guesing that'd be a table with a access level, an id, and an foreign key back to the original table?

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 06 March 2006 - 10:31 PM

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Hmm.. I'm guesing that'd be a table with a access level, an id, and an foreign key back to the original table?[/quote]

Correct. Another table is the way to go. Comma separated columns are bad. Bad!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users