amending duplicate records on database Options

[johnm]

Hi

I have a kids game with a scoreboard. The child enters their name and it goes onto the database together with their score and the date. All entries are deleted after 7 days. The top 40 scores are sent back to the game to be displayed.
I now wish to stop one or more kids dominating the board with multiple entries and am trying to check the child name with existing entries on the database. If the name exists then i only want to display the highest score that child has achieved. I thought that I might check the new entry against those already on the database and if it is higher then I would adjust all other scores for that player to zero. I have the following code.

CODE

<?php
$table="x8tables";
$keeptime=(7*24*60*60);
$user="root";
$conn=mysql_connect("mysql13.hostexcellence.com","santon_public","myscore")or die("err:conn");

$self=$_SERVER['PHP_SELF'];
$pname=$_REQUEST['pname'];
$score=$_REQUEST['score'];

$rs=mysql_select_db("santon_scores",$conn)or die("err:db2");
$sql="select * from bannedNames where banned ='$pname'";

$rs=mysql_query($sql,$conn);

$num=mysql_num_rows($rs);
if($num !=0){$score=0;};

//$sql="select pname,score,tdate from $table WHERE pname =$pname";
//$rs= mysql_query($sql,$conn);
//$num= mysql_num_rows($rs);
//if($num>0){for($i=0;$i<$num;$i++){score=0}};
$sql="insert into $table (pname,score)values(\"$pname\",$score)";
$rs=mysql_query($sql,$conn);


$sql="select pname,score,tdate from $table order by score desc limit 40";
$rs= mysql_query($sql,$conn);

$num= mysql_num_rows($rs);
if($num >40){$num=40;};

    // FLASH DATA CREATED HERE
    for($i=0;$i<$num;$i++)
        
            {echo ("pname" . $i . "=");
            echo (mysql_result ($rs,$i,"pname"));
            echo ("&score" . $i . "=");
            echo (mysql_result ($rs,$i,"score"));
            echo ("&");
            
    }
    
$query="SELECT * FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";
$query="DELETE FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";

mysql_query($query);
mysql_close();
?>

In the middle you will see the 4 lines that I am attempting to use to check for duplicates and adjust the score to zero. However the result is that no records are displayed on the game. Any ideas an where I have made a mistake please?

thanks
john

[Brian Chandler]

Don't quite see the "4 lines" unless you mean the commented out ones... but:

//$sql="select pname,score,tdate from $table WHERE pname =$pname";
//$rs= mysql_query($sql,$conn);
//$num= mysql_num_rows($rs);
//if($num>0){for($i=0;$i<$num;$i++){score=0}};
$sql="insert into $table (pname,score)values(\"$pname\",$score)";
$rs=mysql_query($sql,$conn);

This line surely gives syntax error (for "score=0"):
//if($num>0){for($i=0;$i<$num;$i++){score=0}};

Even if it did something, like $score=0, this simply sets the value of $score to zero any number of times, which has exactly the same effect as setting it to zero once. Then you do one (1) db INSERT. This should +add+ one (1) record. Don't see where you're trying to delete duplicates.

[moo]

I'm not sure I fully understand your requirements, but assuming you want the top 40 scores (or rather, the top 40 scorers) simply change your query:

CODE

$sql="SELECT pname, max(score) AS score, tdate FROM $table GROUP BY pname ORDER BY score DESC LIMIT 40";

This will return the highest scores achieved by each of the top 40 scoring players. This way you wont need to blacklist anyone or delete anything.

[johnm]

Thanks Moo.
You have it spot on. Another trick gratefully learnt.

John

[johnm]

As a supplementary question, I would be grateful for another tip.

I have had a problem with rude names being input and shown on the scoreboard. I have created another table called bannedNames with a field called banned. I am attempting to check each entered name against the bannedNames list and if the player's entered name is on that list, then I want to make their score zero so that it will not be shown on the high score list.
I have tried adding the following code without success.

CODE

$rs="SELECT * FROM $bannedNames WHERE $pname==banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};

What is the problem please?

cheers
john

[Brian Chandler]

I have tried adding the following code without success.

CODE

$rs="SELECT * FROM $bannedNames WHERE $pname==banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};

What is the problem please?

The problem is that you change the value of a variable in you php program, hoping that this will magically change what's written in the database. It won't.

At least, I think this is the problem. More basic problem is that programming is not like making soup - it's no good saying "I added a splash of basil, which really pepped up the taste". Programming is more like writing a recipe book - so if you tell us "I added the word *vigorously* to the recipe", we haven't a clue what difference this would make, because it depends precisely where you added the word.

$rs="SELECT * FROM $bannedNames WHERE $pname==banned";

