Solved

issue with mysql query

Posted on 2012-12-22
6
272 Views
Last Modified: 2012-12-24
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

0
Comment
Question by:prowebinteractiveinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38716958
It seems like the WHERE clause needs additional clarification, maybe testing userStatusDesc or userAccess columns.  You might also consider using an ORDER BY clause.  And if you're checking for only one client record at the time of login, the LIMIT1 will speed up the query.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38717427
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 ?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38717541
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:prowebinteractiveinc
ID: 38718731
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
0
 

Author Comment

by:prowebinteractiveinc
ID: 38718765
its all good, I figured it out... there was a very small mistake in the query
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38718794
Great, glad you got it sorted out.  Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP: Filling Out/Creating a PDF 29 94
How Close unsubmited attempts 10 42
Increase counter and attr inside a while loop 15 34
What is define("__APPROOT__", __DIR__); 6 28
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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

735 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