troubleshooting Question

issue with mysql query

Avatar of prowebinteractiveinc
prowebinteractiveinc asked on
PHPSQL
6 Comments1 Solution307 ViewsLast Modified:
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 ;

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'] . "'
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros