Page 1 of 1
Querying SQL table based on values of another table
Posted: Mon Nov 24, 2008 3:53 am
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
Re: Querying SQL table based on values of another table
Posted: Mon Nov 24, 2008 2:00 pm
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.
Re: Querying SQL table based on values of another table
Posted: Mon Dec 01, 2008 10:27 am
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'>
<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.
Re: Querying SQL table based on values of another table
Posted: Mon Dec 01, 2008 2:59 pm
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.