Help with a php Admin CP with various access levels

[Starfox]

Greetings folks.

 

Currently I'm the owner of a website that is dedicated to an online gaming community.

 

The site is html, and it relies a lot on me to update things. I would like to give access to people without having them know html or give them ftp access.

 

So I'm creating an Admin Control Panel php page, and converting several of the site webpages to php. The Admin CP is just one giant Form, and outputs from this form go on the various pages.

 

k, let me get to the point.

 

I'd love to get some help on the actual logic to do this, but also some specific syntax questions.

 

For example, the mysql select, is in this form:

 

SELECT column FROM table WHERE column operator value

 

My question is, can you have several "wheres" so like

 

SELECT column FROM table WHERE column operator value AND WHERE column operator value

 

So say you have a list of users, and each has a different access level and is assigned to a different unit within the organization. When generating a list of usernames that they have access to edit, I would like to select names under their juristiction. So like slecect username FROM table WHERE Unit = unitname AND WHERE Access <= 4

 

Would this be possible?

 

Thanx in advance, I'll start with this simple question. See how it goes from there.

[chronister]

The short answer is yes, that is correct. A couple syntax corrections though. Put ' ' around your operator value. I have had this cause me problems in the past e.g.

 

select username FROM table WHERE Unit = 'unitname' AND WHERE Access <= '4';

 

 

you can use AND or &&    or you can use OR or ||

 

select username FROM table WHERE Unit = 'unitname' || Unit= 'unitname2' AND WHERE Access <= '4' || Access >= '5';

 

This means select username from table where unit equals unitname or unit equals unitname2 AND where access is less than or equal to 4 or access is greater than or equal to 5.

 

Probably not really practical, but it gives an idea on using this kind of query

 

You can combine these things and make pretty specific queries 

 

[boo_lolly]

My question is, can you have several "wheres" so like

 

$sql = "
        SELECT
                *
        FROM
                your_table
        WHERE
                my_id_column
        IN
                ('4', '41', '2', '6')
";

[Starfox]

Thanx a ton chronister, that really is going to allow me to do what I planned a lot more easily.

 

Also, I'm not sure how sessions and logins work. I need to have each user login, and based on their access level, see different things on the admin php page they are looking.

 

I don't know anything about cookies or sessions, but what I was going to do, was have the database store the usernames and passwords associated with them, and then have a login page, where the person puts in the username and password, this page will essentially be a form, that sends the information to another script, which takes that password and username, and checks them with the database. And I'll have a gigantic if statement, that says if they match, show content of the admin CP, if they don't, show error msg.

 

Also, the main why I'm going to have one page display different info based on access level, is that I will force each user to only see certain items on their popdown menus, so for example, regular users will only be able to edit their own information, while an intermediate leader, will be able to select say a group of 5 people and edit their info, and the fields he can edit will be restricted also. The higher the access, the less restrictions.

 

Is this logic a good one for what I'm trying to do?

 

And boo_lolly, I'm not sure what your code means, could you explain?

 

Thanx

 

Starfox

[dj-kenpo]

I beleive the correct method is sessions, not cookies.

 

make sure the password goes through md5($password); this is pretty good encrpytion that comes with php. also don't sotre a text or readable version of the users password in your database. it's a big security no no if someone finds a hole in your code and starts listing out data they shouldn't.

[Starfox]

dj-kempo, could you please elaborate on how exactly I'd use md5?

 

So I have a form page to get the login and password from the user that sends it to my Admin CP php, what do I do with that login and password? And how would use md5 and store the passwords in the db?

[john010117]

I don't know if it's a coincidence or not, but right now, I'm designing an Admin CP for my website too. 

 

Anyways, to store a password that has been hashed (md5):

$password = $_POST['password'];
$password = md5($password);

... and just use the $password variable when inserting it into a database.

 

Same thing goes with checking to see if a password a user entered matches the hashed version of the password stored in the database, except you'll need one more variable.

 

md5 is just another function. Read more about it here

 

Sessions

[Starfox]

That's cool john, we should keep in touch and talk about our coding

 

So, md5 converts a password, say a 6 or 8 character password, into a 32-character number?

 

And that's what you store in the database? that 32-character entry?

 

That makes sense, so that would make it harder for someone to figure out the pw you say?

 

Now, when you want to check the user-inputted password with the one stored in the database, how do you do that?

 

I.e how do you convert the 32 character encryption, back to the 6 or 8 character password the user chose, so you can compare the two to give access?

 

 

[dj-kenpo]

you don't convert it back, you convert the submitted one into md5 as well, and then compare the two. if they match, log the user in, if not, deny access

[Moon-Man.net]

Hash the user inputted password before you compare it to the database value. providing the password is the same, the hash will also be the same. MD5 has no decryption.

[Starfox]

All righty thanx

 

So would I have say a "login.php" where the user inputs a username and password, and then would you send the results to the admin.php, where I can have a if statement, where if the password matches the one on the database (well, if the hashes match) you display the content of the admin.php page, where as if they don't match, you display an error msg.

 

Also, I'm going to force the users to select from popdown menus, and these menus will display different data depending on the access level of that user.

 

And also, I'll have certain if statements, that display data (and additional forms) depending on that user's access level. These if statements would be simply like this:

 

if accesslevel >= 4  where username = 'username'

{

 

Display a bunch of forms and data to allow this guy to input and/or edit/delete data from database

 

}

else

{}

 

Well, basically there is no else. If the if statement is not met, I don't want to display anything.

 

 

Does everything I've said in this post make sense? Is it good coding logic?

 

 

[Moon-Man.net]

for your login system, it is a good idea to store the session variable in a cookie, and reference that to a database value. So if a user closes the window, when they open it again, they get a new session, and have to login again.

 

I also keep a login timestamp in my database and on my header page, update that time to the currenttimestamp for the session id. And then another query that deletes any entries that are older than 30 mins. so the userlogin will expire, so someone can't forget to logout...

 

Just some idea's

I have written about 5 different login systems, each one more complicated than the last..PM me if you would like help over MSN

-- Nathan

[dj-kenpo]

there's a complete basic login script like half way down the page right now from someone else working on one. just read over that one.

[corbin]

Your idea about the login page is correct... Assuming the username field in the html form was named user and the password was named pass, you could do something like the following:

//assume a mysql conn is already made, and that usernames are stored in the user column and passwords are stored in teh pass column... assume the table is named users and their access levels are stored in 'access_level'
$user = $_POST['user']; //you would want to do addslashes() to this if magic quotes was off
$pass = $_POST['pass'];
$q = mysql_query("SELECT access_level FROM users WHERE user = '$username' AND pass = '".md5($pass)."'");
if(mysql_num_rows($q) > 0) {
$alvl = mysql_fetch_row($q);
$alvl = $alvl[0];
}

 

As for the if clauses, that's sql syntax, not php.... To expand from the script I posted above:

If($alvl >= 4) {
display something
}
if($alvl >= 3) {
display something
}

[Starfox]

I've come across a problem in my coding for this cp.

 

$result = mysql_query("SELECT * FROM $_POST[tablename]");

while($row = mysql_fetch_array($result))
  {
  echo $row['$_POST[colname]'];
  echo "<br>";
  }

 

If I actually put in the tablename and column name manually, the above code prints a list of entries of that column in that table.

 

But when I try to use a form to pass the table and column names, it doesn't work.

 

Does it not work because the form only passes the information once into the loop? Is this impossible to do?

 

The reason why I'm doing this, is so that I can pick any column in any table, and print out all the entries so I can actually see them.

 

Help is appreciated

 

Starfox

[chronister]

$tablename=$_POST['tablename'];

$colname=$_POST['colname'];

 

$result = mysql_query("SELECT * FROM '$tablename' ");

 

while($row = mysql_fetch_object($result))// changed to mysql_fetch_object from mysql_fetch_array

  {

  echo $row->$colname; // changed this to object sytax from $row[$colname]

  echo "<br>";

  }

 

Try that. I have found problems when trying to use array syntax in queries. Set the variables first then reference that variable. It would also be a good idea to use mysql_escape_string() around all your post and get variables.

 

I changed the fetch_array to fetch_object. I find it easier to reference the column names like this, but if you prefer doing it the other way, then go for it.

[Starfox]

I've tried what you suggested chronister, and I tried using both array and object.

 

It gives me this error.

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result

 

or

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result

[Starfox]

k I just found out the form doesn't pass information to the while loop. Anything outside of it works fine.

 

$result = mysql_query("SELECT * FROM $tablename");

while($row = mysql_fetch_array($result))
  {
  echo $row['columnlist']; echo $row['tablelist'];
  echo "<br>";
  }

 

So looking at the above code for example, when I pass $tablename = $_POST[tablename] it works.

 

But when i try to pass $colname = $_POST[colname] to replace the columns "columnlist" and "tablelist" it doesn't pass it.

 

Any idea what I can do to actually have the form pass data INTO the loop?

[Starfox]

nevermin, the following code worked finally (chroinster you were write, I was making a mistake somewhere)

 

$result = mysql_query("SELECT * FROM $tablename");

while($row = mysql_fetch_object($result))
  {
  echo $row->$colname; 
  echo "<br>";
  }

 

Now, got another problem. When I use the fetch_object or fetch_array, it goes through all the entries (rows) of the table. And when I ask it to display a certain column, it creates empty spaces.

 

So say I have column 1 (entry 1,3,5,7) and column2 (entry2,4,6,

 

When I ask it to display column 2 it's like this:

 

empty space

entry 2

empty space

entry 4

empty space

entry 6

 

How can I make it so it displays this instead:

 

entry 2

entry 4

entry 6

 

without the spaces?

[chronister]

hmmmmm, try removing that

echo "<br>"

and see if that works.

 

I don't think that is the problem as it should be making the items go to a new line.

 

It would be logical to assume that if you removed it your results would look like this

 

entry2entry4entry6

 

But, maybe there is already a

<br>

or

/n

in your results that your displaying

[Starfox]

Ok here's the code

 

$result = mysql_query("SELECT * FROM coretable");

echo "<table border=1>";
while($row = mysql_fetch_object($result))
  {
  echo "<tr>";
  echo "<td>" . $row->tablename . "</td><td>" . $row->colname . "</td><td> " . $row->datatype . "</td>";  
  echo "</tr>";
  }
echo "</table>";

 

and here is the output:

 

Output

 

I would like there to be no spaces in the output.

[chronister]

Try this, I think this is what your  after. As for the dropdown, I would have to see the code from that to try and determine why there are spaces in it.

 

*note* I prefer to keep html in html format and simply drop in and out of php when needed so that php code is PHP code and html is plain html, not html being echoed by php. Very rarely do I echo html tags.

 

 

<?php
while($row = mysql_fetch_object($result))
  {
     ?>

<!-- drop out of php and back into html here -->

<table border="1">
    <tr>
      <td><?=$row->tablename ?></td><td><?=$row->colname ?></td><td><?=$row->datatype ?></td>  
     </tr>
</table>
<!-- end html going back into php code -->
<?php
  }
?>

[Starfox]

I found the problem, but I don't like the solution.

 

The problem is, that table entries are done one at a time. So say you have 5 columns in a table, if you only insert data one column at a time, it'll take up a row each time. If there is no entry for that particular column, it'll just create a space.

 

On the otherhand, if you have a form to insert data simultaneously for every single column in the table, it'll consider it all one big entry into the recordset, so even if there is nothing entered for certain columns, it'll still only take up one row.

 

So, to solve it, I either have to have forms for every single column, and leave the ones I don't want to update blank, or if I enter info one column at a time, I'll have to put up with spaces in the output.

 

Any ideas on how to solve this?

 

So for example, if I have table1, with column1 and column2, and column1 entries 11, 12, 13 and column2 entries 21, 22, and 23.

 

If I enter the new entries one column at a time, it'll look like this in an output

 

Column1 Column2

entry11

entry12

            entry21

            entry22

            entry23

entry13

 

If I input the entries in that order.

 

If I insert 2 entries at a time, and leave the second column blank, it'll look like this.

 

Column1 Column2

entry11  entry21

entry12 entry22

entry13 entry23

 

Which is how I want it to look, but I don't want to have to have forms with input for EVERY single column each time, I'd like to work with one column at a time, whether it is addin, deleting and editing entries.

 

 

[Starfox]

lol that was a while ago and I figured that out. I guess we all are noobs when we start eh?

 

Basically what I was doing, was entering data in different fields expecting them to be for the same "row" . Solution to that was of course to use 1 column entry to create the row, and then use UPDATE instead of INSERT for the other fields. Also, using primary key with auto increment helped.

 

Anyways, onto new challenges.

 

Currently I have a working Admin CP that has one main access table, with 8 access levels, that I have numbered 1 to 8, and each access has a name etc.

 

Except, now I want to create other access for other groups, but I don't want to create a new table each time. I'm trying to figure out how to put different access types into the same table.

 

Maybe if I give you some info on the context, that might help. The current CP and access levels are for the staff of one gaming club. This gaming club has 1 roster, 8 access levels in one table, and sub-groups. Now, I'd like to create new access levels for other clubs, using the same structure, i.e the same 8 levels. I could easily do it by adding a new access table each time, and adding a new field to the users table. But I feel this is a bit of a waste of resources. I'd prefer if I could have it all in one table. One way I could do this is add another field with the clubid for each table I currently have for the current club. But then, this doesnt seem to work, cause it would not allow people to be in multi-ple clubs unless they register again. I'd prefer if I'd only have one account for each user.

 

Any ideas? Thanx, and feel free to ask more questions if what I said is not very clear.

 

Thanx

 

StarFox