User management accounts - private page per user....?

[Skeleten Neteleks]

Hi, this is what I'm trying to do. I hope you can help :)

To provide user accounts whereby the usernames and passwords are set up by the webmaster, and the user simply gets to log in to their own private page.

I have so far found scripts which do all but provide a unique, protected html page per user. The scripts I've found allow many people to login to one page.

Each user needs to view their own assessment, not one html page which anyone can log into.

please direct me towards this type of scipt, i'm pulling my hair out!!

cheers

[Skeleten Neteleks]

P.S. I have PHP on my server with MySQL.

[roopurt18]

You typically don't want to write a unique page per user when creating a site, especially if you are expecting a large amount of users.  It is better to filter everyone through a single entry point and have them land in one, or at least one of very few pages.

It is the page that they land on that determines what user type they are (admin, moderator, regular, etc.) and displays the appropriate information.

Could you possibly give an example of the type of output  you're wanting to display on these unique user pages?

[Skeleten Neteleks]

I do not have an example but basically their page should contain details of services they have already purchased.

The details of each customer's purchases will be unique, in that not everyone will have purchased the same service(s).

hope that helps

[Skeleten Neteleks]

So their page should contain their company name, and a list/description of what they purchased.

[CravenBW]

Have you thought about storing all of that info on a mysql table. Display the same page to all users but the content will be dynamic based on what is in the mysql table. You could display all of their order history and a company profile that are both stored in a database but not necessarily the same table.

[Skeleten Neteleks]

Yes, but I haven't got a clue how to do that!

[roopurt18]

You'll want to look up any and all functions starting with mysql_ in the php manual then.

[Skeleten Neteleks]

I assume there's no pre-written script for this purpose?

[Skeleten Neteleks]

I'll keep looking but thanks anyway :)

[Skeleten Neteleks]

Ok, so I installed a script that protects pages. I can add users manually which is what I want. The proble is, I can log into any page with any username and password.

Customer 1 should only be able to sign into this page:
http://www.adkm.34sp.com/test/customer1.php

but all users can. the username and password is user1 and password1 (the same page can be accessed using user2 and password2).

I am not good enough yet to make my own sql table and implement anything, I have better luck making existing scripts work but I'm at a loss on this one.

[Orio]

You should add-

[code]<?php

// $user contains the username that was sent from the form when trying to log in
if ($user != basename($_SERVER['PHP_SELF'], ".php")) die("You can only enter your page!");

?>[/code]


Orio.

[roopurt18]

Are you running on some sort of a time line or is this a casual project you're working on?

If it's more casual I would instead start asking for advice on how to set up a MySQL database and the basics of table design.

It will make your life much, much easier in the long run.

[Skeleten Neteleks]

Cheers I will give Orio's suggestion a shot later. Not sure which file I should put the code in - every file I want to protect? I wish there was a training course in PHP/SQL rather than university or nothing...

[Skeleten Neteleks]

Ok, so I have made a database / table with the following code:

[code]
<?php
mysql_connect("adkm.34sp.com", "databaseusername", "databasepassword") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("databasename") or die(mysql_error());
echo "Connected to Database<br />";

mysql_query("CREATE TABLE customers(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
CompanyID VARCHAR(30),
Username VARCHAR(30),
Product1 VARCHAR(50),
Product2 VARCHAR(50),
Product3 VARCHAR(50),
Product4 VARCHAR(50),
Product5 VARCHAR(50),
Product6 VARCHAR(50),
Product7 VARCHAR(50),
Product8 VARCHAR(50),
Product9 VARCHAR(50),
Product10 VARCHAR(50),
Product11 VARCHAR(50),
Product12 VARCHAR(50),
Product13 VARCHAR(50),
Product14 VARCHAR(50),
Product15 VARCHAR(50),
Description1 VARCHAR(5000),
Description2 VARCHAR(5000),
Description3 VARCHAR(5000),
Description4 VARCHAR(5000),
Description5 VARCHAR(5000),
Description6 VARCHAR(5000),
Description7 VARCHAR(5000),
Description8 VARCHAR(5000),
Description9 VARCHAR(5000),
Description10 VARCHAR(5000),
Description11 VARCHAR(5000),
Description12 VARCHAR(5000),
Description13 VARCHAR(5000),
Description14 VARCHAR(5000),
Description15 VARCHAR(5000),
Password INT)")
or die(mysql_error()); 

