SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy SQL Database Woes - Violation of Unique Key Constraint!

    Hey there. Trying to run a php script to create a new user account row in the Login table of a db & to create an associated "player" for that login in the PLayer table of a db.

    code for the script as follows:

    PHP Code:
    $msconnect=mssql_connect("$dbhost","$dbuser","$dbpasswd");
    $msdb=mssql_select_db("kal_auth",$msconnect);


    mssql_query("INSERT INTO Login ([ID], [PWD], [Birth], [Type], [ExpTime]) VALUES('$id',$pw,'19190101','0','4000')");

    $uid mssql_result(mssql_query("SELECT [UID] from Login WHERE [ID]='$id'"),0,0);

    mssql_close() or die('failed closing mssql');

    echo 
    "Successfully created ID: $id and PW: $pass";

    // DEFINE CLASS VARIABLES
    if($char == 0){ //knight
    $class '0'$strength '18'$health '16'$Intelligence '8'$wisdom '8'
    $dexterity '10'$curhp '183'$curmp '165'$wepid '1'

    } elseif(
    $char == 1){ //mage
    $class '1'$strength '8'$health '10'$Intelligence '18'$wisdom '16'
    $dexterity '8'$curhp '148'$curmp '215'$wepid '90';

    } elseif(
    $char == 2){ //archer
    $class '2'$strength '14'$health '10'$Intelligence '8'$wisdom '10';
    $dexterity '18'$curhp '147'$curmp '174'$wepid '22';
    }

    $msconnect=mssql_connect("$dbhost","$dbuser","$dbpasswd");
    $msdb=mssql_select_db("kal_db",$msconnect);
    // CREATE PLAYER
    mssql_query("INSERT INTO Player ([UID], [Admin], [Name], [Class], [Specialty], [Level], [Contribute], [Exp], [GID], [GRole], [Strength], [Health], [Intelligence], [Wisdom], [Dexterity], [CurHP], [CurMP], [PUPoint], [SUPoint], [Killed], [Map], [X], [Y], [Z], [Face], [Hair], [RevivalId], [Rage])
    VALUES('
    $uid','0','$charname','$class','1','1','0','0','0','0','$strength','$health','$intelligence','$wisdom','$dexterity','$curhp','$curmp','5','0','0','0','257304','259299','16131','0','0','0','0')"); 
    My problem is everytime I run it i get error:

    Warning: mssql_query() [function.mssql-query]: message: Violation of UNIQUE KEY constraint 'IX_Login'. Cannot insert duplicate key in object 'Login'. (severity 14) in C:\xampp\htdocs\sal\reg.php on line 93

    Also, it inserts the player TWICE in player table :/

    Line 93 being: mssql_query("INSERT INTO Login ([ID], [PWD], [Birth], [Type], [ExpTime]) VALUES('$id',$pw,'19190101','0','4000')");

    Here are the structures for the Tables Login & Player

    CREATE TABLE [dbo].[Login] (
    [UID] [int] IDENTITY (1, 1) NOT NULL ,
    [ID] [varchar] (12) COLLATE Korean_Wansung_CS_AS NOT NULL ,
    [PWD] [varbinary] (16) NOT NULL ,
    [Birth] [smalldatetime] NOT NULL ,
    [Type] [tinyint] NOT NULL ,
    [ExpTime] [int] NOT NULL
    ) ON [PRIMARY]
    GO
    player:
    CREATE TABLE [dbo].[Player] (
    [UID] [int] NOT NULL ,
    [PID] [int] IDENTITY (1, 1) NOT NULL ,
    [Admin] [tinyint] NOT NULL ,
    [Name] [varchar] (16) COLLATE Korean_Wansung_CS_AS NOT NULL ,
    [Class] [tinyint] NOT NULL ,
    [Specialty] [tinyint] NOT NULL ,
    [Level] [tinyint] NOT NULL ,
    [Contribute] [smallint] NOT NULL ,
    [Exp] [bigint] NOT NULL ,
    [GID] [int] NOT NULL ,
    [GRole] [tinyint] NOT NULL ,
    [Strength] [tinyint] NOT NULL ,
    [Health] [tinyint] NOT NULL ,
    [Intelligence] [tinyint] NOT NULL ,
    [Wisdom] [tinyint] NOT NULL ,
    [Dexterity] [tinyint] NOT NULL ,
    [CurHP] [smallint] NOT NULL ,
    [CurMP] [smallint] NOT NULL ,
    [PUPoint] [smallint] NOT NULL ,
    [SUPoint] [smallint] NOT NULL ,
    [Killed] [tinyint] NOT NULL ,
    [Map] [tinyint] NOT NULL ,
    [X] [int] NOT NULL ,
    [Y] [int] NOT NULL ,
    [Z] [int] NOT NULL ,
    [Face] [tinyint] NOT NULL ,
    [Hair] [tinyint] NOT NULL ,
    [RevivalId] [tinyint] NOT NULL ,
    [Rage] [int] NOT NULL
    ) ON [PRIMARY]
    GO
    I kid you not I have tried my very best to resolve this & if anyone can provide a hint or suggestion on what to do to fix this I'd really appreciate it.

    thanks
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure what you're doing with that uid++ code but it looks wrong

    are you just trying to find out what the IDENTITY value is that was assigned to the new row?

    unless you use transaction locks, the SELECT MAX() technique is problematic

    there are two better ways to do that

    one way is to use the SCOPE_IDENTITY() function which is specific to SQL Server

    the other way (which works in all databases) is to do is identify an alternate key for the table

    when you use a surrogate key (the IDENTITY column) for the primary key, always look for another column that can be used to uniquely identify each row

    in the case of your Login table, that would be the ID column

    once you have identified it, declare a UNIQUE constraint on it

    then, when you add a new row, you can use the value of the alternate key to query that row back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for shizzle R3. you always come up with the good solutions. I'm not that experienced with MSSQL though so I'm not too sure how to implement what you're suggesting. Guess i'll sleep on it

    ps: the UID select max thing was from smoething i was trying earlier. disregard that.

    I stil dont understand though why the player gets created twice & why I get the error that i get & yet the row is still created in login & player....
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one good way to debug your scripts is always to run your SQL outside of your script first, to make sure that the query is working the way you expect, before rewriting it with parameters for you script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok man I ran the query to insert login into LOgin table & to insert a player into player table directly through SQl query analyser..

    They both worked perfectly. No problems.

    This is why i don't see the error in my code. Because the queries themselves are basically right on perfect. So i don't understand where the heck these errors like violation of PK_LOGIn are coming from........

    Heres the full extract of the script.

    PHP Code:
    <?php
    $msconnect
    =mssql_connect("$dbhost","$dbuser","$dbpasswd");
    $msdb=mssql_select_db("kal_auth",$msconnect);

    //$uid = mssql_result(mssql_query("SELECT MAX (UID) FROM Login"),0,0);
    //$uid++;
    //mssql_query("SET IDENTITY_INSERT Login On");

    mssql_query("INSERT INTO Login ([ID], [PWD], [Birth], [Type], [ExpTime]) VALUES('$id',$pw,'19190101','0','4000')");
    $uid mssql_result(mssql_query("SELECT [UID] from Login WHERE [ID]='$id'"),0,0);

    //mssql_query("SET IDENTITY_INSERT Login Off");
    mssql_close() or die('failed closing mssql');

    echo 
    "Successfully created ID: $id and PW: $pass";

    // DEFINE CLASS VARIABLES
    if($char == 0){ //knight
    $class '0'$strength '18'$health '16'$Intelligence '8'$wisdom '8'
    $dexterity '10'$curhp '183'$curmp '165'$wepid '1'

    } elseif(
    $char == 1){ //mage
    $class '1'$strength '8'$health '10'$Intelligence '18'$wisdom '16'
    $dexterity '8'$curhp '148'$curmp '215'$wepid '90';

    } elseif(
    $char == 2){ //archer
    $class '2'$strength '14'$health '10'$Intelligence '8'$wisdom '10';
    $dexterity '18'$curhp '147'$curmp '174'$wepid '22';
    }

    $msconnect=mssql_connect("$dbhost","$dbuser","$dbpasswd");
    $msdb=mssql_select_db("kal_db",$msconnect);
    // CREATE PLAYER
    mssql_query("INSERT INTO Player ([UID], [Admin], [Name], [Class], [Specialty], [Level], [Contribute], [Exp], [GID], [GRole], [Strength], [Health], [Intelligence], [Wisdom], [Dexterity], [CurHP], [CurMP], [PUPoint], [SUPoint], [Killed], [Map], [X], [Y], [Z], [Face], [Hair], [RevivalId], [Rage])
    VALUES('
    $uid','0','$charname','$class','1','1','0','0','0','0','$strength','$health','$intelligence','$wisdom','$dexterity','$curhp','$curmp','5','0','0','0','257304','259299','16131','0','0','0','0')");


    // RECALL PLAYER ID
    $pid mssql_result(mssql_query("SELECT PID FROM Player WHERE Name='$charname'"),0,0);

    // RECALL ITEM ID
    $iid mssql_result(mssql_query("SELECT IID FROM item order by IID desc"),0,0);
    $iid++;

    // INSERT G1 WEAPON
    mssql_query("INSERT INTO Item ([PID], [IID], [Index], [Prefix], [Info], [Num], [MaxEnd], [CurEnd], [SetGem], [UpgrRate], [UpgrLevel], [Protect], [XAttack], [XMagic], [XDefense], [XHit], [XDodge]) VALUES ('$pid','$iid','$wepid','0','0','1','3','3','0','0','0','0','0','0','0','0','0')");

    // INSERT SKILLZ
    if($char == 1){ mssql_query("INSERT INTO Skill ([PID], [Index], [Level]) VALUES ('$pid','4','1')"); }
    mssql_query("INSERT INTO Skill ([PID], [Index], [Level]) VALUES ('$pid','0','1')");
    mssql_query("INSERT INTO Skill ([PID], [Index], [Level]) VALUES ('$pid','1','1')");
    mssql_query("INSERT INTO Skill ([PID], [Index], [Level]) VALUES ('$pid','11','1')");
    mssql_query("INSERT INTO Quest ([PID], [Quest], [Flag], [Clear]) VALUES ('$pid','9001','1','0')");

    mssql_close() or die('failed closing mssql');*/
    ?>
    any more ideas thoughts comments?
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no more ideas from me, man, i don't do php

    however, i can move the thread over to the php forum for you...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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