You're also muddling up PHP syntax (uses '==' for equality, 1980s style) with SQL syntax (uses '=', 1970s style).

It helps to debug by printing the SQL query that you send - perhaps you will then notice that this "$pname = banned" doesn't mean anything.

[johnm]

hi

Thanks for the response. I should have posted the entire code. It is

CODE

<?php
$table="x8tables";
$keeptime=(7*24*60*60);
$user="root";
$conn=mysql_connect("mysql13.hostexcellence.com","santon_public","myscore")or die("err:conn");

$self=$_SERVER['PHP_SELF'];
$pname=$_REQUEST['pname'];
$score=$_REQUEST['score'];

$rs=mysql_select_db("santon_scores",$conn)or die("err:db2");
$rs="SELECT * FROM $bannedNames WHERE $pname=banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};

$rs=mysql_query($sql,$conn);

$num=mysql_num_rows($rs);
if($num !=0){$score=0;};

//$sql="select pname,score,tdate from $table WHERE pname =$pname";
//$rs= mysql_query($sql,$conn);
//$num= mysql_num_rows($rs);
//if($num>0){for($i=0;$i<$num;$i++){score=0}};
$sql="insert into $table (pname,score)values(\"$pname\",$score)";
$rs=mysql_query($sql,$conn);


$sql="select pname,score,tdate from $table order by score desc limit 40";
$rs= mysql_query($sql,$conn);

$num= mysql_num_rows($rs);
if($num >40){$num=40;};

    // FLASH DATA CREATED HERE
    for($i=0;$i<$num;$i++)
        
            {echo ("pname" . $i . "=");
            echo (mysql_result ($rs,$i,"pname"));
            echo ("&score" . $i . "=");
            echo (mysql_result ($rs,$i,"score"));
            echo ("&");
            
    }
    
$query="SELECT * FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";
$query="DELETE FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";

mysql_query($query);
mysql_close();
?>

I understand your comment that changing the variable in the php will not change the database, but I am not clear as to how I would effect that.


cheers
john

[Brian Chandler]

I'm sorry, but it seems to me the whole thing is hopelessly muddled. Here's what looks like an attempt to do something about banned names:

$rs="SELECT * FROM $bannedNames WHERE $pname=banned";

OK, this sets the PHP variable $rs to a (slightly invalid) SQL request. That's all. You never issue the request to the DB server (with mysql_query() ) or you would get an error message.

But how did you contrive this SQL request? What is it supposed to do?

$bannedNames is a PHP variable which has no value. Do you have a table of banned values? What is it called? What are the fields in it? You need to access it, to see if the name you are dealing with ($pname) is in the table (after deciding what format the table is in).

Your program also includes almost no comments. Writing comments - "What does this bit do..." - means not only that other people have some chance of understanding what's going on, but also that you will be able to yourself in 6 months time.

HTH
And so on. I don't think it's a good idea to write this

[johnm]

I am making a pigs ear of this.
I have a table called "bannedNames" with a field called banned which contains naughtry names.
I can see now that I need a line of code at the beginning such as

CODE

$bannedNames="bannedNames";

Here is may revised attempt with comments. I think that i am totally out of my depth on this one.

CODE

<?php
$table="x8tables";
$banned="bannedNames";
$keeptime=(7*24*60*60);
$user="root";
$conn=mysql_connect("mysql13.hostexcellence.com","santon_public","myscore")or die("err:conn");

$self=$_SERVER['PHP_SELF'];
$pname=$_REQUEST['pname'];
$score=$_REQUEST['score'];

$rs=mysql_select_db("santon_scores",$conn)or die("err:db2");
// below I want to see if the name the player entered is on my list of banned names.  The table banned names has a
// field called banned.  If the player's name is on that list then i wish to alter their score to zero before
// putting their name on the database.  This way it will not show in the high score list.
$rs= mysql_query($sql,$conn);
$rs="SELECT * FROM $bannedNames WHERE $pname=banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};
//the name and score is entered on the database
$rs= mysql_query($sql,$conn);
$sql="insert into $table (pname,score)values(\"$pname\",$score)";

//gets top scorers from 8xtables to display on game
$rs= mysql_query($sql,$conn);
$sql="SELECT pname, max(score) AS score, tdate FROM $table GROUP BY pname ORDER BY score DESC LIMIT 40";

$rs= mysql_query($sql,$conn);
$num= mysql_num_rows($rs);


    // FLASH DATA CREATED HERE
    for($i=0;$i<$num;$i++)
        
            {echo ("pname" . $i . "=");
            echo (mysql_result ($rs,$i,"pname"));
            echo ("&score" . $i . "=");
            echo (mysql_result ($rs,$i,"score"));
            echo ("&");
            
    }
    
$query="SELECT * FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";
$query="DELETE FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";

mysql_query($query);
mysql_close();
?>

Am I on the right track?

cheers
john

[Brian Chandler]

I am making a pigs ear of this.

Yes and no. What you are trying to do is in a sense Difficult, because it's writing a program, which may well be unlike anything else you've ever done. On the other hand it isn't really _that_ difficult.

I have a table called "bannedNames" with a field called banned which contains naughtry names.
I can see now that I need a line of code at the beginning such as

CODE

$bannedNames="bannedNames";

No particular need. You can just write the string "bannedNames" when you need it - no point in having a variable with exactly the same name.

Here is may revised attempt with comments. I think that i am totally out of my depth on this one.

CODE

<?php
$table="x8tables";
$banned="bannedNames";
$keeptime=(7*24*60*60);
$user="root";
$conn=mysql_connect("mysql13.hostexcellence.com","santon_public","myscore")or die("err:conn");

$self=$_SERVER['PHP_SELF'];
$pname=$_REQUEST['pname'];
$score=$_REQUEST['score'];

$rs=mysql_select_db("santon_scores",$conn)or die("err:db2");
// below I want to see if the name the player entered is on my list of banned names.  The table banned names has a
// field called banned.  If the player's name is on that list then i wish to alter their score to zero before
// putting their name on the database.  This way it will not show in the high score list.

Now it's really easy to see what you're doing....

Surely, if a name is "banned", you want to throw it away, not add it with a zero score?

CODE

$rs= mysql_query($sql,$conn);
$rs="SELECT * FROM $bannedNames WHERE $pname=banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};

You still have an invalid bit of SQL, and you still haven't issued the call to the db server. So you aren't getting any information from the "banned" table.

Add echo "<p>SQL: $rs</p>"; before calling mysql_query, so you can see what the SQL error message is about.

CODE

//the name and score is entered on the database

I think programmer comments are neater and clearer if you use the non-finite dictionary form of the verb (or "to-less" infinitive). "Enter the name and score..." The passive makes it unclear if you mean this is what you are doing now (yes?) or that the name and score have already been entered.

CODE

$rs= mysql_query($sql,$conn);
$sql="insert into $table (pname,score)values(\"$pname\",$score)";

//gets top scorers from 8xtables to display on game
$rs= mysql_query($sql,$conn);
$sql="SELECT pname, max(score) AS score, tdate FROM $table GROUP BY pname ORDER BY score DESC LIMIT 40";

$rs= mysql_query($sql,$conn);
$num= mysql_num_rows($rs);


    // FLASH DATA CREATED HERE

Mysterious - what is "FLASH" here?

CODE

    for($i=0;$i<$num;$i++)
        
            {echo ("pname" . $i . "=");
            echo (mysql_result ($rs,$i,"pname"));
            echo ("&score" . $i . "=");
            echo (mysql_result ($rs,$i,"score"));
            echo ("&");
            
    }
    
$query="SELECT * FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";
$query="DELETE FROM $table WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(tdate) > $keeptime";

mysql_query($query);
mysql_close();
?>

Am I on the right track?

Yes, basically. Need to plod a lot more though... <g>

This post has been edited by Brian Chandler: Jan 19 2008, 09:37 AM

[Brian Chandler]

QUOTE
CODE


$rs= mysql_query($sql,$conn);
$rs="SELECT * FROM $bannedNames WHERE $pname=banned";
$num= mysql_num_rows($rs);
if($num>0){$score=0};

You still have an invalid bit of SQL, and you still haven't issued the call to the db server. So you aren't getting any information from the "banned" table.

Sorry, my confusion: you _have_ issued a call to the database, but with an empty value ($sql) as the query. _Then_ you have set something else to the sql query.

Look, here's how it works. Read the php manual entry for mysql_query (at http://php.net); it tells you that the first argument is
string $query

This means that when you call mysql_query the first argument must be the string which is the SQL command you want to run. Precisely. It's no good writing some variable name, and somewhere (in the soup!) making a note that the php interpreter might guess is the query you meant to put somewhere else. In a sense this is very unforgiving, e.g. if you're used to giving instructions to a good secretary, but it's also the only way it could ever be 100% reliable.

I could rewrite these three lines for you, but it wouldn't help for next time. Remember, give a woman a bicycle, and she'll just ride off, but give her a man, and he'll cook her a fish. (Was it? No, no, light a man a fire, and he'll be warm for the rest of the day. Set a man on fire, and he'll be warm for the rest of his life.) Well, anyway...

[moo]

Woah there! All this can be done within the mysql query - don't bother replacing scores and whatnot, just retrieve scores where the name is not on the bannedNames table. Something like:

CODE

