Backend admin Restriction based on mysql row value

LegionSmith

Hi there,

I'm trying to create a backend admin whereby certain created admins only have access to certain data. basically here's the rundown.

End users fill out a profile form with their information. in this information is a Post number. like 1234.
On the backend we want to allow the Commanders of the various Posts to be able to see the profiles of those belonging to their Post. but not members of other posts.
So I've tried to set this up based on the Post number supplied by the end user and the post numbers applied to the various commanders. Basically I'm trying to filter the data based on the Post number matching between profile users and commanders. Currently I'm trying to use a session variable on the backend login that is equal to the [post] row and then filtering by that session on that backend:

SELECT profile.*, users.post
FROM profile, users
WHERE profile.post = totalRows

TotalRows is set to a value of $SESSION['sessPost'] and $SESSION['sessPost'] is equal to the post row on the front side.
Not sure what I'm missing here, but my dataset is showing me all profile users with post numbers matching the two backend users, basically I'm getting all of the post 103 and all of the post 211 profiles at the same time, when I should only be getting one post..

Hope that makes sense! Thanks for your time!
Michael Smith

paulnaj

Hi,

You are not 'joining' the two tables (profile, users) together via a common field. That means that ...

SELECT profile.*, users.post
FROM profile, users

... will contain (number of rows in profile) * (number of rows in users) records.

You will need either a JOIN clause or an extra WHERE clause (AND).

You might realise what to do now anyway, but if not post up your table definitions.

LegionSmith

Yeah, I've been thinking I needed an extra AND but what to do next totally escapes me. I have one table called profile which contains all the end user information posted by form. The other table is called users which just contains the login info for the admins. I'm attaching the structure, I know it's probably not perfect but it works.

I'm still clueless on exactly what to do. I can't have commanders at one post seeing the info of people not in their post. Any further help is truly appreciated.
-Michael

-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- http://www.phpmyadmin.net
-- 
-- Host: localhost:3306
-- Generation Time: Jun 19, 2009 at 11:12 AM
-- Server version: 4.1.20
-- PHP Version: 5.2.6
-- 
-- Database: `profile_form`
-- 

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

-- 
-- Table structure for table `profile`
-- 

CREATE TABLE `profile` (
  `sql_id` int(11) NOT NULL auto_increment,
  `username` varchar(40) NOT NULL default '',
  `password` varchar(16) NOT NULL default '',
  `legion_id` varchar(11) NOT NULL default '',
  `first_name` varchar(45) NOT NULL default '',
  `middle_i` varchar(3) default NULL,
  `last_name` varchar(45) NOT NULL default '',
  `address_1` varchar(45) NOT NULL default '',
  `address_2` varchar(45) default NULL,
  `city` varchar(20) NOT NULL default '',
  `state` varchar(20) NOT NULL default '',
  `zip` varchar(15) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `cell` varchar(15) default NULL,
  `email` varchar(45) default NULL,
  `post` varchar(4) NOT NULL default '',
  `birth_date` varchar(8) NOT NULL default '',
  `membership_status` enum('annual_dues','co_life_member','national_pufl_member','post_honorary_member') NOT NULL default 'annual_dues',
  `branch` enum('air_force','army','coast_guard','marines','navy') NOT NULL default 'air_force',
  `dates_of_service` enum('ww2','korea','vietnam','grenada','panama','gulf/terror') NOT NULL default 'ww2',
  `date_joined` varchar(15) NOT NULL default '',
  `cont_years` varchar(20) NOT NULL default '',
  `social_entertainment` varchar(5) default NULL,
  `social_post_officer` varchar(5) default NULL,
  `social_committees` varchar(5) default NULL,
  `social_honor_guard` varchar(5) default NULL,
  `social_employed_full_time` varchar(5) default NULL,
  `social_semi_retired` varchar(5) default NULL,
  `social_fully_retired` varchar(5) default NULL,
  `programs_legion_baseball` varchar(5) default NULL,
  `programs_oratorical` varchar(5) default NULL,
  `programs_boys_nation` varchar(5) default NULL,
  `programs_scouting` varchar(5) default NULL,
  `programs_shooting` varchar(5) default NULL,
  `youth_safety` varchar(5) default NULL,
  `youth_halloween` varchar(5) default NULL,
  `youth_miracle` varchar(5) default NULL,
  `youth_welfare` varchar(5) default NULL,
  `youth_financial` varchar(5) default NULL,
  `security_fund` varchar(5) default NULL,
  `va_volunteer` varchar(5) default NULL,
  `va_committee` varchar(5) default NULL,
  `va_service_officer` varchar(5) default NULL,
  `riders_scholarship` varchar(5) default NULL,
  `riders_membership` varchar(5) default NULL,
  `sons_membership` varchar(5) default NULL,
  `sons_activities` varchar(5) default NULL,
  `sons_welfare` varchar(5) default NULL,
  `skills_accounting` varchar(5) default NULL,
  `skills_sales` varchar(5) default NULL,
  `skills_technical` varchar(5) default NULL,
  `skills_administration` varchar(5) default NULL,
  `skills_hardware` varchar(5) default NULL,
  `skills_software` varchar(5) default NULL,
  `skills_legal` varchar(5) default NULL,
  `skills_relations` varchar(5) default NULL,
  `trades_electrical` varchar(5) default NULL,
  `trades_carpenter` varchar(5) default NULL,
  `trades_hvac` varchar(5) default NULL,
  `trades_concrete` varchar(5) default NULL,
  `trades_construction` varchar(5) default NULL,
  `trades_mechanical` varchar(5) default NULL,
  `trades_landscaping` varchar(5) default NULL,
  `trades_plumber` varchar(5) default NULL,
  `skills` varchar(255) default NULL,
  `comments` varchar(255) default NULL,
  `other` varchar(255) default NULL,
  PRIMARY KEY  (`sql_id`),
  KEY `post` (`post`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=86 ;

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

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

CREATE TABLE `users` (
  `sql_id` int(11) NOT NULL auto_increment,
  `password` varchar(40) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `post` varchar(4) NOT NULL default '',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`post`),
  KEY `sql_id` (`sql_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;