Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 10 123 ... LastLast
Results 1 to 15 of 143

Thread: join problem

  1. #1
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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,'','SPECIALS',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,'bbscad','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
    }

    <td class="black2">
    <input type="radio" name="ddir" onClick="document.enableform.name.value='<?php
    echo htmlentities($r->name);
    query_db("SELECT info FROM t_directories WHERE sys_pk=".$r->sys_pk);
    $r2=foreach_db();if($r2->info=='t')echo "';document.enableform.check.checked='true";
    document.enableform.benutzer.value='what should i enter to get value'?>'"
    value="<?=$r->sys_pk ?>" id="dir<?=$r->sys_pk ?>" /></td>

    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

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i created 2 tables and i want to fetch name from t_user having sys_pk
    OK. So then your query can't be

    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
    It should be
    query_db("SELECT u.name FROM t_user u INNER JOIN tr_directories_user r ON r.fk_t_user=u.sys_pk WHERE sys_pk='" . $_GET['folderID'] . "'");
    or somethink like that. Where $_GET['folder'] point to the querystring where you have the sys_pk value.

    Or am i missing something here? It's all kinda confusing to me.

    I also don't understand this
    td class="black2">
    <input type="radio" name="ddir" onClick="document.enableform.name.value='<?php
    echo htmlentities($r->name);
    query_db("SELECT info FROM t_directories WHERE sys_pk=".$r->sys_pk);
    $r2=foreach_db();if($r2->info=='t')echo "';document.enableform.check.checked='true";
    document.enableform.benutzer.value='what should i enter to get value'?>'"
    value="<?=$r->sys_pk ?>" id="dir<?=$r->sys_pk ?>" /></td>

    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
    The output is clear, but the other stuff? What exactly are you trying to do? Create a form where the user can click on a link (which has the sys_pk value in the querystringvariable 'folder' or a form with checkboxes or ...

  • #3
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry RAf it wont work with ur querry when i try to run ur query in MYSQL it wont works as im newbie to this field plz help me out

    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

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sory, but i don't understand it.

    As far as i can understand, there are 2 questions:
    - getting the value in the where condition to be inserted automatically (= parametrised)
    --> to do this, you need to replace it with a variable. like $variable (if you get the value from somwhere inside the script) or $_POST['var'] if it's posted inside a form or $_GET['var'] if you get it from the querystring

    - displaying the returned records:
    --> you best use mysql_fetch_row() for that. Check out
    http://be.php.net/manual/en/function.msql-fetch-row.php
    there's som samplecode at the bottom there.

    In your case, it would be something like
    PHP Code:
    $result mysql_query("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='" $var "'") or die("Error:
    mysql_error());
    echo 
    "All names : ";
    while (
    $row mysql_fetch_array($result) {
    echo  (
    "<br />" $row["u.name"]);

    to print out all u.names. you need to replace the $var by the varablename that contains the value you need in the condition.

  • #5
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i dont no how to pass variable please help me i am newbie can i send my php files

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. Zip them and post them up here.

  • #7
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thankls Raf its appreciable for your sincere help,

    the problem is when i select some directory it will show the users which is having rights on that,if no users then it will print in have a look at link

    http://server2.vitodesign.com/scripts/diruser.phtml
    Benutzer une Rechte : No rights
    this field means
    (User and Rights)

    how i can print users name after this (colon) something like

    diruser.phtml having all directories which represent with radio buttons once it selected it will show users name
    like this
    Benutzer une Rechte : VD
    DD
    CC

    i already posted tables and values and lib.php is having all libraries

    thanks alot
    Attached Files Attached Files

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Heuh. Do i understand it right that you want a table with users and right. Where each user has a radiobutton in front of it ?

    Normally, you'll print the usernames (maybe with an image in front of it), as a link (with the userID in the querystring. like showrights.php?userID=xxxx). It saves some screenspace, is more intuitive and saves the user a click.

    Then, in showrights.php, you'd have
    query_db("SELECT u.name FROM t_user u INNER JOIN tr_directories_user ON r.fk_t_user=u.sys_pk WHERE sys_pk='" . $_GET['userID'] . "'");
    to get the userID of the username that was clicked on, and insert it in the condition of your select

    Is it something like that that you need?

  • #9
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thansk alot ur right , i m very much confused how i can get value in benutzer und rechte field as how i can print the users name on thsi field when i select some director could u please modify diruser.phtml

    PHP Code:

    <input type="radio" name="ddir" 
    $r2 = query_db("SELECT u.name FROM t_user u INNER JOIN tr_directories_user r ON r.fk_t_user=u.sys_pk WHERE sys_pk='" . $_GET['ddir'] . "'");//is it correct to pass this ddir variable in this query                                                                  onClick="document.enableform.user.value='<?php  echo htmlentities($r2->name); ? >"  value="<?=$r->sys_pk ?>" id="dir<?=$r->sys_pk ?>" />

    //document.enableform.user.value  how  i can pass the name of below field

    td class="black2" name="user">Benutzer und Rechte:</td>// this is wrong but still i dont no how to print this
    please help me out i dont no how to fetch the variable in query as well as to print in this table benutzer

    please have a look at attached jif
    Attached Thumbnails Attached Thumbnails join problem-dir.gif  
    Last edited by zuzupus; 08-18-2003 at 11:04 AM.

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wait a minute.
    Do you want to select a directory, and then get all the names of the users + their right for that directory?

    I looked at your code but you use objects for the db-interaction and for displaying the records. If you ask me, that's way to much overhead and way to complicated to code, since this is a fairly simple feature, and the recordset itself can be viewd as an object, so why not use the recordset functions?
    And why do you need the radiobuttons? Isn't it easier to use the directoryname as a link, or to use a dropdown-menu?

    if it is the above mentioned, then i'll write you the query + code to build the table.

  • #11
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks alot RAF

    <Do you want to select a directory, and then get all the names of <the users + their right for that directory?

    Yes you are absolutely right this is what i want

    < since this is a fairly simple feature, and the recordset itself can <be viewd as an object, so why not use the recordset functions?

    yes record set can also be used but i want to use lib.php,but if its fairly possible with record set then thats a better way.

    <And why do you need the radiobuttons? Isn't it easier to use <the directoryname as a link, or to use a dropdown-menu?

    I think link is better than radio button the reason for using radio button is you can see one directory name download and ther is no child for tree,are you sur eif you create link then it wont behave like +VDIntern directories whom having lot of root directories ,without + sign it suggests no child directories.

    But still appreciated if it works with link

    thanks alot

  • #12
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I'll write it out this afternoon or this evening (currently at work)

    One last question. The userrights, how are they stored in the database? I see you select on the value for "fk_t_directories", but this means that you have a record in table t_user for each directory for each user ?

  • #13
    Regular Coder
    Join Date
    Jun 2003
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    <OK. I'll write it out this afternoon or this evening (currently at <work)
    thanks alot no problem its appreciable

    <One last question. The userrights, how are they stored in the <database? <I see you select on the value for "fk_t_directories", <but this means that you have a record in table t_user for each directory for each user ?

    im sorry i posted worng table t_directories actually it is tr_directories_user

    and the column rights will tell whether user has rights having values o,1,2 and 3
    0 is for no rights
    1 is for read only
    2 is for write only
    3 is for read and write
    **********tr_directories_user********************

    CREATE TABLE tr_directories_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',
    fk_t_directories int(11) NOT NULL default '0',
    fk_t_user int(11) NOT NULL default '0',
    has_news set('f','t') NOT NULL default 'f',
    rights tinyint(2) default NULL,
    PRIMARY KEY (sys_pk),
    UNIQUE KEY fk_t_directories (fk_t_directories,fk_t_user),
    KEY key_sys_state (sys_state),
    KEY key_sys_dlm (sys_dlm),
    KEY key_sys_doc (sys_doc),
    KEY Key_fk_t_directories (fk_t_directories),
    KEY Key_fk_t_user (fk_t_user),
    KEY has_news (has_news)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `tr_directories_user`
    #

    INSERT INTO tr_directories_user VALUES (836,'f','',20020719122654,'',20020307181759,'',99,37,'t',1);
    INSERT INTO tr_directories_user VALUES (832,'f','',20020411220046,'',20020307181759,'',46,37,'f',1);
    INSERT INTO tr_directories_user VALUES (8754,'f','',20030312180152,1,20021217220324,1,580,130,'f',3);
    INSERT INTO tr_directories_user VALUES (102,'f','',20030115150934,'',00000000000000,'',70,10,'t',3);
    INSERT INTO tr_directories_user VALUES (285,'f','',20030805143420,'',00000000000000,'',69,24,'f',2);

    hope this is clear for u,sorry for ur inconveneience

    thanks

  • #14
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hah. That makes more sense. I'll get back to you this evening.

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I added some code. I inserted some comment to give you some extra info.
    The system is like this:
    - the first time the page is loaded, there no value for the directory in the querystring;
    - all directorys are selected;
    - for each directory, a link is displayed with the sys_pk in the querystring;
    -if you click on the link, the same page is loaded, but this time, there's a value for the dirID;
    - if there's a value for the dirID, then all users and there rights for that directory are selected.
    -for each user, a line with username and rights is displayed.

    There's not much layout, but if you understand this system, then it's easy to display the records in a table, or display images for the rigths etc.

    Just write back if you need more info or if you wan't to clean up the layout by adding radiobuttons or maybe images in front of the directorys (like in windows explorer)
    Attached Files Attached Files
    Last edited by raf; 08-18-2003 at 09:21 PM.


  •  
    Page 1 of 10 123 ... LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •