[PHP-DB] Re: Joinging two different mySQL result?

2004-08-14 Thread Kim Steinhaug 
Here are some tables to reflect the accual problem :
So if you can select the two records in one query I would be
very happy, :D

What I want is this :

users.id = users_info.cid

I would like to select from users,
usr (which is the email ) as email,
aktiv,
newsletter,
emailblock,
htmlmode,
none_registered

and * from users_info where users.id = users_info.cid

At the same time from unregistered_users I would like
epost,
newsletter,
htmlmode,
newsletter,
emailblock

Theese two should be merged together, if at all possible.
It would also be nessersary to include a new field which tells
from what table the result came from, meaning did it come from
users or from unregistered_users.

Well, I might stick to ALTERNATIVE 3 it seems.

DATABASE TABLES WITH 2 ENTRIES :
--
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `usr` varchar(100) NOT NULL default '',
  `pas` varchar(32) NOT NULL default '',
  `aktiv` tinyint(1) NOT NULL default '0',
  `aktivcode` varchar(10) NOT NULL default '',
  `level` tinyint(1) unsigned NOT NULL default '0',
  `price_group` tinyint(1) unsigned NOT NULL default '1',
  `shipping` tinyint(1) unsigned NOT NULL default '0',
  `suspend` tinyint(1) unsigned NOT NULL default '0',
  `newsletter` tinyint(1) unsigned NOT NULL default '0',
  `emailblock` tinyint(1) unsigned NOT NULL default '0',
  `emailstatus` tinyint(1) unsigned NOT NULL default '0',
  `emailcount` int(5) unsigned NOT NULL default '0',
  `htmlmode` tinyint(1) unsigned NOT NULL default '1',
  `timecreate` int(10) unsigned NOT NULL default '1071245466',
  `none_registered` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `i_id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `users` VALUES (1, '[EMAIL PROTECTED]', 'testing', 1, '123', 1,
2, 1, 0, 1, 1, 0, 126, 1, 0, 0);

CREATE TABLE `users_info` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `cstatus` tinyint(1) unsigned NOT NULL default '0',
  `navn` varchar(50) NOT NULL default '',
  `etternavn` varchar(50) NOT NULL default '',
  `adresse` varchar(100) NOT NULL default '',
  `postnummer` varchar(10) NOT NULL default '',
  `poststed` varchar(30) NOT NULL default '',
  `telefon` varchar(13) NOT NULL default '',
  `mobil` varchar(13) NOT NULL default '',
  `epost` varchar(100) NOT NULL default '',
  `brukernavn` varchar(100) NOT NULL default '',
  `passord` varchar(32) NOT NULL default '',
  `fdatod` int(2) NOT NULL default '0',
  `fdatom` int(2) NOT NULL default '0',
  `fdatoy` int(4) NOT NULL default '0',
  `extraa` varchar(50) NOT NULL default '',
  `extrab` varchar(50) NOT NULL default '',
  `extrac` varchar(50) NOT NULL default '',
  `extraba` varchar(255) NOT NULL default '',
  `extrabb` varchar(255) NOT NULL default '',
  `orgnr` varchar(15) NOT NULL default '',
  `fnavn` varchar(50) NOT NULL default '',
  `fadresse` varchar(100) NOT NULL default '',
  `fpbox` varchar(100) NOT NULL default '',
  `fpostnummer` varchar(10) NOT NULL default '',
  `fpoststed` varchar(30) NOT NULL default '',
  `lnavn` varchar(50) NOT NULL default '',
  `ladresse` varchar(100) NOT NULL default '',
  `lpbox` varchar(100) NOT NULL default '',
  `lpostnummer` varchar(10) NOT NULL default '',
  `lpoststed` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`),
  KEY `i_cid` (`cid`),
  FULLTEXT KEY `all_fields`
(`navn`,`etternavn`,`adresse`,`postnummer`,`poststed`,`telefon`,`mobil`,`epo
st`,`orgnr`,`fnavn`,`fadresse`,`fpbox`,`fpostnummer`,`fpoststed`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `users_info` VALUES (1, 1, 1, 'Kim', 'Steinhaug', 'PB 8149
Vaagsbygd', '4622', 'Kristiansand', '8800945', '', '[EMAIL PROTECTED]', '',
'', 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'');

CREATE TABLE `unregistered_users` (
  `id` int(11) NOT NULL auto_increment,
  `navn` varchar(100) NOT NULL default '',
  `epost` varchar(50) NOT NULL default '',
  `htmlmode` tinyint(1) unsigned NOT NULL default '0',
  `aktivcode` varchar(15) NOT NULL default '',
  `newsletter` tinyint(1) unsigned NOT NULL default '1',
  `emailblock` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `unregistered_users` VALUES (1, 'Steinhaug Webdesign',
'[EMAIL PROTECTED]', 1, 'kozexyimoc', 1, 0);


-- 
Kim Steinhaug
-
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
-
www.steinhaug.com - www.easywebshop.no - www.easycms.no www.webkitpro.com
-

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit:

Re: [PHP-DB] Re: Joinging two different mySQL result?

2004-08-14 Thread Micah Stevens 

select users.usr as email, 
users.aktiv,
users.newsletter,
users.emailblock,
users.htmlmode,
users.none_registered,
users_info.*
from users, users_info
WHERE users.id = users_info.cid

That would work. There's no obvious way to join in the 3rd table, so you'll 
have to select that separately.. 

-Micah 


On Saturday 14 August 2004 11:05 am, Kim Steinhaug wrote:
 Here are some tables to reflect the accual problem :
 So if you can select the two records in one query I would be
 very happy, :D

 What I want is this :

 users.id = users_info.cid

 I would like to select from users,
 usr (which is the email ) as email,
 aktiv,
 newsletter,
 emailblock,
 htmlmode,
 none_registered

 and * from users_info where users.id = users_info.cid

 At the same time from unregistered_users I would like
 epost,
 newsletter,
 htmlmode,
 newsletter,
 emailblock

 Theese two should be merged together, if at all possible.
 It would also be nessersary to include a new field which tells
 from what table the result came from, meaning did it come from
 users or from unregistered_users.

 Well, I might stick to ALTERNATIVE 3 it seems.

 DATABASE TABLES WITH 2 ENTRIES :
 --
 CREATE TABLE `users` (
   `id` int(11) NOT NULL auto_increment,
   `usr` varchar(100) NOT NULL default '',
   `pas` varchar(32) NOT NULL default '',
   `aktiv` tinyint(1) NOT NULL default '0',
   `aktivcode` varchar(10) NOT NULL default '',
   `level` tinyint(1) unsigned NOT NULL default '0',
   `price_group` tinyint(1) unsigned NOT NULL default '1',
   `shipping` tinyint(1) unsigned NOT NULL default '0',
   `suspend` tinyint(1) unsigned NOT NULL default '0',
   `newsletter` tinyint(1) unsigned NOT NULL default '0',
   `emailblock` tinyint(1) unsigned NOT NULL default '0',
   `emailstatus` tinyint(1) unsigned NOT NULL default '0',
   `emailcount` int(5) unsigned NOT NULL default '0',
   `htmlmode` tinyint(1) unsigned NOT NULL default '1',
   `timecreate` int(10) unsigned NOT NULL default '1071245466',
   `none_registered` tinyint(1) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `i_id` (`id`)
 ) TYPE=MyISAM AUTO_INCREMENT=2 ;

 INSERT INTO `users` VALUES (1, '[EMAIL PROTECTED]', 'testing', 1, '123', 1,
 2, 1, 0, 1, 1, 0, 126, 1, 0, 0);

 CREATE TABLE `users_info` (
   `id` int(11) NOT NULL auto_increment,
   `cid` int(11) NOT NULL default '0',
   `cstatus` tinyint(1) unsigned NOT NULL default '0',
   `navn` varchar(50) NOT NULL default '',
   `etternavn` varchar(50) NOT NULL default '',
   `adresse` varchar(100) NOT NULL default '',
   `postnummer` varchar(10) NOT NULL default '',
   `poststed` varchar(30) NOT NULL default '',
   `telefon` varchar(13) NOT NULL default '',
   `mobil` varchar(13) NOT NULL default '',
   `epost` varchar(100) NOT NULL default '',
   `brukernavn` varchar(100) NOT NULL default '',
   `passord` varchar(32) NOT NULL default '',
   `fdatod` int(2) NOT NULL default '0',
   `fdatom` int(2) NOT NULL default '0',
   `fdatoy` int(4) NOT NULL default '0',
   `extraa` varchar(50) NOT NULL default '',
   `extrab` varchar(50) NOT NULL default '',
   `extrac` varchar(50) NOT NULL default '',
   `extraba` varchar(255) NOT NULL default '',
   `extrabb` varchar(255) NOT NULL default '',
   `orgnr` varchar(15) NOT NULL default '',
   `fnavn` varchar(50) NOT NULL default '',
   `fadresse` varchar(100) NOT NULL default '',
   `fpbox` varchar(100) NOT NULL default '',
   `fpostnummer` varchar(10) NOT NULL default '',
   `fpoststed` varchar(30) NOT NULL default '',
   `lnavn` varchar(50) NOT NULL default '',
   `ladresse` varchar(100) NOT NULL default '',
   `lpbox` varchar(100) NOT NULL default '',
   `lpostnummer` varchar(10) NOT NULL default '',
   `lpoststed` varchar(30) NOT NULL default '',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `id` (`id`),
   KEY `id_2` (`id`),
   KEY `i_cid` (`cid`),
   FULLTEXT KEY `all_fields`
 (`navn`,`etternavn`,`adresse`,`postnummer`,`poststed`,`telefon`,`mobil`,`ep
o st`,`orgnr`,`fnavn`,`fadresse`,`fpbox`,`fpostnummer`,`fpoststed`)
 ) TYPE=MyISAM AUTO_INCREMENT=2 ;

 INSERT INTO `users_info` VALUES (1, 1, 1, 'Kim', 'Steinhaug', 'PB 8149
 Vaagsbygd', '4622', 'Kristiansand', '8800945', '', '[EMAIL PROTECTED]', '',
 '', 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
 '');

 CREATE TABLE `unregistered_users` (
   `id` int(11) NOT NULL auto_increment,
   `navn` varchar(100) NOT NULL default '',
   `epost` varchar(50) NOT NULL default '',
   `htmlmode` tinyint(1) unsigned NOT NULL default '0',
   `aktivcode` varchar(15) NOT NULL default '',
   `newsletter` tinyint(1) unsigned NOT NULL default '1',
   `emailblock` tinyint(1) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `id` (`id`),
   KEY `id_2` (`id`)
 ) TYPE=MyISAM AUTO_INCREMENT=2 ;

 INSERT INTO `unregistered_users` VALUES (1, 'Steinhaug Webdesign',
 '[EMAIL PROTECTED]',