close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

needs some help please

Thread began 5/18/2012 5:07 pm by akstudio | Last modified 5/21/2012 5:31 pm by akstudio | 1026 views | 8 replies |

akstudio

needs some help please

Im going nuts here. Here is the basic flow of data:

admin logs into backend. fills out a form. This form has a file upload and 4 check boxes. each checkbox is equal to a user level. This allows me to display the file based on user level. Some files may be seen by more than one user level.

my table is as:

table name: policies

id
file_name
1
2
3
4

each number represents a user level

when a file is uploaded and tagged as 3 for example, the data in the "3" field is either 1 or 0.

Now, what I want to do is to display all file_names in 3, that are 1.

Makes sense?

To make matters more complicated, I think I need to define it by '$_SESSION[user_level]'.

I have no idea who to do this. I have tried a ton of things. The closest I have come to is:

$SQL = "SELECT * FROM policies ";
if($_SESSION['user_level']== 1)
{
$SQL = $SQL . "WHERE 1='1'";
}
if($_SESSION['user_level']== 2)
{
$SQL = $SQL . "WHERE 2='1'";
}
if($_SESSION['user_level']== 3)
{
$SQL = $SQL . "WHERE 3='1'";
}
if($_SESSION['user_level']== 4)
{
$SQL = $SQL . "WHERE 4='1'";
}

echo $SQL;

But I still only get one file back, and its usually the first record in the table, doesnt matter if its 1 or 0

This is way out of my league now.. is there an easy way to just : select all "1's" from my table, based on you is logged in?

Sign in to reply to this post

CraigRBeta Tester

post a copy of your table and what you expect to see from a pseudo query.

Normally, to filter, your query should be something like...

"SELECT * FROM tablename WHERE fieldname = 'some criteria' "

btw * is lazy, you should only realy select the fields you want to see, but it will get you started

If your query is working ok, you should be able to see the values returned when you test the recordset in dreamweaver.

If this is Ok, then perhaps you have a problem with your repeat region

Sign in to reply to this post

gruant2000381341

you need a loop to gather all the results
I don't know your table fields so I just used name, fileName, and date
then I echo out the $dynamicList in your page body

<?php
session_start();
// determine which query value based on session
if (isset($_SESSION['user_level'])) {
if($_SESSION['user_level']== 1) {$ul = 1;}
if($_SESSION['user_level']== 2) {$ul = 2;}
if($_SESSION['user_level']== 3) {$ul = 3;}
if($_SESSION['user_level']== 4) {$ul = 4;}
}
//run query
$sql = mysql_query("SELECT * FROM policies WHERE $ul='1'");
//initialize
$dynamicList = "";
//get count
$numRows = mysql_num_rows($sql);
//see if any returns
if ($numRows < 1) {
$dynamicList = "Nothing to show";
//if results start loop
} else {
while($row = mysql_fetch_array($sql)){
$name = $row["name"];
$fileName = $row["fileName"];
$date = $row["date"];
$dynamicList .= '&nbsp;&nbsp;' . $name . '&nbsp;&nbsp;' . $fileName . '&nbsp;&nbsp;' . $date . '<br />';
}
}


//display the list
echo $dynamicList;
?>

Sign in to reply to this post

akstudio

Thanks guys..

I tried gruant2000381341 suggestion... it kinda works.

...$sql = mysql_query("SELECT * FROM policies WHERE $ul='1'"); .... that $ul='1' needs to be like the session var.

when I log in using the code above, as user_level 2 .. it tells me "nothing to show" .. if i change the select to .... $ul='2' ... then it shows ALL results .. weather intended for that user level or not.

This is my current code.


