SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 40
  1. #1
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is wrong with my syntax?

    The first query is pulling all the users, and the second query is pulling all the users.. I need it say if there is any equal usernames then show them.

    PHP Code:
    <?php
    $con 
    mysql_connect("1234","1234","1234");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
    $query mysql_query("SELECT * FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN ( u_one.department , u_one.department2 , u_one.department3 , u_one.department4 , u_one.department5 , u_one.department6 ) WHERE u_one.username = '$_SESSION[user_name]'");
    while (
    $row = @mysql_fetch_array($query)) 
    {
    $variable1=$row["username"];


    $query2 mysql_query("SELECT * FROM timesheet");
    while (
    $row = @mysql_fetch_array($query1)) 
    {
    $variable2=$row["username"];
     
    if ( 
    $_POST['$variable1'] == '$variable2' ) {
      echo 
    $row['username'];
      echo 
    "<br />";
      }
    mysql_close($con);
    ?>

  2. #2
    SitePoint Zealot Servyces's Avatar
    Join Date
    Jun 2007
    Location
    The Netherlands
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are some syntax errors. See the commented lines below.

    PHP Code:
    <?php
    $con 
    mysql_connect("1234","1234","1234");
    if (!
    $con)
      {
          die(
    'Could not connect: ' mysql_error());
      }

    $query mysql_query("SELECT * FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN ( u_one.department , u_one.department2 , u_one.department3 , u_one.department4 , u_one.department5 , u_one.department6 ) WHERE u_one.username = '$_SESSION[user_name]'");

    while (
    $row = @mysql_fetch_array($query)) 
    {
        
    $variable1=$row["username"];
    // <-- Closing bracket needed here

    $query1 mysql_query("SELECT * FROM timesheet"); // <-- Change to $query1, since you are trying to call $query1 in the while loop below, not $query2 as it was.

    while ($row = @mysql_fetch_array($query1)) 
    {
        
    $variable2=$row["username"];
    // <-- Closing bracket needed here

    if ( $_POST['$variable1'] == $variable2 ) { // <-- Do not quote $variable2
      
    echo $row['username'];
      echo 
    "<br />";
    }
    mysql_close($con);
    ?>
    Also just a word of advice .. try to work with a little more structure in your code. Sometimes you are using $row["var"], while at other times you use $row['var']. You can use both single and double quotes to achieve the same, but try to pick one and stick to that.
    Last edited by Servyces; Jun 13, 2007 at 10:27.
    Servyces.com
    Where itís all about you.
    Your partner in online solutions.
    Visit our website at http://www.servyces.com/

  3. #3
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more that Servyces didn't pick up on:
    PHP Code:
    if ( $_POST[$variable1] == $variable2 ) { // <-- Do not quote $variable2 or $variable1 
    PHP questions? RTFM
    MySQL questions? RTFM

  4. #4
    SitePoint Zealot Servyces's Avatar
    Join Date
    Jun 2007
    Location
    The Netherlands
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    One more that Servyces didn't pick up on:
    PHP Code:
    if ( $_POST[$variable1] == $variable2 ) { // <-- Do not quote $variable2 or $variable1 
    Good catch.
    Last edited by Servyces; Jun 13, 2007 at 10:27.
    Servyces.com
    Where itís all about you.
    Your partner in online solutions.
    Visit our website at http://www.servyces.com/

  5. #5
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's working but I think it's not reading it the way I want it to...

    I need to say If a username in the first query is equal is equal to a username in the second query regardless of if all the usernames are the same.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why not combine the queries to return just what you want? that would certainly seem to be an easier approach
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, r937, your probably right unfortunately I'm not quite sure how to do that

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, your first query looks very spiffy and vaguely familiar

    it returns a number of full rows, but i'll bet you only need the username, right?

    okay, now you're going to match it against the timesheet table, right? presumably on the username from that table?

    if there is a match, what do you want done?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that first query was actually preformed by a local sitepoint database guru

    Okay I want it to pull all the usernames from each query, and if there is a match. I want it to show that username.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    do yourself a really big favour, and don't write your queries all on one great big long humungous single line, because you will have a really hard time reading it and, more importantly, understanding it -- instead, wrap it onto multiple lines, with indentation too, and then you won't have to do all this stupid flipping  scrolling  back and forth, back and forth ...
    sorry, couldn't help myself

    okay, combine the queries by another inner join:
    Code:
    SELECT u_one.username
      FROM authorize AS u_one 
    INNER 
      JOIN authorize AS u_all 
        ON u_all.department IN ( u_one.department 
                               , u_one.department2 
                               , u_one.department3 
                               , u_one.department4 
                               , u_one.department5 
                               , u_one.department6 ) 
    INNER
      JOIN timesheet
        ON timesheet.username = u_one.username
     WHERE u_one.username = '$_SESSION[user_name]'
    if a user can have more than one timesheet, then you will want to add the word DISTINCT after the word SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's still not returning any users for some reason. Probably wickedneat's error. However, Okay let me make sure this is what it's doing.

    According to the session username of the person logged it, it read all the departments he is in. It then matches it with people with atleast one matching department. From there it takes the users with one matching department, and if that username is also present in the table timesheet it would show all those usernames?

  12. #12
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my updated code if that helps:

    PHP Code:
    <?php
    $con 
    mysql_connect("1234","1234","1234");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
    mysql_select_db("1234"$con);
    $result mysql_query("SELECT u_one.username
      FROM authorize AS u_one 
    INNER 
      JOIN authorize AS u_all 
        ON u_all.department IN ( u_one.department 
                               , u_one.department2 
                               , u_one.department3 
                               , u_one.department4 
                               , u_one.department5 
                               , u_one.department6 ) 
    INNER
      JOIN timesheet
        ON timesheet.username = u_one.username
     WHERE u_one.username = '
    $_SESSION[user_name]'");
    while(
    $row mysql_fetch_array($result))
      {
      echo 
    $row['username'];
      echo 
    "<br />";
      }
    mysql_close($con);
    ?>

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change

    ON timesheet.username = u_one.username

    to

    ON timesheet.username = u_all.username
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wickedneat View Post
    According to the session username of the person logged it, it read all the departments he is in. It then matches it with people with atleast one matching department.
    before you go too much further developing your application, you really should normalize the authorize table, to remove the repeating departments -- they should be in a separate one-to-many table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I made that change it reported just the username of the user I am logged in as. And that user is not even apart of the timesheet table.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i am now not just partially lost, i have failed completely to understand what you're doing and why

    please have a look at post #14

    if you cannot normalize your tables, then perhaps you could show a few sample rows from each tble which will illustrate what you're trying to achieve
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    authorize:

    columns: username - department - department1 - department2 - department3 -department4 - department5 - department 6

    timesheet: username - monday - tuesday - wednesday - thursday - friday - saturday - sunday

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks, i meant sample data

    show some data which actually represents the relationships you're trying to extract
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me take a step back here, I think I may have found another easier way that will work.

    I know the following does what I need (sorry for spacing):

    PHP Code:
    SELECT FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN u_one.department u_one.department2 u_one.department3 u_one.department4 u_one.department5 u_one.department6 WHERE u_one.username '$_SESSION[user_name]'"; 
    Can we take that a step farther and say the username it displays also has to have lockedstatus column set to LOCKED in order to display the users?


    Authorize:

    Columns: Username - lockedstatus - department - department2 etc

    sample entry:

    user1 - LOCKED - business - accounting

  20. #20
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would it be something like

    PHP Code:
    SELECT u_one.username
      FROM authorize 
    AS u_one 
    INNER 
      JOIN authorize 
    AS u_all 
        ON u_all
    .department IN u_one.department 
                               
    u_one.department2 
                               
    u_one.department3 
                               
    u_one.department4 
                               
    u_one.department5 
                               
    u_one.department6 
    INNER
      JOIN timesheet
        ON timesheet
    .username u_one.username
     WHERE u_one
    .username '$_SESSION[user_name]'
     
    WHERE lockedstatus 'LOCKED'

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, you would use AND instead of a second WHERE, and you would have to qualify the lockedstatus column with the table alias of the table you want checked (likely u_one)

    but i'm not sure i can help you any further because i really don't understand your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not really sure I follow you r937

  23. #23
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean like this:

    WHERE u_one.username = '$_SESSION[user_name]' AND u_one.lockedstatus = 'LOCKED'";

  24. #24
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suspect there's a very minor oversight in this query. Take a look at it this way:
    Code:
    SELECT u_one.username
    /*snip*/
     WHERE u_one.username = '$_SESSION[user_name]'
    If this is in fact what you're trying to achieve, then you don't need the database at all - merely echo $_SESSION['user_name'] (btw, the quotes around a non-numerical array index are required in PHP - your query should look like this: "SELECT...WHERE u_one.username = '{$_SESSION['user_name']}'" - the {} around the variable ensure that PHP will properly interpret it as a variable values).

    I suspect what you want instead is to either "SELECT u_all.username..." or to put the condition as "...WHERE u_all.username...". Honestly, though, I'm at least as lost as Rudy. If neither of these suggestions work, could you restate as clearly as possible your goal, all relevant table structures (authorize and timesheet), and provide a few rows of sample data along with what they should look like in your desired result.
    PHP questions? RTFM
    MySQL questions? RTFM

  25. #25
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay let me give this another whirl..

    Authorize

    Column Names:
    Username - password - lockedstatus - department - department1 - department2

    Sample Entries:
    Supervisor (person who is to see the outcome):
    supervisor - password - unlocked - business - accounting - marketing

    Workers:
    Username1 - password - LOCKED - business
    Username2 - password - UNLOCKED -business
    username3 - password - LOCKED - accounting

    When you submit a form into the database it automatically sets that person to the locked status.. What I need is to be able to display the users of those why have the locked status, and one of there departments are matching up with alteast one of the supervisors departments

    They are not to see users with unlocked status, and with a different department than the ones listed in the supervisors row.


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
  •