fetch data from msql based on logon

[dschuett]

Hi all,

This may be asking a bit much, but this is my FIRST advanced PHP/MySQL project. Any help is appreciated.

I am currently building a website for one of my friends photography business, and she has requested to allow her clients to be able to sign with a username and password to view thier proofs - and then expiring the pictures/account after 30 days.

I know I could make separate pages for each user, but that cold turn out to be A LOT of work. I was just wondering if anyone knew of a good/fairly simple way to go about conquering this? Would I need to use mysql? I would like to use simple viewer to allow users to view their own pics like here for example: http://simpleviewer.net/simpleviewer/examples/modern/ Just not sure how I would make it show THEIR pictures after they logged in with a username and password?

Here is a site that is using the logon and expire method already: https://www.imagequix.com/vando/3/ any ideas how they do this!?

Thanks for your time!

[ixalmida]

How is the user account created?

Assuming the user account is set up before the pictures are uploaded, it should be an easy thing to assign the user's ID to the picture set and automatically expire each picture after 30 days. You'd only need to add the columns "user_id" and "expiration_date" to the pictures table.

The process would be:
1) Input the client data (including login)
2) Upload all pictures, automatically attaching the client's user ID

Setting up the user account after the pictures are uploaded is only slightly more complicated:
1) Upload all pictures with an arbitrary grouping ID
2) Input the client data (including login)
3) Browse or search for the correct picture grouping and assign to the client's user ID

The query is pretty simple:
SELECT picture WHERE user_id = $user_id AND expiration_date > CURDATE()

[dschuett]

ixalmida;10964711 wrote:

How is the user account created?

Assuming the user account is set up before the pictures are uploaded, it should be an easy thing to assign the user's ID to the picture set and automatically expire each picture after 30 days. You'd only need to add the columns "user_id" and "expiration_date" to the pictures table.

The process would be:
1) Input the client data (including login)
2) Upload all pictures, automatically attaching the client's user ID

Setting up the user account after the pictures are uploaded is only slightly more complicated:
1) Upload all pictures with an arbitrary grouping ID
2) Input the client data (including login)
3) Browse or search for the correct picture grouping and assign to the client's user ID

The query is pretty simple:
SELECT picture WHERE user_id = $user_id AND expiration_date > CURDATE()

Ok, as far as the pictures in the database: would I physically store the picture in the database, or would I just store the LINK to the picture?

[ixalmida]

You can store a picture in most databases as a binary value, but according to most articles I've read, storing binary data in a database is not very efficient. I would just store the file name/path and insert it into your HTML where required.

One concern people have about storing files is the possibility of a user getting to files they aren't meant to access. There are ways around that, however. For example, you may want to store files below the root of the website and move them to a temporary folder for viewing.

For user-initiated file uploads/downloads, I store files with arbitrary file names then rename them back to the original file name during the download process. So even if a user could get to an uploaded file, it won't simply execute nor would they know which file was the uploaded file (without also hacking into the database somehow).

[dschuett]

ixalmida;10964737 wrote:

You can store a picture in most databases as a binary value, but according to most articles I've read, storing binary data in a database is not very efficient. I would just store the file name/path and insert it into your HTML where required.

One concern people have about storing files is the possibility of a user getting to files they aren't meant to access. There are ways around that, however. For example, you may want to store files below the root of the website and move them to a temporary folder for viewing.

For user-initiated file uploads/downloads, I store files with arbitrary file names then rename them back to the original file name during the download process. So even if a user could get to an uploaded file, it won't simply execute nor would they know which file was the uploaded file (without also hacking into the database somehow).

Sorry for all the questions, but I'm still in very much of a learning as I go stage. How do you pull files into a temporary folder for viewing? Also, since I want to use simple viewer for the users to view pictures with...how do you populate an xml file with the while loop of the users pictures? here is an example of how the pictures are stored for simple viewer:

<image>
	<filename>img1.jpg</filename>
	<caption>Caption 4</caption>	
</image>
<image>
	<filename>img2.jpg</filename>
	<caption>Caption 5</caption>	
</image>
<image>
	<filename>img3.jpg</filename>
	<caption>Caption 6</caption>	
</image>

This is a separate file. I just don't know how to populate it with php/mysql. Also, what if two people were viewing at the same time? Would the file be able to be over written?

Thanks so much for your help

[ixalmida]

How do you pull files into a temporary folder for viewing?

Just copy it:[code=php]$copy('/real/path/to/file/filename.php', '/srv/www/htdocs/temp_images/filename.php');[/code]

how do you populate an xml file with the while loop of the users pictures

Once the file is in the right place, you just need to output the path/filename:

$web_path_to_file = "http://www.domain.com/temp_images/";

// Query the available filenames for the current user...
$query = "SELECT filename, caption FROM Files
  WHERE user_id = $user_id AND expiration_date > CURDATE()";
mysql_query($query);

// Loop through filenames...
while($row = mysql_fetch_assoc($result))
  {
  $filename = $row['filename'];
  $caption = $row['caption'];
  echo "
    <image>
      <filename>$web_path_to_file.$filename</filename>
      <caption>$caption</caption>
    </image>
    ";
  }

--------------------------------------

Would the file be able to be over written?

Assuming all files have unique names, no. The only gotcha is that you need to write a clean-up script to delete files from the temp folder.

Of course, if security isn't a major concern, forget the temp folder and you can dispense with the copying and deleting part. Most users aren't going to spend the time trying to browse around trying to view someone else's pictures. The only reason I mentioned the copying bit is because I know professional photographers store HUGE files, and you may only want the user to access a much smaller version (or maybe just a thumbnail version).

[dschuett]

Thanks so much for all the input. I have finally found some time to actually get something written up for this.

First, I have created the following table in my database:

create table users (id int NOT NULL auto_increment, firstname varchar(30) NOT NULL, lastname varchar(30) NOT NULL, email varchar(30) NOT NULL, username varchar(30) NOT NULL, password varchar(30) NOT NULL, picture varchar(50), date varchar(30) NOT NULL, PRIMARY KEY (id));

I have then successfully created a login script that uses sessions to know wether or not to allow users to the "member page". I have also greated a logout script that kills the session. Everything works as planned. Here is the code I used to conquer that:

index.htm

<html>

<form action='login.php' method='POST'>
    Username: <input type='text' name='username'><br>
    Password: <input type='password' name='password'><br>
    <input type='submit' value='Log In'>
</form>
</html>

login.php:

<?php

session_start();

$username = $_POST['username'];
$password = $_POST['password'];

if ($username&&$password)
{

$connect = mysql_connect("localhost","xxxx","xxxx") or die ("Unable to connect at this time. Please try again later.");

mysql_select_db("login2", $connect) or die ("Unable to connect to the photo database at this time. Please try again later.");


$query = mysql_query("SELECT * FROM users WHERE username='$username'");

$numrows = mysql_num_rows($query);


if ($numrows!=0)
{
	while ($row = mysql_fetch_assoc($query))
	{
		$dbusername=$row['username'];
		$dbpassword=$row['password'];
		$dbfirstname=$row['firstname'];
	}

	if ($username==$dbusername&&$password==$dbpassword)
	{

header('Location: http://mysite.example.com/member.php?id='.$_SESSION['firstname']=$dbfirstname);			

	}
	else
		echo "Incorrect password.";
}
else
		die("Email address does not exist.");

}
else
		die("Please enter your email address and password.");


?>

member.php:

<?php

session_start();

if ($_SESSION['firstname'])
{
echo "Welcome, ".$_SESSION['firstname']."!";?><br>

<?php
echo "<a href='logout.php'>Log Out</a>";
}
else
        die("You must log in to view this page. <a href='index.htm'>Click here</a> to log in.");
?>

logout.php:

<?php

session_start();

session_destroy();

echo "You have successfully been logged out. <a href='index.htm'>Click here</a> if you wish to log back in.";


?>

I have also created a register page that will allow the owner of the business to input users into the database. It also checks if the users already exists (but it is for some reason it is only working on checking if the email address already exists - when it should be checking the username as well. But it for some reason allows you to use the same username - NOT a huge deal though). here is the code I used for the register page:

register.php **NOTE this page will only be accessed by the owner of the company

<html>
<head>
   <script type="text/javascript">
   var RecaptchaOptions = {
      theme : 'clean'
   };
   </script>
</head>

