1. Задавайте здесь вопросы о коде, которые не подходят в другие разделы, такие как:
    Дизайн > Верстка
    PHP > Как сделать на PHP
    Скрыть объявление

Need help with php mysql

Тема в разделе "Web Coding", создана пользователем GHBB, 11 авг 2012.

Модераторы: latteo
  1. GHBB

    GHBB Писатель

    Регистр.:
    23 май 2012
    Сообщения:
    4
    Симпатии:
    0
    I found the solution, now need help with mysql
    Код:
    function addAccount($email, $finalSalt, $finalV, $userLevel) {
        include('dbconfig.php');
        $email=mysql_real_escape_string($email);
        $finalV = addslashes($finalV);
        $err = mysql_query("INSERT INTO DBAccount (email,salt,passwordVerifier,userLevel) values ('$email','$finalSalt','".$finalV."','0') ") or die(mysql_error());
     
        if( $err )
            return true;
        else return false;
    }
    
    This function registers an account
    but you need to also recorded in this table account's id to a string DBAccount_id
    DBGameAccount
    table structure:
    Код:
    /*
    MySQL Data Transfer
    Source Host: 195.191.126.44
    Source Database: d3login
    Target Host: 195.191.126.44
    Target Database: d3login
    Date: 08.08.2012 1:24:58
    */
     
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for DBAccount
    -- ----------------------------
    CREATE TABLE `DBAccount` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `Email` varchar(255) DEFAULT NULL,
      `Salt` tinyblob,
      `PasswordVerifier` blob,
      `BattleTagName` varchar(255) DEFAULT NULL,
      `HashCode` int(11) DEFAULT NULL,
      `UserLevel` varchar(255) DEFAULT NULL,
      `LastOnline` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for DBActiveSkills
    -- ----------------------------
    CREATE TABLE `DBActiveSkills` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `Rune0` int(11) DEFAULT NULL,
      `Skill0` int(11) DEFAULT NULL,
      `Rune1` int(11) DEFAULT NULL,
      `Skill1` int(11) DEFAULT NULL,
      `Rune2` int(11) DEFAULT NULL,
      `Skill2` int(11) DEFAULT NULL,
      `Rune3` int(11) DEFAULT NULL,
      `Skill3` int(11) DEFAULT NULL,
      `Rune4` int(11) DEFAULT NULL,
      `Skill4` int(11) DEFAULT NULL,
      `Rune5` int(11) DEFAULT NULL,
      `Skill5` int(11) DEFAULT NULL,
      `Passive0` int(11) DEFAULT NULL,
      `Passive1` int(11) DEFAULT NULL,
      `Passive2` int(11) DEFAULT NULL,
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for DBGameAccount
    -- ----------------------------
    CREATE TABLE `DBGameAccount` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `Banner` blob,
      `LastOnline` bigint(20) DEFAULT NULL,
      `Gold` int(11) DEFAULT NULL,
      `StashSize` int(11) DEFAULT NULL,
      `DBAccount_id` bigint(20) unsigned DEFAULT NULL,
      `LastPlayedHero_id` bigint(20) unsigned DEFAULT NULL,
      PRIMARY KEY (`Id`),
      KEY `DBAccount_id` (`DBAccount_id`),
      KEY `LastPlayedHero_id` (`LastPlayedHero_id`),
      KEY `DBAccount_id_2` (`DBAccount_id`),
      KEY `LastPlayedHero_id_2` (`LastPlayedHero_id`),
      CONSTRAINT `FK6BE1E67D17DD8A68` FOREIGN KEY (`DBAccount_id`) REFERENCES `DBAccount` (`Id`),
      CONSTRAINT `FK6BE1E67D58A58F13` FOREIGN KEY (`LastPlayedHero_id`) REFERENCES `DBToon` (`Id`),
      CONSTRAINT `FKB0F23B044D29D1B8` FOREIGN KEY (`DBAccount_id`) REFERENCES `DBAccount` (`Id`),
      CONSTRAINT `FKB0F23B0482377C5E` FOREIGN KEY (`LastPlayedHero_id`) REFERENCES `DBToon` (`Id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for DBInventory
    -- ----------------------------
    CREATE TABLE `DBInventory` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `EquipmentSlot` int(11) DEFAULT NULL,
      `LocationX` int(11) DEFAULT NULL,
      `LocationY` int(11) DEFAULT NULL,
      `DBGameAccount_id` bigint(20) unsigned DEFAULT NULL,
      `DBToon_id` bigint(20) unsigned DEFAULT NULL,
      `DBItemInstance_id` bigint(20) unsigned NOT NULL,
      PRIMARY KEY (`Id`),
      KEY `DBGameAccount_id` (`DBGameAccount_id`),
      KEY `DBToon_id` (`DBToon_id`),
      KEY `DBItemInstance_id` (`DBItemInstance_id`),
      KEY `DBGameAccount_id_2` (`DBGameAccount_id`),
      KEY `DBToon_id_2` (`DBToon_id`),
      KEY `DBItemInstance_id_2` (`DBItemInstance_id`),
      CONSTRAINT `FK143822B851C3A8FA` FOREIGN KEY (`DBToon_id`) REFERENCES `DBToon` (`Id`),
      CONSTRAINT `FK143822B8A5E99DE2` FOREIGN KEY (`DBItemInstance_id`) REFERENCES `DBItemInstance` (`Id`),
      CONSTRAINT `FK143822B8A69A76BF` FOREIGN KEY (`DBGameAccount_id`) REFERENCES `DBGameAccount` (`Id`),
      CONSTRAINT `FK86B0DE7E4C0425AC` FOREIGN KEY (`DBGameAccount_id`) REFERENCES `DBGameAccount` (`Id`),
      CONSTRAINT `FK86B0DE7EA98CAE9E` FOREIGN KEY (`DBItemInstance_id`) REFERENCES `DBItemInstance` (`Id`),
      CONSTRAINT `FK86B0DE7EC0ADA6C6` FOREIGN KEY (`DBToon_id`) REFERENCES `DBToon` (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for DBItemInstance
    -- ----------------------------
    CREATE TABLE `DBItemInstance` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `GbId` int(11) DEFAULT NULL,
      `Affixes` varchar(255) DEFAULT NULL,
      `Attributes` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for DBToon
    -- ----------------------------
    CREATE TABLE `DBToon` (
      `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `Class` varchar(255) DEFAULT NULL,
      `Deleted` tinyint(1) DEFAULT NULL,
      `Experience` int(11) DEFAULT NULL,
      `Flags` varchar(255) DEFAULT NULL,
      `Level` tinyint(3) unsigned DEFAULT NULL,
      `Name` varchar(255) DEFAULT NULL,
      `TimePlayed` int(10) unsigned DEFAULT NULL,
      `DBGameAccount_id` bigint(20) unsigned DEFAULT NULL,
      PRIMARY KEY (`Id`),
      KEY `DBGameAccount_id` (`DBGameAccount_id`),
      KEY `DBGameAccount_id_2` (`DBGameAccount_id`),
      CONSTRAINT `FK77D377384C0425AC` FOREIGN KEY (`DBGameAccount_id`) REFERENCES `DBGameAccount` (`Id`),
      CONSTRAINT `FKF8752C35A69A76BF` FOREIGN KEY (`DBGameAccount_id`) REFERENCES `DBGameAccount` (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
     
    -- ----------------------------
    -- Table structure for FriendsToFriends
    -- ----------------------------
    CREATE TABLE `FriendsToFriends` (
      `AccountAId` bigint(20) unsigned NOT NULL,
      `AccountBId` bigint(20) unsigned NOT NULL,
      KEY `AccountBId` (`AccountBId`),
      KEY `AccountAId` (`AccountAId`),
      KEY `AccountBId_2` (`AccountBId`),
      KEY `AccountAId_2` (`AccountAId`),
      CONSTRAINT `FK626D5D1451455CB3` FOREIGN KEY (`AccountBId`) REFERENCES `DBAccount` (`Id`),
      CONSTRAINT `FK626D5D14DDE05CB3` FOREIGN KEY (`AccountAId`) REFERENCES `DBAccount` (`Id`),
      CONSTRAINT `FK9474B4A516CC8AEC` FOREIGN KEY (`AccountAId`) REFERENCES `DBAccount` (`Id`),
      CONSTRAINT `FK9474B4A516CC8EB3` FOREIGN KEY (`AccountBId`) REFERENCES `DBAccount` (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    you help?
    <<< SELECT id FROM DBAccount WHERE id = '$id'

    <<< INSERT INTO DBGameAccount (DBAccount_id) values ('$id')
    id take
    and write it in the db column
    how to implement?
     
  2. motzmo

    motzmo Создатель

    Регистр.:
    20 июн 2007
    Сообщения:
    43
    Симпатии:
    16
    use subquery:

    Код:
    insert into DBGameAccount (DBAccount_id) values ( (SELECT id FROM DBAccount WHERE id = '$id') );
     
  3. GHBB

    GHBB Писатель

    Регистр.:
    23 май 2012
    Сообщения:
    4
    Симпатии:
    0
    Код:
    INSERT INTO `dbgameaccount` (`DBAccount_id`) VALUES ( (SELECT id FROM DBAccount) );
    1242 - Subquery returns more than 1 row
    Rows
    Код:
    1    test@    0x76328F0737E45D68EF472215A86200F2C0F9A7B82B62DBF460E15B6B41C81306    0xD1610ED1C3CC356BA5FECF4B92451EB58B3EB810791E2C777AA4971934E2FC7E88569D2FDEDE73A82B61E4C6FC13CBBF9E61B4FFDB7F3B4C2AB252942E08A2563C0F6E19C2F726C4495E1B21D05604DAE96AF97F2899B03997E0A2BF801B676FDB5EC942D00E2B35D73372CD63AE48B17AE18DF07ED37DB6E7F241F5B9288462    test    852    Owner    1344364213251255
    2    ghbb@i.ua    0x03098AFCF55348954C8E608DCD68284AAB04A1084E695090DC6F853EC288AAC5    0xFFDC8E55F50324DCD962D023810D6836925EA8F20DEF22C9C0E2A14AF02015FE6C92124724C453D6F5B6FC2B6C9850944A6E9048BC7F171EDFBC1F01160FBC5C21960F556C0FCA056041DE2085D8FBCB7503B7BAC32EFB6C43CF9ECE69148D812BAF45EC14ED5A72F65657CA07E328B742B6504772D5269E13C4244D0C39996B        0    User    0
    3    test2@test.ru    0x90B5C1C6B276A9E2C7DA381DE97F978231AC7DA140E091D86C5A27236BF92873    0x63EFF62922C35C5E52A174C2DD0676B38A3D2E2411BD5AFB7F1F6F0EBB3F2746ED8718BDB0D7D68A58A4A1A966618546FEE5D4094BF00B3B0B042D1B39A5F6EE7CE502F3091F3E83B98A529286565622FE162C35DED2B2910BD7011CF3F6D316EF08DAE8980A557083E02DFD111257813DB86AAAE378B7CA697E2CF521010B2F    test2    151    User    1344364727857688
    20    tyjl@yu.yu    0x7E9EE0D6204792CD238C3D49B373D467CDB5CC34FD2DDD14F38E404CC0AB5487    0x93BB4D424CB087CE130F7D3F977FC2221848171C1E03323A57D3D96724FEF21C49345FC32FA35E7AD8BA3253CCD0FAD3BC1A7B3EE561BF85C18FF4785EE07C88473DABC2238E3DF424A91C1BA7EC45CEA566AD626E1A4BA53718539C50080D5A30CFFA9CE37351B718097637031EEF18EEFF89BA345837BB87A873EDFC60955E            0   
    
    db function
    Код:
    <?php
    /*
        File for functions that interact with database, need to do more check and class coding!
    */
     
    function addAccount($email, $finalSalt, $finalV, $userLevel) {
        include('dbconfig.php');
        $email=mysql_real_escape_string($email);
        $finalV = addslashes($finalV);
        $err = mysql_query("INSERT INTO dbaccount (email,salt,passwordVerifier,userLevel) values ('$email','$finalSalt','".$finalV."','0') ") or die(mysql_error());
       
        if( $err )
            return true;
        else return false;
    }
     
    function getAccountdata($email) {
        include('dbconfig.php');
        $email=mysql_real_escape_string($email);
       
        $q=mysql_query("SELECT salt,passwordVerifier FROM dbaccount WHERE email = '$email' ");
        if( mysql_num_rows($q) == 0 ) return false; // wasnt found any match with that email in database!
            else {
                    $Account_data = mysql_fetch_array($q);
                    return $Account_data;
                }
       
        return false;
    }
     
    function getAccountid($id) {
        include('dbconfig.php');
        $id=mysql_real_escape_string($id);
       
        $q=mysql_query("SELECT id FROM dbaccount WHERE id = '$id' ");
       
    }
     
    function addAccountid($id) {
        include('dbconfig.php');
        $id=mysql_real_escape_string($id);
        $err = mysql_query("INSERT INTO DBGameAccount (DBAccount_id) values ('$id') ");
       
    }
     
    ?>
    Dont work
    Код:
    function getAccountid($id) {
     
        include('dbconfig.php');
     
        $id=mysql_real_escape_string($id);
     
     
     
        $q=mysql_query("SELECT id FROM dbaccount WHERE id = '$id' ");
     
     
     
    }
     
     
     
    function addAccountid($id) {
     
        include('dbconfig.php');
     
        $id=mysql_real_escape_string($id);
     
        $err = mysql_query("INSERT INTO DBGameAccount (DBAccount_id) values ('$id') ");
     
     
     
    }
    Help please
     
  4. motzmo

    motzmo Создатель

    Регистр.:
    20 июн 2007
    Сообщения:
    43
    Симпатии:
    16
    man, pay more attention to what i've said. You forgot to add 'WHERE' clause to subquery. It needs to be "INSERT INTO `dbgameaccount` (`DBAccount_id`) VALUES ( (SELECT id FROM DBAccount WHERE id = $id) ) "

    or if you have ID in variable already, why you want to get it again?
    Why not to use:
    Код:
    INSERT INTO `dbgameaccount` (`DBAccount_id`) VALUES ( $id )
    ?


    ADD:
    btw, i've just saw you have AUTO_INCREMENT on you `id` column, you can use NULL value to insert id automatically.
     
    GHBB нравится это.
  5. GHBB

    GHBB Писатель

    Регистр.:
    23 май 2012
    Сообщения:
    4
    Симпатии:
    0
    I just need a ready-made function, were taken to the id, and were put into another data table.

    my knowledge this is still very raw ...

    I have no account can request for the id, and therefore does not suit me
    INSERT INTO `DBGameAccount` (`DBAccount_id`) VALUES ( (SELECT id FROM DBAccount WHERE id = $id) );
     
  6. motzmo

    motzmo Создатель

    Регистр.:
    20 июн 2007
    Сообщения:
    43
    Симпатии:
    16
    ok...

    Код:
    function addAccount($email, $finalSalt, $finalV, $userLevel) {
        include('dbconfig.php');
        $email=mysql_real_escape_string($email);
        $finalV = addslashes($finalV);
        $err = mysql_query("INSERT INTO DBAccount (email,salt,passwordVerifier,userLevel) values ('$email','$finalSalt','".$finalV."','0') ") or die(mysql_error());
        addAccountid($email);
    
     if( $err )
            return true;
        else return false;
    }
    
    function addAccountid($email) {
     
        include('dbconfig.php');
     
        $err = mysql_query("INSERT INTO DBGameAccount (DBAccount_id) values ( (SELECT id FROM DBAccount WHERE email = '$email' ) ) ");
     
     
     
    }
    modified two functions. replace your funcions with these.
    should work