Need help with php mysql

GHBB · 11 Авг 2012

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?

motzmo · 13 Авг 2012

use subquery:

Код:

insert into DBGameAccount (DBAccount_id) values ( (SELECT id FROM DBAccount WHERE id = '$id') );

GHBB · 14 Авг 2012

Код:

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

motzmo · 15 Авг 2012

GHBB написал(а):
Код:

INSERT INTO `dbgameaccount` (`DBAccount_id`) VALUES ( (SELECT id FROM DBAccount) ); 1242 - Subquery returns more than 1 row

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 · 15 Авг 2012

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) );

motzmo · 15 Авг 2012

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

Need help with php mysql

GHBB

Писатель

motzmo

Создатель

GHBB

Писатель

motzmo

Создатель

GHBB

Писатель

motzmo

Создатель