SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I code this select statement?!

    Well I have a select statement like this:

    PHP Code:
    mysql_connect($db_host, $db_user, $db_pwd); 

    mysql_select_db($db_name); 

    $department "SELECT department FROM authorize"; 

    $sql = "select * from authorize"; 
    $result = mysql_query($sql); 
    ?> 
    <form action="super.php" method="post"> 
    <select name="username"> 
    <?php 
    while($row=mysql_fetch_assoc($result)) 

    $username $row['username']; 
    $name $row['firstname']." ".$row['lastname']; 

    echo 
    "<option value=\"$username\">$name</option>"

    ?> 
    </select> 
    <input type="submit" value="Submit" name="submit" /> 
    </form>
    I think i need to do it like the following in order to make it work:

    Select department, department2, department3, department4, department5, department6 from table where username ="$_SESSION[username];

    Select department from all users. If department is equal to department, department2, department3, department4, department5, department6 then show all users in select option list.

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you mean how do I formulate an sql query, post this in the MySQL sub-forum of the database forum. Include a list of the attributes in the table in question as well.

    Your current question suggests you have a table with, among others, six columns, called departmentX where X = 1 to 6. Is that correct? You might need to include a bit more info on other tables you use as well.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am aware of how to do the sql query, just not how to blend it with php to give the output I am wanting.

    My database has a username and 6 department columns..
    One user is in more than one department, while one user is just in one. I need the user who is in more than one department to be able to see all the people in the departments which they are in.

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your table is not really normalised.
    You need two more tables - one for department and one to link user to department, to solve the many to many relationship
    authorise ---< userDept >-- dept

    userDept {username, dept} (joint primary key)

    Then try
    Code:
    SELECT username, dept
    FROM userDept
    WHERE dept in (SELECT dept
    	        FROM userDept
    	        WHERE username =$username)
    AND username !=$username;
    The sub-query finds the departments that your user is in, and the main query finds the others in that list of depts. The final AND prevents you listing the original user.
    Now you will have a list of users and their depts.


Bookmarks

Posting Permissions

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