Avatar of prowebinteractiveinc
prowebinteractiveinc
 asked on

issue with mysql query

Im working on a login script Im having alittle issue with the query when it comes to validate the status of the account & the user that is logging in that is associated with the account. (accounts can have many users)

I created a table for systemStatus since there will be many different statuses. Im joining the tables with a LEFT JOIN 2x as I need to make sure the account is active and the user is active. my query below for some reason is returning 2 results. Im pretty sure because systemStatus has 2 rows. view attached image... the second result is all Im expecting to get

query result
DB Structure
-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Dec 22, 2012 at 09:43 PM
-- Server version: 5.0.95
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `admin_iPrint`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts`
--

CREATE TABLE IF NOT EXISTS `accounts` (
  `accountId` int(9) unsigned NOT NULL auto_increment,
  `accountOpenDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `accountStatusId` int(3) NOT NULL,
  `companyName` blob NOT NULL,
  `referredBy` blob NOT NULL,
  PRIMARY KEY  (`accountId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `systemStatus`
--

CREATE TABLE IF NOT EXISTS `systemStatus` (
  `statusId` int(3) unsigned NOT NULL auto_increment,
  `statusDescription` varchar(255) NOT NULL,
  `allowAccess` varchar(5) NOT NULL default 'false',
  PRIMARY KEY  (`statusId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(9) unsigned NOT NULL auto_increment,
  `accountId` int(9) NOT NULL,
  `accountStatusId` int(3) NOT NULL,
  `userAddedDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `userPositionId` int(3) NOT NULL,
  `firstName` varchar(35) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `userEmail` varchar(100) NOT NULL,
  `username` varchar(35) NOT NULL,
  `password` blob NOT NULL,
  `preferredLanguageId` int(2) NOT NULL,
  PRIMARY KEY  (`userId`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

Open in new window


Current query
SELECT a.companyName, u.userId, u.firstName, u.lastName, u.username, u.password, s.statusDescription AS companyStatusDesc, s.allowAccess AS companyAccess, ss.statusDescription AS userStatusDesc, ss.allowAccess AS userAccess 
									   FROM users AS u 
									   LEFT JOIN accounts AS a
									   ON u.accountId=a.accountId
									   LEFT JOIN systemStatus AS s
									   ON a.accountStatusId=s.statusId
									   LEFT JOIN systemStatus AS ss
									   ON u.accountStatusId=s.statusId
									   WHERE u.username ='" . $_SERVER['username'] . "'

Open in new window

PHPSQL

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
prowebinteractiveinc

ASKER
by using limit1 how will I be garantied to get the result I want from the image above. is joining the same table twice what I need to do in this case ?
Ray Paseur

LIMIT alone will not guarantee anything except a reduced number of rows in the results set.  It's purpose is to reduce the number of rows returned in the query results set, thus improving the performance.  The important strategic elements are in the WHERE and ORDER clauses, and maybe the GROUP.  In these clauses your objective is to cause the "right" answer to percolate up to the top.
prowebinteractiveinc

ASKER
so can you help me with the query that will give me the right result. Im looking for the second result to be the only result in the image I have attached above

Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
prowebinteractiveinc

ASKER
its all good, I figured it out... there was a very small mistake in the query
Ray Paseur

Great, glad you got it sorted out.  Thanks for the points and thanks for using EE, ~Ray