SELECT pname, max(score) AS score, tdate, (SELECT count(*) FROM $bannedNames WHERE banned = pname) AS banned FROM $table WHERE banned < 1 GROUP BY pname ORDER BY score DESC LIMIT 40

[Brian Chandler]

Woah there! All this can be done within the mysql query - don't bother replacing scores and whatnot, just retrieve scores where the name is not on the bannedNames table. Something like:

CODE

SELECT pname, max(score) AS score, tdate, (SELECT count(*) FROM $bannedNames WHERE banned = pname) AS banned FROM $table WHERE banned < 1 GROUP BY pname ORDER BY score DESC LIMIT 40

Yes, but it's surely simpler and more sensible not to add the banned name to the database in the first place.

I guess this script handles someone playing the game and giving their username. In which case, if someone chooses a banned name, you want to respond differently and reject the name ("Sorry this username is not available"). You don't want to appear to accept it, then make it silently disappear.

Much of this "banned name" stuff is oversimplistic nonsense anyway. I've seen cases where if you write the word "fifteenth" it gets corrupted into "fifIbiblioth" (?) by some piece of stupid "programming".

[moo]

Yes, but it's surely simpler and more sensible not to add the banned name to the database in the first place.

I totally agree. Perhaps the best solution would be to clean up the existing data to remove records with disallowed names. Then do as you suggest and stop banned names being entered in to the database in the first place.

Alternatively, johnm should ditch the idea of checking usernames for rudewords - someone will always get around name checks with strings that are not yet banned or fail to meet any criteria for a automated checker function. Instead put a disclaimer that says "Rude or defamatory entries will be deleted" and periodically check the scoreboard (maybe add a 'report bad entries' facility) and manually delete bad entries.

This post has been edited by moo: Jan 21 2008, 05:44 AM

[johnm]

Hi

thanks for the input. The game is a flash game and the data is ouput to the game, hence "FLASH DATA CREATED HERE".
I did not want to disqualify names as they were input as it gives the player the opportunity to try lots of rude names until he finds one that is not on the database. If he inputs a rude name and it just fails to appear then he will need to play the game again to have another go at a rude name.
The reason to allow the score onto the database but adjust it to zero is that I look at the number of entries on the database to establish how many times the game has been played.

I liked moo's suggestion of using

CODE

SELECT pname, max(score) AS score, tdate, (SELECT count(*) FROM $bannedNames WHERE banned = pname) AS banned FROM $table WHERE banned < 1 GROUP BY pname ORDER BY score DESC LIMIT 40

However it does not seem to produce any output. I have messed around and come up with

CODE

$banned="bannedNames";
$sql="SELECT pname, max(score) AS score, tdate, WHERE pname != banned  FROM $banned ORDER BY score DESC LIMIT 40";
$rs= mysql_query($sql,$conn);

This also does not work. Is there a syntax error please?
thanks again for your help
john

This post has been edited by johnm: Jan 21 2008, 09:35 AM

[moo]

Its difficult to error check without access to the database but the following should work:

CODE

SELECT pname, max(score) AS score, tdate FROM $table LEFT JOIN $bannedNames ON pname = banned WHERE bannedName IS NULL GROUP BY pname ORDER BY score DESC LIMIT 40

[johnm]

thanks for your prompt help.moo. I had to slightly modify the code to get output. My final code is

CODE

$sql="SELECT pname, max(score) AS score, tdate FROM $table LEFT JOIN $bannedNames ON pname = banned WHERE banned IS NULL GROUP BY pname ORDER BY score DESC LIMIT 40";

thanks again
John

[moo]

thanks for your prompt help.moo. I had to slightly modify the code to get output. My final code is

CODE

$sql="SELECT pname, max(score) AS score, tdate FROM $table LEFT JOIN $bannedNames ON pname = banned WHERE banned IS NULL GROUP BY pname ORDER BY score DESC LIMIT 40";

thanks again
John

Duh, silly me. Glad it worked for you though.

[johnm]

just one final query. If I write

CODE

$bannedNames="bannedNames";
$sql="SELECT pname, max(score) AS score, tdate FROM $table LEFT JOIN $bannedNames ON pname = banned WHERE banned IS NULL GROUP BY pname ORDER BY score  LIMIT 40";

then it works but if I try removing the first line and use

CODE

$sql="SELECT pname, max(score) AS score, tdate FROM $table LEFT JOIN "bannedNames" ON pname = banned WHERE banned IS NULL GROUP BY pname ORDER BY score LIMIT 40";

then it doesn't. It appears that I cannot replace $bannedNames with "bannedNames".
What is the correct syntax please?

[johnm]

ive worked it out. It is just bannedNames with no inverted commas.

cheers
john