Page 1 of 1

help with a select

Posted: Wed Mar 30, 2005 1:34 am
by pelegk2
i am making this select :

Code: Select all

SELECT * from order_header,users WHERE 
order_id='544' AND firm_id=2 AND 
(users.user_name='peleg' AND find_in_set('12',users.dep_list)) ORDER BY order_id DESC

the thing is that i make a search here on an order_id that another user have made, but i check if my user have prviliges
to access this order_id :
(users.user_name='peleg' AND find_in_set('12',users.dep_list))


the thing is that the query is ok but the name that is return
from the users table is mine and the the original user that made the order!!!
how do i fix it!
thnaks in advance
PEleg

Posted: Wed Mar 30, 2005 10:05 am
by feyd
I don't understand. :?

ok i explain

Posted: Thu Mar 31, 2005 2:50 am
by pelegk2
i have this select :

Code: Select all

SELECT * from order_header,users WHERE order_id='544' AND firm_id=2 AND (users.user_name='peleg' AND find_in_set('12',users.dep_list)) ORDER BY order_id DESC
i want to check if the user "peleg" has the authorization to watch department 12 in this code!
i recive the result and everything is ok!
the point is that althought i make the select on the order_id from the order's table, i want to recive the name in hebrew from the users table,of the person that originally has written the order, but i recive the hebrew name of "peleg".

i hope its more clear now.
thnaks in advance
peleg

Posted: Thu Mar 31, 2005 3:00 am
by feyd
post the SQL export of the two tables.

here it is

Posted: Thu Mar 31, 2005 3:14 am
by pelegk2

Code: Select all

# MySQL-Front Dump 1.22
#
# Host: uniapp3 Database: purchise2005
#--------------------------------------------------------
# Server version 4.0.13-nt


#
# Table structure for table 'order_header'
#

CREATE TABLE /*!32300 IF NOT EXISTS*/ order_header (
  order_id int(6) unsigned NOT NULL auto_increment,
  date int(11) unsigned ,
  done_date int(11) unsigned ,
  vendor text ,
  tel varchar(11) ,
  fax varchar(11) ,
  price_num varchar(10) ,
  cont_person varchar(30) ,
  firm_id tinyint(2) unsigned ,
  dep_id tinyint(3) unsigned ,
  aim_id tinyint(3) unsigned ,
  user_name varchar(10) ,
  payment_cond text ,
  descr text ,
  comment text ,
  our_comment text ,
  sum float ,
  sum_invoice float ,
  tax enum('1','0') DEFAULT '0' ,
  budget_id int(6) unsigned NOT NULL DEFAULT '0' ,
  budget_parent int(6) unsigned ,
  acc_mankal enum('0','1','2') NOT NULL DEFAULT '0' ,
  acc_hashav enum('0','1','2') NOT NULL DEFAULT '0' ,
  acc_minahel enum('0','1','2') NOT NULL DEFAULT '0' ,
  status int(4) unsigned NOT NULL DEFAULT '0' ,
  cancell tinyint(1) unsigned zerofill NOT NULL DEFAULT '0' ,
  send tinyint(1) unsigned zerofill NOT NULL DEFAULT '0' ,
  closed enum('1','0') DEFAULT '0' ,
  clause_owner varchar(10) ,
  clause_parent_owner varchar(10) ,
  lastapproved varchar(10) ,
  PRIMARY KEY (order_id),
  UNIQUE order_id (order_id),
  INDEX order_id_2 (order_id)
);



#
# Table structure for table 'users'
#

CREATE TABLE /*!32300 IF NOT EXISTS*/ users (
  user_id tinyint(3) unsigned NOT NULL auto_increment,
  user_name varchar(10) ,
  user_name_heb varchar(30) ,
  user_pass varchar(10) ,
  firm_list varchar(20) ,
  level enum('1','0') NOT NULL DEFAULT '1' ,
  is_mankal enum('1','0') DEFAULT '0' ,
  is_hashav enum('1','0') DEFAULT '0' ,
  dep_list text ,
  signature varchar(20) ,
  tel varchar(12) ,
  tools varchar(50) ,
  invoice_access enum('1','0') DEFAULT '0' ,
  PRIMARY KEY (user_id),
  UNIQUE user_id (user_id),
  INDEX user_id_2 (user_id)
);

feyd | :?

Posted: Thu Mar 31, 2005 3:37 am
by feyd
you have a user_name field in the order_header table.. you can match that to a user.. although I'd suggest storing only a user_id reference in the table..