PHP - Retrieving Database Info Based On User Logged In

I have two tables.

Table Name:Users

Fields:

User_name
user_email
user_level
pwd


2.Reference

Fields:

refid
username
origin
destination

user_name in the users table and the username field in reference fields are common fields.



There is user order form.whenever an user places an order, refid field in reference table will be updated.So the user will be
provided with an refid

Steps:

1.User needs to log in with a valid user id and pwd
2.Once logged in, there will be search, where the user will input the refid which has been provided to him during the time of order placement.
3.Now User is able to view all the details for any refid
3.Up to this we have completed.

Query:

Now we need to retrieve the details based on the user logged in.

For eg: user 'USER A' has been provided with the referenceid '1234' during the time of order placement
        user 'USER B' has been provided with the referenceid '2468' during the time of order placement

When the userA login and enter the refid as '2468' he should not get any details.He should get details only for the reference ids which is assigned to him.

Hi - how's it going? I've been working on something with no luck and I'm wondering if you have any ideas. I have two tables; Members and Events. When a member logs in they are redirected to a page where I want them to be able to see a list of events that they have created based on a user ID that is common in both tables. So if the member has a memberID of 123 I want them to see any events that have the userID of 123. I used two different column names just for clarity. I've tried a number of variations on the following code:

<?php
session_start();
   header("Cache-control: private");
 
 
// Connect to Database
  include ('includes/db.php'); //Has login info
 
// If not logged in
  if (!$_SESSION['pkMemberID']) {
   
    echo ("<div class='box'><h2>Sorry, you are not logged in!</h2>");
    exit();
  }
     
// Convert Session variable 'pkMemberID' to simple variable.
$memberID = $_SESSION['memberID'];
$pkMemberID = $_SESSION['pkMemberID'];
$name = $_SESSION['name'];
   
 //Query Events
  $sql = "SELECT * FROM events WHERE userID = $memberID";
  $result = mysql_query($sql);
  $num_rows = mysql_num_rows($result);
 
  echo ("<div>Events:<br />");
  // Event exists
  if (!($num_rows == 0)) {
   $myrow = mysql_fetch_array($result);
   do {
     printf ('<span>&ndash; <a href="event_edit.php?pkEventID=%s">%s</a></span><br />', $myrow['pkEventID'], $myrow['eventName']);
   }
   while ($myrow = mysql_fetch_array($result));
 }
   echo ("</div>"); //End Events
      //end page
 
?>

Sometimes I get errors, sometimes just nothing. Any advice would be appreciated.

Hi, All:

I'm trying to figure out what way it's the best way to pull info belonging to a specific user based on whether he's a logged-in "member", and want to make sure he's not able to access any other member's details... would the best way be to try to match the user's "username" stored in a $_SESSION when fetching his info, something like this:

Code: [Select]
<?php
// Assume the login combo is this:
$username = $_POST['username'];
$password = $_POST['password'];
// Assume he has already logged in:
$_SESSION['username'] = $username;

//EXAMPLE 1: SELECTING user info simply from actual DB username/password match:
if ($_SESSION['username'])
{
$userRecords  = mysql_query("SELECT * FROM users WHERE username = '$username'  AND password = '$password'");
$userInfo = mysql_fetch_array($userRecords);
echo $userInfo['id'] . $userInfo['username'] . $userInfo['first-name'] . $userInfo['last-name'] . $userInfo['date-register'];
}


// EXAMPLE 2: or  SELECTING user info based on $_SESSION['username'] value: 
if ($_SESSION['username'])
{
$userRecords  = mysql_query("SELECT * FROM users WHERE username = . $_SESSION['username'] .  AND password = '$password'");
$userInfo = mysql_fetch_array($userRecords);
echo $userInfo['id'] . $userInfo['username'] . $userInfo['first-name'] . $userInfo['last-name'] . $userInfo['date-register'];
}

?>

So, my question is, are this actually working differently? is one better than the other as far as security, preventing other users from hacking either on purpose or accidenally into other user's details?

thank! Appreciate any feedback...

Im trying to create a website where users login, and then when they add a new entry to the database there name is put as the author. This is how my tables are set up. One table is named job and has the columns id, jobtext, jobdate, and authorid. Another table is called author. This table contains the columns id, username, password, and name. Authorid from the job table matches with id from the author table. When a user logins in this code is used to register the name...session_start();
$_SESSION['myusername'] = $_POST['myusername'];
$_SESSION['mypassword'] = $_POST['mypassword'];
header("location: index.php");
}
else {
echo "Wrong Username or Password";
}

This is the form users use to add a new entry...
if (isset($_GET['add']))
{
$pagetitle = 'New Job';
$action = 'addform';
$text = '';
$authorid = '';
$id = '';
$button = 'Add job';

include $_SERVER['DOCUMENT_ROOT'] . '/jobs/includes/db.inc.php';

// Build the list of authors
$sql = "SELECT id, name FROM author";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error fetching list of authors.';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$authors[] = array('id' => $row['id'], 'name' => $row['name']);
}

// Build the list of categories
$sql = "SELECT id, name FROM category";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error fetching list of categories.';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$categories[] = array(
'id' => $row['id'],
'name' => $row['name'],
'selected' => FALSE);
}

include 'form.html.php';
exit();
}

if (isset($_GET['addform']))
{
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

$text = mysqli_real_escape_string($link, $_POST['text']);
$author = mysqli_real_escape_string($link, $_POST['author']);

if ($author == '')
{
$error = 'You must choose an author for this job.
Click &lsquo;back&rsquo; and try again.';
include 'error.html.php';
exit();
}

$sql = "INSERT INTO job SET
jobtext='$text',
jobdate=CURDATE(),
authorid='$author'";
if (!mysqli_query($link, $sql))
{
$error = 'Error adding submitted job.';
include 'error.html.php';
exit();
}

$jobid = mysqli_insert_id($link);

if (isset($_POST['categories']))
{
foreach ($_POST['categories'] as $category)
{
$categoryid = mysqli_real_escape_string($link, $category);
$sql = "INSERT INTO jobcategory SET
jobid='$jobid',
categoryid='$categoryid'";
if (!mysqli_query($link, $sql))
{
$error = 'Error inserting job into selected category.';
include 'error.html.php';
exit();
}
}
}

header('Location: .');
exit();
}


Form.html.php =
<?php include_once $_SERVER['DOCUMENT_ROOT'] .
'/includes/helpers.inc.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title><?php htmlout($pagetitle); ?></title>
<meta http-equiv="content-type"
content="text/html; charset=utf-8"/>
<style type="text/css">
textarea {
display: block;
width: 100%;
}
</style>
</head>
<body>
<?php session_start(); ?>

<h1><?php htmlout($pagetitle); ?></h1>
<form action="?<?php htmlout($action); ?>" method="post">
<div>
<label for="text">Type your job he </label>
<textarea id="text" name="text" rows="3" cols="40"><?php
htmlout($text); ?></textarea>
</div>
<div>
<label for="author">Author:</label>
<select name="author" id="author">
<option value="">Select one</option>
<?php foreach ($authors as $author):?>
<option value="<?php htmlout($author['id']); ?>"<?php
if ($author['id'] == $authorid)
echo ' selected="selected"';
?>><?php htmlout($author['name']); ?></option>
<?php endforeach; ?>
</select>
</div>
<fieldset>
<legend>Categories:</legend>
<?php foreach ($categories as $category): ?>
<div><label for="category<?php htmlout($category['id']);
?>"><input type="checkbox" name="categories[]"
id="category<?php htmlout($category['id']); ?>"
value="<?php htmlout($category['id']); ?>"<?php
if ($category['selected'])
{
echo ' checked="checked"';
}
?>/><?php htmlout($category['name']); ?></label></div>
<?php endforeach; ?>
</fieldset>
<div>
<input type="hidden" name="id" value="<?php
htmlout($id); ?>"/>
<input type="submit" value="<?php htmlout($button); ?>"/>
</div>
</form>
</body>
</html>


Right now, under authors, it displays all the authors in the database. I want it to just show/submit the authorid of the logged in user.

I have two tables.

Table Name:Users

Fields:

User_name
user_email
user_level
pwd


2.Reference

Fields:

refid
username
origin
destination

user_name in the users table and the username field in reference fields are common fields.



There is user order form.whenever an user places an order, refid field in reference table will be updated.So the user will be
provided with an refid

Steps:

1.User needs to log in with a valid user id and pwd
2.Once logged in, there will be search, where the user will input the refid which has been provided to him during the time of order placement.
3.Now User is able to view all the details for any refid
3.Up to this we have completed.

Query:

Now we need to retrieve the details based on the user logged in.

For eg: user 'USER A' has been provided with the referenceid '1234' during the time of order placement
user 'USER B' has been provided with the referenceid '2468' during the time of order placement

When the userA login and enter the refid as '2468' he should not get any details.He should get details only for the reference ids which is assigned to him.



<?php


session_start();

if (!$_SESSION["user_name"])


      

{
        // User not logged in, redirect to login page
        Header("Location: login.php");
     }
$con = mysql_connect('localhost','root','');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("login", $con);
$user_name = $_POST['user_name'];
$refid = $_POST['refid'];
$query = "SELECT * from reference,users WHERE reference.username=users.user_name AND reference.refid='$refid' AND ";

$result = mysql_query($query) or trigger_error('MySQL encountered a problem<br />Error: '  . mysql_error() . '<br />Query: ' . $query);

 while($row = mysql_fetch_array($result))
  {
  echo $row['refid'];
  echo $row['origin'];
  echo $row['dest'];
  echo $row['date'];
  echo $row['exdate'];
  echo $row['username'];
  }
echo "<p><a href=\"logout.php\">Click here to logout!</a></p>";
?>

 <html>
<form method="post" action="final.php">
Ref Id:<input type="text" name="refid">
<input type="submit" value="submit" name="submit">
</html>