join problem

hi,
i created 2 tables and i want to fetch name from t_user having sys_pk

CREATE TABLE t_directories (
sys_pk int(11) NOT NULL auto_increment,
sys_del char(1) NOT NULL default 'f',
sys_state tinyint(2) NOT NULL default '0',
sys_dlm timestamp(14) NOT NULL,
sys_klm int(11) NOT NULL default '0',
sys_doc timestamp(14) NOT NULL,
sys_koc int(11) NOT NULL default '0',
fk_t_directories_parent int(11) NOT NULL default '0',
name varchar(50) default NULL,
sort smallint(4) NOT NULL default '0',
bild_path varchar(250) NOT NULL default '',
bild_width smallint(4) NOT NULL default '0',
bild_height smallint(4) NOT NULL default '0',
html_file varchar(250) NOT NULL default '',
info enum('f','t') NOT NULL default 't',
PRIMARY KEY (sys_pk),
KEY key_sys_state (sys_state),
KEY key_sys_dlm (sys_dlm),
KEY key_sys_doc (sys_doc),
KEY Key_fk_t_directories_parent (fk_t_directories_parent),
KEY Key_sort (sort)
) TYPE=MyISAM;

#
# Dumping data for table `t_directories`
#

INSERT INTO t_directories VALUES (1,'f','',20030811151259,1,20020113175903,1,'','IN ACTION',4,'',32,32,'','f');
INSERT INTO t_directories VALUES (570,'f','',20030331101620,1,20021211124003,1,69,' Rolta Import',31,'','','','','f');
INSERT INTO t_directories VALUES (4,'f','',20030811151259,1,20020113175937,1,'','SP ECIALS',6,'','','','','f');


CREATE TABLE t_user (
sys_pk int(11) NOT NULL auto_increment,
sys_del char(1) NOT NULL default 'f',
sys_state tinyint(2) NOT NULL default '0',
sys_dlm timestamp(14) NOT NULL,
sys_klm int(11) NOT NULL default '0',
sys_doc timestamp(14) NOT NULL,
sys_koc int(11) NOT NULL default '0',
user varchar(50) NOT NULL default '',
password varchar(50) default NULL,
admin char(1) default NULL,
name text,
logo_path varchar(250) default NULL,
logo_width smallint(4) default NULL,
logo_height smallint(4) default NULL,
intern char(1) default NULL,
radmin char(1) default NULL,
fk_t_directories int(11) NOT NULL default '0',
PRIMARY KEY (sys_pk),
KEY key_sys_state (sys_state),
KEY key_sys_dlm (sys_dlm),
KEY key_sys_doc (sys_doc),
KEY Key_user (user),
KEY Key_fk_t_directories (fk_t_directories)
) TYPE=MyISAM;

#
# Dumping data for table `t_user`
#

INSERT INTO t_user VALUES (12,'f','',20030505112239,1,20020117130250,1,'dd', 'xxxxx','t','VD DDLust','','','','t','t','');
INSERT INTO t_user VALUES (8,'f','',20030505111943,1,20011122114534,'','vd', 'xxxxx','t','VD VV','5367a5c8aa2c48cf.gif',459,141,'t','t','');
INSERT INTO t_user VALUES (11,'f','',20030504213155,1,20020117125826,1,'bbsc ad','xxxxxx','f','BBS CAD Abteilung','713c587b512ac340.jpg',250,472,'f','f', '');


I used use JOIN for both tables and i want to fetch name in my field when some directory is selcted let say SUCHEN then name should display like VD DD,VD VV,BBS CAD
$names=array();
//SUCHEN have sys_pk is 843 and i dont want to hard code like this plz modify the code
query_db("SELECT u.name FROM t_user u INNER JOIN tr_directories_user r ON r.fk_t_user=u.sys_pk WHERE r.fk_t_directories='843'");//how i can get this value instead of hardcoding 843 how i can pass sys_pk
while($r = foreach_db()) {
$names[$r->sys_pk] = $r->names;//here some problem dont no how to use u.name but when i run above querry in MySQL it works fine
}

so when i select on directory whose sys_pk is 843 so that my Benutzer und Recte field display something like this

Benutzer und Recte: VD DD
VD VV
BBS CAD

and when no cusotmer found simply display No customer found in above field



hope this is enough to get understand thanks

arun

not sure if I understand you but it looks like you want to select a user(s) where the directory is some number (in this case 843), since you are putting the directory as a secondary key in the user table you don't need a join. SELECT * FROM t_user WHERE fk_t_directories = '$dirNum'

just pass the directory as $dirNum and it should work.

sorry for trouble but if i use

select * from t_user WHERE fk_t_directories = '$dirNum' then it will return directories name but actually i want customer name which depends on sys_pk from t_directories
as when i run my JOIN querry in MySQL it diplays users name on particular directory let say SUCHEN have id 843 so how i can use in PHP to get the name thats my question
have a look ar http://server2.vitodesign.com/scripts/log.phtml
SELECT u.name FROM t_user u INNER JOIN tr_directories_user r ON r.fk_t_user=u.sys_pk WHERE r.fk_t_directories='843'

thanks

I think you want a left join:

this is from a shopping cart I wrote:

itemId and categoryId were both in the joins table but the result set comes from the items table. I always forget how joins work and need to mess around with them depending on what kind of SQL I am dealing with.

sorry my JOIn querry is absolutely alright when i run this query in MYSQL it runs fine

but when i try to use in PHP
$names=array()
query_db("SELECT u.nameFROM t_user u INNER JOIN tr_directories_user r ON r.fk_t_user=u.sys_pk WHERE r.fk_t_directories='514'");//i dont no how to pass variable in fk_t_directories

i want to print u.name so in this loop how can i write to get this u.name value
while($r = foreach_db()) {
$names[$r->sys_pk] = $r;
}

when i write like this $names[$r->sys_pk] = $r->u.names; it wont works

for eg. when

$info=array();
query_db("SELECT info FROM t_directories");
while($r2 = foreach_db()) {
$info [$r2->sys_pk] = $r2->info ;
}
print_r($info); //this works perfect


but not for join u.name and i want to print u.name like this simple select querry

hope this is clear

thanks

you aren't formating your php correctly:

when you want the data back just do a for loop through the name array. This assumes you have a db connection called $connection

sorry it still wont works if you look at table t_directories i dont no how to pass variabel in fk_t_directories


thanks