Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts

    Checking id in database for match

    So I have a banlist where you put in the user id of the person you wish to ban.

    In the database it goes like this

    Code:
    166,52,102,240,etc
    The id of each user that is banned. Now say someone wishes to join but their id is banned. How would I check the database to see if their id is in there?

    I cannot use == because it would check the whole number, and the rest of the operators won't work either. Do I have a foreach function?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You could normalize the table to include a single record for each id that has been banned. This allows a simple select to determine if it exists. Otherwise a foreach will do it for you, if you explode the result (I assumed that the result would be 166,52,102,240) on a comma, you can simply use an in_array function to determine if they are there. SQL should have a split function as well that will allow you to separate each one and use an IN for determining its existence.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Sounds very complex to do, how do you normalize a table for single entries? Because it's setup to be banned from one script. If you go to that page is where your banned from getting to.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Like a page-by-page ban? If you want to normalize that you'll need 3 tables to do it, one that tracks a page, one that tracks bans, and one that flattens them together. Since you have a userid field, I assume that you have a user table, so you can just use that.

    banScripts
    ID | URL
    1 | script.php
    2 | controlpanel.php

    bannedMembers
    scriptID | userID | reason
    1 | 166 | Attempted Access
    1 | 52 | Spamming
    1 | 102 | Flooding
    1 | 240 | ...
    2 | 52 | ...

    For example. BannedMembers would simply use a combination key between the scriptID and the userID. This is called flattening between many-to-many relationships. Then a query like so:
    Code:
    SELECT reason FROM bannedMembers INNER JOIN banScripts WHERE userID = youruserid AND URL = $_SERVER['SCRIPT_NAME'].
    If the query pulls up a result, it means this user has been banned from this script.

    As for normalization, generally you want you're database as normalized as possible to create clean and easy to write queries, prevent duplicate data, and prevent insertion, modification and deletion anomalies. Sometimes you need to weigh out normalization to un-normalized - 1 table that takes a horrible looking query to access data is probably better than 50 tables representing that data from a speed point of view. Just avoid * in that case
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    I already banned id's and reason in a table. But the page has an ID, so I don't think this is possible. So what I need to do is setup a new table with the following fields,

    scriptid
    bannedusers
    reason

    and update that table each time a person is banned, and then when doing a check, check user id in the bannedusers list to see if it pulls a match.

    Does that sound about right? or am I like totally far away?

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yep that sounds about right. I've never done a script-by-script ban before, but its conceptually the same thing as a standard ban, just a little more complex.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    masterofollies (07-17-2008)

  • #7
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Yeah it's unique but it's a good way for me to learn stuff better. Sorry for being such a noobie here, I am trying my best to learn as much by myself as possible.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    No problem, everyone starts somewhere. I can't stress enough a good database design - I've gotten caught many times with a poor design that has led to nothing but anguish .
    I found an article here: http://databases.about.com/od/specif...malization.htm. Didn't go over it all, but it covers a lot of the basics which is a great starting point!
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #9
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Could be useful. Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •