Solved

how to get multiple mysql table lookups

Posted on 2014-09-05
22
230 Views
Last Modified: 2014-09-10
I have a unique situation, i have 3 tables and one description table id like to pull info from.
i was looking at join and im new to join mysql command and i do not understand how to make it work with what i need
im not saying it should be used im open to other ways it just seamed on trying to figure this out it was the logical choice to use.
here my problem
i have tables denoted by - with there field names under them as
- Contact
Active
username
password

ContactID
ClientID

Contact Name
EmailAddress
Secretary Name
EmailAddress2

- Client
Active
username
password

ClientID
Company Name
EndUserID
Created
Modified


- EndUser
Active
Login
Password

EndUserID
UserTypeID
FirstName
LastName

-UserType
UserTypeID
Description

Open in new window


what im looking to do it:
i need to look at Contact table and look for username and password if it is not found look in Client for username and password if that is not found look in EndUser for Login and Password, i know i need to do 'WHERE `Active` = '1' on all the tables someplace too.
my farther problem is if its the first two (Contact and Client) tables i need the info joined by ContactID and ClientID so i can pull the info Company_name and Contact Name etc from appropriate tables.
if the look up is EndUser i need just that tables info and the reference of UserTypeID description form the UserTypeID form EndUser along with the FirstName and LastName.

Im trying to make this so if a user logs in the admin(EndUser) and client can log into there accounts, but the tables i have are in 3 different spots(and i cant change them) i would have made it one table for login info and then broke up the tables in client/contact/admin using ids for each) but i didnt make the tables so im stuck with looking them up this way.

so whats the best way to do this please in MySQLi with php i really only need the sql statement but i wont complain if the php is there too
Thank you in advance for any code or help you may provide.
Johnny
0
Comment
Question by:Johnny
  • 9
  • 7
  • 5
  • +1
22 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
A very useful technique for explaining what is required is to provide "sample data" and an "expected result"

I suggest you provide some rows of data from each table.

Then also provide the result you would expect from that data.

You can use Excel or text (like CSV or tab delimited) for the data. Include the field names as headings too. Private data (like email addresses) can changed or, if not important for this purpose, excluded.
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

The easiest way is to query the tables like this

select username,password
from contact
where active = '1'
union
select username,password
from client
where active = '1'
union
select login as username,password 
from enduser
where active = '1'
order by username

Open in new window


This will give you one list of unique usernames and passwords.
I suggest ( if you can ) to create a view of active users with this sql (excluding the order by line) then you can query that view like this

select username,password
from activeusers_v
order by username

Regards,
    Tomas Helgi
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Tomas' solution is a good start - UNION is certainly appropriate here - but I think your requirements call for a different approach.
i need to look at Contact table and look for username and password if it is not found look in Client for username and password if that is not found look in EndUser for Login and Password
The most obvious way is to query the first table, check your return, and query the other tables if necessary:
$queries = array(
                 "SELECT username, password FROM contact WHERE active=1",
                 "SELECT username, password FROM client WHERE active=1",
                 "SELECT Login as username, Password as password FROM enduser WHERE active=1",
                );
$myrow = NULL;
foreach ($queries as $query) {
  // only run the next query is $myrow is not populated yet
  if (!$myrow && $result = $dbconn->query($query)) {
    $myrow = $result->fetch_assoc();
  }
}
// $myrow will either have a record, or be NULL

Open in new window

Going back to the UNION idea, as I said, it is certainly appropriate here.  But as you said, you would have just made one consolidated table if you had designed this.  You can't change the tables, but you can create an access mechanism to make your life easier:
CREATE VIEW consolidate_users AS
select username,password,active,1 as user_type
from contact
union
select username,password,active,2 as user_type
from client
union
select login as username,password,active,3 as user_type
from enduser;

Open in new window

That is Tomas' UNION query, with two small additions - the active field, and a flag on each record to indicate the original source.  In your code, you can now call:
$query="SELECT username,password,user_type FROM consolidate_users WHERE active=1 ORDER BY user_type";

Open in new window

The first record (if any) will be your preferred record.

One other small note... if you are using this for authentication, you should phrase the query like this:
SELECT username,user_type FROM consolidate_users WHERE username="My Name" AND password="encrypted_password"

Open in new window

That way, you're only pulling a matching record, and communication between the app and the database exposes as little as possible.
0
 

Author Comment

by:Johnny
Comment Utility
ok so how would i get the Company_name and Contact Name etc from appropriate tables and  the reference of UserTypeID description form the UserTypeID form EndUser along with the FirstName and LastName.
also how would i fire off consolidate_users to make my first temp table? - you know now that i look at it i think the for next loop maybe a better idea, heck i dont know... im still at a loss on how to check for login info then get the info i need - i had thought that i might check enduser first for match if 0 rows returned fire off next and so forth this was i didn't do joins and such but id like to understand the join and CORRECT way of doing this so if i run across it in the future i can, i also have a buncha tables in this like the UserTypeID i need to cross ref from table data so how to use join right would be nice too. i defer to see how this is done.

@Steve Bink seams to be exactly what im looking for. How would i get the rest of my info using your consolidate_users approach please. (im understanding the concept but i have never done temp tables before nor query such as these {complex}) thank you for your help so far

@Tomas Helgi that looked good till "Steve Bink" piped in. thank you for your reply

@PortletPaul i didn't know any info more then i posted i had no idea where to start at all hence me asking the question i looked up as much as i could and nothing seamed to fit correctly..again hence the post (thanks for pointing out it need more info)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"... i had no idea where to start ..."

and that is why I suggested the technique of "sample data" and "expected result". This technique requires less words and is also more accurate than explanation using words. Words are still needed, just less of them.

My problem is that while I see several potential joins, e.g.
Contact.ClientID joins to Client.ClientID
Client.EndUserID joins to EndUser.EndUserID
EndUser.UserTypeID join to UserType.UserTypeID

Without some sample data I cannot assess which is the best/correct type of join to apply. Without data you force us to guess (and guessing is not a great way to solve your problem).

So, here are 3 guesses on how to join your tables. I stress these are GUESSES
select
*
from client cl
left join Contact co on cl.ClientID = co.ClientID
left join EndUser eu on cl.EndUserID = eu.EndUserID
left join UserType ut on eu.UserTypeID = ut.UserTypeID
;

select
*
from EndUser eu
inner join UserType ut on eu.UserTypeID = ut.UserTypeID
left join client cl on eu.EndUserID = cl.EndUserID
left join Contact co on cl.ClientID = co.ClientID
;

select
*
from Contact co
inner join client cl on co.ClientID = cl.ClientID
left join EndUser eu on cl.EndUserID = eu.EndUserID 
left join UserType ut on eu.UserTypeID = ut.UserTypeID
;

Open in new window

{+ edit}
please note that while I have used select * in the above, that is only used to abbreviate the answer. You should not use "select *" in production code (and that includes php). Instead of "select *" you should list out the fields.
0
 

Author Comment

by:Johnny
Comment Utility
@PortletPaul my data is over 200 megs with over 10k lines of code its a database for a friend that someone else made, and unfortunately died. So my hands are tied and he does not want to change the data. i can only manipulate it.

i ran into a problem of how to get the logins to look up the matches across 3 tables and then get the appropriate items as needed. i figured from the table names and fields i needed was sufficient.

this is now becoming way to complicated and it looks like im just gonna lang do it, i figured this was something fairly simple for someone that knew join tables and i knew mysql could make use of id linking for data lookups. but it seams im gonna have to find tutorials or something as this is not looking like it easy.

i thought i was just missing something. sometimes i wonder why things are so complicated when they dont have to be.
with all that said
reason for this post is i know NOTHING about join for complex mysql calls, i am currently looking up the inner join and left join on what it actually does, a more leniently explanation would be fantastic please.

what info do you want for testing or something would you like me to call all the fields ive listed in each tables and make a sql file and post that here? with lets say 5 records each?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"sample data" does not have to be large
the real trick is that it requires you to make it "representative" (but I suspect that will be too hard right now)

so:  5 records from each table will be enough to get started.

-------------
joins are not that hard, you can be doing it without even realizing it
e.g. a letter from Bank X, account Y get filed to into the Bank X folder, account Y section

you have just "joined" correspondence to folder

If you are really at such a beginning stage with sql joins I suggest you try a tutorial
e.g. sqlzoo.net

