SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard
    Join Date
    Jul 2006
    Location
    New Zealand
    Posts
    1,300
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    importing sql files from other scripts using php and mysql

    there are several sfree scripts i have selected over the years and i have the sql files for htem and i was wondering how deo i go about importing the members database info from their script into my script without any issues.


    this is how the script structure is layed out.

    REATE TABLE `members` (
    `id` smallint(14) NOT NULL auto_increment,
    `myname` varchar(80) NOT NULL default '',
    `mypass` varchar(80) NOT NULL default '',
    `fpassreq` smallint(6) NOT NULL default '1',
    `oldpass` varchar(25) NOT NULL default '',
    `key1` int(16) NOT NULL default '0',
    `color` varchar(15) NOT NULL default '',
    `passquestion` int(3) NOT NULL default '0',
    `passquestionset` int(2) NOT NULL default '1',
    `questionanswer` text NOT NULL,
    `datejoined` date NOT NULL default '0000-00-00',
    `datejoined2` text NOT NULL,
    `rank` tinyint(5) unsigned NOT NULL default '0',
    `email` varchar(30) NOT NULL default '',
    `aim` varchar(16) NOT NULL default 'None',
    `gto` varchar(30) NOT NULL default '',
    `bwwins` smallint(4) unsigned NOT NULL default '0',
    `bwlosses` smallint(4) unsigned NOT NULL default '0',
    `bwdiscs` smallint(4) unsigned NOT NULL default '0',
    `bwlwins` smallint(4) unsigned NOT NULL default '0',
    `bwllosses` smallint(4) unsigned NOT NULL default '0',
    `bwldiscs` smallint(4) unsigned NOT NULL default '0',
    `logindays` date NOT NULL default '0000-00-00',
    `disable` int(1) NOT NULL default '0',
    `disabled` blob NOT NULL,
    `lastdemo` date NOT NULL default '0000-00-00',
    `d2xhigh` varchar(15) NOT NULL default 'Name',
    `quote` blob NOT NULL,
    `newsposts` text NOT NULL,
    `recruits` int(16) NOT NULL default '0',
    `bwlhigh` int(4) NOT NULL default '0',
    `lastip` varchar(15) NOT NULL default '',
    `d2high` varchar(20) NOT NULL default 'Name',
    `d2hchigh` tinyint(2) unsigned NOT NULL default '0',
    `d2xhchigh` tinyint(2) unsigned NOT NULL default '0',
    `scwins` smallint(4) unsigned NOT NULL default '0',
    `sclosses` smallint(4) unsigned NOT NULL default '0',
    `scdiscs` smallint(4) unsigned NOT NULL default '0',
    `sclwins` smallint(4) unsigned NOT NULL default '0',
    `scllosses` smallint(4) unsigned NOT NULL default '0',
    `scldiscs` smallint(4) unsigned NOT NULL default '0',
    `sclhigh` smallint(4) unsigned NOT NULL default '0',
    `wc3wins` tinyint(3) unsigned NOT NULL default '0',
    `wc3losses` tinyint(3) unsigned NOT NULL default '0',
    `wc3discs` tinyint(2) unsigned NOT NULL default '0',
    `wc3lwins` tinyint(3) unsigned NOT NULL default '0',
    `wc3llosses` tinyint(3) unsigned NOT NULL default '0',
    `wc3ldiscs` tinyint(2) unsigned NOT NULL default '0',
    `wc3lhigh` tinyint(4) unsigned NOT NULL default '0',
    `wc3xwins` tinyint(3) unsigned NOT NULL default '0',
    `wc3xlosses` tinyint(3) unsigned NOT NULL default '0',
    `wc3xdiscs` tinyint(2) unsigned NOT NULL default '0',
    `wc3xlwins` tinyint(3) unsigned NOT NULL default '0',
    `wc3xllosses` tinyint(3) unsigned NOT NULL default '0',
    `wc3xldiscs` tinyint(2) unsigned NOT NULL default '0',
    `wc3xlhigh` tinyint(4) unsigned NOT NULL default '0',
    `tournwins` int(12) NOT NULL default '0',
    `recruiter` text NOT NULL,
    `dka` tinyint(1) unsigned NOT NULL default '0',
    `lastpromo` date NOT NULL default '0000-00-00',
    `promo` date NOT NULL default '0000-00-00',
    `loginnum` text NOT NULL,
    `SC` tinyint(1) unsigned NOT NULL default '0',
    `BW` tinyint(1) unsigned NOT NULL default '0',
    `W2` tinyint(1) unsigned NOT NULL default '0',
    `W3` tinyint(1) unsigned NOT NULL default '0',
    `W3X` tinyint(1) unsigned NOT NULL default '0',
    `D2` tinyint(1) unsigned NOT NULL default '0',
    `D2X` tinyint(1) unsigned NOT NULL default '0',
    `CS` tinyint(1) unsigned NOT NULL default '0',
    `RTCW` tinyint(1) unsigned NOT NULL default '0',
    `moh` tinyint(1) unsigned NOT NULL default '0',
    `ddsm` tinyint(1) unsigned NOT NULL default '0',
    `dss` tinyint(1) unsigned NOT NULL default '0',
    `dms` tinyint(1) unsigned NOT NULL default '0',
    `na` tinyint(1) unsigned NOT NULL default '0',
    `nc` tinyint(1) unsigned NOT NULL default '0',
    `lom` tinyint(1) unsigned NOT NULL default '0',
    `ms` tinyint(1) unsigned NOT NULL default '0',
    `ads` tinyint(1) unsigned NOT NULL default '0',
    `apc` tinyint(1) unsigned NOT NULL default '0',
    `un` tinyint(1) unsigned NOT NULL default '0',
    `ph` tinyint(1) unsigned NOT NULL default '0',
    `dkc` tinyint(1) unsigned NOT NULL default '0',
    `mka` tinyint(1) unsigned NOT NULL default '0',
    `dfc` tinyint(1) unsigned NOT NULL default '0',
    `pow` tinyint(1) unsigned NOT NULL default '0',
    `pas` tinyint(1) unsigned NOT NULL default '0',
    `col` tinyint(1) unsigned NOT NULL default '0',
    `wc3high` tinyint(4) NOT NULL default '0',
    `eow` varchar(100) default NULL,
    `web` tinyint(1) unsigned NOT NULL default '0',
    `money` int(11) default NULL,
    `nonm` int(11) default NULL,
    `image` varchar(100) default NULL,
    `age` int(11) default NULL,
    `loc` varchar(100) default NULL,
    `name` varchar(30) default NULL,
    `nn` varchar(30) default NULL,
    `country` varchar(30) default NULL,
    `city` varchar(30) default NULL,
    `inactive` tinyint(1) NOT NULL default '0',
    `width` text NOT NULL,
    `height` text NOT NULL,
    `donate` tinyint(1) NOT NULL default '0',
    `war` tinyint(1) unsigned NOT NULL default '0',
    `admin` tinyint(1) unsigned NOT NULL default '0',
    `spy` tinyint(1) unsigned NOT NULL default '0',
    `dip` tinyint(1) unsigned NOT NULL default '0',
    `smurf` text NOT NULL,
    `yim` varchar(16) NOT NULL default '',
    `msn` varchar(16) NOT NULL default '',
    `bd` varchar(30) NOT NULL default '',
    `ct` varchar(50) NOT NULL default '',
    `cspeed` varchar(30) NOT NULL default '',
    `ics` varchar(30) NOT NULL default '',
    `ram` varchar(30) NOT NULL default '',
    `net` varchar(20) NOT NULL default '',
    `os` varchar(30) NOT NULL default '',
    `recruit` tinyint(2) NOT NULL default '1',
    `approved` text NOT NULL,
    `dateap` date NOT NULL default '0000-00-00',
    `access` blob NOT NULL,
    `rechelp` int(5) NOT NULL default '0',
    `recruitcon` int(5) NOT NULL default '0',
    `recabil` int(3) NOT NULL default '0',
    `disabledate` date NOT NULL default '0000-00-00',
    PRIMARY KEY (`id`),
    KEY `username` (`myname`)
    ) TYPE=MyISAM AUTO_INCREMENT=93 ;");


    @mysql_query("INSERT INTO `members` VALUES (62, 'Admin', 'newclan', 1, 'newclan', 0, '', 0, 1, '', '2005-02-20', '', 28, '', 'None', '', 0, 0, 0, 0, 0, 0, '2005-02-20', 0, '', '0000-00-00', 'Name', '', '0', 0, 0, '0.0.0.0', 'Name', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '0000-00-00', '0000-00-00', '310', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, '', '', 0, 0, 1, 0, 0, '', '', '', '', '', '', '', '', '', '', 1, '', '0000-00-00', '', 0, 0, 0, '2005-02-15'); ");


    now here is my members table structure.

    CREATE TABLE `members` (
    `mid` int(255) unsigned NOT NULL auto_increment,
    `username` varchar(255) NOT NULL default '',
    `password` varchar(255) NOT NULL default '',
    `rank` varchar(255) NOT NULL default '',
    `email` varchar(255) NOT NULL default '',
    `aim` varchar(255) NOT NULL default '',
    `msn` varchar(255) NOT NULL default '',
    `disable` enum('0','1') NOT NULL default '0',
    `disabledreason` varchar(255) NOT NULL default '',
    `Recruitedby` varchar(255) NOT NULL default '',
    `LastedLogin` varchar(255) NOT NULL default '',
    `joineddate` varchar(255) NOT NULL default '',
    `hideemail` enum('Y','N') NOT NULL default 'N',
    `previousclans` text NOT NULL,
    `logindays` date NOT NULL default '0000-00-00',
    `loginnum` varchar(255) NOT NULL default '0',
    `ips` varchar(255) NOT NULL default '',
    `country` char(1) NOT NULL default '',
    `web` char(1) NOT NULL default '',
    `squestion` text NOT NULL,
    `sanswer` text NOT NULL,
    `recruits` varchar(255) NOT NULL,
    `headshots` varchar(255) NOT NULL default '0',
    `kills` varchar(255) NOT NULL default '0',
    `Deaths` varchar(255) NOT NULL default '0',
    `wins` varchar(255) NOT NULL default '0',
    `losses` varchar(255) NOT NULL default '0',
    `favoriteteam` varchar(255) NOT NULL default '0',
    `active` varchar(255) NOT NULL default 'N',
    `hashed_id` varchar(255) NOT NULL,
    `signons` varchar(255) NOT NULL,
    `onlinestatus` varchar(255) NOT NULL,
    `gamesplayed` varchar(255) NOT NULL,
    `ia` enum('0','1') NOT NULL,
    `consoletype` enum('1','0') NOT NULL default '1',
    `joineddate2` varchar(255) NOT NULL,
    PRIMARY KEY (`mid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;


    That is how my members table structure is layred out how can i insert Admin and password newclan into my members table successfully without having to worry about left out fields?

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I am presuming you want to take items from the first table and insert them into the members table, and there will be loads of unused fields, and the fields that do match do not have the same name?

    eg

    id, myname
    becomes
    mid, username

    Have I understood?

    Well I'd say one way would be to create an array which maps one required value to the other.

    $map = array(
    'id'=>'mid',
    'myname'=>'username',
    'datejoined'=>'joineddate',
    );

    Then loop through the array keys once to select the required values, then loop through it again to insert the values.

    But I think there is a way of doing this in an sql statement, something like:

    "insert into members (mid, username, datejoined) select (id, username, joineddate) from old_members"

    http://dev.mysql.com/doc/refman/5.1/en/insert.html
    Quote Originally Posted by mysql_man
    If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to fin
    d out.
    Set up some test tables and have a go.

    I can forsee some problems with dates though, unsure if joineddate is supposed to translate or from datejoined.

    Dont forget to backup all your tables first, naturally.


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
  •