PHP Update help!

cadac

I am still quite new to php and mysql but i've managed to setup a login system, where a member can register and login to our website. Once they have logged in they can view the secure section of the site and also view their profile. This profile stores the information that relates to the database, ie personal details etc.

I am able to retrieve the data using php and display it in a form using the echo commands but I want my members to be able to update their information if necessary.

I have tried many solutions to no avail. I have started an update script in which once the form is submitted the data is stored successfully into the database but i don't know how to retrieve that data and display it on the profile page again where they submitted their new information. Hope this all makes sense.

Here is my php code:

<?php


// connect to your MySQL database here 
require_once "config2.php"; 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

// the values you want to change to 
$id = "1"; 
   $fname = $_POST['firstname'];
   $lname = $_POST['lastname'];
   $address = $_POST['address'];
   $address2 = $_POST['address2'];
   $county = $_POST['county'];
   $postcode = $_POST['postcode'];
   $email = $_POST['email'];
   $telephone = $POST['telephone'];
   $login = $_POST['login'];

// Build sql command to update just one record or "row" 
$sqlCommand = "UPDATE members SET firstname='$fname', lastname='$lname', address='$address', address2='$address2', county='$county', postcode='$postcode', telephone='$telephone', email='$email', login='$login' WHERE member_id='$id' LIMIT 1";

// Execute the query here now 
$query = mysql_query($sqlCommand) or die (mysql_error()); 

// Replace sql command to Select the data now 
$sqlCommand = "SELECT firstname, lastname, address, address2, county, postcode, email, telephone, login FROM members WHERE member_id='$id' LIMIT 1";
$query = mysql_query($sqlCommand) or die (mysql_error()); 


??


?>

Ideally, i'd like for them to update their details on the myprofile.php page without going to another page if that is possible?

Thanks

djjjozsi

on a success login, you can save the member table's primary number in a session variable. (lets say: $_SESSION["memberid"] )
You can retrieve the member details based on this variable.

/* Here goes something, explained later*/


$sql="select * from members WHERE memberid=". $_SESSION["memberid"] ;
$res=mysql_qeury($sql) or die( mysql_error() . " in the query:" . $sql );
$row=mysql_fetch_assoc($res);
// then write a form to update, and fill input fields with user details.
// broke out from the PHP block to able to write clean HTML codes!
?>

Create a form and fill with the user's details.

<form method="post" action="profile.php">
<input type="text" name="phone" value="<?php echo $row["phone"]?>">
...other input fields should go here.
...

On the very beggining of your profile.php, you can make an SQL update based on the new values from your form.
(/ Here goes something, explained later/)

If you have problem how to create an update SQL statement feel free to ask..😃

cadac

Thanks for the reply, I see what you mean.

Here is the scenario.

User visits my site: www.cadacagility.co.uk
Registers or Logs in using Members Login hyperlink
I have scripts to check to see if they are registered.
Once logged in, their session is stored and they have access to index.php and myprofile.php

In myprofile.php there is required php script, auth.php that checks member like you said, i think.

auth.php

<?php
	//Start session
	session_start();


//Check whether the session variable SESS_MEMBER_ID is present or not
if(!isset($_SESSION['SESS_MEMBER_ID']) || (trim($_SESSION['SESS_MEMBER_ID']) == '')) {
	header("location: access-denied.php");
	exit();
}
?>

myprofile.php

<?php
	require_once('includes/auth.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
	<meta http-equiv="Content-type" content="text/html; charset=UTF-8" />
	<title>Caerphilly Agility Club - Members - Home</title>
	<meta http-equiv="Content-Language" content="en-us" />
	<meta http-equiv="imagetoolbar" content="no" />
	<meta name="MSSmartTagsPreventParsing" content="true" />

<meta name="description" content="Description" />
<meta name="keywords" content="Keywords" />

<meta name="author" content="Enlighten Designs" />
<link href="_css/main.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="page-container">
  <div id="logo"></div>
  <div id="main-nav">
    <div id="navbar">
      <ul>
        <li><a href="index.php" title="Home">Home</a></li>
        <li><a href="pages/points.php" title="Training Rota">Training Rota</a></li>
        <li><a href="pages/points.php" title="Club Points">Club Points</a></li>
        <li><a href="downloads/calendar/cadac_cal_11.pdf" title="Yearly Calendar" target="_blank">Yearly Calendar</a></li>
        |
      </ul>
    </div>
    <div id="mlogin">
      <ul>
   <li><a href="myprofile.php">My Profile</a></li>
   |
   <li><a href="logout.php">Logout</a></li>
      </ul>
    </div>
  </div>

  <div id="header">
</div>
  <div id="sidebar-a">
    <div class="padding">
      <p><a href="http://www.thekennelclub.org.uk/" target="_blank">The Kennel Club</a></p>
      <p><a href="http://www.agilityclub.org/" target="_blank">AgilityVoice Magazine</a></p>
      <p><a href="http://www.agilitynet.com" target="_blank">AgilityNet</a></p>
      <p><a href="http://agilityforum.agilityaddicts.net" target="_blank">Agility Addicts Agility Forum</a></p>
      <div class="widget" id="widget">
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <a href="http://www.facebook.com/groups/66212196944/" target="_blank"><img src="_images/logos/fbook.jpg" alt="Find us on Facebook" width="200" height="60" border="0" /></a></div>
    </div>
  </div>
  <div id="spacer">
</div>
<div id="content">
	<h1>My Profile	</h1>
	<h3>Updating your account is still unavailable.</h3>
	<h2>&nbsp;</h2>
	<form id="account" name="account" method="post" action="includes/update-exec.php">
	  <table width="500" border="0">
	    <tr>
	      <td colspan="2"><h2>Account Details: </h2></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td width="105">First Name: </td>
	      <td width="144"><label for="firstname"></label>
          <input name="firstname" type="text" id="firstname" value="<?php echo $_SESSION['SESS_FIRST_NAME'];?>" /></td>
	      <td width="200">&nbsp;</td>
	      <td width="13">&nbsp;</td>
	      <td width="16">&nbsp;</td>
        </tr>
	    <tr>
	      <td>Last Name: </td>
	      <td><input name="lastname" type="text" id="lastname" value="<?php echo $_SESSION['SESS_LAST_NAME'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Address</td>
	      <td><input name="address" type="text" id="address" value="<?php echo $_SESSION['SESS_ADDRESS'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>&nbsp;</td>
	      <td><input name="address2" type="text" id="address2" value="<?php echo $_SESSION['SESS_ADDRESS2'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>County</td>
	      <td><input name="county" type="text" id="county" value="<?php echo $_SESSION['SESS_COUNTY'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Postcode</td>
	      <td><input name="postcode" type="text" id="postcode" value="<?php echo $_SESSION['SESS_POSTCODE'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Telephone</td>
	      <td><input name="telephone" type="text" id="telephone" value="<?php echo $_SESSION['SESS_TELEPHONE'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Mobile</td>
	      <td><input name="mobile" type="text" id="mobile" value="<?php echo $_SESSION['SESS_MOBILE'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Email:</td>
	      <td><input name="email" type="text" id="email" value="<?php echo $_SESSION['SESS_EMAIL'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Username:</td>
	      <td><input name="login" type="text" id="login" value="<?php echo $_SESSION['SESS_LOGIN'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>Password: </td>
	      <td><input name="textfield5" type="password" disabled="disabled" id="textfield5" value="<?php echo $_SESSION['SESS_PASSWD'];?>" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
	    <tr>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
	      <td><input type="submit" name="button" id="button" value="Submit" /></td>
	      <td>&nbsp;</td>
	      <td>&nbsp;</td>
        </tr>
      </table>
	</form>
	<p>&nbsp;</p>
</div>
<div id="footer">
  <div id="altnav"> <a href="../index.php" title="Home">Home</a> - <a href="../about-us.php" title="About Us">About Us</a> - <a href="../latest-news.php" title="Latest News">Latest News</a> - <a href="../training.php" title="Training">Training</a> - <a href="../contact-us.php" title="Contact Us">Contact Us</a> - <a href="../links.php" title="Links">Links</a> </div>
  <a>Copyright © 2011 | Caerphilly Agility Club</a>
  <p> <a href="http://www.idesignsuk.com" target="_blank">Powered by iDesignsUK</a></p>
</div>
</div>
</body>
</html>

Their details are shown via echoing, inside a form. The submit button goes to update-exec.php

At the moment when I press submit, the code runs to a blank page but when I check the mysql database the new info is there. That's fine, just want to be able to have that information relayed back to the myprofile.php form.

Hope that makes sense?

cadac

My login script for your reference 😃

<?php
	//Start session
	session_start();

//Include database connection details
require_once('config.php');

//Array to store validation errors
$errmsg_arr = array();

//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
	$str = @trim($str);
	if(get_magic_quotes_gpc()) {
		$str = stripslashes($str);
	}
	return mysql_real_escape_string($str);
}

//Sanitize the POST values
$login = clean($_POST['login']);
$password = clean($_POST['password']);

//Input Validations
if($login == '') {
	$errmsg_arr[] = 'Login ID missing';
	$errflag = true;
}
if($password == '') {
	$errmsg_arr[] = 'Password missing';
	$errflag = true;
}

//If there are input validations, redirect back to the login form
if($errflag) {
	$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
	session_write_close();
	header("location: access-denied.php");
	exit();
}

//Create query
$qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
$result=mysql_query($qry);

//Check whether the query was successful or not
if($result) {
	if(mysql_num_rows($result) == 1) {
		//Login Successful
		session_regenerate_id();
		$member = mysql_fetch_assoc($result);
		$_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
		$_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
		$_SESSION['SESS_LAST_NAME'] = $member['lastname'];
		$_SESSION['SESS_ADDRESS'] = $member['address'];
		$_SESSION['SESS_ADDRESS2'] = $member['address2'];
		$_SESSION['SESS_COUNTY'] = $member['county'];
		$_SESSION['SESS_POSTCODE'] = $member['postcode'];
		$_SESSION['SESS_TELEPHONE'] = $member['telephone'];
		$_SESSION['SESS_MOBILE'] = $member['mobile'];
		$_SESSION['SESS_EMAIL'] = $member['email'];
		$_SESSION['SESS_LOGIN'] = $member['login'];
		$_SESSION['SESS_PASSWORD'] = $member['passwd'];
		session_write_close();
		header("location: ../index.php");
		exit();
	}else {
		//Login failed
		header("location: login-failed.php");
		exit();
	}
}else {
	die("Query failed");
}
?>

djjjozsi

its a bad technique to save user's whole details into $_SESSION variables, why do you store my details duplicated? If i want to change my details you need to change in two place (in DB and in Sesssion).

You have my memberID, update the members table on a profile update, and retrieve my details when you needed (on a payement ect, before you write my details into the profile form!...) You can make a query.

cadac

Thanks for the tip, how would I go about changing that using the current script below, which I've managed to get working. Update works on the database and the new data is showing on the form.

Any help improving this code would be awesome?

Thanks

<?php


// connect to your MySQL database here 
require_once "config.php"; 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

// the values you want to change to 
$id = "1"; 
	$fname = $_POST['firstname'];
	$lname = $_POST['lastname'];
	$address = $_POST['address'];
	$address2 = $_POST['address2'];
	$county = $_POST['county'];
	$postcode = $_POST['postcode'];
	$email = $_POST['email'];
	$telephone = $_POST['telephone'];
	$mobile = $_POST['mobile'];
	$login = $_POST['login'];
	$passwd = $_POST['passwd'];

// Build sql command to update just one record or "row" 
$sqlCommand = "UPDATE members SET firstname='$fname', lastname='$lname', address='$address', address2='$address2', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile',email='$email', login='$login' WHERE member_id='$id' LIMIT 1";
// Execute the query here now 
$query = mysql_query($sqlCommand) or die (mysql_error()); 
// Replace sql command to Select the data now 
$sqlCommand = "SELECT firstname, lastname, address, address2, county, postcode, email, telephone, mobile, login FROM members WHERE member_id='$id' LIMIT 1";

session_start();
session_unset();

//Create query
$qry="SELECT * FROM members WHERE member_id='1'";
$result=mysql_query($qry);

//Check whether the query was successful or not
if($result) {
	if(mysql_num_rows($result) == 1) {
		//Login Successful
		session_regenerate_id();
		$member = mysql_fetch_array($result);
		$_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
		$_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
		$_SESSION['SESS_LAST_NAME'] = $member['lastname'];
		$_SESSION['SESS_ADDRESS'] = $member['address'];
		$_SESSION['SESS_ADDRESS2'] = $member['address2'];
		$_SESSION['SESS_COUNTY'] = $member['county'];
		$_SESSION['SESS_POSTCODE'] = $member['postcode'];
		$_SESSION['SESS_TELEPHONE'] = $member['telephone'];
		$_SESSION['SESS_MOBILE'] = $member['mobile'];
		$_SESSION['SESS_EMAIL'] = $member['email'];
		$_SESSION['SESS_LOGIN'] = $member['login'];
		$_SESSION['SESS_PASSWORD'] = $member['passwd'];
		session_write_close();
		header("location: update-success.php");
		exit();
	}else {
		//Login failed
		header("location: update-failed.php");
		exit();
	}
}else {
	die("Query failed");
}
?>

cadac

Aha, i've just tried testing the code and I have one issue. Although the record updates to the database and the new info shows on the form, when I go to check the database, record 1 is replaced with that persons information not theirs?

Any solution to that one, I presume it's something to do with the below php code and my sessions?

johanafm

cadac;10989935 wrote:

$sqlCommand = "UPDATE members SET firstname='$fname' WHERE member_id='$id' LIMIT 1";

$query = mysql_query($sqlCommand) or die (mysql_error()); 

session_start();
session_unset();

Well, $id doesn't seem to be defined. You should have something like

session_start();
$id = $_SESSION['id'];
$sqlCommand = "UPDATE members SET firstname='$fname' WHERE member_id='$id' LIMIT 1";

djjjozsi

in your code $id should be $_SESSION['SESS_MEMBER_ID'] instead!

cadac

Thanks, I have tried that and the form didn't pick up the new data values when submitted.

Also checked on the $id = '1';

Looks like it's just getting the first row that equals to 1 in mysql and not the person who's logged in?

cadac

Not sure if this helps but I logged in as someone else, tried changing a value, submitted it and all their details are replaced by the member whose member_id=1

djjjozsi

clear that $id='1'; line, and replace with:

$id=$_SESSION['SESS_MEMBER_ID'] ;

cadac

No such luck, just returns results for member id 1

// the values you want to change to 
$id = $_SESSION['SESS_MEMBER_ID']; 
	$fname = $_POST['firstname'];
	$lname = $_POST['lastname'];
	$address = $_POST['address'];
	$address2 = $_POST['address2'];
	$county = $_POST['county'];
	$postcode = $_POST['postcode'];
	$email = $_POST['email'];
	$telephone = $_POST['telephone'];
	$mobile = $_POST['mobile'];
	$login = $_POST['login'];
	$passwd = $_POST['passwd'];

johanafm

Well, doing this means that you previsouly must have done

# at login
$id = somehow_get_id_from_db($username, $password);
# and assuming $id === false means failure
if (!$id)
{
	show_login_form_again('Incorrect username or password');
	exit;
}

session_start();
$SESSION['SESS_USER_ID'] = $id;

So, do you still have these two lines in your code

            $member = mysql_fetch_assoc($result);
            $_SESSION['SESS_MEMBER_ID'] = $member['member_id'];

or did you remove that as well?

cadac

I hadn't removed the second part but I did change the fetch_array to your suggestion, no luck

I presume the first piece of code goes before the session part, not sure but my user would already be logged in as they are stored in a session and wouldn't need to check to see if they are still logged in? could be wrong mind.

<?php


// connect to your MySQL database here 
require_once "config.php"; 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

// the values you want to change to 
$id = $_SESSION['SESS_MEMBER_ID']; 
	$fname = $_POST['firstname'];
	$lname = $_POST['lastname'];
	$address = $_POST['address'];
	$address2 = $_POST['address2'];
	$county = $_POST['county'];
	$postcode = $_POST['postcode'];
	$email = $_POST['email'];
	$telephone = $_POST['telephone'];
	$mobile = $_POST['mobile'];
	$login = $_POST['login'];
	$passwd = $_POST['passwd'];

// Build sql command to update just one record or "row" 
$sqlCommand = "UPDATE members SET firstname='$fname', lastname='$lname', address='$address', address2='$address2', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile',email='$email', login='$login' WHERE member_id='$id' LIMIT 1";

// Execute the query here now 
$query = mysql_query($sqlCommand) or die (mysql_error()); 

// Replace sql command to Select the data now 
$sqlCommand = "SELECT firstname, lastname, address, address2, county, postcode, email, telephone, mobile, login FROM members WHERE member_id='$id' LIMIT 1";

session_start();
$SESSION['SESS_MEMBER_ID'] = $id; 
session_unset();

//Create query
$qry="SELECT * FROM members WHERE member_id='1'";
$result=mysql_query($qry);

//Check whether the query was successful or not
if($result) {
	if(mysql_num_rows($result) == 1) {
		//Login Successful
		session_regenerate_id();
		$member = mysql_fetch_assoc($result);
                    $_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
		$_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
		$_SESSION['SESS_LAST_NAME'] = $member['lastname'];
		$_SESSION['SESS_ADDRESS'] = $member['address'];
		$_SESSION['SESS_ADDRESS2'] = $member['address2'];
		$_SESSION['SESS_COUNTY'] = $member['county'];
		$_SESSION['SESS_POSTCODE'] = $member['postcode'];
		$_SESSION['SESS_TELEPHONE'] = $member['telephone'];
		$_SESSION['SESS_MOBILE'] = $member['mobile'];
		$_SESSION['SESS_EMAIL'] = $member['email'];
		$_SESSION['SESS_LOGIN'] = $member['login'];
		$_SESSION['SESS_PASSWORD'] = $member['passwd'];
		session_write_close();
		header("location: ../myprofile.php");
		exit();
	}else {
		//Login failed
		header("location: update-failed.php");
		exit();
	}
}else {
	die("Query failed");
}
?>

djjjozsi

	//Create query
	$qry="SELECT * FROM members WHERE member_id='1'";
	$result=mysql_query($qry);

why do you want to hardcode member_id='1' in your code😕

session_start( ) should be the very first of your file.

Now you'r doing this:
You did not start session, $id will be empty!
On a profile page load you save EMPTY values into the members table (WHERE member_id=0). On a page load do you think $_POST has passed values? i Don't think so.
In your further program you have selected a record based on the empty $id from members...

You should buy a PHP book and learn about IF conditions and SESSIONS 🙂

cadac

I might take your advice on that one 😉

Thanks anyway for your time, much appreciate it..

djjjozsi

cadac;10989959 wrote:
I might take your advice on that one 😉

Thanks anyway for your time, much appreciate it..

you can read about sessions: sessions

Here is a good tutorial about IF condition

And try to find the isset() and empty() functions.

Using the knowledge from these tutorial, you could check the user login state.