<?php require_once('Connections/conn_resellers.php'); ?>
<?php require_once( "webassist/security_assist/helper_php.php" ); ?>
<?php
if (!WA_Auth_RulePasses("Logged in to users")){
WA_Auth_RestrictAccess("login.php");
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

// EVENTS SELECT
mysql_select_db($database_conn_resellers, $conn_resellers);
$query_events = "SELECT * FROM events";
$events = mysql_query($query_events, $conn_resellers) or die(mysql_error());
$row_events = mysql_fetch_assoc($events);
$totalRows_events = mysql_num_rows($events);





?>
<?php
//session_start();
// determine which query value based on session
if (isset($_SESSION['user_level'])) {
if($_SESSION['user_level']== 1) {$ul = 1;}
if($_SESSION['user_level']== 2) {$ul = 2;}
if($_SESSION['user_level']== 3) {$ul = 3;}
if($_SESSION['user_level']== 4) {$ul = 4;}
}
//run query
$sql = mysql_query("SELECT * FROM policies WHERE $ul='1'");
//initialize
$dynamicList = "";
//get count
$numRows = mysql_num_rows($sql);
//see if any returns
if ($numRows < 1) {
$dynamicList = "Notta";
//if results start loop
} else {
while($row = mysql_fetch_array($sql)){
//$name = $row["name"];
$filename = $row["file_name"];
//$date = $row["date"];
$dynamicList .= '&nbsp;&nbsp;' . $filename . '<br />';
}
}


//display the list

?>


....
<body>


<?php if(WA_Auth_RulePasses("authorized")){ // Begin Show Region ?>
<p>#2 Authorized rule shown</p>
<div class="policy-list"> <?php echo $dynamicList; ?> </div>
<?php } // End Show Region ?>
</body>


Here is the DB:

//-----------------------------------------------
CREATE TABLE `policies` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`file_name` varchar(45) NOT NULL,
`1` varchar(45) NOT NULL DEFAULT '0',
`2` varchar(45) NOT NULL DEFAULT '0',
`3` varchar(45) NOT NULL DEFAULT '0',
`4` varchar(45) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
//-----------------------------------------------

that 1, 2, 3, 4 is equal to the user_level.

Sign in to reply to this post

gruant2000381341

thats odd because you shouldn't have any 2's in the db, only 0 or 1 ..right?
maybe instead of having 1,2,3,4 columns with a 0 or 1 have one column named user_level with an enum of 1,2,3,4 default of your lowest level or even a zero
then you could change that query to
$sql = mysql_query("SELECT file_name FROM policies WHERE user_level='$ul'");

also, just to do a tiny bit of db optimization..
`1` varchar(45) NOT NULL DEFAULT '0',
`2` varchar(45) NOT NULL DEFAULT '0',
`3` varchar(45) NOT NULL DEFAULT '0',
`4` varchar(45) NOT NULL DEFAULT '0',
if those are only ever going to be a 1 or a 0...you don't need a limit of (45)...change that limit to (1)...or go with enum '0','1'

Sign in to reply to this post

akstudio

I'll keep trying your method as stated above. The issue I am having is that each file name may be viewed by more than one user group. It will be uncommon, but there will be occurrences. Otherwise, your second suggestion would work out well.

I think thats where alot of my confusion comes in, when I need one file to be seen by several groups.

Sign in to reply to this post

akstudio

the statement, I would believe, should kinda be like:

$sql = mysql_query("SELECT * FROM policies WHERE '$_SESSION[user_level]' = '$ul'");

But this, again, spits out all the records.

When I login as, lets say, user level 2... there should only be 3 files available for me. but instead, it lists out the entire db.

Sign in to reply to this post

Jason ByrnesWebAssist

i would do this a completely different way.

I would use three tables to create a many to many relation of files to user groups:

files:
fileID - primary key
fileName - varchar

usergroups:
userGrouplID - Primary Key
userGroup - varchar

usergroupfiles:
usergroupfileID - primary key
usergroupfileFileID - relates to files.fileID
usergroupfileGroupID - relates to usergroups.userGrouplID


then you can use a join query to get all files that belong to a specific user group:

SELECT files.fileName
FROM files
INNER JOIN usergroupfiles
ON usergroupfiles.usergroupfileGroupID = files.fileID
WHERE usergroupfiles.usergroupfileGroupID = paramID

variables:
name: paramID
type: integer
default value = -1
run time value: $_SESSION[user_level]

Sign in to reply to this post

akstudio

That method looks cool to, thanks.

I will report that I FINALLY got it to work...

I had to go into the db and change the 1,2,3,4 to userlevel1, userlevel2, etc.... then select:

$sqlquery = "SELECT * FROM policies WHERE UserLevel" . $_SESSION['user_level'] . "='1'";


Thanks a ton guys... i feel i can now move on =)

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...