Xampp - Database restrictions

[alexandra12]

Hello,

I have a small bit of Xampp experience. I have created a database with a number of tables. I need to create some conditions on some of the fields within the tables. For example -
table "USERS" {user1,user2}
table "USER TYPES" {admin,customer}
table "RESOURCE" {kettle,iron}

Only certain users are allowed to borrow a resource. I believe this should be done via a database schema or DTD, but I dont know how to do this with Xampp and mysql

Can anyone guide me on how I put in the restrictions ?


Thanks in advance

[anton_k]

Really nice to meet u alexandra12.

So u want to do the restrictions on borrowing some items with Xampp and mysql ?

special steps are given below:
1. Just start your XAMPP Control Panel (make sure to install the XAMPP first), Click the button "start" on apache and mysql to start their server respectively.
2. Open your favorite browser, type in the address bar: http://localhost/phpmyadmin (ok, actually you must do some configurations to make your XAMPP server "the localhost" be more secure(=just type "http://localhost/" in the address bar, if u're success installing the XAMPP then there'd be a message said "Congratulations about successfully installation XAMPP". We continue, on the left side frame you also would see the link "security". That's what i mean that every user who begins the first installation must accomplish the security section, at least on "the localhost" access & "the database,MySQL").
3. Have u typed "http://localhost/phpmyadmin" (if u make some security configurations above, u would be asked for the accounts u've set, accomplish it to enter the "New World of very user-friendly database web-based named phpmyadmin").
4. U must learn all the links in there (such as create database, make tables, running SQL Query=sql button).
5. Ok, u've got the database created, then your "USERS" table should consist of "id","username", "idTypes"(this "idTypes" should be "Foreign Key" and must be the same data type,int, because it is a part of the "USER_TYPES" table) fields. The "USER_TYPES" table should consist of "idTypes","nameofTypes" fields. Finally, the "RESOURCE" table should consist of "idSrc","nameofSrc" fields. U must see for the word "id" on each tables i type, u must notice here for the important case to add/treat them as a "Primary Key" and "int_DataType", "Auto-increment" respectively when the first u create the tables ok. Because this special key is the primary one we're going to use most on some "transaction" in the MySQL Database later.
6. Have u all set ? Now, straight to ur question; you must see or find the icon button to insert the value for the each tables u've created in order to make u understand more for the query/transaction within the MySQL database later. (e.g. "USERS" table, the value of inserting for the username field: user1,user2 just leave it the "id".).
7. Have u all set for the insertings? ok, now you should see the icon button for SQL to run the query u want plainly. Click it and copy paste the following syntax (in this case u must learn more about the MySQL query; http://dev.mysql.com/ for a reference). But before that here is a short review for the scheme of the database:

If u want only user type of "admin" can borrow all the resources (u must know in the "USER_TYPES" table, you've got the "idTypes" which should contain integer values 1, 2, so on, and followed by the "nameofTypes" fields which contain admin, customer, so on). So, the id 1 > admin, 2 > customer, so on. Also notice in the "USERS" table, u've got the "idTypes" field which refers to the "USER_TYPES" table (This is what we call the great feature of MySQL database "RELATIONSHIP"). So ur "USERS" table should contain like this:
plain-id: 1 user1 1 > for admin user-type
2 user2 2 > for customer user-type
so on..(or u can add to become plain-id 3 user3 1 > of course this is another admin user-type which explicitly refers to "USER_TYPES" table.)
8. That's a short brief, now we are leaving further on a jet plane . Just copy paste the following SQL syntax as i mentioned before:

Code: Select all

SELECT * FROM `USERS` WHERE `idTypes` = '1' (of course this is for admin user type)

You want to get all values for the "USER_TYPES" table:

Code: Select all

SELECT * FROM `USER_TYPES` ORDER BY `idTypes` asc

9. That's a short brief of plainly SQL syntax. Now our Jet Plane begins to run out of fuel. We must go even further from here.(to make the php code).
10. The PHP code (open notepad/text editor, save it as "connectionDB.php", "formIntro.php", "result.php" in the htdocs directory respectively (=make a certain folder)):
connectionDB.php:

Code: Select all

<?php
$conn=mysql_connect("localhost","root","thisisyourdbPassword"); //(this is your security configuration for phpmyadmin as first explanation)
if(!mysql_select_db("yourDataBaseName")) { die("Your Database doesn't exist"); exit(); }
?>


formIntro.php:

Code: Select all

<html><head><title>formIntro</title></head>
<body>
<form action="result.php" method="post" name="formIntro">
           Name: <input type="text" name="nameofuser" value="" /><br />
           <!--in this case, perhaps u want to automatically call all values from the "USER_TYPES" table to be embedded in the html tags you can do it with php--> 
            <select name="usertype">
           <?php include "connectionDB.php"; //include the connection to database here or you can place the code before <html> tag. (of course with php tag enclosed)
              $sql="SELECT * FROM `USER_TYPES` ORDER BY `idTypes` asc"; //remember this query?
              $query=mysql_query($sql); //now query it from the database for result syntax usage below
              while($row=mysql_fetch_array($query)) {
              echo "<option value=".$row['idTypes'].">".$row['nameofTypes']."</option>";
              }
           ?>             
            </select>
            <input type="submit" name="find" value="Find Resources">           
          </form>
</body></html>

result.php:

Code: Select all

<?php
include "connectionDB.php"; //as usual for a connection to database, you must use it
//Now get all values from the Form
$hishername=(string) $_POST['nameofuser']; //make sure it's just a string
$cleanname=strip_tags($hishername); //just for a cleaning, just do it ok. Because we're gonna use this section only (for hishername)
$hishertype=(int) $_POST['usertype']; //make sure it's just a numeric for an integer value

### NOW OUR JET PLANE WILL HAVE A CRASH LANDING SMOOTHLY, IT MEANS TO AN END ###
/// also now you want only a certain type of user to borrow the resources (e.g. admin type could borrow all resources, while customer just one resource/certain resource. Here u go:)

$sqlCheckUsertype="SELECT * FROM `USER_TYPES` WHERE `idTypes`='$hishertype'";
$query=mysql_query(sqlCheckUsertype);
list($idType,$nameofType)=mysql_fetch_array($query);
if($nameofType==="admin") {
/// here we check again for the second time whether he/she (the user) is really an admin (by his/her $idType)
$sqlCheckUsertype2="SELECT * FROM `USERS` WHERE `username`='$cleanname' and `idTypes`='$idType'";
$query=mysql_query(sqlCheckUsertype2);
if(mysql_num_rows($query)== 0) { echo "We are sorry, we don't allow you to borrow our certain resources"; exit(); } else {
echo "Welcome Admin, you can borrow all these resources:<br />";
/// Now we get the resources for admins
$sqlGetResources="SELECT * FROM `RESOURCE` ORDER BY `idSrc` ASC";
$query=mysql_query($sqlGetResources);
while($row=mysql_fetch_array($query)) {
              echo $row['nameofSrc']."<BR />";
              }
 } ///Now what about the user is "customer-type". You must learn it by yourself (or i would visit this forum again later, i got limited time now)
}
?>


Now, type in the address bar to run it: http://localhost/[yourFoldername]/formIntro.php.

Well, that's the end of all briefs, i hope u could learn & understand it.ok. I would drop by again.
And what about our jet plane ? Well finally we almost land on the most beautiful island.

[alexandra12]

Hello anton_k,


Your post has been extremley helpful and has clairified my question. Thank you very much for the detailed response.I can take away alot from that

Regards

Alexandra12