0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
@Steve Bink seams to be exactly what im looking for. How would i get the rest of my info using your consolidate_users approach please. (im understanding the concept but i have never done temp tables before nor query such as these {complex}) thank you for your help so far
To get the extra info, just supply some joins, and a common field name.  You have ContactName for contacts, CompanyName for clients, and Description for end users.  If you use the loop strategy, your queries will look like this:
$queries = array(
                 "SELECT username, password, ContactName as ExtraInfo FROM contact WHERE active=1",
                 "SELECT username, password, CompanyName as ExtraInfo FROM client WHERE active=1",
                 "SELECT a.Login as username, a.Password as password, b.description as ExtraInfo 
                     FROM enduser a INNER JOIN usertype b on a.UserTypeID=b.UserTypeID 
                     WHERE a.active=1",
                );

Open in new window

If the contact and client tables actually need a join to get this data, use the enduser query as a template for how it should look.

If you think a view work better, then create the view like this:
CREATE VIEW consolidate_users AS
select username,password,active,1 as user_type,ContactName as ExtraInfo
from contact
union
select username,password,active,2 as user_type,CompanyName as ExtraInfo
from client
union
select a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from enduser a inner join usertype b on a.UserTypeID=b.UserTypeID;

Open in new window

A view is essentially a saved query.  When you query a view, MySQL runs the creation query you gave it, and uses those results as the data source for your current query.  See the MySQL docs for more information on how to use views, or the syntax for the CREATE VIEW statement.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@Steve Bink

While pursuing the unioned approach may indeed be worthwhile, please use UNION ALL instead of UNION.
(Now that each table has 1 or 2 or 3 as user_type, there can no longer be any purpose to union that is not achieved by union all)

@Pern

The reason for raising this point is that UNION attempts to ensure all rows are unique, but UNION ALL does not do this. So UNION ALL is faster.
0
 

Author Comment

by:Johnny
Comment Utility
Today I am really busy it being sunday. I will review and look stuff up to understand this.  Thx for the zoo link too.
0
 

Author Comment