<body>
      <?php
  require_once('recaptchalib.php');
  // you got this from the signup page
  $publickey = "xxxxxx";
  $privatekey = "xxxxx";
  $resp = recaptcha_check_answer($privatekey, $_SERVER["REMOTE_ADDR"], $_POST["recaptcha_challenge_field"], $_POST["recaptcha_response_field"]);

  require_once "formvalidator.php";

  $error_hash='no';

  $show_form=true;

  if(isset($_POST['Submit']))

  {

      $validator = new FormValidator();


      $validator->addValidation("Firstname","req","<B>Please enter a Firstname</B>");

      $validator->addValidation("Lastname","req","<B>Please enter a Lastname </B>");

      $validator->addValidation("Email","email","<B>The input for Email should be a valid Email address</B>");

      $validator->addValidation("Email","req","<B>Please enter an Email</B>");

  $validator->addValidation("Username","req","<B>Please enter a Username</B>");

      $validator->addValidation("Password","req","<B>Please enter a Password</B>");


      if($validator->ValidateForm())

      {



          $show_form=false;

      }

      else

      {

          echo "<font color='#CC0000'><B>Validation Errors:</B></font>";



          $error_hash = $validator->GetErrors();

          foreach($error_hash as $inpname => $inp_err)

          {

              echo "<p>$inpname : $inp_err</p>\n";
	$show_form=true;

          }        

      }

if($show_form===false)
              {
if (!$resp->is_valid && $show_form === false) {
              $message = '&nbsp; CAPTCHA - word verification was incorrect.<br /><br />';

	$show_form = true;
      	} else {

                  $con = mysql_connect("localhost","xxx","xxxx")or die ("damnit");

                  mysql_select_db("login2", $con);

$email = mysql_real_escape_string($_POST['Email']);

   $dupcheck = mysql_query("SELECT * FROM users WHERE (email = '$email') OR (username = '$username')",$con);

if (! mysql_num_rows ($dupcheck))
{


	//safe insert to prevent injection attacks
			$firstname = mysql_real_escape_string($_POST['Firstname']);
			$lastname = mysql_real_escape_string($_POST['Lastname']);
			$username = mysql_real_escape_string($_POST['Username']);
			$password = mysql_real_escape_string($_POST['Password']);
			//$email is defined above

                        $sql="INSERT INTO users (id,firstname,lastname,email,username, password,date)

                               VALUES ('','$firstname','$lastname','$email','$username','$password',NOW())";

		 mysql_query($sql,$con);


			echo "<font color='#003366'><b>User Was Successfully Created!</b></font><br /><br />
				Username:<b> $username</b> <br />
				Password: <b>$password</b>";

		//need to set up mail to client here to notify them that their proofs are available.

}
		else
		{
		$message='This email already exists in the database.<br /><br />';

	}

                  mysql_close($con);

      } 

 echo "<font color='#CC0000'><B>$message</B></font>";
}
}
if (true == $show_form){                   

?>



    <form action="" method="POST" name="contactForm" accept-charset='UTF-8'>

  <table cellspacing='0' cellpadding='10' border='0' bordercolor='#000000' bgcolor='#C0C0C0'>



               <tr>        

                   <td colspan="3">&nbsp;</td>

              </tr>

              <tr>

                 <td align='left'>Firstname:</td>

                 <td>

                    <input type='text' name='Firstname' size='20' value="<?php if(isset($_POST['Firstname'])){ print $_POST['Firstname']; } ?>">

                 </td>

              </tr>

              <tr>

              <td align='left'>Lastname:</td>

                 <td>

                    <input type='text' name='Lastname' size='20' value="<?php if(isset($_POST['Lastname'])){ print $_POST['Lastname']; } ?>">

                 </td>

              </tr>

              <tr>

                 <td align='left'>Email:</td>

                 <td>

                    <input type='text' name='Email' size='20' value="<?php if(isset($_POST['Email'])){ print $_POST['Email']; } ?>">

                 </td>


	</tr>
              <tr>

                 <td align='left'>Username:</td>

                 <td>

                    <input type='text' name='Username' size='20' value="<?php if(isset($_POST['Username'])){ print $_POST['Username']; } ?>">

                 </td>


            </tr>

	<tr>        

             <td align='left'>Password:</td>

                 <td>

                    <input type='password' name='Password' size='20' value="<?php if(isset($_POST['Password'])){ print $_POST['Password']; } ?>">

                 </td>
	<td colspan="3">&nbsp;</td>

              </tr>

	   <tr>
                 <td><p><?php echo recaptcha_get_html($publickey);?></p></td>
	  </tr>
	  <tr>	
              <td colspan="2">
		<input type='submit' name='Submit' value='Submit'>&nbsp;&nbsp;<input type="reset" value="Reset">
		</td
                 </tr>


           </table>
  </form>


</table>
<?php
      }//true == $show_form
?>

Now that I have got all that working, I am STILL very confused on how to get the pictures to link to each user.... I thought maybe since you can actually see how I started this that you may have a better understanding of how I need this to work. I guess I just don't know exactly how to get the path of the pictures into the table, and then how to name them something that will relate to the user? I think I understand that I would have to upload the images first, then in my registration form i would have to change my insert somehow. Or do i need to make a completely seperate table for the pictures ( but i will admit that i have never used more than one table before, so i'm not sure how to connect or relate between the two.

Sorry for throwing so much information out there at you. I hope you can help!

thanks again!