echo "Table Created!";
?>
[/code]

Then made a file called databasecaller.php with the code below:

[code]<?php
// Make a MySQL Connection
mysql_connect("adkm.34sp.com", "databaseusername", "databasepassword") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM customers")
or die(mysql_error()); 

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo "Company: ".$row['CompanyID'];
echo " Username: ".$row['Username'];
echo " Product 1: ".$row['Product1'];
echo " Description: ".$row['Description1'];
echo " Password: ".$row['Password'];
?>[/code]

This produces the following page: http://www.adkm.34sp.com/databasecaller.php

The script seems to just display the contents of the first record in the table, ignoring the other records.

Will a login script do the job of displaying the record related to the visitor rather than simply the first record in the table?

[roopurt18]

Try a table set up like the following:
[size=10pt][pre]Users
+-----------+--------------------+
| id        | int                |
| username  | varchar(30)        |
| password  | int                |
+-----------+--------------------+

Products
+-------------+-------------------+
| id          | int               |
| name        | varchar(50)       |
| description | tinytext          |
+-------------+-------------------+

UserProductLinks
+--------------+---------------+
| user_id      | int           |
| product_id   | int           |
+--------------+---------------+
[/pre][/size]
Some queries...

[b]Get all users from the system:[/b]
[code]SELECT * FROM Users WHERE 1[/code]

[b]Get all products from the system:[/b]
[code]SELECT * FROM Products WHERE 1[/code]

[b]Get all products who have user links (Here we join the three tables):[/b]
[code]SELECT * FROM Users u, Products p, UserProductLinks l WHERE
             l.user_id = u.id AND
             l.product_id=p.id[/code]

This will limit the number of columns you have to put into your Users table.  You don't really want to put your product information into the User table.  The only stuff that should go into the User table is User information.  The only thing that should go into the Product table is product information.  The third table is so you can link the two tables together.  Note that this also prevents you from having the same product entered all over in your database.  With your current database design, imagine if you have 100 users with the product "Peanut Butter" but now you have to change it to "Peanut Butter Nutty."  Would you rather change it one time in one table or change it all over the place?

As for your specific problem, you need to loop the statement:
$row = mysql_fetch_array( $result );

Try this:
[code]<?php
// store the record of the "example" table into $row
while($row = mysql_fetch_array( $result )){
  // Print out the contents of the entry
  echo "Company: ".$row['CompanyID'];
  echo " Username: ".$row['Username'];
  echo " Product 1: ".$row['Product1'];
  echo " Description: ".$row['Description1'];
  echo " Password: ".$row['Password'];
}
?>[/code]

Also, while it doesn't matter [i]that[/i] much, I recommend making only a single echo statement for all of your pages.  So instead of
[code]<?php
echo $string1;
echo $string2;
echo $string3;
?>[/code]

Do this:
[code]<?php
$out = '';
// some code
$out .= $string1
      . $string2;
// more code
$out .= $string3;
$out .= SomeFunctionToCreateHTML();
// Now we output
echo $out;
?>[/code]

[Skeleten Neteleks]

[code]mysql_connect("host", "un", "pw") or die(mysql_error());
mysql_select_db("firequest") or die(mysql_error());

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM Users WHERE 1")
or die(mysql_error());

$result = mysql_query("SELECT * FROM Products WHERE 1")
or die(mysql_error());

