Check a column for 4 or more of the same value.


#1

Could someone help me with some sample syntax of how i would check a column for 4 or more of the same value?

It’s to stop someone who has invited 3 people to the site and is trying to invite a 4th + member. Here’s some of my error checking in the script.

[php]$sql_logincount_check = mysql_query(“SELECT userid FROM inviter
WHERE userid=’$userid’”);//I think the $userid would probbly have to be replaced with something that makes it check for 4 or more of the same userid (a user id can only show up 3 times, so 4 or more would be not alloud)

$logincount_check = mysql_num_rows($sql_logincount_check);

if(($logincount_check > 0)){
echo "Please fix the following errors:
";
if($logincount_check > 0){
echo “You may NOT invite more than 3 members!
”;
unset($newmemberemail);
}
include ‘login_form.html’; // Show the form again!
exit();
} [/php]

So how would i be able to get the $userid in the first line to check for 4 or more of the same thing?


#2

I would use a query like
SELECT COUNT(userid) FROM inviter WHERE userid = ‘$userid’


#3

i would do this:
[size=99px]SELECT COUNT(userid) AS count FROM inviter WHERE userid = ‘$userid’[/size]

this will make the count of user id actually appear as a column in the table. in other words, if you use mysql_fetch_object, you can output the data like this:

[php]
$querystring = “SELECT COUNT (userid) AS count FROM inviter WHERE userid = ‘$userid’”;
$query = mysql_query( $querystring );

while( $queryobject = mysql_fetch_object( $query ) ) {
if( $queryobject->count = 3 ) {
print “You can’t invite any more friends you loser.”;
} else {
$invitesleft = 3-$queryobject->count;
print “You can invite “.$invitesleft.” more people.”;
};
};
[/php]


#4

Thanks gogglecollector and peg110 I’m going to give your sample script a try, does that require there to be a column in the database for the number of times a user logged in? going to test it anyway.

Something else i’m wondering… I have a line of code like this:

[php]$sql_secaccount_check = mysql_query(“SELECT userid FROM login
WHERE userid=’*2’”);[/php]

userid=’*2’" i know if wrong, what i’m trying to do is have it find any user id with the number 2 at the end. What would go in ‘*2’ to make that work?


#5

write the query like the following:

[php]
$sql_secaccount_check = mysql_query(“SELECT userid FROM login
WHERE userid LIKE ‘%2’”);?>
[/php]

I think that is it, correct me if I am wrong.


#6

This depends on what you store. Both examples given by me (and gogglecollector) assume that you are making a database entry for each invite. Presumably with some other Unique index. For example for each invite you might do an INSERT statement that includes the Userid of inviter, Userid of Invitee, Date and Time, etc…

Thus if you do a query to check the count of USERID and it’s 3 (should never be more) then you can echo some error message "Too many invites. " if it’s less than 3 then you can do another Insert of the Invited.

Try changing the * to a % . In MySQL the percent sign (%) is the wildcard (like * in MS SQL Server 2000) also a Single Character Wild Card is the Undersoore _ symbol (again as opposed to MS SQL Server 2000’s Question Mark )


#7

Good news: dosen’t allow me to login with SatanCow2
Bad News: dosen’t allow me to login with Satancow

it triggers the script either way.
Here’s what my code looks like, any idea what i did wrong?

[php]/* Let’s do some checking and ensure that the user’s not using his second account to invite members. */

$sql_secaccount_check = mysql_query(“SELECT userid FROM login WHERE userid LIKE ‘%2’”);

$secaccount_check = mysql_num_rows($sql_secaccount_check);

if(($secaccount_check > 0)){
echo "Please fix the following errors:
";
if($secaccount_check > 0){
echo “You MAY NOT invite users using your second account!
”;
unset($userid);
}
include ‘login_form.html’; // Show the form again!
exit(); // exit the script so that we do not create this account!
}[/php]


#8

Look at your first if statement. I am not sure if this has anything to do with it, but you have (($secaccount_check > 0)) when it should look like
[php]

<? if($secaccount_check >0){ //CODE HERE } [/php] Not sure if that helps anything, but couldn't hurt to try.

#9

I removed the extra ) on either side but still the samething, it always returns a value of true. Is there another way to write %2? Mabey an IF statement? can a variable = and IF statement?

either way it’s picking up %2 as both SatanCow and SatanCow2


#10

Ok, looking through the code…Maybe I am not fully understanding what is going on, but the way I am reading this is your are going out to the database and looking to see if there is a second account name SatanCow2… Now if there is an account you send them an error. So techinically the code is working how it should. Is it not?


#11

yes exactly, and it is working how it should, but both SatanCow (which should be alloud and SatanCow2 (which should not be alloud) trigger the script to bring up the error.

The script SHOULD pick up SatanCow2 but NOT SatanCow.

The most probable cause is that my dumb monkey ass screwed something up HAHA.

the worst part is this is the last of 2 security features needed to finish the script I have been working on.


#12

Ok i think i figured out an easier way to do what i want to do. I need an IF statement that will check the varible $userid for the number 2.

if anyone know what the syntax would look like to find out if a varible contains a certain varible then that would help.


#13

I beleive the problem is that the SQL is returning any userid with a 2 on it. Shouldn’t it be

$sql = “SELECT userid FROM table WHERE userid LIKE (CONCAT($firstUserid,2));”;
echo $sql

I beleive this should search the database for a user id that consists of the first user id concatinated to 2. don’t know if that made any sense at all… :)

Reference:
http://dev.mysql.com/doc/mysql/en/String_functions.html


#14

I realize now that i don’t need to use mysql for this function at all, all i need to so is figure out how to write an IF statement that will see if the user submitted value for $userid contains a 2 at the end of it.

Only problem is since I don’t know PHP well at all searching the php manual is really hard. So again any sample code I can take a look at would help SO much.


#15

at http://www.codewalkers.com in the tutorial/basics section there are some really good tuorials for the beginner. You might want to check out “A Tour of Decision Making Structures in PHP”. For basic database stuff check out “Create dynamic sites with PHP & MySQL” (Just skip the software installation parts).


#16

I would look into strpos().

http://www.php.net/strpos There you will find out to use it.