What is wrong with my syntax?

[wickedneat]

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
$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);
?>

[Servyces]

There are some syntax errors. See the commented lines below.

<?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.

[kromey]

One more that Servyces didn't pick up on:

if ( $_POST[$variable1] == $variable2 ) { // <-- Do not quote $variable2 or $variable1 

[Servyces]

One more that Servyces didn't pick up on:

if ( $_POST[$variable1] == $variable2 ) { // <-- Do not quote $variable2 or $variable1 

Good catch.

[wickedneat]

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.

[r937]

why not combine the queries to return just what you want? that would certainly seem to be an easier approach

[wickedneat]

Well, r937, your probably right unfortunately I'm not quite sure how to do that

[r937]

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?

[wickedneat]

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.

[r937]

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:

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

[wickedneat]

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?

[wickedneat]

Here is my updated code if that helps:

<?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);
?>

[r937]

change

ON timesheet.username = u_one.username

to

ON timesheet.username = u_all.username

[r937]

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

[wickedneat]

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.

[r937]

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

[wickedneat]

authorize:

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

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

[r937]

thanks, i meant sample data

show some data which actually represents the relationships you're trying to extract

[wickedneat]

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):

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

[wickedneat]

Would it be something like

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'; 

[r937]

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

[wickedneat]

I'm not really sure I follow you r937

[wickedneat]

You mean like this:

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

[kromey]

I suspect there's a very minor oversight in this query. Take a look at it this way:

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.

[wickedneat]

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.