$result = mysql_query("SELECT * FROM Users u, Products p, UserProductLinks l WHERE
            l.user_id = u.id AND
            l.product_id=p.id")
or die(mysql_error());

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo "Your user name is: ".$row['Username'];
echo "<p>Your Password: ".$row['Password'];
echo "<p>Product Name: ".$row['Name'];
echo "<p>Description: ".$row['Description'];
?>[/code]

I kept the echo stuff the same as I'm learning and don't want to confuse myself at this early stage. I like to keep it simple!

I made two user accounts and added two product descriptions in the tables (through my server's admin console).

The above code does not display anything other than the headings I entered ( http://www.adkm.34sp.com/test2/databasecaller.php ) - is this because the information is relying on a login script?

sorry to be so thick...

[roopurt18]

Glad to see you're working on the DB approach, the effort will pay off immensely!

Now, one thing you might want to add to your Products table is a uniqueness constraint on the product name.  This will prevent the same product from being entered multiple times.
[code]<?php
mysql_query("CREATE TABLE Products(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
Name VARCHAR(50),
Description TINYTEXT,  // Notice the added comma
UNIQUE(Name))")         // And here is the uniqueness constraint
or die(mysql_error());
?>[/code]

Now if you enter a product "Sam's Jelly" and later try to insert it again, the database will not insert the new record.  This is dependent on if you want to prevent duplicate rows.

You can do the same thing with the UserProductLinks table to ensure that each user is only associated with each unique product once.
[code]<?php
mysql_query("CREATE TABLE UserProductLinks(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
user_id INT,
product_id INT,                        // Note the added comma
UNIQUE(user_id, product_id))")   // The constraint, notice how we can enforce uniqueness across column combinations
or die(mysql_error());
?>[/code]

You could use a similar constraint on the username column of your user table.  The advantage to this approach is for tables that need to have unique records you no longer have to check if the record exists before inserting a new one as the constraint will prevent it from inserting duplicates in the first place.

Now, for your script that is pulling data from the database:
[code]<?php
<?php
// *** ATTENTION ***
// IN YOUR POSTED CODE YOU DISPLAYED YOUR HOST, USERNAME, AND PASSWORD
// EDIT THAT INFORMATION OUT ___NOW___
mysql_connect("host", "user", "password") or die(mysql_error());
mysql_select_db("table") or die(mysql_error());

/* I'm commenting out this entire block since it's unnecessary.  You only need to do this if
    you want to display all of the users in your system, which is not what we want.
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM Users WHERE 1")
or die(mysql_error());
*/

/* I'm commenting out this block for the same reason as the previous mysql_query call
$result = mysql_query("SELECT * FROM Products WHERE 1")
or die(mysql_error());
*/

$result = mysql_query("SELECT * FROM Users u, Products p, UserProductLinks l WHERE
             l.user_id = u.id AND
             l.product_id=p.id")
or die(mysql_error());

// YOU STILL NEED TO LOOP OVER THE $result
// store the record of the "example" table into $row
while($row = mysql_fetch_array( $result )){;
  echo "Your user name is: ".$row['Username'];
  echo "<p>Your Password: ".$row['Password'];
  echo "<p>Product Name: ".$row['Name'];
  echo "<p>Description: ".$row['Description'];
}
?>[/code]

That will display any users who are linked to products.  What you are probably missing is entries into your UserProductLinks table.

If you have 2 user accounts, their ids are probably 1 and 2.  Likewise for your 2 products.

Try inserting the following rows into UserProductLinks
[code]
+------------------+------------------+
|  user_id        |      product_id  |
+------------------+------------------+
| 1                |        1        |
| 1                |        2        |
| 2                |        1        |
| 2                |        2        |
+------------------+------------------+
[/code]

Try that and watch the results.  Then remove rows from the UserProductLinks table and watch what happens.

[Skeleten Neteleks]

http://www.adkm.34sp.com/testing123/databasecaller.php hooooooooooooooo! this place is great! now all i need to do is make a cookie based login script to display this page!

ahem...

*cough*

i will have a gander on the net, unless you want to teach me that too! cheers...

[Skeleten Neteleks]

Hmm, when I made links for my 3rd user to a different product, it appeared on the same page.

[code]<?php
mysql_connect("host", "un", "pw") or die(mysql_error());
mysql_select_db("firequest") or die(mysql_error());

/* I'm commenting out this entire block since it's unnecessary.  You only need to do this if
    you want to display all of the users in your system, which is not what we want.
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM Users WHERE 1")
or die(mysql_error());
*/

/* I'm commenting out this block for the same reason as the previous mysql_query call
$result = mysql_query("SELECT * FROM Products WHERE 1")
or die(mysql_error());
*/

$result = mysql_query("SELECT * FROM Users u, Products p, UserProductLinks l WHERE
            l.user_id = u.id AND
            l.product_id=p.id")
or die(mysql_error());

// YOU STILL NEED TO LOOP OVER THE $result
// store the record of the "example" table into $row
while($row = mysql_fetch_array( $result )){;
  echo "Your user name is: ".$row['Username'];
  echo "<p>Your Password: ".$row['Password'];
  echo "<p>Product Name: ".$row['Name'];
  echo "<p>Description: ".$row['Description'];
}
?>[/code]

I notice that in the PHP you originally gave me, it had two starting [code]<?php[/code] bits. Was that a mistake or should I have left them in? I'll check...

[Skeleten Neteleks]

No the extra [code]<?php[/code] gives a warning...

[roopurt18]

I would recomment using sessions for your login script unless your server, for whatever reason, doesn't support them.

Here's a few tips for using sessions.

The PHP manual:
http://www.php.net/session

You [b]must[/b] call [i]session_start()[/i] at the top of every single page [b]before[/b] you send any output (i.e. calling echo, print, etc.).

You store values in the autoglobal $_SESSION array.

Following is a sample index.php that has the outline for a login script which, on successful login, redirects to home.php

[b]index.php[/b]
[code]<?php
 // index.php
 // A sample PHP script to display a login form and validate user identity
 // Upon successful login, we redirect to our universal home.php
 // I will wrap up functionalities specific to your application in functions
 // which you can fill out for yourself

 session_start(); // The first think we must do is start our session

 $Out = ''; // This variable is going to hold our final output for the page
 $Errors = Array(); // This is our errors array for the form

 // Now we check in $_SESSION if the user is already logged in
 if(isset($_SESSION['LoggedIn']) && $_SESSION['LoggedIn'] === true){
   // This user is already logged in, we should just redirect to home.php
   header("Location: home.php");
   exit();
 }

 // We're going to count how many entries are in $_POST, if there are _any_
 // entries in $_POST, then our login form must have been submitted and we
 // need to validate the user
 if(count($_POST)){
   // Form has been submitted, we need to validate our user
   if(ValidateForm()){
     // Our user is a valid user, so let's log them in and redirect
     $_SESSION['LoggedIn'] = true;
     $_SESSION['UserClean'] = CleanFormField($_POST['User']);
     $_SESSION['User'] = $_POST['User'];
     // We have set our $_SESSION parameters, so now we can redirect
     header("Location: home.php");
     exit();
   }else{
     // Our form was submitted but it's invalid!  This means we need to
     // redisplay the form
     $Out .= ShowForm();
   }
 }else{
   // Form not submitted, we need to show it
   $Out .= ShowForm();
 }

 echo $Out; // Dump our output at the very end

 // ShowForm
 // RETURN: The html to display for the form
 function ShowForm(){
   $Form = ''; // Start with an empty variable

   // First we check for errors
   global $Errors;
   if(count($Errors)){
     // We have errors
     $Form .= 'The follow error(s) were encountered:'
            . '<ul><li>' . implode('</li><li>', $Errors) . '</li><ul>';
   }

   // Set up default values for our form, using the ones from the previous
   // submission if one was made
   $defUser = isset($_POST['User']) ? $_POST['User'] : NULL;

   // Now display the form - we use the post method so that we can use
   // the $_POST array above
   $Form .= '<form name="login" method="post" action="">'
          // Create the login field, using the default
          . 'Login: <input type="text" name="User" value="' . $defUser . '" />'
          . '&nbsp;'
          // Create the password field, never set a default password
          . 'Password: <input type="password" name="Password" value="" />'
          . '<input type="submit" name="login" value="Login" />'
          . '</form>';

   // Return our form
   return $Form;
 }

 // ValidateForm
 // This function validates the log in form
 // RETURN: true if form is valid, false otherwise
 function ValidateForm(){
   global $Errors; // We need access to our errors array
   $HadErrors = false; // We initially assume our form is valid

   // We are going to systematically check our field for good data
   // Any time we find bad data, we set $HadErrors to true and add an error
   // message to our $Errors array

   // First we check if the username is valid, the condition to do so varies
   // based on your application.  A valid username might be alphanumeric only,
   // or alpha only, and usually they have a length restriction.  It's a good
   // idea to test for that here
   // The value $user_name_is_invalid is a dummy to represent whatever check
   // you might actually make
   if(!$user_name_is_invalid){
     $HadErrors = true; // Not valid, so mark that we had errors
     $Errors[] = "Login name appears to be invalid.";
   }

   // Now we'll check that the user exists in our database, we make sure to
   // clean each of the form fields (User & Password)
   $Clean['User'] = CleanFormField($_POST['User']);
   $Clean['PW'] = CleanFormField($_POST['Password']);
   $sql = "SELECT COUNT(*) AS Num FROM UserTable WHERE "
        . "User=" . $Clean['User'] . " AND "
        . "Password=" . $Clean['PW'];
   $q = mysql_query($sql);
   $HaveUser = false; // Initially we have no user
   if($q){
     // Query successful, let's make sure we have a user
     while($row = mysql_fetch_array($q)){
       $HaveUser = $row['Num'] == 1; // Set $HaveUser to the result of the test
       break;
     }
   }
   // By now $HaveUser is true or false depending on if we have a user
   if(!$HaveUser){
     // We have no user
     $HadErrors = true; // Not valid, so mark that we had errors
     $Errors[] = "Your account could not be found.";
     // It is very important that when checking an account that you NEVER
     // tell the user which of the fields is correct or incorrect.
     // This makes it harder for an attacker to determine if the login
     // or password they are working with are correct or not
   }

   // Now we return the NOT of $HadErrors
   return !$HadErrors;
 }

 // CleanFormField
 // $fld - the input field to clean
 // RETURN: $fld cleaned for safe use
 function CleanFormField($fld){
   if(is_string($fld)){
     // $fld is a string so we must enclose in single quotes and escape
     // special characters
     $fld = "'" . addslashes($fld) . "'";
   }else if(!is_numeric($fld)){
     // We already knew it wasn't a string, but now we know it's not numeric
     // either, so trash it
     $fld = NULL;
   }
   return $fld;
 }
?>[/code]

[b]home.php[/b]
[code]<?php
 // home.php
 // This is our homepage for users

 session_start(); // The first think we must do is start our session

 $Out = ''; // This variable is going to hold our final output for the page

 // First check if we have a valid user
 if(!isset($_SESSION['LoggedIn']) || $_SESSION['LoggedIn'] !== true){
   // Invalid user is trying to hack our site!
   $Out .= 'You do not have permission to view this page.';
 }else{
   // User is valid - print welcome message
   $Out .= "Welcome, {$_SESSION['User']}!";

   // THIS IS WHERE YOU'D PULL MORE INFORMATION FROM THE DATABASE DEPENDING
   // ON WHICH USER HAS LOGGED IN AND DISPLAY IT TO THEM!
   $Out .= $more_info_from_database;

   // Let's also print out some debugging information
   $Out .= '<pre style="text-align: left;">' . print_r($_SESSION, true)
         . '</pre>';
 }

 echo $Out;
?>[/code]

[roopurt18]

Remove the extra <?php from whatever I gave you, that was an error.

That SQL query that links users to products is pulling [b]all[/b] of the possible links.

In the sample home.php I gave you, the line:
$Out .= $more_info_from_database;

Should be replaced with something like:
[code]
$sql = "SELECT * FROM Users u, Products p, UserProductLinks l WHERE "
     . "l.user_id = u.id AND "
     . "l.product_id=p.id AND "
     . "u.User={$_SESSION['UserClean']}"; // Notice the added condition here!
$result = mysql_query($sql) or die(mysql_error());

// YOU STILL NEED TO LOOP OVER THE $result
// store the record of the "example" table into $row
while($row = mysql_fetch_array( $result )){ // <- there was a semicolon there,
                                            // which can be removed.  I guess
                                            // I fat-fingered something :D
  $Out .= "<p>Product Name: " . $row['Name'];
        . "<p>Description: " . $row['Description'];
}
[/code]

[Skeleten Neteleks]

Ok, I got this far: http://www.adkm.34sp.com/test95/index.php - The script wouldn't work without my db info at the top of the index.php page (so I added it).

John123 and password 1111 doesn't log me in. These details match what is on my database....

[Skeleten Neteleks]

Am I missing something?