by:Johnny
Comment Utility
@Steve Bink
i corrected the query so it reflected my table names (i also noticed a space in a lot of the fields like "Client Name" question is it ok to have white space in a field name? i try to use `` for field names.

CREATE VIEW consolidate_users AS
select username,password,active,1 as user_type,ContactName as ExtraInfo
from Contact
union
select username,password,active,2 as user_type,CompanyName as ExtraInfo
from Client
union
select a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;

Open in new window

the above using the querry
SELECT * FROM `consolidate_users` WHERE 1

Open in new window


returns
username		password	active	user_type	ExtraInfo
FRANK HUGG		[pw removed]	1	1		FRANK HUGG
AMY MARRON		[pw removed]	1	1		AMY MARRON
JAMES D. HOEY, III	[pw removed] 	1	1		JAMES D. HOEY, III
EANBAR			[pw removed]	1	1		ELIC ANBAR
ROXANNE LOPEZ		[pw removed]	1	1		ROXANNE LOPEZ
STEPHEN F. LOPEZ	[pw removed]	1	1		STEPHEN F. LOPEZ
STERLING J. STIRES	[pw removed]	1	1		STERLING J. STIRES
RICHARD DEVIRIAN	[pw removed]	1	1		RICHARD DEVIRIAN
RICK BARTON, ESQ.	[pw removed]	1	1		RICK BARTON, ESQ.
LTWITCHELL		[pw removed]	1	1		LAURIE TWITCHELL, ESQ.

Open in new window


im not understanding some thing here
3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;

Open in new window


shouldn't that give me extrainfo with the description of the EndUser.UserType??

and how would i get ContactID & ClientID from Contact table so i can use there info in there respective tables please
beyond that the username and pw is perfectly joined.
if i have the example above i think i can wrap my head around this. and if i can see the output as the table like i did in myphpadmin. (i guess i could use a few query example calls to understand it a bit more.

oh one other thing OH wow it actually makes a table - wow

Thank you so much for your help so far.

here is test data and table structure
CREATE TABLE IF NOT EXISTS `Client` (
  `ClientID` int(11) NOT NULL AUTO_INCREMENT,
  `CompanyName` varchar(50) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(15) DEFAULT NULL,
  `PaymentTierID` int(11) DEFAULT '0',
  `OrderingAddress` varchar(50) DEFAULT NULL,
  `Ordering City` varchar(50) DEFAULT 'CA',
  `Ordering State` varchar(2) DEFAULT NULL,
  `Ordering Zip` varchar(10) DEFAULT NULL,
  `Phone Number` varchar(20) DEFAULT NULL,
  `Fax Number` varchar(20) DEFAULT NULL,
  `Billing Address` varchar(50) DEFAULT NULL,
  `Billing City` varchar(50) DEFAULT NULL,
  `Billing State` varchar(2) DEFAULT 'CA',
  `Billing Zip` varchar(10) DEFAULT NULL,
  `Billing Phone Number` varchar(20) DEFAULT NULL,
  `Billing Fax Number` varchar(20) DEFAULT NULL,
  `SalesRepId` int(11) DEFAULT '0',
  `SalesTaxRateID` int(11) NOT NULL DEFAULT '0',
  `CompanyURL` varchar(255) DEFAULT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '0',
  `EndUserID` int(11) NOT NULL DEFAULT '0',
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ClientID`),
  KEY `ClientID` (`ClientID`),
  KEY `EndUserID` (`EndUserID`),
  KEY `PaymentTierID` (`PaymentTierID`),
  KEY `SalesRepId` (`SalesRepId`),
  KEY `SalesTaxRateID` (`SalesTaxRateID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2036 ;

INSERT INTO `Client` (`ClientID`, `CompanyName`, `username`, `password`, `PaymentTierID`, `OrderingAddress`, `Ordering City`, `Ordering State`, `Ordering Zip`, `Phone Number`, `Fax Number`, `Billing Address`, `Billing City`, `Billing State`, `Billing Zip`, `Billing Phone Number`, `Billing Fax Number`, `SalesRepId`, `SalesTaxRateID`, `CompanyURL`, `Active`, `EndUserID`, `Created`, `Modified`) VALUES
(1, 'HOEY & MORGAN', 'HOEY', 'xxxx', 1, '8910 UNIVERSITY CENTER LANE - STE. 425', 'SAN DIEGO', 'CA', '92122', '8585501002', '8585502043', '8910 UNIVERSITY CENTER LANE - STE. 425', 'SAN DIEGO', 'CA', '92122', '8585501002', '8585502043', 1, 8, '', 1, 9, '2004-11-02 21:37:01', '2004-12-09 15:23:04'),
(2, 'FRANK B. HUGG ESQ.', 'HUGG', 'xxxx', 1, '5810 TELEGRAPH AVENUE', 'OAKLAND', 'CA', '94609', '5105959575', '5105959525', '5810 TELEGRAPH AVENUE', 'OAKLAND', 'CA', '94609', '5105959575', '5105959525', 1, 8, '', 1, 23, '2004-11-05 17:43:34', '2008-06-12 20:10:21'),
(3, 'JOHN R. GARCIA ESQ.', '', 'xxxxx', 1, '3579 FIFTH AVE., STE. 100', 'SAN DIEGO', 'CA', '92103', '6192944040', '6192944174', '3579 FIFTH AVE., STE. 100', 'SAN DIEGO', 'CA', '92103', '6192944040', '', 1, 8, '', 1, 24, '2004-11-05 17:47:02', '2005-04-29 20:55:08'),
(4, 'WARD NORTH AMERICA', 'WNA', 'xxxxx', 1, '610 NORTH ASH SUITE 1900', 'SAN DIEGO', 'CA', '92101', '6195572777', '6195951689', '610 NORTH ASH SUITE 1900', 'SAN DIEGO', 'CA', '92101', '6195572777', '6195951689', 1, 8, '', 1, 9, '2004-11-05 17:49:12', '2004-11-05 17:49:12'),
(5, 'LINCOLN,  GUSTAFSON & CERCOS', 'LG&C', 'xxxxx', 1, '550 WEST C ST., #1400', 'SAN DIEGO', 'CA', '92101', '6192331150', '6192336949', '550 WEST C ST., #1400', 'SAN DIEGO', 'CA', '92101', '6192331150', '6192336949', 2, 8, '', 1, 23, '2004-11-05 14:02:18', '2014-08-19 16:20:19');


CREATE TABLE IF NOT EXISTS `Contact` (
  `ContactID` int(11) NOT NULL AUTO_INCREMENT,
  `ClientID` int(11) DEFAULT '0',
  `ContactName` varchar(50) NOT NULL,
  `EmailAddress` varchar(255) DEFAULT NULL,
  `Secretary Name` varchar(50) DEFAULT NULL,
  `EmailAddress2` varchar(255) DEFAULT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '0',
  `EndUserID` int(11) NOT NULL DEFAULT '0',
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(15) DEFAULT NULL,
  `Master` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ContactID`),
  KEY `ClientID` (`ClientID`),
  KEY `ContactID` (`ContactID`),
  KEY `EndUserID` (`EndUserID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4026 ;


INSERT INTO `Contact` (`ContactID`, `ClientID`, `ContactName`, `EmailAddress`, `Secretary Name`, `EmailAddress2`, `Active`, `EndUserID`, `Created`, `Modified`, `username`, `password`, `Master`) VALUES
(2, 2, 'FRANK HUGG', 'FHUGG@PACBELL.NET', 'SUSAN', '', 1, 9, '2004-11-05 17:43:59', '2007-02-14 12:12:27', 'FRANK HUGG', 'xxxxx', 1),
(8, 7, 'AMY MARRON', '', 'MERINN HURD', '', 1, 9, '2004-11-06 16:52:29', '2005-03-16 15:22:51', 'AMY MARRON', 'xxxxx', 0),
(18, 1, 'JAMES D. HOEY, III', '', 'DAVID PERKINS', '', 1, 9, '2004-11-07 17:02:57', '2007-02-14 11:49:02', 'JAMES D. HOEY, III', 'xxxxx', 0),
(26, 21, 'ELIC ANBAR', '', '', '', 1, 9, '2004-11-15 15:17:22', '2004-11-15 15:17:22', 'EANBAR', 'xxxxx', 0),
(37, 7, 'ROXANNE LOPEZ', '', 'MERINN HURD', '', 1, 9, '2004-11-22 22:32:36', '2005-03-16 15:22:08', 'ROXANNE LOPEZ', 'xxxxx', 0);

CREATE TABLE IF NOT EXISTS `EndUser` (
  `EndUserID` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL,
  `Login` varchar(50) NOT NULL,
  `Password` varchar(90) NOT NULL,
  `UserTypeID` int(11) NOT NULL DEFAULT '0',
  `Active` tinyint(1) NOT NULL DEFAULT '0',
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`EndUserID`),
  KEY `EndUserID` (`EndUserID`),
  KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;


INSERT INTO `EndUser` (`EndUserID`, `FirstName`, `LastName`, `Login`, `Password`, `UserTypeID`, `Active`, `Created`, `Modified`) VALUES
(9, 'NORMAL', 'NORMAL', 'ADMIN', 'xxx', 0, 1, '2004-06-10 23:01:02', '2005-04-11 19:31:08'),
(10, 'KAREN', 'MOORE', 'ORDER ENTRY', 'xxxxx', 2, 0, '2004-06-10 23:07:01', '2005-04-12 01:25:39'),
(11, 'Normal', 'User', 'User', 'xxxx', 1, 1, '2004-06-10 23:07:13', '2004-06-18 22:11:00'),
(22, 'BRAD', 'VEACH', 'BRAD', 'xxxxx', 0, 0, '2005-04-12 01:22:49', '2005-04-12 01:24:46'),
(23, 'CHERYLE', 'ONG', 'CHERYLE ONG', 'xxxxx', 0, 1, '2005-04-12 01:25:24', '2007-11-30 22:11:18');

CREATE TABLE IF NOT EXISTS `UserType` (
  `UserTypeID` int(11) NOT NULL DEFAULT '0',
  `Description` varchar(50) DEFAULT NULL,
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`UserTypeID`),
  KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `UserType` (`UserTypeID`, `Description`, `Created`, `Modified`) VALUES
(0, 'Admin', '2004-06-10 23:05:47', '2004-06-10 23:06:01'),
(1, 'Normal User', '2004-06-10 23:06:19', '2004-06-10 23:06:35'),
(2, 'Order Entry', '2004-06-10 23:06:07', '2004-06-10 23:06:19');

Open in new window


@PortletPaul i changed the UNION to UNION ALL it didnt seam to change much.
Thank you too for all your help so far
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
(i also noticed a space in a lot of the fields like "Client Name" question is it ok to have white space in a field name? i try to use `` for field names.
You should always try to avoid whitespace in database object names.  It makes things more complicated, and prone to error.  If you do have field names with spaces in them, encapsulating them in backtick (`) characters will allow MySQL to parse them properly.
3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;

Open in new window

shouldn't that give me extrainfo with the description of the EndUser.UserType??
Sort of...  The ExtraInfo field will be populated with UserType.description, after having been joined to the table EndUser.  That information only exists for records not found in Contact or Client.  It is important to keep in mind that ExtraInfo is context-sensitive field.  It's purpose will change depending on the value of consolidated_users.user_type (either 1, 2, or 3).  
and how would i get ContactID & ClientID from Contact table so i can use there info in there respective tables please
Which fields from which tables would you like to have present in the results?  Remember that, in a UNION query, all source queries must have the same number of fields.  So, adding, for example, ClientID into one of the source queries, we'll need to add, at the least, a placeholder field with an arbitrary NULL value in the other source queries.  For example, this query adds ContactID, but it is only populated with anything useful if user_type=1:
CREATE VIEW consolidate_users AS
select ContactID,username,password,active,1 as user_type,ContactName as ExtraInfo
from Contact
union
select NULL as ContactID,username,password,active,2 as user_type,CompanyName as ExtraInfo
from Client
union
select NULL as ContactID,a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;

Open in new window

0
 

Author Comment

by:Johnny
Comment Utility
@Steve Bink
ok this is where my problem is and im confused. the rest makes a little sense now ill have to play with it to understand it.

 It's purpose will change depending on the value of consolidated_users.user_type (either 1, 2, or 3).  
ok so how would i get user type id 2 and 3 and how is it only getting 1
and FYI i need it to look at ALL usernames/pws and then give me back the relevant data

im trying to login in with either client,contact,enduser(admin/worker) and assign them an the id (so i can look up info later in each table like invoices for clients and sales for end user(worker), im trying to assign the id needed for that and assign them (admin rights or client/contact rights) as sessions vars this way i can then if is usertype then look in this data table for info type thing or so is my plan. Sorry i guess i was not clear on that)

so how do i get this table to show me the joined info so i can do that please.
BTW i think i learned more about this then i ever could (again thanks, i cant say enoph how great this is)
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 300 total points
Comment Utility
Don't try to do too much at once.  The purpose of this UNION query is to verify login information.  You need to make sure that the user credentials are valid, and what type of user they are.  Thus, the results of this query should be limited to that task, especially considering your users type have rather disparate object models.  When you get around to actually using the user's information, you'll be expecting different information from each type of user.

When you get the results back, you should check for the first record - that should be your preferred user account.  Inside that record will be data points for login name, the user type, and the ExtraInfo we added most recently.  Check the user_type field to determine the type of user logging in, then execute a second query to pull any information you need specifically for that user type.  Some possible PHP:
$query="SELECT UserID,username,user_type,ExtraInfo FROM consolidated_users WHERE active=1 and username='myuser' and password='encrypted_password'";
if (!($result=$dbconn->query($query)) || $result->row_count < 1) {
  // no records returned, or an error.  Fail to log in
  die('failed to log in');
}
// grab the user login record
$row=$result->fetch_assoc();
// now search for the actual user record
$user_tables = array(1=>'Contact', 2=>'Client', 3=>'EndUser');
$query = "SELECT * FROM " .$user_tables[$row['user_type']]. " WHERE blahblahblah";

Open in new window

As I said in my last post, you can add the ID field into the join query so you can pick out by the PK.  To do it for all three tables (remember, we want a common field name to reference):
CREATE VIEW consolidate_users AS
select ContactID as UserID,username,password,active,1 as user_type,ContactName as ExtraInfo
from Contact
union
select ClientID as UserID,username,password,active,2 as user_type,CompanyName as ExtraInfo
from Client
union
select EndUserID as UserID,a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;

Open in new window

0
 

Author Comment

by:Johnny
Comment Utility
@ Steve Bink

We do not seam to be on the same page here.

The user_type needs to be the usertype more then 1 it needs to be :
CREATE TABLE IF NOT EXISTS `UserType` (
  `UserTypeID` int(11) NOT NULL DEFAULT '0',
  `Description` varchar(50) DEFAULT NULL,
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`UserTypeID`),
  KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `UserType` (`UserTypeID`, `Description`, `Created`, `Modified`) VALUES
(0, 'Admin', '2004-06-10 23:05:47', '2004-06-10 23:06:01'),
(1, 'Normal User', '2004-06-10 23:06:19', '2004-06-10 23:06:35'),
(2, 'Order Entry', '2004-06-10 23:06:07', '2004-06-10 23:06:19');

Open in new window


the table created only has type id 1's info i need both 2 and 0 as well


i also need to know it its from table Client,contact,or enduser or i cant call the relevant data to each table right? to know like the clients address info etc

Sorry im all new to this, im understanding it tho i think.

once i have the referance table with the user name,pw, user type, user_id, and database type(client,contact,enduser)
id simply call it like you gave me as if its its own table then use its id and ref table to pull the data. this will work out great if the references are all there
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>" i changed the UNION to UNION ALL it didnt seam to change much."
EXACTLY!

The query you are trialling does NOT BENEFIT from using union!

union all will be FASTER
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
the table created only has type id 1's info i need both 2 and 0 as well

We're not on the same page, and it is really my fault for re-using a term from your existing schema.  The usertype field in my example queries does not relate to your existing UserTypeID.  Instead, think of my version as a "user category" or "user class", meaning it signifies only if the discovered login belongs to a contact, client, or enduser record.  It is a separate and distinct data point.

Also, PortletPaul is correct - UNION ALL will be faster than UNION.  You likely will not see much benefit until your record count is a little higher, though.  As you can see in this example, UNION can be about 3-1/2 times slower than UNION ALL.
0
 

Author Comment

by:Johnny
Comment Utility
@Steve Bink

if the discovered login belongs to a contact, client, or enduser record
ok then why are all my returned items in row user_type all 1 and i do not have any from enduser for data at all
i gave you the test data above please try and run the query your self maybe im missing something but i dont think so

i currently can not distinguish between Client,Contact,EndUser (i assume as there all 1 in the user_type field)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
see http://sqlfiddle.com/#!9/72b47/1 the view proposed by Steve (but using UNION ALL)  provides records from all 3 source tables (under the heading "USER_TYPE")
| USERID |           USERNAME | PASSWORD | ACTIVE | USER_TYPE |                    EXTRAINFO |
|--------|--------------------|----------|--------|-----------|------------------------------|
|      2 |         FRANK HUGG |    xxxxx |      1 |         1 |                   FRANK HUGG |
|      8 |         AMY MARRON |    xxxxx |      1 |         1 |                   AMY MARRON |
|     18 | JAMES D. HOEY, III |    xxxxx |      1 |         1 |           JAMES D. HOEY, III |
|     26 |             EANBAR |    xxxxx |      1 |         1 |                   ELIC ANBAR |
|     37 |      ROXANNE LOPEZ |    xxxxx |      1 |         1 |                ROXANNE LOPEZ |
|      1 |               HOEY |     xxxx |      1 |         2 |                HOEY & MORGAN |
|      2 |               HUGG |     xxxx |      1 |         2 |           FRANK B. HUGG ESQ. |
|      3 |                    |    xxxxx |      1 |         2 |          JOHN R. GARCIA ESQ. |
|      4 |                WNA |    xxxxx |      1 |         2 |           WARD NORTH AMERICA |
|      5 |               LG&C |    xxxxx |      1 |         2 | LINCOLN,  GUSTAFSON & CERCOS |
|      9 |              ADMIN |      xxx |      1 |         3 |                        Admin |
|     10 |        ORDER ENTRY |    xxxxx |      0 |         3 |                  Order Entry |
|     11 |               User |     xxxx |      1 |         3 |                  Normal User |
|     22 |               BRAD |    xxxxx |      0 |         3 |                        Admin |
|     23 |        CHERYLE ONG |    xxxxx |      1 |         3 |                        Admin |

Open in new window

0
 

Author Comment

by:Johnny
Comment Utility
well dont i feel stupid!!
i re-looked at the setup and found that myphpadmin was limited by default to 500 rows thus in my almost 6k records not pulling them all thus not having the admin (end users) at the end. so now that i have the correct info

SELECT * FROM `consolidate_users`LIMIT 0, 7000

Open in new window

did the trick.
so id just take the USER_TYPE and that be my table to use..OK now i get it light bulb!!

ok im so grateful you all didn't call me stupid...

@PortletPaul is it ok if i give you 200 points, as you assisted in the solution and Steve Bink gave complete code and examples solution
ill wait till tomorrow to accept but i believe this is now answered, unless you answer before

@Steve Bink Thank you in all that i can thank you!!
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
Comment Utility
it is fine by me, as long as you agree to use UNION ALL! :) really, you should use it.

It was Steve who proposed the fundamental approach.
0
 

Author Comment

by:Johnny
Comment Utility
@PortletPaul
i used your pull but ill redo it to make sure later i still need to play a bit to pull info...
thank you again
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now