Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post

    Easy way to list users with PHP & MySQL

    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:

    PHP Code:
    SELECT `FirstName`, `SecondNameFROM `UsersWHERE `UserID` = 
    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.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:

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

  • Users who have thanked Fumigator for this post:

    Anthony2oo4 (12-21-2010)

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Fumigator View Post
    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:

    Code:
    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.

  • #4
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    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:

    PHP Code:
    SELECT a.*, b.FirstNameb.SecondName
    FROM Record 
    as a
    JOIN Users 
    as b
    ON a
    .UserID b.UserID 
    Following your example, that resulted in the following output:



    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:

    PHP Code:
    SELECT a.*, b.FirstNameb.SecondName
    FROM Record 
    as a
    JOIN Users 
    as b
    ON a
    .UserID b.UserIDa.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:

    PHP Code:
    SELECT a.*, b.FirstNameb.SecondNamec.*
    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:

    PHP Code:
    CREATE TABLE IF NOT EXISTS `Contacts` (
      `
    ContactIDint(11NOT NULL auto_increment,
      `
    FirstNametext NOT NULL,
      `
    SecondNametext NOT NULL,
      `
    Positiontext NOT NULL,
      `
    Intreststext NOT NULL,
      `
    Address1text NOT NULL,
      `
    Address2text NOT NULL,
      `
    Address3text NOT NULL,
      `
    PostCodetext NOT NULL,
      `
    MobileNumbertext NOT NULL,
      `
    HomeNumbertext NOT NULL,
      `
    WorkNumbertext NOT NULL,
      `
    WorkExtensiontext NOT NULL,
      `
    CompanyIDint(5NOT NULL,
      
    PRIMARY KEY  (`ContactID`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=;

    CREATE TABLE IF NOT EXISTS `Record` (
      `
    RecordIDint(4NOT NULL auto_increment,
      `
    CustomerIDint(11NOT NULL,
      `
    UserIDint(11NOT NULL,
      `
    Datedatetime NOT NULL,
      `
    CallReporttext NOT NULL,
      `
    ContactIDint(11NOT NULL,
      
    PRIMARY KEY  (`RecordID`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

    CREATE TABLE IF NOT EXISTS `Users` (
      `
    UserIDint(11NOT NULL auto_increment,
      `
    Usernametext NOT NULL,
      `
    Passwordtext NOT NULL,
      `
    FirstNametext NOT NULL,
      `
    SecondNametext NOT NULL,
      `
    EMailtext NOT NULL,
      `
    MobileNumbertext NOT NULL,
      `
    AccessLevelint(1NOT NULL,
      `
    Strikesint(1NOT NULL,
      `
    Menutext NOT NULL,
      
    PRIMARY KEY  (`UserID`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=
    Last edited by Anthony2oo4; 12-21-2010 at 10:32 AM.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

    Code:
    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:

    Code:
    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.)

  • Users who have thanked Fumigator for this post:

    Anthony2oo4 (12-22-2010)

  • #6
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    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 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:

    PHP Code:
    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
    .`Datedesc 
    Thank very much to everyone who helped.

    Regards

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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

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

  • Users who have thanked Fumigator for this post:

    Anthony2oo4 (12-22-2010)

  • #8
    Regular Coder
    Join Date
    Jul 2004
    Posts
    201
    Thanks
    8
    Thanked 1 Time in 1 Post
    Funny you should mention that, I'm going for one in 2 weeks time ! Thanks very much, il get that learned now


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •