View Full Version : Checking id in database for match
07-17-2008, 02:22 AM
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
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?
07-17-2008, 02:27 AM
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.
07-17-2008, 02:45 AM
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.
07-17-2008, 04:25 AM
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.
ID | URL
1 | script.php
2 | controlpanel.php
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:
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 ;)
07-17-2008, 04:59 AM
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,
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?
07-17-2008, 05:04 AM
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.
07-17-2008, 06:12 AM
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.
07-17-2008, 07:28 AM
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/specificproducts/a/normalization.htm. Didn't go over it all, but it covers a lot of the basics which is a great starting point!
07-17-2008, 02:56 PM
Could be useful. Thanks
Powered by vBulletin® Version 4.2.2 Copyright © 2015 vBulletin Solutions, Inc. All rights reserved.