Jump to content
  • Advertisement
Sign in to follow this  
OBallard

How to add updates via mysql & user permissions

This topic is 2599 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Ok, so this is my original topic; here.
In this post here, SimonForsman suggested that I updated my updates page using a database so that I don't have to keep editing the original updates.html file.
Since then, I've added a login, registration, etc to the site from using a tutorial. Now, what I'm wondering is two things... How would I go about having different permissions for each user, what sql commands should I enter into the database and why - higher than your average members' level would have to be updated from a present administrator if I'm correct? And also, how would I go about making a new database for the updates? I've attempted searching and haven't been so lucky to find anything that could explain how it all works. - although saying that, I suppose it's almost like a blog feature? I'm sure I could find something along those lines..
Again, I'm sorry for the "nooby" questions, but I guess everybody had to learn somewhere.. :P
Thanks a lot. :)

Share this post


Link to post
Share on other sites
Advertisement

Ok, so this is my original topic; here.
In this post here, SimonForsman suggested that I updated my updates page using a database so that I don't have to keep editing the original updates.html file.
Since then, I've added a login, registration, etc to the site from using a tutorial. Now, what I'm wondering is two things... How would I go about having different permissions for each user, what sql commands should I enter into the database and why - higher than your average members' level would have to be updated from a present administrator if I'm correct? And also, how would I go about making a new database for the updates? I've attempted searching and haven't been so lucky to find anything that could explain how it all works. - although saying that, I suppose it's almost like a blog feature? I'm sure I could find something along those lines..
Again, I'm sorry for the "nooby" questions, but I guess everybody had to learn somewhere.. :P
Thanks a lot. :)


Starting with the updates system, The easy way to start is to ignore the login and input portions of it to start with, just change the update.html file to read the relevant information from the database, you can add new entries and update the database using the tools that come with your database (or third party tools if you prefer those) to start with.

First off, create a new table for the updates, based on the current data you have on your updates page you can get by with 3 columns (technically speaking you could use just 2, but i find having an identifier column to be a good idea in general).

Thus we start by creating the table: if you're using mysql you can do this with the text based mysql client by entering the command:
CREATE TABLE news (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, text TEXT);

you can then add a new entry to it using:
INSERT INTO news (date,text) VALUES(CURRENT_DATE(),"Adding some database voodoo");

(There are graphical clients that allow you to create tables and add new entries using a point and click interface aswell)

your new update page would then look like this (The change is basically that i removed your list of news with the php code (the "big" <?php .... ?> tag)

update.php

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<title>Development Updates</title>
<link rel="stylesheet" href="stylesheets.css">
</head>


<body>

<!-- Site Nav Menu -->
<ul class="navbar">
<b>Site navigation</b>
<li><a href="http://realitysleeps.com/index.html">Home Page</a>
</ul>

<!-- Useful Links -->
<ul class="navbar2">
<b>Useful Links</b>
<li><a href="http://makewebgames.com/">Make Web Games</a>
<li><a href="http://gamedev.net">Game Dev</a>
<li><a href="http://codingforums.com">Coding Forums</a>
</ul>


<!-- Main Content -->
<h1>Reality Sleeps Updates</h1>
<ul>
<?php
$sqlConnection = mysql_connect("localhost","usernameforyourdatabaseaccount","password"); //change those to match the required values for your database
mysql_select_db("yourdatabasename",$sqlConnection);

$result = mysql_query("SELECT * FROM news ORDER BY date DESC;",$sqlConnection);

while ($row = mysql_fetch_array($result)) {
echo "<li>".$row["date"]." - ".nl2br($row["text"])."</li>"; //the nl2br function replaces newlines in the database text entry with html linebreaks, you can skip that and store html directly if you prefer
}

mysql_free_result($result);
mysql_close($sqlConnection);
?>
</ul>
</body>
</html>


This basically fetches the necessary data from the database and you only need to make changes there to change the content of the site.

the insert query i showed above the code can be used to create new entries from php aswell, i'd leave that as an exercise for you, the key things you want to look at are:

1) html forms (input fields, buttons, etc)
2) The php $_POST variable (which you use to get the contents of the form into your php code).
3) the mysql functions (or equivalent if you're using a different database)
4) the INSERT and UPDATE commands (SQL).

To keep things simple you could just make a user id or username check for posting updates (If the username is admin or the userid 1 (or 0) you allow it, if not well.. you don't)
To add permissions to your login system you could basically add one permission column to the user database, personally i'd use an integer bitmask for the permission so that for example:

1: Allowed to post comments/etc (normal users got this unless you remove it)
2: Allowed to post updates/etc
4: Allowed to delete/modify useraccounts

a normal user would thus have a permission of 1
an admin would have 7 (1|2|4)

to check if a user has the appropriate permission to post updates you'd basically just do:
if ($user["permissions"]&2 != 0) {
//Allow it
} else {
//don't allow it
}
Its a good idea to make an array like

$permissions = Array(
"Comments"=>1,
"Updates"=>2,
"UserAccounts"=>4);
as it would allow you to do if($user["permissions"]&permissions["Updates"] != 0) instead which makes it a bit easier to read (and saves you the hassle of remembering exactly what each number stands for)


The reason this works is quite simple:
& is the bitwise and operator | is the bitwise or
if we use a 4 bit integer then
0000 = 0
0001 = 1
0010 = 2
0100 = 4
1000 = 8

bitwise and basically checks if a certain bit in 2 numbers is set in both of them so taking for example 11 (1011) and 3 (0011) gives us:

1011 &
0011
------
0011 (3) // The bits in the result is set if the same bits in both operands are set aswell.

if we use the or operator instead and the values 4 (0100) and 10 (1010) we get:
0100 |
1010
------
1110 (14) //the bits in the result is set if the same bits in either of the operands is set

(There is also a bitwise exclusive or operator ^ that sets the result bit if the bit in exactly one of the operands is set so 1010 ^ 1111 becomes 0101)

since all our permissions is represented by a single bit (1,2,4,8,16,32,64,etc) we can use the bitwise and operator to check if a specific bit is set and we can use the bitwise or operator to merge permissions.

This post is becoming rather long.

There are a few things in my example that you most likely want to change later on such as adding more fields for your updates (having a topic, a short description and a long text could be nice, then you can display the topic and short description in the list and when clicked on you display the full thing.

You probably also want to move things that are used in multiple places to separate files and include those using php (your menu for example looks like it will be duplicated on every page of your website, move it to a menu.php file and add <?php include "menu.php"?> at its place in all pages that use it.

There might be some errors in the posted code, i havn't tested it and i wrote it directly in the forums post editor.

Share this post


Link to post
Share on other sites
The php code isn't working, I've tried playing around with it a little but when it's uploaded, it just displays this: "; } mysql_free_result($result); mysql_close($sqlConnection); ?>
Also, I noticed that when you look at the source code, the username and password for the database is visible, will this still show with working code or is there something I could do to prevent it showing?

Share this post


Link to post
Share on other sites

The php code isn't working, I've tried playing around with it a little but when it's uploaded, it just displays this: "; } mysql_free_result($result); mysql_close($sqlConnection); ?>
Also, I noticed that when you look at the source code, the username and password for the database is visible, will this still show with working code or is there something I could do to prevent it showing?


If the actual php code is showing then your webserver isn't configured correctly(does it support php ?) or you forgot to rename your updates.html to updates.php. (if the file is named .html it will normally be treated as a html file and the entire content of it is sent to the webbrowser , if its named .php the webserver will run it through the php module first and replace the <?php .... ?> tag with whatever the php code prints using echo / printf, etc

Share this post


Link to post
Share on other sites
Christ, what an idiot.. I didnt change the file extension..
The page is now coming up with two errors but I'm looking into fixing them. :) Again, thanks for the help.. It's very much appreciated. :)

Share this post


Link to post
Share on other sites

Christ, what an idiot.. I didnt change the file extension..
The page is now coming up with two errors but I'm looking into fixing them. :) Again, thanks for the help.. It's very much appreciated. :)


If you have trouble with the errors you could post them here along with your version of the code (just make sure you strip out your username and password from the posted code)

Share this post


Link to post
Share on other sites
I fixed one of the errors but now this keeps coming up and I'm not sure how to fix it; Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home4/iroyleco/public_html/update.php on line 35

Line 35: while ($row = mysql_fetch_array($result)) {

Share this post


Link to post
Share on other sites

I fixed one of the errors but now this keeps coming up and I'm not sure how to fix it; Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home4/iroyleco/public_html/update.php on line 35

Line 35: while ($row = mysql_fetch_array($result)) {


That would normally be caused by $result = mysql_query(....) failing.

Try adding:

if ($result==FALSE) echo mysql_errno($sqlConnection) . ":" . mysql_error($sqlConnection) ."<br>";

just after the $result = .... line and see what it prints out (if anything)
you can add similar lines after each mysql command actually, that way it will print any SQL errors returned by the database itself
after $sqlConnection = mysql_connect(....) it would be
if ($sqlConnection == FALSE) echo mysql_errno($sqlConnection) . ":" . mysql_error($sqlConnection) ."<br>";

and so on. (basically most mysql_xxx functions will return false if there is a problem with the SQL syntax used or with the database itself)

The fetch array function however doesn't return false on errors, it returns false when there are no more entries left in the database so you don't want to add anything after that.

For a complete list of mysql functions for php you can look at : http://www.php.net/manual/en/ref.mysql.php
for mysql documentation you can look at http://dev.mysql.com/doc/refman/5.6/en/ (if you're using an older version click on the appropriate link to the left)

Share this post


Link to post
Share on other sites
1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESCENDING' at line 1
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home4/iroyleco/public_html/update.php on line 37

I had a feeling (after seeing that message) that it had something to do with the ID going 5, 1, 2, 3, 4 I've deleted the first entry (ID 5) but it doesn't seem to do anything. :/

Share this post


Link to post
Share on other sites

1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESCENDING' at line 1
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home4/iroyleco/public_html/update.php on line 37

I had a feeling (after seeing that message) that it had something to do with the ID going 5, 1, 2, 3, 4 I've deleted the first entry (ID 5) but it doesn't seem to do anything. :/


My bad, it should be:

$result = mysql_query("SELECT * FROM news ORDER BY date DESC;",$sqlConnection);
(the keyword for sorting in descending order is simply DESC, and queries should end with a ;)

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!