...

View Full Version : Easy way to list users with PHP & MySQL



Anthony2oo4
12-16-2010, 03:40 PM
Iv developed a system but cant seem to figure out what seems to be a simple thing.

I am trying to normalise the database so that every time a record is inserted, it has the users ID who inserted it, rather than having his name entered every time.

Simple enough, done that.

My problem is when it comes to listing the records. How do I get the name of the user by their ID. Obviously I can do the following:


SELECT `FirstName`, `SecondName` FROM `Users` WHERE `UserID` = 1

But I don't want to do that for every record that is displayed in the loop. Especially when the same user may have more than one record, that means I will be querying their name more than once.

I was thinking of querying them all once at the start into an array, but I don't know how to match them up. Can anyone help me with the best way to do this please?

Any help is greatly appreciated.

Thank you.

Fumigator
12-16-2010, 03:52 PM
You can use a JOIN query. I'm assuming these records you're talking about are coming from some other table, not the users table? Join that table with the users table:



SELECT a.somedata, a.otherdata, b.firstname, b.lastname
FROM maintable as a
JOIN users as b
ON a.userid = b.userid

Anthony2oo4
12-16-2010, 03:59 PM
You can use a JOIN query. I'm assuming these records you're talking about are coming from some other table, not the users table? Join that table with the users table:



SELECT a.somedata, a.otherdata, b.firstname, b.lastname
FROM maintable as a
JOIN users as b
ON a.userid = b.userid


Yes they are coming from another table called Records. I completely forgot about that join. Il have a play with that now.

Thanks very much for your help.

Anthony2oo4
12-21-2010, 11:23 AM
Hi again,

Im still stuck on this problem, I think I might need a Left join. Iv beeen reading up on them and cant seemt o get my head around it. I used the following query:


SELECT a.*, b.FirstName, b.SecondName
FROM Record as a
JOIN Users as b
ON a.UserID = b.UserID

Following your example, that resulted in the following output:

http://i52.tinypic.com/2zz6sz6.png

That information is great but I need contact ID to link with the Contacts table and get Contacts.FirstName and Contacts.SecondName if possible.

Can anyone tell me how to do this ? I tried the following:


SELECT a.*, b.FirstName, b.SecondName
FROM Record as a
JOIN Users as b
ON a.UserID = b.UserID, a.ContactID = b.ContactID

But to no avail.

Thank you very much for your time and help.

***** EDIT *****

I just had a brain wave, and came up with the following:


SELECT a.*, b.FirstName, b.SecondName, c.*
FROM Record as a
JOIN Users as b
JOIN Contacts as c
ON a.UserID = b.UserID
AND a.ContactID = c.ContactID

Which is recognised as a valid query, but returns no results. I think its because of the AND statement. Here is the structure for the 3 tables in question:


CREATE TABLE IF NOT EXISTS `Contacts` (
`ContactID` int(11) NOT NULL auto_increment,
`FirstName` text NOT NULL,
`SecondName` text NOT NULL,
`Position` text NOT NULL,
`Intrests` text NOT NULL,
`Address1` text NOT NULL,
`Address2` text NOT NULL,
`Address3` text NOT NULL,
`PostCode` text NOT NULL,
`MobileNumber` text NOT NULL,
`HomeNumber` text NOT NULL,
`WorkNumber` text NOT NULL,
`WorkExtension` text NOT NULL,
`CompanyID` int(5) NOT NULL,
PRIMARY KEY (`ContactID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

CREATE TABLE IF NOT EXISTS `Record` (
`RecordID` int(4) NOT NULL auto_increment,
`CustomerID` int(11) NOT NULL,
`UserID` int(11) NOT NULL,
`Date` datetime NOT NULL,
`CallReport` text NOT NULL,
`ContactID` int(11) NOT NULL,
PRIMARY KEY (`RecordID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

CREATE TABLE IF NOT EXISTS `Users` (
`UserID` int(11) NOT NULL auto_increment,
`Username` text NOT NULL,
`Password` text NOT NULL,
`FirstName` text NOT NULL,
`SecondName` text NOT NULL,
`EMail` text NOT NULL,
`MobileNumber` text NOT NULL,
`AccessLevel` int(1) NOT NULL,
`Strikes` int(1) NOT NULL,
`Menu` text NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Fumigator
12-21-2010, 09:27 PM
Do you want to see the first/last name of the user AND the first/last name of the contact? If so, you are very close, syntax is just not quite right. You have to tell each JOIN what to join on separately.



SELECT a.*, b.FirstName, b.SecondName, c.*
FROM Record as a
JOIN Users as b
ON a.UserID = b.UserID
JOIN Contacts as c
ON a.ContactID = c.ContactID


Keep in mind, when joining 3 tables like this, there MUST be a matching row in all three tables for any result to be returned. So if the row in "record" contains a user_id of 1, and a contact_id of 0, but there is no row in "contacts" with a contract_id of 0, then the row from record will not get included in the resultset.

There is a way around this, and you mentioned it already-- the left (or left outer) join. Left joins simply say "I will return all rows on the left side, even if there is no match from the table on the right side". With three tables, you look at the JOIN condition to determine which table goes in the left side and which in the right side. So if you were to do this:



SELECT a.*, b.FirstName, b.SecondName, c.*
FROM Record as a
JOIN Users as b
ON a.UserID = b.UserID
LEFT JOIN Contacts as c
ON a.ContactID = c.ContactID


Now you are still requiring a match between "record" and "users", but since you are LEFT joining "contacts", the "record" row will be included even if there is no match between "record" and "contacts". (The data you select from "contacts" will be NULL if there is no match.)

Anthony2oo4
12-22-2010, 10:10 AM
Fumigator thank you very much for your help. Even though I didn't use your query, your explanation on Joins and Left Join helped me a lot in understanding how they work.

I was recommended to use QuerySmith (http://www.powdersoftware.com/querysmith/) by a friend, which sorted it out for me. Although im not normally a fan of using such programs I must admit it saved me a lot of time, and I will be purchasing a license for it.

It created the following query for me:


select
C.ContactID As ContactID,
D.CustomerName As CustomerName,
A.`Date` As `Date`,
A.CallReport As CallReport,
B.FirstName As U_FirstName,
B.SecondName As U_SecondName,
C.FirstName As C_FirstName,
C.SecondName As C_SecondName
from
( ( ( cssphone_Sales.Record A
inner join cssphone_Sales.Users B on B.UserID = A.UserID )
inner join cssphone_Sales.Contacts C on C.ContactID = A.ContactID )
inner join cssphone_Sales.Customers D on A.CustomerID = D.CustomerID )
where
A.CustomerID =8
order by
A.`Date` desc

Thank very much to everyone who helped.

Regards

Fumigator
12-22-2010, 04:11 PM
Ah yes, I use a similar tool at my workplace; these tools do make life simpler. Just a word of advice-- make sure you understand how the queries work. It's a real beast trying to debug a query you don't understand :thumbsup:

(oh and p.s., every technical interview I've ever had unfailingly includes the question "What's the difference between an inner and outer join?" So if you ever find yourself interviewing for an IT job, make sure you know the answer)

Anthony2oo4
12-22-2010, 04:39 PM
Funny you should mention that, I'm going for one in 2 weeks time ! Thanks very much, il get that learned now :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum