Querying SQL table based on values of another table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Querying SQL table based on values of another table

Post by simonmlewis »

I am building a web site that runs reports from SQL tables, and uses User Rights to know what data it can extract.

There is a table with id (basically the user's id number), name, and department.
There is a userrights table, with userid (the user's ID taken from the previous table, and a row for each department they are permitted to see.
2 - Shop
2 - Stairs
2 - Kitchen
.... for example, so this person can see Shop, Stairs and Kitchen.

There is a third table called Haneng (the graphics charts people) which shows each department in one row.

That chart has values, and I want the person to be able to see the values for Shop and Stairs, but not Kitchen.

Because there are multiple columns per user in userrights, I can pick out the departments, but since there is just one column per department in Haneng, I can only get one department as a result.

Any ideas how to make both departments come from Haneng when the user has rights to both?

Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Querying SQL table based on values of another table

Post by califdon »

I'm really having difficulty following your description. Please omit everything else and just show us your complete tables structure, formatted essentially like this:

Code: Select all

[b]tblPeople[/b]:
    ID    (primary key)
    Name   (text)
    Department   (text? integer? foreign key?)
 
[b]tblUserRights[/b]:
    ID    (foreign key)
    Department   (text? integer? foreign key?)
 
[b]tblHaneng[/b]:
    ID  ??
    Department  (text? integer? foreign key?)
    ...other fields?...
I'm guessing, from your description, that your tables are not "normalized", which would explain your difficulty.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Querying SQL table based on values of another table

Post by simonmlewis »

I have worked through a little further with this, but still stuck.

The Haneng table has 4 rows of date, two columns: ID and Dept.
the Userrights table still has a row for each right the user has:
1 - Shop
1 - Kitchen
1 - Stairs
1 - landing

... for example.

I can pass all four through a looped hidden field to an array in the following posted page. If I echo the variable, it shows "Shop, Kitchen, Stairs, landing".

I now want to search on that, to see what rights they have got and where those words match with what's in the Dept field of Haneng.

But it won't work and I can kinda see why. If you are asking Haneng for data matching anything LIKE "Shop, Kitchen, Stairs, landing", then obviously won't work because there are only one-word responses in the Haneng Dept field.

So how do I do it???

This is it so far:

Code: Select all

<?php
$id=$_POST['id'];
 
if ( isset($_POST['department']) ) { 
     $_POST['department'] = implode(', ', $_POST['department']); //Converts an array into a single string
   }
 
if (isset($_COOKIE["user"])) {
  echo "Logged in as: " . $_COOKIE["user"] . "<br/><a href='index.php?page=logout' 
 
class='bodylink'>Logout</a><br/>{$_POST['department']}";
 
$sqlconn=mysql_connect("localhost","user","pass");
$rs=mysql_select_db("dbname",$sqlconn);
$result = mysql_query ("SELECT * FROM haneng WHERE dept LIKE '%Litigation,%'");
 
while ($row = mysql_fetch_object($result)) {
    echo "
<input type='text' name='dept' value='$row->dept'>&nbsp;
<input type='text' name='value' value='$row->value'><br/>
<input type='hidden' name='id' value='$row->id'>
";
}       
    mysql_free_result($result);
    mysql_close($sqlconn);
All help gratefully received.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Querying SQL table based on values of another table

Post by califdon »

I'll try again: please omit everything else (no conversational description, that only makes it more difficult for us to understand what you are working with), and show us your table structure in the format I showed you. I'm not trying to be difficult, I'm trying to understand your table structure, which is probably the root of your problem. Again, please, no commentary at all, just the structure in the format I suggested. That's what most of us understand.
Post Reply